PostgreSQL database stand-alone expansion for stream replication

1. Installing the Postgres database on the standby server does not require initialization.

installation process See: http://www.cnblogs.com/ilifeilong/p/6979288.html

2. Create a replication user with replication permissions on the primary server

postgres = # CREATE ROLE repl WITH REPLICATION PASSWORD ‘repl’ LOGIN;
3. Allow replication users to remotely connect to the primary server

$ grep "^ host" pg_hba.conf
host all all 127.0.0.1/32 trust
host replication repl 0.0.0.0/0 md5
host all all :: 1/128 trust
4. Set the parameters related to streaming replication on the primary server

$ mkdir / usr / local / pgsql / arch
$ egrep "archive_mode | max_wal_senders | wal_keep_segments | archive_command | wal_level | hot_standby" postgresql.conf
al_level = hot_standby # minimal, archive, hot_standby, or logical
archive_mode = on # enables archiving; off, on, or always
archive_command = ‘test! -f / usr / local / pgsql / arch /% f && cp% p / usr / local / pgsql / arch /% f’
max_wal_senders = 5 # max number of walsender processes
wal_keep_segments = 30 # in logfile segments, 16MB each; 0 disables
hot_standby = on # "on" allows queries during recovery
#hot_standby_feedback = off # send info from standby to prevent
5. Restart the primary server process

$ pg_ctl stop -m fast
$ pg_ctl start
6. Make a complete backup of the primary server and transfer it to the standby server

   Backup through the pg_ (start | stop) _backup function on the primary server
postgres = # SELECT pg_start_backup (‘label‘, true);
 pg_start_backup
-----------------
 7 / E6000060
(1 row)
$ rsync -az --progress $ {PGDATA} [email protected] 10.189.100.195:/usr/local/pgsql/ --exclude postmaster.pid
postgres = # SELECT pg_stop_backup ();
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup
----------------
 7 / E60005C8
(1 row)
 Backup by the pg_basebackup command on the standby server, requiring the standby PGDATA directory to be empty
$ pg_basebackup --host = 10.189.102.118 --username = repl --port = 5432 --label = backup --verbose --progress --pgdata = / usr / local / pgsql / data --checkpoint = fast --format = p --xlog-method = stream
Password:
transaction log start point: 7 / EA000028 on timeline 1
pg_basebackup: starting background WAL receiver
65933562/65933562 kB (100%), 1/1 tablespace
transaction log end point: 7 / EA000830
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
7. Set the standby database replication related parameters so that the standby can work as the main database after failover

$ mkdir / usr / local / pgsql / arch
$ egrep "archive_mode | max_wal_senders | wal_keep_segments | archive_command | wal_level | hot_standby" postgresql.conf
wal_level = hot_standby # minimal, archive, hot_standby, or logical
archive_mode = on # enables archiving; off, on, or always
archive_command = ‘test! -f / usr / local / pgsql / arch /% f && cp% p / usr / local / pgsql / arch /% f’
max_wal_senders = 5 # max number of walsender processes
wal_keep_segments = 30 # in logfile segments, 16MB each; 0 disables
hot_standby = on # "on" allows queries during recovery
#hot_standby_feedback = off # send info from standby to prevent
8. Create a recovery file in the standby file

$ cat recovery.conf
restore_command = ‘cp / usr / local / pgsql / arch /% f"% p "‘
standby_mode = ‘on’
primary_conninfo = ‘user = repl password = repl host = 10.189.102.118 port = 5432 sslmode = disable sslcompression = 1‘
archive_cleanup_command = ‘pg_archivecleanup -d / usr / local / pgsql / arch% r >> /usr/local/pgsql/arch/archive_cleanup.log’
trigger_file = ‘/ usr / local / pgsql / data / trigger_active_standby’
9. Start the standby database process, automatically start streaming replication

$ pg_ctl start -w
waiting for server to start .... LOG: could not create IPv6 socket: Address family not supported by protocol
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".
 done
server started
10. Check the delay of the primary and standby databases

View through functions and system tables
edbstore = # select * from pg_stat_replication; #View in the primary library
-[RECORD 1] ---- + ------------------------------
pid | 15013
usesysid | 19206
usename | repl
application_name | walreceiver
client_addr | 10.189.100.195
client_hostname |
client_port | 56072
backend_start | 2017-06-13 08: 10: 35.400508-07
backend_xmin |
state | streaming
sent_location | 7 / EC01A588
write_location | 7 / EC01A588
flush_location | 7 / EC01A588
replay_location | 7 / EC01A588
sync_priority | 0
sync_state | async

edbstore = # SELECT pg_current_xlog_location (); #View in the primary library
 pg_current_xlog_location
--------------------------
 7 / EC01A588
(1 row)

postgres = # select pg_last_xlog_receive_location (), pg_last_xlog_replay_location (), pg_last_xact_replay_timestamp (); #View in standby
 pg_last_xlog_receive_location | pg_last_xlog_replay_location | pg_last_xact_replay_timestamp
------------------------------- + ------------------ ------------ + -------------------------------
 7 / EC01A588 | 7 / EC01A588 | 2017-06-13 08: 25: 20.281568-07
(1 row)
View by process
$ ps -ef | grep sender | grep -v grep #View in the primary library
postgres 15013 24883 0 08:10? 00:00:00 postgres: wal sender process repl 10.189.100.195 (56072) streaming 7 / EC01A668

$ ps -ef | grep receiver | grep -v grep #View in the standby library
postgres 12857 12843 0 08:10? 00:00:00 postgres: wal receiver process streaming 7 / EC01A668
 

PostgreSQL database stand-alone expansion to streaming replication