Shulou(Shulou.com)06/01 Report--

1. Create a replication user:

CREATE USER repl WITH replication login password 'repl'

two。 Edit the recovery file:

Cat / pgdata/10/data/recovery.conf

Sync:

Standby_mode = 'on'

Primary_conninfo = 'host=192.168.56.202 port=5432 user=postgres application_name=db3 user=repl password=repl keepalives_idle=60 keepalives_interval=5 keepalives_count=5'

Restore_command =''

Recovery_target_timeline = 'latest'

The configuration file postgresql.conf of the main library adds the following:

Synchronous_standby_names = 'db3'

Pg_ctl reload

Async:

Recovery_target_timeline = 'latest'

Standby_mode = on

Restore_command =''

Primary_conninfo = 'host=192.168.56.202 port=5432 user=repl password=repl'

3. Execute on db3, ip:192.168.56.203:

Pg_basebackup-D / pgdata/10/data-Fp-Xs-v-P-h 192.168.56.202-p 5432-U repl

Pg_basebackup: initiating basebackup, waiting for checkpoint to complete # # if tamping occurs at this step, you can manually execute the checkpoint; command on the main library.

Pg_basebackup: checkpoint completed

Transaction log start point: 460B/6F006F68 on timeline 16

Pg_basebackup: starting background WAL receiver

4. Start the database:

Pg_ctl start

5. Check the roles of the master and slave libraries:

a. The main library checks the replication status:

Select * from pg_stat_replication

Prepare the library:

Ps-ef | grep postgres | grep receiver

Main library:

Ps-ef | grep postgres | grep sender

B.SELECT pg_is_in_recovery ()

If you return t to indicate that it is a standby library, return f to indicate that it is the main library.

c. View database control information:

Pg_controldata | grep cluster

Master: Database cluster state: in production

Standby: Database cluster state: in archive recovery

d. Query the pg_stat_replication view:

There are records on the main database, but there are no records in the reserve database.

Note:

If you use pha4pgsql, check the login distributed transaction to ensure that you can log in without a password:

Psql-U postgres-h 10.11.55.31-p 5432-d postgres

6.postgresql master / slave switching steps:

a. Close the main library. It is recommended to use the-m fast mode to close:

Pg_ctl stop-m fast

b. Execute the pg_ctl promote command on the slave database to activate the slave database. If recovery.conf becomes recovery.done, the slave database has been switched to the master database:

Pg_ctl promote

c. Switch the old main library to a standby library and create a recovery.conf file in the $PGDATA directory of the old main library (if the recovery.conf file does not exist in this directory)

You can copy one according to the $PGHOME/recovery.conf.sample template file. If there is a recovery.done file in this directory, you need to send the recovery.done file

Rename it to recovery.conf), the configuration is the same as the old slave library, except that the IP in the primary conninfo parameter is replaced by the peer IP.

Cat recovery.conf

Recovery_target_timeline = 'latest'

Standby_mode = on

Restore_command =''

Primary_conninfo = 'host=192.168.56.203 port=5432 user=repl password=repl'

d. Start the old master library, and observe whether the master and slave processes are normal. If so, it means that the master / slave switch is successful.

7. Query the information about lsn and log names accepted by the repository:

9.x:

Prepare the logs accepted by the database query lsn:

SELECT pg_last_xlog_receive_location ()

Select pg_last_xlog_replay_location ()

Determine the specific log through the function pg_xlogfile_name:

Select pg_xlogfile_name (pg_last_xlog_replay_location ())

Offset of the two WAL log locations:

SELECT pg_xlog_location_diff ('4741Compact 620E4CC8')

Displays the time when the log was last applied:

SELECT pg_last_xact_replay_timestamp ()

10.x:

Execute in the standby library:

SELECT pg_last_wal_receive_lsn ()

SELECT pg_last_wal_replay_lsn ()

Find out the specific logs in the main database according to the lsn checked out by the slave database:

Select pg_walfile_name ('xxxxxx')

Offset of the two WAL log locations:

SELECT pg_wal_lsn_diff ('4741 Compact 5B7C9488')

Displays the time when the log was last applied:

SELECT pg_last_xact_replay_timestamp ()

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.