Centos7.6部署postgresql15主从

安装pg15（master和standby）

Centos7.6安装postgresql15

• 注意：只要完成初始化数据库之前的步骤

主数据库配置(master)

初始化数据库

${PGHOME}/bin/initdb -D ${PGDATA} --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 #初始化数据库

创建归档日志目录

mkdir -p $PGDATA/pg_archive

设置数据库访问权限

vi $PGDATA/pg_hba.conf #添加修改
#添加host    replication     repl            10.0.1.203/24           md5
host    all             all             0.0.0.0/0               md5

修改数据库配置文件

# basic
listen_addresses = '*'                # 监听所有ip
port = 5432                           # 端口
max_connections = 1000                # 最大连接数
superuser_reserved_connections = 10   # 给超级用户预留的连接数
shared_buffers = 1GB                  # 共享内存，一般设置为内存的1/4
work_mem = 16MB                       # 设置在写入临时磁盘文件之前查询操作(例如排序或哈希表)可使用的最大内存容量
maintenance_work_mem = 256MB          # 在维护性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）中使用的 最大的内存量
timezone = 'Asia/Shanghai'            # 系统时区
hot_standby = on                      # 打开热备# optimizer
default_statistics_target = 500       # 默认100，ANALYZE在pg_statistic中存储的信息量，增大该值，会增加ANALYZE的时间，但会让解释计划更精准# wal
max_wal_size = 1GB                    # 建议与shared_buffers保持一致
min_wal_size = 80MB                   # 建议max_wal_size/12.5
wal_log_hints = on                    # 控制WAL日志记录的方式，建议打开
wal_level = replica                   # wal日志写入级别，要使用流复制，必须使用replica或更高级别
wal_sender_timeout = 60s              # 设置WAL发送者在发送WAL数据时等待主服务器响应的超时时间# archive
archive_mode = on                     # 
archive_command = 'gzip < %p > /data/pgsql/data/pg_archive/%f.gz'# log 近7天轮询
log_destination = 'csvlog'            # 日志格式
logging_collector = on                # 日志收集器
log_directory = 'pg_log'              # 日志目录 $PGDATA/pg_log
log_filename = 'postgresql.%a'        # 7天日志轮询
log_file_mode = 0600                  # 日志文件的权限
log_rotation_size = 0                 # 日志的最大尺寸，设置为零时将禁用基于大小创建新的日志文件
log_truncate_on_rotation = on         # 这个参数将导致PostgreSQL截断（覆盖而不是追加）任何已有的同名日志文件
log_min_duration_statement = 0        # 如果语句运行至少指定的时间量，将导致记录每一个这种完成的语句的持续时间
log_duration = on                     # 每一个完成的语句的持续时间被记录
log_lock_waits = on                   # 控制当一个会话为获得一个锁等到超过deadlock_timeout时，是否要产生一个日志消息
log_statement = 'mod'                 # 控制哪些 SQL 语句被记录。有效值是 none (off)、ddl、mod和 all（所有语句）。ddl记录所有数据定义语句，例如CREATE、ALTER和 DROP语句。mod记录所有ddl语句，外加数据修改语句例如INSERT, UPDATE、DELETE、TRUNCATE, 和COPY FROM
log_timezone = 'Asia/Shanghai'        # 设置在服务器日志中写入的时间戳的时区

• pg的postgresql.conf的配置可以参考以下网站

PGTune - calculate configuration for PostgreSQL based on the maximum performance for a given hardware configuration

开启数据库

pg_ctl start
psql postgres
# 修改postgres的密码
alter role postgres with password 'Aa123456';
# 创建主从同步用户
create role repl with login replication encrypted password 'Aa123456';
\q

从数据库配置(standby)

同步主库的数据文件

chmod 0700 $PGDATA
su - postgres
pg_basebackup -Fp --progress -D $PGDATA -R -h ty-m2 -p 5432 -U repl --password

创建文件standby.signal

vi $PGDATA/standby.signal
standby_mode = on #on为从库
primary_conninfo = 'host=ty-m2 port=5432 user=repl password=Aa123456' #主库信息
recovery_target_timeline = 'latest' #流复制同步最新数据

启动从数据库

pg_ctl start

主从状态验证

master上验证

su - postgrespsql postgresselect client_addr,sync_state from pg_stat_replication;

\x on
select * from pg_stat_replication;
\q

pg_controldata | grep 'Database cluster state'

standby上验证

pg_controldata | grep 'Database cluster state'

实操测试

• 在主库建立一个pgtest的数据库

psql postgres
create database pgtest;
\connect pgtest
\q

• 从库也同步建了一个pgtest的数据库

psql pgtest
\q

参考

26.2. 日志传送后备服务器