Postgresql14安装及主从配置

目录

版本环境

主数据库操作

安装服务

创建主从同步用户

pg_hba.conf配置

postgresql.conf配置

重启主库

从数据库操作

安装服务

postgresql.conf配置

创建 standby.signal

启动从库

验证主从同步

主库验证方式

从库验证

1. 版本环境

• 服务器系统: CentOS 7

• PostgreSQL 版本: 14.2

• 主数据库内网 IP : 172.16.98.200

• 从数据库内网 IP : 172.16.98.201

• 执行操作用户 : commonuser

• 服务安装目录 : /data/app/postgreSql

• 数据存放目录 : /data/appData/postgreSql

• 日志存放目录 : /data/logs/postgreSql

2. 主数据库操作

2.1 安装服务

如果postgresql已安装可跳过此小节步骤，直接根据 《2.2 创建主从同步用户》 小节进行

# 下载安装包
cd /opt
wget https://repo.huaweicloud.com/postgresql/source/v14.2/postgresql-14.2.tar.gz
# 解压postgresql包
tar -xf postgresql-14.2.tar.gz
# 安装依赖包
yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake

# 创建目录 
mkdir -pv /data/appData/postgreSql    # 数据目录 
mkdir -pv /data/logs/postgreSql       # 日志目录 
mkdir -pv /data/appData/postgreSql/pg_archive/xlog_files   # 归档目录，前提：archive_mode = on #允许归档

​
# 创建用户组commonuser并创建用户commonuser 
useradd commonuser

# 修改权限
chown -R commonuser.commonuser /data/app/postgreSql
chown -R commonuser.commonuser /data/appData/postgreSql
chown -R commonuser.commonuser /data/logs/postgreSql

# 编译安装
cd postgresql-14.2
./configure --prefix=/data/app/postgreSql
make && make install

# 切换用户到commonuser初始化数据
su - commonuser
cd /data/app/postgreSql
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/data/app/postgreSql/libhub"
/data/app/postgreSql/bin/initdb -D /data/appData/postgreSql --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8
cp -avf /data/app/postgreSql/postgresql.conf /data/appData/postgreSql/
echo "host    all             all             0.0.0.0/0               trust" >> /data/appData/postgreSql/pg_hba.conf
​
​
​
# 启动服务
/data/app/postgreSql/bin/pg_ctl start -D /data/appData/postgreSql/
# 登录pg
/data/app/postgreSql/bin/psql -h 127.0.0.1 -p 5432 -U postgres
​
# 创建用户
/data/app/postgreSql/bin/createuser -U commonuser -h127.0.0.1 -p5432 -s postgres
/data/app/postgreSql/bin/psql -d postgres -U commonuser -h127.0.0.1 -p5432 -c "create user synthetic_user with superuser password '123456';"

2.2 创建主从同步用户

# 如果已安装postgresql建议先进行备份操作，备份操作参考如下：

# 创建备份SQL文件存放目录
mkdir -p /data/appData/backup /data/app/backup /data/tmp
## 导出系统库
cd /data/appData/postgreSql/
/data/app/postgreSql/bin/pg_dumpall -U postgres -h 127.0.0.1 -p 5432 -f /data/appData/postgreSql/all_DB.sql
# 停止服务
bash /data/app/postgreSql/scripts/postgreSql stop
# 备份文件
cd /data/app
cp -arp /data/app/postgreSql  /data/app/backup/
cp -arp /data/appData/postgreSql  /data/appData/backup/
# 创建主从同步用户
create role repl login replication encrypted password '123456';

2.3 pg_hba.conf配置

# 添加从库网段
echo "host    replication     repl            从库ip/24        md5" >> /data/appData/postgreSql/pg_hba.conf

2.4 postgresql.conf配置

vim /data/appData/postgreSql/postgresql.conf
listen_addresses = '*'      # 监听所有ip
wal_level = hot_standby     # 热备模式
archive_mode = on           #允许归档
archive_command = 'cp %p /data/appData/postgreSql/pg_archive/xlog_files/%f' # 此目录需要创建
max_wal_senders = 32        #同步最大的进程数量
wal_sender_timeout = 60s    #流复制主机发送数据的超时时间
max_connections = 200       #最大连接数，从库的max_connections必须要大于主库的

2.5 重启主库

/data/app/postgreSql/bin/pg_ctl restart -D /data/appData/postgreSql/

3. 从数据库操作

3.1 安装服务

# 解压postgresql包
tar -xf postgreSql.tar.gz -C /data/app/
​
# 创建目录
mkdir -pv /data/appData/postgreSql
mkdir -pv /data/logs/postgreSql

注意从库无需上文主库安装流程中的初始化步骤，使用pg_basebackup命令从主库同步数据

# 从主库同步数据
/data/app/postgreSql/bin/pg_basebackup -h 主库ip -p 5432 -U repl -F p -P -D /data/appData/postgreSql
​
# 输入repl用户密码：123456

参数说明：

-h 指定连接的数据库的主机名或IP地址

-U 指定连接的用户名

-F 指定了输出的格式，支持p（原样输出）或者t（tar格式输出）。

-P 表示允许在备份过程中实时的打印备份的进度。

-D 指定把备份写到哪个目录，必须为空目录

3.2 postgresql.conf配置

从 PostgreSQL 12 开始已经移除了 recovery.conf 文件,相关配置合并到了 postgresql.conf 中,因为配置postgresql.conf是从主库同步过来的，这里需要修改一些配置，改为从库的配置。

vim /data/appData/postgreSql/postgresql.conf
## 移除或注释 wal_level，从库不需要这个配置
# wal_level = hot_standby
## 修改或添加以下
max_standby_streaming_delay=30s #可选，流复制最大延迟
wal_receiver_status_interval=10s #可选，向主库报告状态的最大间隔时间
hot_standby_feedback=on #可选，查询冲突时向主库反馈
max_connections=1000 #最大连接数一般大于主库就行
primary_conninfo = 'host=172.16.98.200 port=5432 user=repl password=123456'  # 表示主库连接信息
recovery_target_timeline = 'latest'     # 表示恢复最新的数据

3.3 创建 standby.signal

创建 standby.signal 文件,声明从库

echo "standby_mode = on" >> /data/appData/postgreSql/standby.signal

3.4 启动从库

/data/app/postgreSql/bin/pg_ctl start -D /data/appData/postgreSql/

4. 验证主从同步

4.1 主库验证方式

4.1.1 查看进程

ps aux |grep "sender"|grep -v "grep"
# 返回 postgres: walsender repl 172.16.98.201(53768) streaming

4.1.2 查表验证

# 登录主库
/data/app/postgreSql/bin/psql -h 127.0.0.1 -p 5432 -U postgres
# 执行select
postgres=# select pid,application_name,state,client_addr,sync_priority,sync_state from pg_stat_replication;
# 返回 async

4.2 从库验证

ps aux |grep "receiver" |grep -v "grep"
# 返回 postgres: walreceiver streaming