Aleph Technologies Blog

When a postgresql standby gets out of sync and the replication lag is relatively big, the usual solution is to clone the primary database again, to avoid the slow proess of re-applying all archived WALs that are lagging.

However, a complete clone of a large database could be long. As I wondered the other time how I could do the same technique as for Oracle databases: identify the current SCN on the physical standby, create an incremental RMAN backup from that SCN on the primary and restore it on the standby.

I then realized that wal-g has a feature that does exactlyu that: an incremental backup from a LSN. Here’s an example illustrating this scenario with a 3-node patroni cluster having 2 out-of-sync replicas.

Cluster: node (6733556523882578031) -------+----+-----------+
| Member    |      Host   |   Role  |  State  | TL | Lag in MB |
+--------------------------+---------------+---------+---------+----+-----------+
| node-0    | 10.1.66.140 |  Leader | running | 36 |           |
| node-1    |  10.1.64.94 | Replica | running | 34 |       593 |
| node-2    | 10.1.65.121 | Replica | running | 34 |     53457 |
+--------------------------+---------------+---------+---------+----+-----------+

As you can see, node 2 is quite out of sync. Let’s see how the catchup feature works. We first connect to node 2 and prepare the node.

root@node-2:~# sv patroni stop
root@node-2:~# su postgres
postgres@node-2:~$ pg_ctl -D /var/pgdata/ stop

Now we have to identify the point from which have to restore.

postgres@node-2:~$ pg_controldata /var/pgdata/ |grep "Minimum recovery ending location"
Minimum recovery ending location:     185C/4E00FD88

Great. Now we need to convert that to integer.

postgres@node-0:~$ psql -tA -c "select (pg_lsn('185C/4E00FD88') - pg_lsn('0/0'))::bigint;"
26784724745608

And we’ll use that LSN to push an incremental backup from the primary node.

postgres@node-0:~$ export PGHOST=/run/postgresql
postgres@node-0:~$ wal-g catchup-push /var/pgdata/ --from-lsn 26784724745608
INFO: 2021/02/06 09:21:57.974516 Calling pg_start_backup()
INFO: 2021/02/06 09:22:03.378569 Walking ...
INFO: 2021/02/06 09:22:03.378982 Starting part 1 ...
INFO: 2021/02/06 09:22:03.383200 Starting part 2 ...
INFO: 2021/02/06 09:22:03.383312 Starting part 3 ...
INFO: 2021/02/06 09:22:03.383395 Starting part 4 ...
INFO: 2021/02/06 09:32:57.889956 Finished writing part 1
...

We then just need to grab the backup label and use it to restore the catchup on the standby node 2. We obviously assume the backup destination is shared across all nodes and that wal-g is configured properly.

postgres@node-2:~$ wal-g catchup-fetch /var/pgdata/ base_000000240000186800000056
INFO: 2021/02/06 09:40:12.707303 Finished decompression of part_003.tar.lz4
INFO: 2021/02/06 09:40:12.710038 Finished extraction of part_003.tar.lz4
INFO: 2021/02/06 09:40:13.682607 Finished extraction of part_001.tar.lz4
INFO: 2021/02/06 09:40:13.682670 Finished decompression of part_001.tar.lz4
INFO: 2021/02/06 09:40:22.338425 Finished extraction of part_004.tar.lz4
INFO: 2021/02/06 09:40:22.338503 Finished decompression of part_004.tar.lz4

At this point we just need to start postgres and patroni.

postgres@node-2:~$ pg_ctl -D /var/pgdata/ start
waiting for server to start….2021-02-02 09:42:27 UTC [21707]: [1-1] 60191e82.54cb 0 LOG: Auto detecting pg_stat_kcache.linux_hz parameter…
2021-02-02 09:42:27 UTC [21707]: [2-1] 60191e82.54cb 0 LOG: pg_stat_kcache.linux_hz is set to 1000000
2021-02-02 09:42:27 UTC [21707]: [3-1] 60191e82.54cb 0 LOG: listening on IPv4 address "0.0.0.0", port 5432
2021-02-02 09:42:27 UTC [21707]: [4-1] 60191e82.54cb 0 LOG: listening on IPv6 address "::", port 5432
2021-02-02 09:42:27 UTC [21707]: [5-1] 60191e82.54cb 0 LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-02-02 09:42:27 UTC [21707]: [6-1] 60191e82.54cb 0 LOG: redirecting log output to logging collector processog".
….. done
server started
postgres@node-2:~$ exit
root@node-2:~# sv start patroni
ok: run: patroni: (pid 23662) 0s
root@node-2:~# patronictl list

Cluster: node (6733556523882578031) -------+----+-----------+
| Member    |      Host   |   Role  |  State  | TL | Lag in MB |
+--------------------------+---------------+---------+---------+----+-----------+
| node-0    | 10.1.66.140 |  Leader | running | 36 |           |
| node-1    |  10.1.64.94 | Replica | running | 34 |         0 |
| node-2    | 10.1.65.121 | Replica | running | 34 |         0 |
+--------------------------+---------------+---------+---------+----+-----------+

This process saved a lot of time and avoided the full replica clone that could be initiated with a patronictl reinit.
Overall, a very simple and handy feature !