【Postgresql】环境部署初级之手工安装完整过程

最近开始接触PostgreSQL数据库，因为是首次使用，首先得给自己准备一个学习环境，于是从零开始，记录下完整操作过程以备后期使用。主要参考文档是官方安装手册：PostgreSQL: Linux downloads (Ubuntu)

演示环境

 开始之前先介绍我的演示环境配置：一台安装了Ubuntu20的虚拟机，内存4G,虚拟硬盘20G,已经配置外网的访问权限。一切准备就绪，下面就开始我的安装之旅。

安装数据库软件（root）

这次安装的是postgresql14

#创建文件存储库配置
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
#导入存储库签名密钥：
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
#更新包列表：
sudo apt-get update
#安装最新版本的PostgreSQL。
#如果需要特定版本，请使用“postgresql-12”或类似版本，而不是“postgresql”：
sudo apt-get -y install postgresql-14
 
## 等待安装过程....
## 安装结束，修改postgres密码
root@gant-ThinkPad-T430:/home/gant# passwd postgres

验证：切换到postgres用户

su postgres
postgres@gant-ThinkPad-T430:~$ psql --version
psql (PostgreSQL) 14.2 (Ubuntu 14.2-1.pgdg20.04+1)

出现版本号说明数据库软件安装成功。

数据库访问权限配置（postgres）

外网访问数据库的权限，修改/etc/postgresql/pg_hba.conf

# 编辑访问权限配置文件pg_hba.conf
postgres@gant-ThinkPad-T430:$ vi /etc/postgresql/14/main/pg_hba.conf
# add new
host    all             all             0.0.0.0/0               md5
# modify
# 把 local的认证方式改为trust

 md5表示密码加密方式，这个安全性也不高，目前实际生产环境推荐使用scram-sha-256

trust表示信任模式，无需密码，这个通常本地访问设置，生产环境也不推荐，可以使用密码文件

host    all             all             0.0.0.0/0表示放开所有外部网络对数据库的访问限制，默认postgresql数据库外网是访问不了的

数据库参数配置（postgres）

对数据库基本参数的一些配置修改，修改/etc/postgresql/14/main/postgresql.conf

除了：listen_addresses参数，其他都可选修改

#配置参数文件
postgres@gant-ThinkPad-T430:$ vi /etc/postgresql/14/main/postgresql.conf
#以下为主要的配置参数
data_directory = '/var/lib/postgresql/14/main'          
hba_file = '/etc/postgresql/14/main/pg_hba.conf'
listen_addresses = '*' # localhost
port = 5432
max_connections = 1000 # 100 #最大连接数
authentication_timeout = 5min           # 1min 认证超时时间
password_encryption = scram-sha-256  #口令加密方式
shared_buffers = 8GB  #128MB #最大数据库内存，推荐物理内存的20%
temp_buffers = 32MB  #8MB #临时段最大内存，用于临时表
work_mem = 16MB     #4MB #单个会话可用内存，实际会随会话数成倍增长

listen_addresses = '*' ：表示外网可以访问，这个参数必须和pg_hba.conf一起配置，否则外网还是无法访问数据库

重启数据库

完成上述配置修改后需要重启数据库才能生效，重启方式有如下两种：

方式1（postgres）

/etc/init.d/postgresql restart # 重启
#查看服务状态
postgres@gant-ThinkPad-T430:~$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Mon 2022-03-28 17:13:32 CST; 1min 1s ago
    Process: 1727701 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1727701 (code=exited, status=0/SUCCESS)

方式2（root）

systemctl restart postgresql # 重启
#查看服务状态
postgres@gant-ThinkPad-T430:~$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Mon 2022-03-28 17:13:32 CST; 1min 1s ago
    Process: 1727701 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1727701 (code=exited, status=0/SUCCESS)

 到这里我们就有了一个可用的postgresql14数据库，默认已经有一个postgres数据库，用户名postgres,在这个库上可以进行学习实践。那如果我们想建一个自己的数据库该怎么操作呢，这里简单介绍一下创建过程。

创建用户数据库（postgres）

创建自定义用户

postgres=# create user test1 password 'test1';
CREATE ROLE

#创建表空间关联目录(一般不推荐在安装目录里放数据文件)

postgres@gant-ThinkPad-T430:$ mkdir /etc/postgresql/14/data
postgres@gant-ThinkPad-T430:$ mkdir /etc/postgresql/14/data/CORETBS

#创建新表空间

postgres@gant-ThinkPad-T430:$ psql  -c "CREATE TABLESPACE CORETBS owner test1 LOCATION '/etc/postgresql/14/data/CORETBS';"

# 创建数据库

postgres@gant-ThinkPad-T430:~$ createdb -D CORETBS --owner=test1 --host=localhost --port=5432 --username=postgres --no-password

#登录验证
postgres@gant-ThinkPad-T430:~$ psql -U test1 -d CORETBS -c "select 1 as n;"
n
------------
           1

这样一个简单的用户自定义数据库就创建完成了

附：

自动安装脚本

安装插件Postgres Decoderbufs

这是一个解析数据库wal日志的插件，可用在数据同步等应用场景，通过解析后的日志在目标库上进行sql重放，实现数据同步。

# 安装依赖包
apt-get update && apt-get install -f -y software-properties-common build-essential pkg-config git postgresql-server-dev-14
apt-get install -f -y libproj-dev liblwgeom-dev
add-apt-repository "deb http://ftp.debian.org/debian testing main contrib" && apt-get update
# 这里可能遇到需要导入软件源的pubkey,执行如下导入语句
# apt-key adv --keyserver keyserver.ubuntu.com --recv-key <报错提示的key>
apt-get install -y libprotobuf-c-dev
apt-get install -y protobuf-c-compiler
apt install clang-9

 获取源码

git clone https://github.com/debezium/postgres-decoderbufs.git
cd postgres-decoderbufs

build及安装

export PATH=/usr/lib/postgresql/14/bin:$PATH
make
make install

参数文件设置 

vi /etc/postgresql/14/main/postgresql.conf

# MODULES,如果有多个插件只需逗号分隔
shared_preload_libraries = 'decoderbufs'

# REPLICATION
wal_level = logical
max_wal_senders = 8
#wal_keep_segments = 4 #14中已过时
max_replication_slots = 4

:wq

# MODULES
sed -i "s/shared_preload_libraries = 'decoderbufs'/shared_preload_libraries ='' 'pg_stat_statements,decoderbufs'/g" /etc/postgresql/14/main/postgresql.conf
sed -i "s/#wal_level = replica/wal_level = logical/g" /etc/postgresql/14/main/postgresql.conf
sed -i "s/#max_wal_senders = 10/max_wal_senders = 8/g" /etc/postgresql/14/main/postgresql.conf
sed -i "s/#max_replication_slots = 10/max_replication_slots = 4/g" /etc/postgresql/14/main/postgresql.conf

vi /etc/postgresql/14/main/pg_hba.conf

local   replication     <youruser>                          trust
host    replication     <youruser>  127.0.0.1/32            trust
host    replication     <youruser>  ::1/128                 trust

:wq

# 重启数据库
root $> systemctl restart postgresql
root $> systemctl status postgresql

验证 

su - postgres
pql
-- 创建一个复制槽
select * from pg_create_logical_replication_slot('decoderbufs_demo', 'decoderbufs');

-- 对表进行增删改的操作
create table t_test (id int,cname varchar(30));
insert into t_test values(1,'a');
insert into t_test values(2,'b');
update t_test set cname='aa' where id=1;
delete from t_test where id=2;
-- 用decoderbufs调试模式查看在SQL控制台上的WAL变更信息
select data from pg_logical_slot_peek_changes('decoderbufs_demo', NULL, NULL, 'debug-mode', '1');
-- 使用decoderbufs获取WAL changes变更
select data from pg_logical_slot_get_changes('decoderbufs_demo', NULL, NULL, 'debug-mode', '1');

-- 检查复制槽信息
select * from pg_replication_slots where slot_type = 'logical';