Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use pg_rewind". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to use pg_rewind.

Pg_rewind

Is a tool for synchronizing the data directory of the postgresql main cluster database. The target server needs to allow wal_log_hints in postgresql.conf, or checksums when initdb initializes the cluster, and full_page_writes must also be on

Pg_rewind copies only the changed blocks in the table data file; all other files are copied in full, including the configuration file. The advantage of pg_rewind over using tools such as pg_basebackup backup or rsync is that pg_rewind does not need to read unchanged blocks in the database. This makes it much faster when the database is large and only a small portion of the blocks are different.

Pg_rewind [option...] {- D |-- target-pgdata} directory {- source-pgdata=directory |-- source-server=connstr

Parameters:

-D directory-- target-pgdata=directory

This option specifies the destination data directory that is synchronized with the source. The target server must be shut down cleanly before running pg_rewind

-- source-pgdata=directory

Specifies the file system path to the data directory of the source server that you want to synchronize. This option requires a clean shutdown of the source server

-- source-server=connstr

Specifies the libpq connection string to connect to the source PostgreSQL server. The connection must be a normal (non-replication) connection with root access. This option requires that the source server is running, not in recovery mode.

-n-- dry-run

Perform all operations except for the actual modification of the target directory.

-P-- progress

Make a progress report.

The lab uses two hosts, both with postgresql-10.7 installed, and stream replication configured

Master: 192.168.56.5 M1

Cluster: 192.168.56.25m7

M1 (master): create test tables and data

Postgres=# create table test; CREATE TABLEpostgres=#\ dList of relationsSchema | Name | Type | Owner-+-public | test | table | postgres (1 row) postgres=# insert into test values; INSERT 0 1postgres=# select * from test Id | e_name | e_mail | d_id----+-1 | zbs | 123@126.com | 10 (1 row)

M7 (cluster): query data was copied successfully

[postgres@z_leader ~] $psql postgrespsql Type "help" for help.postgres=#\ dList of relationsSchema | Name | Type | Owner-+-public | test | table | postgres (1 row) postgres=# select * from test Id | e_name | e_mail | d_id----+-1 | zbs | 123@126.com | 10 (1 row)

Promote the cluster library to the new main library

[postgres@z_leader data] $pg_ctl promote-D / usr/local/pg/datawaiting for server to promote.... Doneserver approved [Postgres @ z_leader data] $psql postgrespsql (10.7) Type "help" for help.postgres=# select pg_is_in_recovery (); pg_is_in_recovery-f (1 row)

M1 (original master database) inserts a record to simulate that the data on the original master database is not copied to the original cluster library.

Postgres=# insert into test values; INSERT 0 1postgres=# select * from test;id | e_name | e_mail | d_id----+-1 | zbs | 123@126.com | 102 | zbs1 | 124@126.com | 10 (2 rows)

M7: insert a record on the original cluster library (promoted to main library) and view the result

Postgres=# insert into test values; INSERT 0 1postgres=# select * from test;id | e_name | e_mail | d_id----+-1 | zbs | 123@126.com | 103 | zbs2 | 124@126.com | 10 (2 rows)

M1 turns the original master library into the cluster library of the new master library.

[postgres@localhost ~] $kill-INT `head-1 / usr/local/pg/data/ postmaster.pid`

-- configure the stream to copy files and parameters

[postgres@localhost data] $mv recovery.done recovery.conf [postgres@localhost data] $cat recovery.confstandby_mode = 'on'restore_command =' cp / usr/local/pg/arch/%f'primary_conninfo = 'host=192.168.56.25 port=5432 user=rep'recovery_target_timeline =' latest' [postgres@localhost data] $

-- start the database

[postgres@localhost] $/ usr/local/pg/bin/pg_ctl-D / usr/local/pg/data-l logfile startwaiting for server to start.... Doneserver started [postgres@localhost data] $psql postgrespsql Type "help" for help.postgres=# select pg_is_in_recovery (); pg_is_in_recovery-t (1 row) postgres=# select * from test Id | e_name | e_mail | d_id----+-1 | zbs | 123@126.com | 102 | zbs1 | 124@126.com | 10 (2 rows)

-- the record inserted on M7 failed to copy

-Log information

2019-03-02 09 CST 15 consistent recovery state reached at 0/D0000982019 17.415 CST [2492] LOG: consistent recovery state reached at 0/D0000982019-03-02 09 CST 15 17 17 15 LOG: invalid record length at 0/D000098: wanted 24 Got 02019-03-02 09 database system is ready to accept read only connections2019 15 CST [2490] LOG: database system is ready to accept read only connections2019-03-02 09 CST [2500] LOG: fetching timeline history file for timeline 6 from primary server2019-03-02 09 15 FATAL: could not start WAL streaming: ERROR: requested starting point 0/D000000 on timeline 5 is not in thisserver's historyDETAIL: This server's history forked from timeline 5 at 0/C003168.cp: missing destination file operand after `/ Usr/local/pg/arch/00000006.history'Try `cp-- help' for more information.cp: missing destination file operand after `/ usr/local/pg/arch/00000007.history'Try `cp-- help' for more information.cp: missing destination file operand after` / usr/local/pg/arch/00000006.history'Try `cp-- help' for more information.2019-03-02 09Partition 15 CST [2492] LOG: new timeline 6 forked off current database system timeline 5 before current recovery point 0/D000098cp: Missing destination file operand after `/ usr/local/pg/arch/00000005000000000000000D [postgres@localhost ~] $kill-INT `head-1 / usr/local/pg/data/ postmaster.pid`

-causes pg_rewind to synchronize the database timeline

[[postgres@localhost] $pg_rewind-- target-pgdata / usr/local/pg/data-- source-server='host=192.168.56.25 port=5432 user=postgres dbname=postgres'-Pconnected to serverservers diverged at WAL location 0/C003168 on timeline 5rewinding from last common checkpoint at 0/C003010 on timeline 5reading source file listreading target file listreading WAL in targetneed to copy 100 MB (total source directory size is 118MB) 102599 kB!

-- this file needs to be reconfigured after pg_rewind

[postgres@localhost data] $cat recovery.confstandby_mode = 'on'restore_command =' cp / usr/local/pg/arch/%f'primary_conninfo = 'host=192.168.56.25 port=5432 user=rep'recovery_target_timeline =' latest' [postgres@localhost ~] $/ usr/local/pg/bin/pg_ctl-D / usr/local/pg/data-l logfile startwaiting for server to start.... Doneserver started [postgres@localhost ~] $psql postgrespsql "help" for help.postgres=# select * from test;id | e_name | e_mail | d_id----+-1 | zbs | 123@126.com | 103 | zbs2 | 124@126.com | 10 (2 rows) postgres=# select pg_is_in_recovery () Pg_is_in_recovery-t (1 row)

-- the records that were not copied to the cluster library by the original master library disappear, and the records inserted on the new master library are synchronized.

M7 (new main library) [postgres@z_leader] $psql postgrespsql (10.7) Type "help" for help.postgres=# insert into test values; INSERT 0 1postgres=# select * from test Id | e_name | e_mail | d_id----+-1 | zbs | 123@126.com | 103 | zbs2 | 124@126.com | 104 | zbs2 | 124@126.com | 10 (3 rows)

M1 (new cluster library)

Postgres=# select * from test;id | e_name | e_mail | d_id----+-1 | zbs | 123@126.com | 103 | zbs2 | 124@126.com | zbs2 | 124@126.com | 10 (3 rows) so far, I believe you have a better understanding of "how to use pg_rewind". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.