Differences

This shows you the differences between two versions of the page.

--- postgresql_pgbackrest_configuration [2025/05/12 15:48] – created andonovj
+++ postgresql_pgbackrest_configuration [2025/05/19 06:22] (current) – [Restore] andonovj
@@ Line 1: / Line 1: @@
-https://www.scaleway.com/en/docs/tutorials/backup-postgresql-pgbackrest-s3/
+=====Overview=====
+I find pgbackrest, way more "stupid" (or maybe I am "stupid) than Barman. I am sorry, but I guess I just don't get it.
+You have a pgbackrest executable, which checks the config file, it generates the postgresql.auto.conf file and the server STILL restores till the last WAL files when you try to do PITR. But I guess I will return to this section, when I am smarter and know how to use it.
+Either case, let's get into it. Unlike Barman, pgbackrest REQUIRES passwordless connection for the backup / restore to the server and unlike Barman in REQUIRES you to archive the WAL Files to a common repo or global repo. P.S. Barman doesn't require passwordless connection for backup, as we can configure "streaming" server...LOL.
+Either case, so in general. My setup is. 1 central backup server, 1 NFS on that backup server, which is mounted on the data nodes and the data nodes push the WAL Files to that repo using pgbackrest. Simple as that.
+=====Setup=====
+After you install pgbackrest on all servers: data nodes + backup server. You can set the archive command as follows:
+<Code:bash |Setup archive & restore commands>
+archive_command: "pgbackrest --stanza=cluster_backup archive-push %p"
+restore_command: "pgbackrest --stanza=cluster_backup archive-get %f %p"
+</Code>
+After that, you can configure the /etc/pgbackrest.conf file on all servers: db nodes + backup server
+<Code:bash|Pgbackrest Configuration>
+[global]
+repo1-path=/backups
+repo1-retention-full=14
+repo1-retention-full-type=time
+repo1-host-user=pgbackrest
+archive-check=n
+process-max=2
+log-level-console=info
+log-path=/backup/pgbackrest/log
+log-level-file=debug
+start-fast=y (performs a checkpoint)
+delta=y
+[cluster_backup]
+pg1-path=/db/pgdata
+</Code>
+After that, we need to create the Stanza (Configuration of server and a backup). That is done from the backup server.
+===Create Stanza===
+<Code:bash|Create Stanza>
+pgbackrest --stanza=cluster_backup stanza-create
+</Code>
+That command will create you the stanza and then the archive command from the data nodes will work. You can also manually archive a file as follows, ran from the data node.
+<Code:Bash| Manually archive a file>
+pgbackrest --stanza=cluster_backup archive-push /db/pgdata/pg_wal/000000010000000000000001 --log-level-console=debug
+</Code>
+=====Backup=====
+Now, pgbackrest has the following backups:
+  - Full
+  - Differential (What has changed since FULL)
+  - Incremental (What has changed since FULL or Differential)
+I know, it is the opposite of Oracle, but hey, we cannot live in perfect world. If you try to make DIfferential or Incremental without Full, pgbackrest is "smart" and it will do FULL instead.
+<Code:bash|Create Full backup>
+[postgres@backup ~]$ pgbackrest --stanza=cluster_backup --type=full --log-level-console=info backup
+-05-17 04:41:41.193 P00   INFO: backup command begin 2.55.1: --no-archive-check --delta --exec-id=17762-aead3b0a --log-level-console=info --log-level-file=debug --log-path=/backups --pg1-host=node00 --pg2-host=node01 --pg3-host=node02 --pg1-host-user=postgres --pg2-host-user=postgres --pg3-host-user=postgres --pg1-path=/db/pgdata --pg2-path=/db/pgdata --pg3-path=/db/pgdata --pg1-port=5432 --pg2-port=5432 --pg3-port=5432 --process-max=2 --repo1-path=/backups --repo1-retention-full=14 --repo1-retention-full-type=time --stanza=cluster_backup --start-fast --type=full
+-05-17 04:41:42.409 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
+-05-17 04:41:42.757 P00   INFO: backup start archive = 00000003000000000000000C, lsn = 0/C000028
+-05-17 04:41:48.940 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
+-05-17 04:41:48.966 P00   INFO: backup stop archive = 00000003000000000000000C, lsn = 0/C000120
+-05-17 04:41:49.080 P00   INFO: new backup label = 20250517-044142F
+-05-17 04:41:49.139 P00   INFO: full backup size = 29.4MB, file total = 1272
+-05-17 04:41:49.140 P00   INFO: backup command end: completed successfully (7950ms)
+-05-17 04:41:49.140 P00   INFO: expire command begin 2.55.1: --exec-id=17762-aead3b0a --log-level-console=info --log-level-file=debug --log-path=/backups --repo1-path=/backups --repo1-retention-full=14 --repo1-retention-full-type=time --stanza=cluster_backup
+-05-17 04:41:49.142 P00   INFO: repo1: time-based archive retention not met - archive logs will not be expired
+-05-17 04:41:49.345 P00   INFO: expire command end: completed successfully (205ms)
+</Code>
+After that we can create an incremental or differential backup:
+<Code:bash|Create Incremental & Differential backup>
+#Incremental
+pgbackrest --stanza=cluster_backup --type=incr --log-level-console=info backup
+#Differential
+pgbackrest --stanza=cluster_backup --type=diff --log-level-console=info backup
+</Code>
+With that at hand, we can drop and delete all database:
+=====Restore=====
+Now, the restore can be:
+  - Complete
+  - PITR: Time, LSN, Checkpoint, Transactions, etc
+Don't forget that you need also the WAL Files in either case. The cluster has to be consistent after all. So make the difference between:
+  * Restore (Moving the data files from backup to the data dir)
+  * Recover (Applying the WAL Files, until we are consistent state and/or until we are told (recovery_target_time)
+These are important concepts. To restore a backup we can use the following command.
+<Code:bash| Restore latest backup>
+pgbackrest --stanza=cluster_backup restore --type=immediate
+</Code>
+Or we can restore only to a PITR (using time)
+<Code:bash| Restore to PITR>
+pgbackrest --stanza=cluster_backup \
+           --type=time --target="2025-05-16 19:58:08.384753+00" \
+           --target-action=pause restore
+</Code>
+Despite, whatever you chose, MY BIGGEST PROBLEM was that Patroni continued to apply the WAL Files, despite what I was telling him. So my HUMBLE opinion. After restore with pgbackrest, DO NOT START IT WITH PATRONI, BUT start it outside of Patroni:
+<Code:bash|Starting outside of Patroni>
+/usr/pgsql-17/bin/pg_ctl start -D /db/pgdata -w
+</Code>
+Check if that is the correct state and if you are happy. Then promote it and THEN stop it:
+<Code:bash | Promote the PostgreSQL>
+/usr/pgsql-17/bin/pg_ctl promote -D /db/pgdata -w
+</Code>
+<Code:bash | stopping outside of Patroni>
+/usr/pgsql-17/bin/pg_ctl stop -D /db/pgdata -w
+</Code>
+After that, you can start it from patroni and maybe failover:
+<Code:bash|Start within patroni>
+service patroni start
+su - postgres
+patronictl -c /etc/patroni/stampede.yml failover stampede
+</Code>
+That is how I was able to make it work. I am 99.(9)8% (Math nerds here), sure I do something wrong, but still. If you cannot get it work with patroni initially, that way works.