postgresql12 主从部署

postgresql12 主从部署

1. yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
2. yum install postgresql12
3. yum install postgresql12-server
4. /usr/pgsql-12/bin/postgresql-12-setup initdb
5. systemctl enable postgresql-12
6. systemctl start postgresql-12
7. 服务器时区设置
   [root@localhost ~]# cp -rf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
8. 配置postgresql.conf

listen_addresses = '*'                     
port = 5432
max_connections = 500
superuser_reserved_connections = 10
full_page_writes = on
wal_log_hints = off
max_wal_senders = 50
hot_standby = on
log_destination = 'csvlog'
logging_collector = on/off  ----  是否将日志重定向至文件中，默认是off（该配置修改后，需要重启DB服务）
log_directory = 'pg_log' ---- 日志文件目录，默认是PGDATA的相对路径，即{PGDATA}/pg_log，也可以改为绝对路径
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' ---- 日志文件命名形式，使用默认即可
log_rotation_age = 7d ----  单个日志文件的生存期，默认1天，在日志文件大小没有达到log_rotation_size时，一天只生成一个日志文件
log_rotation_size = 10MB  ---- 单个日志文件的大小，如果时间没有超过log_rotation_age，一个日志文件最大只能到10M，否则将新生成一个日志文件。
log_truncate_on_rotation = off ---- 当日志文件已存在时，该配置如果为off，新生成的日志将在文件尾部追加，如果为on，则会覆盖原来的日志。
log_lock_waits = off ---- 控制当一个会话等待时间超过deadlock_timeout而被锁时是否产生一个日志信息。在判断一个锁等待是否会影响性能时是有用的，缺省是off。
log_statement = 'mod' # none, ddl, mod, all ---- 控制记录哪些SQL语句。none不记录，ddl记录所有数据定义命令，比如CREATE,ALTER,和DROP 语句。mod记录所有ddl语句,加上数据修改语句INSERT,UPDATE等,all记录所有执行的语句，将此配置设置为all可跟踪整个数据库执行的SQL语句。
log_duration = off ---- 记录每条SQL语句执行完成消耗的时间，将此配置设置为on,用于统计哪些SQL语句耗时较长。
log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements and their durations, > 0 logs only statements running at least this number of milliseconds
-1表示不可用，0将记录所有SQL语句和它们的耗时，>0只记录那些耗时超过（或等于）这个值（ms）的SQL语句。个人更喜欢使用该配置来跟踪那些耗时较长，可能存在性能问题的SQL语句。虽然使用log_statement和log_duration也能够统计SQL语句及耗时，但是SQL语句和耗时统计结果可能相差很多行，或在不同的文件中，但是log_min_duration_statement会将SQL语句和耗时在同一行记录，更方便阅读。
log_connections = off ----是否记录连接日志
log_disconnections = off ---- 是否记录连接断开日志
log_line_prefix = '%m %p %u %d %r ' ---- 日志输出格式（%m,%p实际意义配置文件中有解释）,可根据自己需要设置（能够记录时间，用户名称，数据库名称，客户端IP和端口，方便定位问题）
log_timezone = 'Asia/Shanghai' ---- 日志时区，最好和服务器设置同一个时区，方便问题定位
timezone = 'PRC'
unix_socket_directories = '/tmp'
shared_buffers = 512MB (可用memory40%)
temp_buffers = 16MB
work_mem = 32MB
effective_cache_size = 2GB (可用memory90%)
maintenance_work_mem = 128MB
max_stack_depth = 20MB
dynamic_shared_memory_type = posix
## PITR
full_page_writes = on
wal_buffers = 16MB
wal_writer_delay = 200ms
commit_delay = 0
commit_siblings = 5
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /data/pgdata/archivedir/%f && cp %p /data/pgdata/archivedir/%f'
archive_timeout = 60s

9. 配置pg_hba.conf
   host replication repl 172.26.131.155/32 trust
   host replication repl 172.26.131.156/32 trust
   host all all 0.0.0.0/0 md5

10.createuser -s -W repl
psql
alter user repl with password ‘1234’ ;
(不能设置成replicat用户,否则不同步)

11.pg_basebackup -h 10.0.16.9 -U replica -D /DBdata/data/11/data -X stream -P
/usr/pgsql-11/share/

12.服务器时区设置
[root@localhost ~]# cp -rf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

根据主库的postgresql.conf 配置文件 修改参数配置:
listen_addresses = ‘*’
wal_level = hot_standby
max_connections = 600, # 一般比主库大一些.
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on # 如果有错误的数据复制,是否向主进行报告

在postgre.auto.conf 添加 application_name =slave1,配置如下
primary_conninfo = ‘application_name=slave1 user=repl passfile=’’/home/postgres/.pgpass’’ host=192.168.3.62 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any’

11.检查主从同步情况：
select * from pg_stat_replication;

##备库只读, 所以任何操作, 都会告警失败:
postgres=# drop database pgtest;
ERROR:  cannot execute DROP DATABASE in a read-only transaction

## 查看主备的状态
#主
ps -ef  |grep sender
postgres  92996  51692  0 15:34 ?        00:00:00 postgres: walsender repl 192.168.3.61(53958) streaming 0/8000060
postgres 101845  20470  0 15:45 pts/2    00:00:00 grep --color=auto sender

#备
ps -ef  |grep recover
postgres  98311  98309  0 15:34 ?        00:00:00 postgres: startup   recovering 000000010000000000000008
postgres 108029  21054  0 15:46 pts/2    00:00:00 grep --color=auto recover

#通过此命令判断主备
pg_controldata -D /data/pgdata/data/ |grep state

in archive recovery ## 表示从库
in production       ## 表示主库