PostgreSQL lifting the operation of the main library

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