在CentOS系统上,基于PostgreSQL 12.1搭建集群。配置文件按照同步流复制模式配置。
1. 主数据库配置
1.1 配置postgresql.conf
data_directory = '/opt/pgsql/data' # use data in another directory
# (change requires restart)
hba_file = '/opt/pgsql/data/pg_hba.conf' # host-based authentication file
# (change requires restart)
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
wal_level = hot_standby # minimal, replica, or logical
# (change requires restart)
hot_standby = on # "off" disallows queries during recovery
# (change requires restart)
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp %p /home/postgres/pg_archive/%f' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
max_wal_senders = 2 # max number of walsender processes
# (change requires restart)
wal_keep_segments = 256 # in logfile segments; 0 disables
wal_sender_timeout = 60s # in milliseconds; 0 disables
synchronous_standby_names = '"dw-greenplum-2"' # standby servers that provide sync rep
# method to choose sync standbys, number of sync standbys,
# and comma-separated list of application_name
# from standby(s); '*' = all
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
#These are only used if logging_collector is on:
log_directory = '/home/postgres/pri_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
NOTE: synchronous_standby_names是设置同步流复制的备库的主机名,该名称会在备库的参数primary_conninfo中application_name=dw-greenplum-2指定。
1.2 配置pg_hba.conf
1.3 启动主库
/opt/pgsql/bin/pg_ctl start -D /opt/pgsql/data
1.4 创建一个数据库用户进行主从同步。创建用户replica,并赋予登录和复制的权限
CREATE ROLE replica login replication encrypted password 'replica';
2. 备数据库配置
2.1 创建备库数据目录
mkdir /opt/pgsql/standby_data
chown -R postgres:postgres /opt/pgsql/standby_data/
su - postgres
2.2 设置做为备库启动
touch /opt/pgsql/standby_data/standby.signal
2.3 修改postgresql.conf
data_directory = '/opt/pgsql/standby_data' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5433 # (change requires restart)
primary_conninfo = 'host=192.168.31.14 port=5432 user=replica password=replica application_name=dw-greenplum-2' # connection string to sending server
# (change requires restart)
hot_standby = on # "on" allows queries during recovery 设置为备库时是否支持可读
recovery_target_timeline = 'latest' # 'current', 'latest', or timeline ID
# (change requires restart)
2.5. 设置连接主库密码.pgpass
vi /home/postgres/.pgpass
192.168.31.14:5432:replication:replica:replica //备库到主库同步WAL日志使用
chmod 600 /home/postgres/.pgpass
2.6 主库数据备份到备库
pg_basebackup -h 192.168.31.14 -p 5432 -U replica -D /opt/pgsql/standby_data -X stream --progress
2.7 启动备库
/opt/pgsql/bin/pg_ctl -D /opt/pgsql/standby_data -l logfile_stdby start
3. 验证结果
3.1 walsender/walreceiver进程起来
3.2 可以从数据库查询出集群信息
SELECT * FROM pg_stat_replication;
select pg_is_in_recovery(); 结果是f则为主库,t为备库。
问题
执行INSERT操作时,总是无法返回;Ctrl+c取消后,又收到警告。而且,备节点上也数据也已经同步完成。
primarydb1=# INSERT INTO user_tbl(name, signup_date) VALUES('李四', '2021-01-05');
^CCancel request sent
WARNING: canceling wait for synchronous replication due to user request
DETAIL: The transaction has already committed locally, but might not have been replicated to the standby.
INSERT 0 1
如果把主库的postgresql.conf文件参数synchronous_commit,由on改为local可以解决hang住的问题。但是,如果是同步流复制,可能会有丢失数据的风险。
synchronous_commit = local # synchronization level;
# off, local, remote_write, remote_apply, or on
另一个问题是,按照同步流复制去配置,但是始终是异步流复制。无论如何都无法转换成同步流复制。
通过查找资料找到原因:主机名字dw-greenplum-2带有横线,在主库的postgresql.conf文件中,参数synchronous_standby_names需要用双引号括起来。但是在备库的postgresql.conf文件中,参数primary_conninfo也会用到此名字,在这儿是不需要用引号括起来的。
去掉备库参数primary_conninfo中的双引号,异步模式立马转为同步模式。