PostgreSQL lifting the operation of the main library

Source: Internet
Author: User
Tags postgresql postgres version

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.