Our current PostgreSQL is a master-slave streaming replication mode, standby no external service, only as a hot spare.
Machine Environment Description:
Postgres version 10
Topological relationship prior to switchover:
master:10.0.20.25
standby:10.0.20.26
Before operation, switch to Postgres account:
10.0.20.25 Stop the Main library:
Psql Select Pid,datname,usename,client_addr,client_port, application_name from Pg_stat_activity; --View current connection then, notify the SA to remove the current main library from the SLB and prevent the user from entering a new connection. Select Pg_terminate_backend (PID) from pg_stat_activity where usename= ' admin '; --Kill the current connection to the Admin account (my business account is Admin)/usr/pgsql-10/bin/pg_ctl stop-m fast-d/var/lib/pgsql/10/data/--Turn off pgs for the current master node QL Process
10.0.20.26 active as the new Main library:
/usr/pgsql-10/bin/pg_ctl promote-d/var/lib/pgsql/10/data/
10.0.20.26 Check to see if the new Main library has been promoted successfully:
/usr/pgsql-10/bin/pg_controldata-d/var/lib/pgsql/10/data/| grep cluster
Database cluster State:in Production--Description: If the value is "in production" the description is the main library, if the value is "in Archive recovery" The description is a standby library.
Create a test table on 10.0.20.26 to verify that the new replication is healthy at a later time
CREATE TABLE TEST_T1 (a int); INSERT INTO TEST_T1 select 100;select * from Test_t1;
10.0.20.25 use Pg_rewind to become a new slave library:
/usr/pgsql-10/bin/pg_rewind--target-pgdata/var/lib/pgsql/10/data/--source-server= ' host=10.0.20.26 port=5432 user =postgres dbname=postgres password=postgres '-p
--Note: Here 10.0.20.26 on the Postgres user's password is postgres, need to according to their own situation to write pg_rewind situation
10.0.20.25 View postgresql.conf There are several places to make changes
Max_connections = 200 # general Chado to write applications the maximum number of connections from the library is greater Hot_standby = on # indicates that this machine is not only used for data archiving, but also For data Query Max_standby_streaming_delay = 30s # Maximum delay time for data stream backup Wal_receiver_status_interval = 10s # How often do I report to the main From the state, of course, from each data copy will report the state to the main, here just set the longest interval hot_standby_feedback = on # If there is wrong data replication, feedback to the main
The contents of the recovery.conf file written on 10.0.20.25 are as follows:
Recovery_target_timeline = ' latest ' Standby_mode = Onprimary_conninfo = ' host=10.0.20.26 port=5432 user=replica password =replica '
10.0.20.25 New start Pgsql from library
/usr/pgsql-10/bin/pg_ctl start-d/var/lib/pgsql/10/data/
10.0.20.26 The new Main Library execution:
\x select * from Pg_stat_replication;
10.0.20.26 the new main library to perform the insert operation:
Insert INTO TEST_T1 Select 1;select * from Test_t1;
Then, in the main library, query the replication status from the library:
Select Txid_current_snapshot ();
PostgreSQL lifting the operation of the main library