一台主机上搭建PostgreSQL集群------一主一备

在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中的双引号,异步模式立马转为同步模式。

参考
https://stackoverflow.com/questions/57362648/postgresql-master-server-hangs-on-replication-flow/57375530#57375530

  • 2
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值