Pgbackrest Restore and Restore Delta

Hello, I will tell you about restore and restore delta on pgbackrest.

The normal restore command returns to the data path you specified. Restore –delta, on the other hand, restores the data path you gave from scratch on a different home.

All operations must be done with the postgres user.

cat /etc/pgbackrest.conf

pgbackrest info

pgbackrest archive-get --stanza=testdb

After doing the pre-run checks, we should get the –stanza name from pgbackrest.conf.
In addition, before the backup-restore we made, the existing DB can be backed up as dumpall.

pg_dump  testdb -f backup.sql
pg_dumpall -f backup_all.sql

It can be used for post-restore control by creating a table.

Even if the DATA folder cannot be completely backed up, files such as postgres.conf and pg_hba MUST be backed up.


mv /postgres/data /postgres/data_old

mkdir /postgres/data

pg_ctl stop -D /postgres/data

pgbackrest --stanza=testdb-int --log-level-console=info --type=time "--target=2021-07-07 08:00:00.024378+01" --db-path=/postgres/data2/ restore

The pg_hba.conf content is updated after restoration.

mv /postgres/data/pg_hba.conf /postgres/data/pg_hba_07072021.conf

cp /postgres/data_07072021/pg_hba.conf /postgres/data/pg_hba.conf

pg_ctl start -D /postgres/data
pg_ctl reload -D /postgres/data



We are returning the database on a different path

pgbackrest --stanza=testdb --log-level-console=info --type=time "--target=2021-07-07 08:00:00.024378+01" --db-path=/postgres/data2 restore --delta

We are changing the port via postgresql.conf. We output dumps on a table basis via the new instance.

pg_dump -d testdb -p 5432 -n public test -f backup_all.sql

Let’s not forget to delete the database we created later.




About tayfun inam

Computer Engineer -PostgreSql-Oracle Database Administrator I am about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 8+years experience.I have OCA,PostgreSql senior Certificates I have worked 50+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.


  1. Hi,

    The are some pretty big errors here. Please correct the following:

    Delta restores-

    Delta restores do NOT require a separate db-path (deprecated var, please use the Official, current var; –pg1-path).

    Doing a delta restore to ANY alternate location that does not possess the same checksums that were present during the backup is pointless. Delta restores use checksums to determine what is still present, and what needs to be restored.

    Postgres user-

    You do NOT have to do your backup operations as the postgres user. To the contrary, it’s advised to use a separate user for this. If you have a postgres cluster on the same node as your pgBackRest repo, you can always add the actual postgres OS user to whatever OS group your backup user is placed in. That way the postgres OS user isn’t able to do unwanted things with the repository.

    Please let me know if anything above is unclear.


  2. This article is practically completely incorrect.
    I’d recommend just following the standard pgBackRest docs

Leave a Reply

Your email address will not be published. Required fields are marked *