CYBERTEC Logo

Remote Backup and Restore with pgBackRest

09.2019 / Category: / Tags: | |

In my previous post about pgBackRest, we saw how to install and setup pgBackRest and make a backup of a PostgreSQL database with it. It was a very basic single server setup,  only intended to get the hang of the tool. Such setups are not used in a production environment, as it is not recommended (or rather does not serve the purpose) to perform the backup on the same server where the database is running. So: let's get familiar with how remote backup servers are set up with pgBackRest, and how a full and incremental backup is performed from the backup server and restored on the database server.

We need two servers. Let's call ours:

  1. pgbackup
  2. db1

 

Installing pgbackrest:

We need to install pgBackRest on the database and the backup server. Make sure you install the same version on both.

 

For the database server, please follow the installation steps from my previous post. The steps are slightly different for the backup server, since it is a better practice to create a separate user to own the pgBackRest repository.

 

Create a pgbackrest user on the backup server

 

 

Install required Perl package and pgBackRest from a package or manually on pgbackup as below

 

 

Create pgBackRest configuration files, directories and repository on pgbackup

 

 

Now we are ready to proceed with enabling communication between the database and the backup server. For that, pgBackRest requires a passwordless SSH connection.

We can do that by generating an SSH authentication key file using the

command.

 

On the pgbackup server as pgbackrest user:

On db1 as postgres user:

Exchange the public keys generated between the servers

On pgbackup:

 

On db1:

 

Test the passwordless connection as follows:

 

You can find more details about passwordless SSH connection here.

 

Configuration

On the pgbackup server, configure the pgBackRest configuration file with the database host and path, along with the repository path where the backups will be stored. We have added the database host as 'pg1-host' so that we can add more database hosts later as 'pg2-host' and so on. The same goes for pg1-path.

The Start Fast Option (--start-fast) has been used to force a checkpoint to start the backup quickly. Otherwise, the backup will start after the next regular checkpoint.

 

On server db1, configure the pgBackRest configuration file with the database path and backup host as follows:

 

On db1, update postgresql.conf to have the following changes:

 

Now restart PostgreSQL to reflect the configuration changes.

 

Create a stanza on the pgbackup server and check if it's working. Getting no result means the stanza has been created successfully.

 

Check if the stanza configuration is correct on db1:

Remote Backup

With the setup ready, let's take our first remote backup from pgbackup. The default backup is incremental, but the first backup is always a full backup.

 

 

You will find directories and files being created in the backup location.

 

 

Restore

Taking a full backup was easy. Let's see some examples of incremental backup and recovery with a target:

 

Create a test database on db1:

 

Make another backup. By default, this will be incremental since it's the second backup and we are not specifying the type:

 

 

To restore, stop PostgreSQL on db1 and run pgbackrest with the restore command:

 

 

If you check the database now, you will not find the test database. This is because the backup was restored from the first full backup.

 

To restore the data up to the incremental backup, run the restore command with recovery_target in --recovery-option.

 

Let's check the database now:

 

I hope this exercise could help to get you started with remote backup and pgBackRest! To learn more commands, please visit the pgBackRest official site here.

 

Stay tuned for more tutorials and exercises with pgBackRest. If you are not sure how to use PostgreSQL efficiently or how to setup your systems in general consider checking out our support services.


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

0 0 votes
Article Rating
Subscribe
Notify of
guest
6 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Sujeet
Sujeet
2 years ago

Nice article. Just one question where do I setup async-archiving, on DB server or repository server?

Chatragadda martin raj kumar
Chatragadda martin raj kumar
3 years ago

I am not able to restore one backup to another new servers.
i get error "invalid backupset"

Chatragadda martin raj kumar
Chatragadda martin raj kumar
3 years ago

Requesting to post a solution on restores on remote nodes .
say backup on hots1 and restore the same backup on host2,host3..hostn..
I get error like "invalid backup set"

jojo roxas
jojo roxas
4 years ago

May i know what is the syntax to backup only 1 database using pgbackrest command? Tnx

Granthana Biswas
Granthana Biswas
4 years ago
Reply to  jojo roxas

Since it's works on the same principle as pg_basebackup, it doesn't backup individual database but entire cluster. However, you can restore just a selected database with pgBackRest https://pgbackrest.org/user-guide.html#restore/option-db-include

Muhammed Ali
Muhammed Ali
4 years ago

Great Article.
I have some questions regarding pgbackrest.
1: can i restore the full backup on another machine using Pgbackrest restore command?
2: PITR is functioning properly when i dropped a table and restored it( with differential backup) but when i apply incremental backup and then drop table, the table isn't restored.
3: If my data resides in different table spaces, is it going to be a problem when performing backups?

Thanks Granthana Biswas

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram