PostgreSQL主从数据库数据同步

运行环境

操作系统：Debian 11.5

数 据 库：PostgreSQL 14.6

主数据库：192.168.8.68

从数据库：192.168.8.69

使用apt-get安装postgresql，安装方法可以参考 https://blog.csdn.net/itbs/article/details/127909359?spm=1001.2014.3001.5501
官方推荐是用源码安装，优点是可以指定安装位置，实现应用程序、配置文件、数据文件、日志文件在一个文件夹里。用apt-get安装文件位置是分散的，应用程序在/usr/lib/postgresql，配置文件在 /etc/postgresql，数据文件在 /var/lib/postgresql，安装过程比较简单

主库操作

1、创建同步账号

创建同步账号 repl，密码 repl

	su postgres
	psql
	create role repl login replication encrypted password 'repl';
1
2
3

2、修改pg_hba.conf

文件位置 /etc/postgresql/14/main 。在#replication下，增加下面内容

host	replication	repl	192.168.8.69/32		trust
1

3、修改postgresql.conf

文件位置 /etc/postgresql/14/main 。增加下面内容

wal_level = replica
archive_mode = on
archive_command = 'cp %p /opt/postgresql/pg_archive/%f'
max_wal_senders = 8 
wal_sender_timeout = 60s
1
2
3
4
5

/opt/postgresql/pg_archive 这个路径中的文件夹要手动创建一下

4、主库重启

	systemctl restart postgresql
1

从库操作

1、测试从库访问主库

	psql -h 192.168.8.68 -U postgres
1

正确的情况下，输入密码即可访问

2、查询数据库的数据文件位置

	su postgres
	psql
	show data_directory;
1
2
3

查看后用exit退出psql命令行。如果清楚文件所在位置，此步骤可以略过。

3、停止从库

	systemctl stop postgresql
1

如果使用posgres用户执行，需要输入root密码，用root用户执行不需要

4、清空从库数据库文件

	rm -rf   /var/lib/postgresql/14/main/*  
1

5、从主库拉取数据

	pg_basebackup -h 192.168.8.68 -D /var/lib/postgresql/14/main -p 5432 -U repl -Fp -Xs -Pv -R --checkpoint=fast
1

-D 后面是主库数据库文件路径，如果配置了数据库文件地址，需要修改为配置后的路径

6、修改postgresql.conf

primary_conninfo = 'host=192.168.8.68 port=5432 user=repl password=repl'
recovery_target_timeline = latest 
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
max_connections = 200  #要比主库连接数大，默认连接数是100
max_worker_processes = 20
1
2
3
4
5
6
7
8

7、启动从库

	systemctl start postgresql
1

同步状态查询

在主库执行，查询从库同步状态。可以新建表或者添加数据进行测试。

	select client_addr,usename,backend_start,application_name,sync_state,sync_priority FROM pg_stat_replication;
1