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

This article mainly introduces how to use rsync to achieve postgres log transmission standby server, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Log shipping standby server

Continuous archiving can be used to create a high availability (HA) cluster in conjunction with one or more backup servers that are ready to replace the failed primary server. This capability is often referred to as warm backup or log shipping

Moving WAL from one database server to another is often called log shipping (LogShipping). PostgreSQL implements file-based log delivery, which means that WAL records move one complete file (WAL segment) at a time. It can also be delivered based on recorded logs.

Log shipping is asynchronous, that is, WAL records are not delivered until after the transaction is committed. You can use archive_timeout to set the log shipping interval (which should be the maximum interval).

At startup, the standby server calls the restore_command command to start restoring all WAL that is valid in the wal archive location, fails once the available WAL,restore_command is restored, and will attempt to restore the available WAL from the pg_wal directory. If that also fails and streaming replication is configured, try to connect to the primary server and start WAL streaming replication from the last valid record found in the archive or pg_wal. If that also fails, or if stream replication is not configured, or the connection is disconnected, the standby server goes back to step 1 again and tries to recover the file from the archive. The loop attempts to copy channels from archives, pg_wal, and continuous streams until the server stops or a failover is triggered by a trigger file.

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

Main library: 192.168.56.25 M1 uses the rsync command to transfer wal files to M7

Cluster library: 192.168.56.5 m7 configure rsync to run as a service on this machine to receive wal files sent by cluster M1

Configure the cluster library and set hot_standby to make the standby library receive connections. Omit the restore process of the standby library from the basic backup of the main library

[postgres@localhost data] $cat recovery.confstandby_mode = 'on'restore_command =' cp / usr/local/pg/arch_bak/%f% p'recovery_target_timeline = 'latest'postgres=# show hot_standby; hot_standby- on (1 row)

Configure rsync server on M1, use rsync-3.1.3 version, omit rsyn installation process

[root@localhost ~] # cat / etc/rsyncd.conf uid=postgresgid=postgresuse chroot = nomax connections = 5read only = false pid file = / var/run/rsyncd.pidlog file = / var/log/rsync.logtransfer logging = yeslog format = t% a% m% f% btimeout = 300 [arch_bak] path = / usr/local/pg/arch_bakauth users = tridge, susansecrets file = / etc/ rsyncd.secrets [Wal _ bak] path = / usr/local/pg/data/pg_walauth users = tridge Susansecrets file = / etc/rsyncd.secrets [root@localhost ~] # cat / etc/rsyncd.secretstridge:mypasssusan:herpass

Start the rsync server

[root@localhost arch_bak] # / usr/local/bin/rsync-- daemon [root@localhost arch_bak] # lltotal 0 [root@localhost arch_bak] # pwd/usr/local/pg/arch_bak

Archive wal log files before creation from M7 synchronous standby server

[root@z_leader rsync-3.1.3] # / usr/local/bin/rsync-av / usr/local/pg/arch/ tridge@192.168.56.5::arch_bak/Password:sending incremental file list./00000005000000000000000B00000005000000000000000C.partial00000006.history00000006000000000000000C00000006000000000000000D00000006000000000000000E00000006000000000000000F000000060000000000000010sent 117470037 bytes received 179 bytes 18072340.92 bytes/sectotal size is 117440721 speedup is 1.00

View transferred files and standby server table records on M1

[root@localhost arch_bak] # lltotal 114692When RW-1 postgres postgres 16777216 Mar 2 09:02 000000050000000000000B RW-1 postgres postgres 16777216 Mar 2 09:08 000000050000000000000C.partialAfter RW-1 postgres postgres 16777216 Mar 2 11:15 0000600000000000C postgres postgres 16777216 Mar 17 09:51 00006000000000000DLY RW-1 postgres postgres 16777216 Mar 17 09:55 00000000000000000ERAW-1 postgres postgres 16777216 Mar 17 10:13 000000060000000000Flyrw- 1 postgres postgres 16777216 Mar 17 20:02 00000006000000000010RW-1 postgres postgres 209 Mar 209: 08 00000006.historypostgres=# select * from test Id | e_name | e_mail | zbs | 123@126.com | 103 | zbs2 | 124@126.com | 104 | zbs2 | 124@126.com | 102 | zbs1 | 124@126.com | 105 | zbs2 | 124@126.com | 106 | zbs2 | 124@126.com | 107 | zbs2 | 124@126.com | 108 | zbs2 | 124@126.com | 10 (8 rows)

Insert two records on the main library and file them

Postgres=# select * from test Id | e_name | e_mail | zbs | 123@126.com | 103 | zbs2 | 124@126.com | 104 | zbs2 | 124@126.com | 102 | zbs1 | 124@126.com | 105 | zbs2 | 124@126.com | 106 | zbs2 | 124@126.com | 107 | zbs2 | 124@126.com | 108 | zbs2 | 124@126.com | 10 (8 rows) postgres=# insert into test values (9) 'zbs3','124@126.com',20) INSERT 0 1postgres=# insert into test values (INSERT 0 1postgres=# select pg_switch_wal ()); pg_switch_wal- 0 1postgres=# select pg_switch_wal (1 row)

Use the rsync command on M7 to synchronize the newly generated archive

[root@z_leader rsync-3.1.3] # / usr/local/bin/rsync-av / usr/local/pg/arch/ tridge@192.168.56.5::arch_bak/Password:sending incremental file list./000000060000000000000011sent 16781696 bytes received 46 bytes 4794783.43 bytes/sectotal size is 134217937 speedup is 8.00

Check whether the table data is synchronized on M1, and the 2 records inserted in the master library are successfully applied to the slave library.

Postgres=# select * from test Id | e_name | e_mail | zbs | 123@126.com | 103 | zbs2 | 124@126.com | 104 | zbs2 | 124@126.com | 102 | zbs1 | 124@126.com | 105 | zbs2 | 124@126.com | 106 | zbs2 | 124@126.com | 107 | zbs2 | 124@126.com | 108 | zbs2 | 124@126.com | 109 | zbs3 | 124@126.com | 20 10 | zbs3 | 124@126.com | 20 (10 rows)

At this point, using rsyn to transfer wal archive files to the standby server was successful, and rsync only output new files 00000006000000000011

The following experiment synchronizes the wal log file directly

M1 current wal directory

[postgres@localhost pg_wal] $pwd/usr/local/pg/data/pg_ Wal [Postgres @ localhost pg_wal] $lltotal 81948 RW-1 postgres postgres 41 Mar 2 09:24 00000002.RW-1 postgres postgres 83 Mar 2 09:24 00000003.When RW-1 postgres postgres 302 Mar 2 09:24 00000004000000000009.0028.upRW-1 postgres postgres 125 Mar 2 09:24 00000004.When postgres postgres RW-1 postgres postgres 167 Mar 2 09:24 00000005.When RW-1 postgres postgres 16777216 Mar 23 09:38 00000006000000000010Michal RW-1 postgres postgres 16777216 Mar 23 09:46 00006000000000011Ur RW-1 postgres postgres 16777216 Mar 2 09:24 00006000000000012UR RW-1 postgres postgres 16777216 Mar 17 10:05 00000006000000000013ml RW-1 postgres postgres 16777216 Mar 17 10:15 00000006000000000014 RW-1 postgres postgres 209 Mar 209: 2400000006.roomydrwx-2 postgres postgres 4096 Mar 23 09:46 archive_status

View the wal directory on M7 and output different files

[root@z_leader pg_wal] # / usr/local/bin/rsync-av / usr/local/pg/data/pg_wal/ tridge@192.168.56.5::wal_bak/Password:sending incremental file list./00000002.history00000003.history00000004.history000000040000000000000009.00000028.backup00000005.history00000006.history000000060000000000000011000000060000000000000012000000060000000000000013000000060000000000000014000000060000000000000015archive_status/archive_status/00000002.history.donearchive_status/00000004.history.donearchive_status/000000040000000000000009.00000028.backup.donearchive_status/00000006.history.donearchive_status/000000060000000000000011.donesent 33626454 bytes Received 98679 bytes 3967662.71 bytes/sectotal size is 83887007 speedup is 2.49

Now insert two more records on the main library

Postgres=# insert into test values (11 1postgres=# select zbs3); INSERT 0 1postgres=# insert into test values (12) zbs3); INSERT 0 1postgres=# select * from test Id | e_name | e_mail | zbs | 123@126.com | 103 | zbs2 | 124@126.com | 104 | zbs2 | 124@126.com | 102 | zbs1 | 124@126.com | 105 | zbs2 | 124@126.com | 106 | zbs2 | 124@126.com | 107 | zbs2 | 124@126.com | 108 | zbs2 | 124@126.com | 109 | zbs3 | 124@126.com | 20 10 | zbs3 | 124@126.com | 20 11 | zbs3 | 124@126.com | 20 12 | zbs3 | 124@126.com | 20 (12 rows)

Synchronize wal log files to M1 on M7

[root@z_leader pg_wal] # / usr/local/bin/rsync-av / usr/local/pg/data/pg_wal/ tridge@192.168.56.5::wal_bak/Password:sending incremental file list./000000060000000000000012000000060000000000000016archive_status/sent 16802460 bytes received 24649 bytes 3739357.56 bytes/sectotal size is 83887007 speedup is 4.99

Check whether the table data is synchronized on M1, and the 2 records inserted in the master library are successfully applied to the slave library.

Postgres=# select * from test Id | e_name | e_mail | zbs | 123@126.com | 103 | zbs2 | 124@126.com | 104 | zbs2 | 124@126.com | 102 | zbs1 | 124@126.com | 105 | zbs2 | 124@126.com | 106 | zbs2 | 124@126.com | 107 | zbs2 | 124@126.com | 108 | zbs2 | 124@126.com | 109 | zbs3 | 124@126.com | 20 10 | zbs3 | 124@126.com | 20 11 | zbs3 | 124@126.com | 20 12 | zbs3 | 124@126.com | 20 (12 rows)

At this point, after using rsyn to transfer wal files to the standby server, rsync will recognize the changed files and synchronize the changed parts.

Thank you for reading this article carefully. I hope the article "how to use rsync to achieve postgres log transmission standby server" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you 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.