What we are going to cover in this series is the postgresql standby / replication database configuration. Since there are more than one way to configure the standby / replication option, this may be confusing to setup a fully functional replication database so i will cover starting from the ancient ruins of the replication and cover the every feature in the upcoming posts within the same series.
First, there was log shipping. Log shipping is the most basic replication method which relies on the transaction logs (wals). Almost every transactional database supports log shipping in their own recipe. Transaction logs are not the subject of this post, so a basic understanding of how and why transaction logs (or wals in postgresql world which stands for write-ahead-logs) works would be a nice to have asset before start following these series.
In this first post of the series, I am going to configure this basic implementation in postgresql version 12.4. The idea is simple, backup the database, restore it to a new host and continuously recover the generated transaction logs from the primary database.
There are some prerequisites for the sake of the successful configuration.
First primary database should be in achive_mode=on. You can follow the necessary actions from one of my previous blogs archive_mode on postgresql 12.4 .
And i also setup an ssh user equivalency between the nodes, you can also follow the SSH user equivalency post.
Set the archive_command on the primary database correctly to be sure that wal files are shipping to the standby node.
Shipping wal archives to the standby database
If you have followed the previous posts you should have an archive_mode=on and password-less ssh connectivity between the servers. You need to add a configuration on top of archive_mode as follows;
archive_command = 'cp %p /var/lib/pgsql/12/wal_archive/%f; scp %p 192.168.56.103:/var/lib/pgsql/12/wal_primary/%f'So, archive_command not only archive the wal files but also ship them to the standby server. Lets check, if archived wals are also exist on the standby node.
-bash-4.2$ cd wal_primary/
-bash-4.2$ ls -l
total 32768
-rw------- 1 postgres postgres 16777216 Jan 25 15:50 000000010000000000000020
-rw------- 1 postgres postgres 16777216 Jan 25 18:59 000000010000000000000021
-bash-4.2$ pwd
/var/lib/pgsql/12/wal_primary
-bash-4.2$ And yes, they are shipping.
First thing is first, backup the primary database
-bash-4.2$ pg_basebackup -D /var/lib/pgsql/12/backups -Ft -Xn -z -v -l "base_backup_nowal_noreco"
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/17000028 on timeline 1
pg_basebackup: write-ahead log end point: 0/17000100
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
-bash-4.2$ Official backup tool of the postgresql database is the pg_basebackup. In this series pg_basebackup will be our primary tool to consistently backup the database. Since this is not a backup/restore centric post, i will not cover all the parameter of he pg_basebackup but the options i used are as follows.
-Ft : -F option defines the format of the backup file. Here i used "t" which stands for tar format.
-Xn : -X option tells the pg_basebackup how to handle wal archives. I used "n" which stands for none. So I will not handle wal archives.
-z : whether the pg_basebackup will compress the tar archive.
-v : verbose for detailed log output.
-l : backup label in order to indentify our backups in case.
Copy the backup file to our standby node
We need to move the backup file to the standby node in order to restore it. First, clean the $PGDATA directory on the remote node. In my installation, $PGDATA is /var/lib/pgsql/12/data which is the default. All the files under the data directory will be restored from the primary database. As we used the compressed option with the tar format. We are going to uncompress the file and un-tar it on the destination.
-bash-4.2$ scp base.tar.gz postgres@192.168.56.103:/var/lib/pgsql/12/backups/.
base.tar.gz 100% 3987KB 87.5MB/s 00:00
-bash-4.2$
-bash-4.2$ cd /var/lib/pgsql/12/backups
-bash-4.2$ gunzip base.tar.gz
-bash-4.2$ tar -xvf base.tar -C /var/lib/pgsql/12/data
-bash-4.2$ cd /var/lib/pgsql/12/dataNow we restored our primary database, the last part is the "continuously recover the transaction logs"
First we need to inform the postgresql to not to recover and open the database, instead wait for the new wal files to continuously recover them. For this, we need to create the standby.signal file under the $PGDATA directory. Postgresql will check the existence of this file before opening the database and instead of opening the database and understands the role as standby.
touch $PGDATA/standby.signalOK. If we intend to start our database we will most probably get the following warning which quite self explanatory.
2021-01-25 15:29:30.612 +03 [2999] WARNING: specified neither primary_conninfo nor restore_command
2021-01-25 15:29:30.612 +03 [2999] HINT: The database server will regularly poll the pg_wal subdirectory to check for files placed there.Postgresql simply complains about the wal files and how to find them. I will cover primary_conninfo parameter in the next part of the series. But we should set the restore_command. Simply set the parameter within the $PGDATA/postgresql.conf file. Remember archive_command setting on the primary. restore_command on the standby should resemble it.
restore_command = 'cp /var/lib/pgsql/12/wal_primary/%f %p'Before starting the postgresql database, i will tail the $PGHOME/log/<log_file_name>.log file from a different session to check what is going on.
# monitoring session
tail -100f $PGHOME/log/postgresql-Mon.log
# ops session
pg_ctl start
# and in monitoring session
2021-01-25 20:45:29.635 +03 [3302] LOG: database system is ready to accept read only connections
cp: cannot stat ‘/var/lib/pgsql/12/wal_primary/000000010000000000000024’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/12/wal_primary/00000002.history’: No such file or directory
cp: cannot statLets test out standby server by creating a table on the primary and check if the table will be created on the standby automagically. I will also explicitly checkpoint the primary and switch the wal file since this is a test system and there is not any transactions going on.
# on the primary
postgres=# \d
Did not find any relations.
postgres=# create table test (col1 text);
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/25019FF0
(1 row)
postgres=# After I switch the wal on the primary, standby log file applies the new wal, as it is received from the primary, thanks to our archive_command.
2021-01-25 22:30:26.007 +03 [4008] LOG: restored log file "000000010000000000000025" from archive
cp: cannot stat ‘/var/lib/pgsql/12/wal_primary/000000010000000000000026’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/12/wal_primary/00000002.history’: No such file or directoryOk it seems the new wal file is restored and applied on the standby. But what about our newly created table ?
-bash-4.2$ psql
psql (12.4)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
prod | ergemp | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \d
Did not find any relations.
postgres=# \d
Did not find any relations.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
postgres=#
There it is. And the log file indicated, standby database waits for the next wal to apply. What about the history file that could not be found. We will come that later. But first we are going the switch our standby database to a primary. There are two ways to achieve this;
execute pg_ctl promote command from the command line.
create a trigger file with the same name as the configuration indicates. which is null as default. #promote_trigger_file = '' # file name whose presence ends recovery
-bash-4.2$ pg_ctl promote
waiting for server to promote.... done
server promoted
# and on the log files
2021-01-25 22:38:21.291 +03 [4008] LOG: received promote request
2021-01-25 22:38:21.291 +03 [4008] LOG: redo done at 0/25019FD8
2021-01-25 22:38:21.291 +03 [4008] LOG: last completed transaction was at log time 2021-01-25 22:30:03.129765+03
2021-01-25 22:38:21.311 +03 [4008] LOG: restored log file "000000010000000000000025" from archive
cp: cannot stat ‘/var/lib/pgsql/12/wal_primary/00000002.history’: No such file or directory
2021-01-25 22:38:21.326 +03 [4008] LOG: selected new timeline ID: 2
2021-01-25 22:38:21.345 +03 [4008] LOG: archive recovery complete
cp: cannot stat ‘/var/lib/pgsql/12/wal_primary/00000001.history’: No such file or directory
2021-01-25 22:38:21.354 +03 [4006] LOG: database system is ready to accept connectionsAbout the history file
History files simply holds the incarnation information of the database. Every startup operation after a point in time recovery creates new time line (incarnation) and the information of the new timeline information holds in the history files. A brief summary can bu found in this link.
So, after the promotion we should see a history file as opened our database and new timeline should be created right?
-bash-4.2$ ls -l
total 16388
-rw------- 1 postgres postgres 16777216 Jan 25 22:44 000000020000000000000026
-rw------- 1 postgres postgres 42 Jan 25 22:38 00000002.history
-bash-4.2$ pwd
/var/lib/pgsql/12/wal_archive
-bash-4.2$ Yes, there is a history file. And please consider the 000000020000000000000026 wal name. it starts with 2 instead of 1 which is the case in primary. So this is the 2nd incarnation (timeline).
Monitoring
Monitoring features is not as rich as the streaming replication since primary database only archives the wal files and does not know the rest of the operation. So monitoring the archival process is crucial.
On the standby database pg_is_in_recovery() functions returns true, if the recovery works fine. pg_last_wal_replay_lsn() function also returns the last replayed lsn. You can also check the database log file for the ongoing continuous recover operation.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=# select pg_is_in_recovery(),pg_is_wal_replay_paused(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();
pg_is_in_recovery | pg_is_wal_replay_paused | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
-------------------+-------------------------+-------------------------+------------------------+-------------------------------
t | f | | 0/2E000000 |
(1 row)
postgres=# \x
Expanded display is on.
postgres=# select pg_is_in_recovery(),pg_is_wal_replay_paused(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();
-[ RECORD 1 ]-----------------+-----------
pg_is_in_recovery | t
pg_is_wal_replay_paused | f
pg_last_wal_receive_lsn |
pg_last_wal_replay_lsn | 0/2E000000
pg_last_xact_replay_timestamp |
postgres=# Pros and Cons
Since this recipe of replication is the most basic one, monitoring options are less and you always bounded to the archival of the wal files. This also means, recover operation based on the wal files only.
Part 2 of this series will cover the streaming replication. In log shipping type of replication we need to wal file to be filled and / or switched in order to be shipped to standby and be applied. as the default value of the archive_timeout is 15 minutes, our standby database may lag and RTO may increase.
Comments