In order for patroni and pgbackrest to work together, pgbackrest has to be installed
first on pgbr and set as a backup server.
Install pgbackrest in all database servers and pgbr should be accessed without a
password with the postgres user
For pgbackrest installation steps, you can review the chapter 11.
A backup is taken from the primary server, namely pgdb1, with pgbackrest to the pgbr
backup server.
If the role changes (Failover/switchover) i.e., standby primary, if the primary server
is standby, it automatically detects the new primary server and does the backup and
archiving of WAL files from the new primary.
Step 1 (creation of public keys)
Password less ssh access must be enabled on all servers.
# pgdb1 ve pgdb2 de tek tek çalıştırılır. ssh-keygen -t rsa #ls ~/.ssh dizininde id_rsa.pub varsa bu adıma gerek yok ssh-copy-id pgbr
In pgbr
ssh-keygen -t rsa #ls ~/.ssh dizininde id_rsa.pub varsa bu adıma gerek yok ssh-copy-id pgdb1 ssh-copy-id pgdb2
Step 2
If not done before, the following postgres parameters should be set on all nodes. (erp
name is an example.)
patronictl -c /etc/patroni/config.yml edit-config # Aşağıdaki satırlar eklenir. archive_mode: ‘on’ archive_command: ‘pgbackrest --stanza=erp archive-push %p’ patronictl -c /etc/patroni/config.yml reload erp patronictl -c /etc/patroni/config.yml restart erp
Step 3
sudo mkdir -p -m 770 /var/log/pgbackrest sudo chown postgres:postgres /var/log/pgbackrest sudo mkdir -p /etc/pgbackrest sudo touch /etc/pgbackrest/pgbackrest.conf sudo chmod 640 /etc/pgbackrest/pgbackrest.conf sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
The above files and directories are created on all servers as sudo user or with root.
Step 4
pgbrvim /etc/pgbackrest/pgbackrest.conf
[erp] pg1-path=/pg/data/13/main pg1-host=pgdb1 pg1-host-user=postgres pg2-path=/pg/data/13/main pg2-host=pgdb2 pg2-host-user=postgres [global] # Aşağıdaki parametreler için bölüm 11’e bakabilirsiniz. repo1-path=/RA/pgbackrest repo1-retention-full=2 process-max=4 log-level-console=info start-fast=y lock-path=/var/tmp/pgbackrest [global:archive-push] compress-level=3
Backup server (repository) i.e., in pgbr /etc/pgbackrest/pgbackrest.conf is
edited.
Step 5
In all database servers (pgdb1 and pgdb2…)
pgdb1
Edit /etc/pgbackrest/pgbackrest.conf .
[erp] pg1-path=/pg/data/13/main pg1-socket-path=/var/run/postgresql lock-path=/var/tmp/pgbackrest [global] log-level-file=detail repo1-host=pgbr repo1-host-user=postgres
pgdb2
Edit /etc/pgbackrest/pgbackrest.conf .
[erp] pg1-path=/pg/data/13/main pg1-socket-path=/var/run/postgresql lock-path=/var/tmp/pgbackrest [global] log-level-file=detail repo1-host=pgbr repo1-host-user=postgres
Step 6
The pgbackrest stanza is created with the following command on the pgbackrest
backup repository server (pgbr).
pgbackrest --stanza=erp --log-level-console=info stanza-create
The pgbackrest configuration is checked on each of the Postgres servers. In Primary,
the output will be similar to the following as it is primary and used for backup.
pgbackrest --log-level-console=info --stanza=erp check 17:54:16.564 P00 INFO: check command begin 2.40: --exec-id=18778- bb0c78d2 --log-level-console=info --log-level-file=detail --pg1-path=/ pg/data/13/main --pg1-socket-path=/var/run/postgresql --repo1-host=pgbr --repo1-host-user=postgres --stanza=erp 17:54:17.183 P00 INFO: check repo1 configuration (primary) 17:54:17.688 P00 INFO: check repo1 archive for WAL (primary) 17:54:18.097 P00 INFO: WAL segment 000000060000000000000015 successfully archived to ‘/RA/pgbackrest/archive/erp/13- 1/0000000600000000/000000060000000000000015-aa86ba3d627b4f3c1e60fe82bd0 655363b0c76d4.gz’ on repo1 17:54:18.198 P00 INFO: check command end: completed successfully (1637ms)
In standby servers, the output of the check command will be as follows.
pgbackrest --log-level-console=info --stanza=erp check INFO: check command begin 2.40: --exec-id=20770-49a7bd0b --loglevel- console=info --log-level-file=detail --pg1-path=/pg/ data/13/main --pg1-socket-path=/var/run/postgresql --repo1- host=pgbr --repo1-host-user=postgres --stanza=erp 19:27:10.363 P00 INFO: check repo1 (standby) 19:27:10.690 P00 INFO: switch wal not performed because this is a standby 2022-09-02 19:27:10.793 P00 INFO: check command end: completed successfully (1112ms)
In the pgbackrest backup repository server, pgbackrest backup is created with the
following command.
pgbackrest --log-level-console=info --stanza=erp backup
Note: Make sure pgbackrest archive-timeout > Postgres archive_
timeout and checkpoint_timeout
Restore to a Different Server with Pgbackrest
To get a copy from the live environment to the test database server
Step 1: Stop the postgresql cluster on the test database server and delete the pg_data
folder if it already exists.
Step 2: Pgbackrest is installed on the test database server (installation steps are
available in the backup section).
The following commands are run on the test database server.
pgbackrest --log-level-console=info --log-level-file=info --archive-mode=off \ --repo1-path=/RA/pgbackrest --repo1-host=pgbr --repo1-hostuser= postgres \ --stanza=erp --pg1-path=/var/lib/postgresql/13/main restore
Note: The archive-mode of the test database should be turned off. Password less ssh
access must be enabled between the pgbackrest repository server and the test server.
- repo1-path: Pgbackrest backup directory
- repo1-host: Pgbackrest backup server (repository) IP or hostname
- repo-host-user: Postgres user for login
- pg1-path: $PGDATA directory where new server or test server will be
restored
Point-in-time Recovery:
Below are the steps to restore the test database to a specific time.
Step 1: Determine the specific time, the test database will be restored.
Step 2: Pgbackrest is installed and restored with the following command
pgbackrest --log-level-console=info --log-level-file=info --archive-mode=off \ --repo1-path=/RA/pgbackrest --repo1-host=pgbr --repo1-hostuser= postgres \ --stanza=erp --pg1-path=/var/lib/postgresql/13/main --type=time --target=”2022 13:58:00” restore