PostgreSQL database back up with the use of EuroDB tools

PostgreSQL database back up with the use of EuroDB tools

There are two kinds of people: those who create back up copies and those who will start creating them. In today’s article, we will take a closer look at the concept of backup and perform basic configuration of the backup database using the EuroDB backup tool.

There are two kinds of people: those who create back up copies and those who will start creating them. In today’s article, we will take a closer look at the concept of backup and perform basic configuration of the backup database using the EuroDB backup tool.

The replication mechanisms, referred to in the previous article in this series, protects data against corruption, creating a sense of data security. However, in the case of a human error or a hacker attack directed at our database, additional protection in the form of a backup is necessary.

What is a database backup?

Creating a backup copy involves creating a physical or logical map of the native database. Physical backup consists in copying file by file (bit by bit) of the database while logical backup involves copying the entire structure of the database (in particular diagrams, tables or data), and then processing it into SQL commands.

Example problem

Let’s put ourselves in the role of an administrator of the PostgreSQL database containing confidential information about the country’s armaments. One morning, we learn that the supervisor’s computer was infected with a virus allowing the hackers to access the database whose protection is our responsibility. In addition to declassifying the data, we note that the attackers renamed some records. The situation is serious, as the last backup was made a week earlier, and from the logs you can only find out that the attack took place today around 4:00.

The event described above is a hypothetical situation that should never take place in a well-secured organisation. However, let us assume that it took place and the database should be restored to the state from before the attack. For convenience, let’s ignore the problem of data secrecy.

Solving the problem using EuroDB tools

The solution to this problem may prove to be trivially simple. The EuroDB package includes a tool to physically create a copy of the database. It also has a mechanism that records all transactions that took place later. This gives you the ability to recreate the database up to any required point in time. Therefore, in the case of the problem described above, with the EuroDB replication module properly configured, it is enough to execute the barman restore command using the --target-time option. The database will be restored to a specific point in time from before the attack.

EuroDB backup module

Learn more about the tool

An important advantage of the backup module from the EuroDB package is centralisation. One central console allows you to crate backup copies and restore them from many different instances. The backup manager has a number of parameters that can be configured, such as minimum number of copies or recovery window – a date range that allows you to restore the database to a selected point in time.

The backup copy can also be restored in another location, such as a temporary server. Thanks to this, it is possible to implement a scenario where a copy is first restored on the temporary server, and only from this server a single table is selectively restored on the production server.

The manager has modules that enable both monitoring in Nagios format and management via Puppet. For more specific applications, it is equipped with the RESTful API.

Basic tool configuration

Below the basic configuration of the tool for creating backup copies is presented. The process will be conducted on virtual machines called Master and Backup.

Initial assumptions

Master – machines with the native EuroDB database – PostgreSQL version 13.3.1, IP: 192.168.121.27

Backup – the machine on which the backup will be performed. It has all the required backup packages installed; IP: 192.168.121.184

Master machine configuration

  1. Create a user called barman with administrator rights.
sudo createuser --interactive -P barman

2. Configure the pg_hba.conf and postgresql.conf files for connection to the Backup machine. By default, the file path is as follows: /var/lib/pgsql_13.3-1/data/. In the file pg_hba.conf add a line:

host all all 192.168.121.184/32 trust

And in postgresql.conf:

listen_addresses = '*'

3. Make sure that as a postgres user we have a .ssh folder and we are its owners. This is required to establish a connection between the Master machine and the Backup machine.

4. Configure the postgresql.conf file for creating backup copies.

wal_level = archive
archive_mode = on
archive_command = 'rsync -a %p [email protected]:/var/lib/barman/pgsql/incoming/%f'

Backup machine configuration

1. Assign a password and log in as the barman user.

sudo passwd barman

su - barman

2. Create a .pgpass file that allows you to connect to the database without having to enter your password manually.

echo "192.168.121.27:5432:*:barman:PASSWORD" >> ~/.pgpass

3. Change the access to the .pgpass file.

sudo chmod 600 /var/lib/barman/.pgpass

4. Configure the pgsql.conf file for creating backup copies. By default, the path to the file is as follows: /etc/barman.d/.

[pgsql]
description = "Old PostgreSQL server"
conninfo = host=192.168.121.27 user=postgresql dbname=dvdrental
ssh_command = ssh [email protected]
retention_policy = RECOVERY WINDOW OF 2 WEEKS

5. Make sure that as a barman user we have a .ssh folder and we are its owners. This is required to establish a connection between the Master machine and the Backup machine.

Connection

To establish a two-way connection, perform the same procedure on each of the machines:

1. Generate the rsa key.

Execute this command as either postgres (Master) or barman (Backup) user.

ssh-keygen -t rsa

2. Copy the key.

For the Master machine:

ssh-copy-id [email protected]

For the Backup machine:

ssh-copy-id [email protected]

In the case of the Backup machine, we also make a copy of the key to localhost:

ssh-copy-id postgres@localhost

Restarting the service

To confirm the settings we configured during setup, restart the eurodb13 service.

sudo systemctl restart eurodb13.service

Configuration validation

To confirm the correct execution of the machine configuration, we can use the following command:

barman check pgsql

If WAL archive is marked as FAILED, we can execute the following command for repair:

barman switch-xlog --force --archive pgsql

Summary

With database backups at our disposal, we are secure and ready for unforeseen situations. For convenient, cyclical and complete creation of copies of the PostgreSQL database, it is best to use proven solutions from the EuroDB package.

Authors

The blog articles are written by people from the EuroLinux team. We owe 80% of the content to our developers, the rest is prepared by the sales or marketing department. We make every effort to ensure that the content is the best in terms of content and language, but we are not infallible. If you see anything that needs to be corrected or clarified, we'd love to hear from you.