PostgreSQL-10 主从同步复制测试
一、主节点开启同步复制
1、 新建一个复制账号
[postgres@test ~]$ /usr/local/pg10/bin/psql psql (10.0) Type "help" for help. postgres=# CREATE ROLE replication WITH REPLICATION PASSWORD '6220104' LOGIN; //复制账号 postgres=# CREATE ROLE tank SUPERUSER LOGIN PASSWORD '6220104'; //超级管理员
2、修改主配置文件及开启流复制
[postgres@test pgdata]$ grep -Ev "^$|^[#;]" postgresql.conf # (change requires restart) # (change requires restart) # (change requires restart) # (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) max_connections = 100 # (change requires restart) # (change requires restart) # (change requires restart) # (change requires restart) # (change requires restart) # 0 selects the system default # 0 selects the system default # 0 selects the system default shared_buffers =2048MB # min 128kB # (change requires restart) # (change requires restart) # (change requires restart) work_mem = 128MB # min 64kB maintenance_work_mem = 256MB # min 1MB dynamic_shared_memory_type = posix # the default is the first option # supported by the operating system: # posix # sysv # windows # mmap # use none to disable dynamic shared memory # (change requires restart) # in kB, or -1 for no limit # (change requires restart) # can be used in parallel queries # (change requires restart) wal_level = replica # minimal, replica, or logical # (change requires restart) # (turning this off can cause # unrecoverable data corruption) synchronous_commit = on # synchronization level; # off, local, remote_write, remote_apply, or on # supported by the operating system: # open_datasync # fdatasync (default on Linux) # fsync # fsync_writethrough # open_sync # (change requires restart) # (change requires restart) max_wal_size = 16GB min_wal_size = 8GB # (change requires restart) # 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' # number of seconds; 0 disables max_wal_senders = 10 # max number of walsender processes # (change requires restart) wal_sender_timeout = 10s # in milliseconds; 0 disables # (change requires restart) # (change requires restart) synchronous_standby_names = '*' # 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 hot_standby = on # "off" disallows queries during recovery # (change requires restart) # when reading WAL from archive; # -1 allows indefinite delay # when reading streaming WAL; # -1 allows indefinite delay # 0 disables # query conflicts # communication from master # in milliseconds; 0 disables # retrieve WAL after a failed attempt # (change requires restart) effective_cache_size = 4GB # JOIN clauses log_timezone = 'PRC' timezone = 'PRC' # abbreviations. Currently, there are # Default # Australia (historical usage) # India # You can create your own file in # share/timezonesets/. # encoding lc_messages = 'C' # locale for system error message # strings lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C' # locale for number formatting lc_time = 'C' # locale for time formatting default_text_search_config = 'pg_catalog.english' # (change requires restart) # (change requires restart) # (max_pred_locks_per_transaction # / -max_pred_locks_per_relation) - 1 # directory 'conf.d' [postgres@test pgdata]$
3、修改访问控制文件
[postgres@test pgdata]$ grep -Ev "^$|^[#;]" pg_hba.conf local all all trust host all all 127.0.0.1/32 trust host all all 14.23.51.58/32 md5 host all all ::1/128 trust local replication all trust host replication all 14.23.51.58/32 md5 host replication all ::1/128 trust [postgres@test pgdata]$
4、重启pg实例
# /etc/init.d/pg10 restart
二、 备机作业
2.1 初始化生成data基备份
[postgres@node02 ~]$ /usr/local/pg10/bin/pg_basebackup -D data -h 139.159.247.78 -U replication -X stream //注意其它表空间位置是否存在
2.2 编辑recovery.conf文件。
# cp /usr/local/pg10/share/postgresql/recovery.conf.sample recovery.conf [postgres@node02 backup]$ vi recovery.conf #--------------------------------------------------------------------------- # STANDBY SERVER PARAMETERS #--------------------------------------------------------------------------- # # standby_mode # # When standby_mode is enabled, the PostgreSQL server will work as a # standby. It will continuously wait for the additional XLOG records, using # restore_command and/or primary_conninfo. # standby_mode = on # # primary_conninfo # # If set, the PostgreSQL server will try to connect to the primary using this # connection string and receive XLOG records continuously. # primary_conninfo = 'host=139.159.247.78 port=5432 user=replication password=6220104 application_name=ghan' # e.g. 'host=localhost po rt=5432'
//如果设置application_name=* 将同步所有主节点数据库
2.3启动pg查看日志
[postgres@node02 backup]$ /usr/local/pg10/bin/pg_ctl -D data start -l kkk.log
三、同步复制状态检查在主节点
[postgres@test ~]$ /usr/local/pg10/bin/psql psql (10.0) Type "help" for help. postgres=# \x Expanded display is on. postgres=# SELECT * FROM pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 18242 usesysid | 16575 usename | replication application_name | ghan client_addr | 14.23.51.58 client_hostname | client_port | 52308 backend_start | 2017-10-31 17:41:04.982858+08 backend_xmin | state | streaming sent_lsn | B/31C17120 write_lsn | B/31C17120 flush_lsn | B/31C17120 replay_lsn | B/31C17120 write_lag | flush_lag | replay_lag | sync_priority | 1 sync_state | sync postgres=#
四、关闭备节点测试
ghan=# insert into t2_test values(generate_series(1,70)); ^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 70 ghan=# ^C ghan=#
五、关闭主节点同步提修改为异步提交
5.1修改配置文件
# vi postgresql.conf synchronous_commit = off # synchronization level;
5.2、重启pg实例
# /etc/init.d/pg10 restart