I am a developer. I write code, I interact with the database only as a user. In no case do I pretend to be a system administrator and, especially, dba. But…
It turned out that I needed to organize a backup of the postgresql database. No clouds - keep SSH and make everything work and not ask for money. What do we do in such cases? That's right, we push pgdump into cron, every day we backup everything to the archive, and if we’ve completely dispersed, we send this archive somewhere to hell.
This time, the difficulty was that according to the plans, the base was supposed to grow by about + - 100 MB per day. Of course, after a couple of weeks, the desire to backup everything with pgdump will disappear. Here incremental backups come to the rescue.
Interesting? Welcome to cat.
Incremental backup is a type of backup when not all source files are backed up, but only new and changed since the previous backup was created.Like any developer, TOTALLY not wanting (at that time) to understand the intricacies of postgres, I wanted to find a green button. Well, you know, as in AWS, DigitalOcean: clicked one button - got replication, clicked the second - set up backups, the third - it rolled back a couple of hours ago. Buttons and a beautiful GUI tool I have not found. If you know this (free or cheap) - write about it in the comments.
Googling, I found two tools pgbarman and pgbackrest . I just didn’t have a problem with the first one (very poor documentation, I tried to raise everything according to old manuals), but the second one turned out to be at the level, but not without flaws. To simplify the work for those who are faced with a similar task and this article has been written.
After reading this article, you will learn how to make incremental backups, save them to a remote server (a repository with backups) and restore them in case of data loss or other problems on the main server.
Training
To play the manual you will need two VPS. The first will be the repository (the repository on which the backups will lie), and the second, in fact, the server itself with postgres (in my case, version 11 of postgres).
It is understood that on the server with postgres you have root, sudo user, postgres user, and postgres itself is installed (postgres user is created automatically when postgresql is installed), and on the repository server there is root and sudo user (the username pgbackrest will be used in the manual) .
So that you have less problems when reproducing the instructions, in italics I write down where, by which user and with what rights I executed the command while writing and checking the article.
Install pgbackrest
Repository (user pgbackrest):
1. Download the archive with pgbackrest and transfer its contents to the / build folder:
sudo mkdir /build sudo wget -q -O - \ https://github.com/pgbackrest/pgbackrest/archive/release/2.18.tar.gz | \ sudo tar zx -C /build
2. We install the dependencies necessary for assembly:
sudo apt-get update sudo apt-get install build-essential libssl-dev libxml2-dev libperl-dev zlib1g-dev \ libpq-dev
3. We collect pgbackrest:
cd /build/pgbackrest-release-2.18/src && sudo ./configure sudo make -s -C /build/pgbackrest-release-2.18/src
4. Copy the executable file to the / usr / bin directory:
sudo cp /build/pgbackrest-release-2.18/src/pgbackrest /usr/bin sudo chmod 755 /usr/bin/pgbackrest
5. Pgbackrest requires the presence of perl. Install:
sudo apt-get install perl
6. Create directories for logs, give them specific rights:
sudo mkdir -p -m 770 /var/log/pgbackrest sudo chown pgbackrest:pgbackrest /var/log/pgbackrest sudo mkdir -p /etc/pgbackrest sudo mkdir -p /etc/pgbackrest/conf.d sudo touch /etc/pgbackrest/pgbackrest.conf sudo chmod 640 /etc/pgbackrest/pgbackrest.conf sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf
7. Check:
pgbackrest version
Postgres server (sudo user or root):
The process of installing pgbackrest on a server with postgres is similar to the process of installing on a repository (yes, pgbackrest should be on both servers), but in the 6th paragraph, the second and last command:
sudo chown pgbackrest:pgbackrest /var/log/pgbackrest sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf
replace with:
sudo chown postgres:postgres /var/log/pgbackrest sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
Configuring communication between servers via passwordless SSH
In order for pgbackrest to work correctly, you need to configure the interaction between the postgres server and the repository using the key file.
Repository (user pgbackrest):
Create a key pair:
mkdir -m 750 /home/pgbackrest/.ssh ssh-keygen -f /home/pgbackrest/.ssh/id_rsa \ -t rsa -b 4096 -N ""
Attention! We execute the above commands without sudo.
Postgres server (sudo user or root):
Create a key pair:
sudo -u postgres mkdir -m 750 -p /var/lib/postgresql/.ssh sudo -u postgres ssh-keygen -f /var/lib/postgresql/.ssh/id_rsa \ -t rsa -b 4096 -N ""
Repository (sudo user):
Copy the public key of the postgres server to the repository server:
(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' && \ echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' && \ sudo ssh root@<postgres_server_ip> cat /var/lib/postgresql/.ssh/id_rsa.pub) | \ sudo -u pgbackrest tee -a /home/pgbackrest/.ssh/authorized_keys
At this step, it will ask for the password from the root user. Enter the root password of the postgres server user!
Postgres server (sudo user):
Copy the public key of the repository to the server with postgres:
(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' && \ echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' && \ sudo ssh root@<repository_server_ip> cat /home/pgbackrest/.ssh/id_rsa.pub) | \ sudo -u postgres tee -a /var/lib/postgresql/.ssh/authorized_keys
At this step, it will ask for the password from the root user. You need to enter the root password of the repository user!
We check:
Repository (root user, for the purity of the experiment):
sudo -u pgbackrest ssh postgres@<postgres_server_ip>
Postgres server (root user, for the purity of the experiment):
sudo -u postgres ssh pgbackrest@<repository_server_ip>
We make sure that we get access without problems.
Setting up postgres server
Postgres server (sudo user or root):
1. Let’s “knock” on the postgres server from external ip. To do this, edit the postgresql.conf file (located in the / etc / postgresql / 11 / main folder), adding the line to it:
listen_addresses = '*'
If such a line already exists, either uncomment it or set the parameter value to '*'.
In the pg_hba.conf file (also located in the / etc / postgresql / 11 / main folder) add the following lines:
hostssl all all 0.0.0.0/0 md5 host all all 0.0.0.0/0 md5
Where:
hostssl/host - SSL ( ) all - all - , () 0.0.0.0/0 - md5 -
2. Make the necessary settings in postgresql.conf (it is located in the / etc / postgresql / 11 / main folder) for pgbackrest to work:
archive_command = 'pgbackrest --stanza=main archive-push %p' # main - . postgres main. archive_mode = on max_wal_senders = 3 wal_level = replica
3. Make the necessary settings in the pgbackrest configuration file (/etc/pgbackrest/pgbackrest.conf):
[main] pg1-path=/var/lib/postgresql/11/main [global] log-level-file=detail repo1-host=<repository_server_ip>
4. Restart postgresql:
sudo service postgresql restart
Configuring a repository server
Repository (pgbackrest user):
Make the necessary settings in the pgbackrest configuration file
( /etc/pgbackrest/pgbackrest.conf ):
[main] pg1-host=<postgres_server_ip> pg1-path=/var/lib/postgresql/11/main [global] repo1-path=/var/lib/pgbackrest repo1-retention-full=2 # , . .. - . start-fast=y # , https://postgrespro.ru/docs/postgrespro/9.5/continuous-archiving
Storage creation
Repository (pgbackrest user):
Create a new storage for the main cluster:
sudo mkdir -m 770 /var/lib/pgbackrest sudo chown -R pgbackrest /var/lib/pgbackrest/ sudo -u pgbackrest pgbackrest --stanza=main stanza-create
Check
Postgres server (sudo user or root):
Check on the postgres server:
sudo -u postgres pgbackrest --stanza=main --log-level-console=info check
Repository (pgbackrest user):
Check on the repository server:
sudo -u pgbackrest pgbackrest --stanza=main --log-level-console=info check
We make sure that in the output we see the line “check command end: completed successfully”.
Are you tired? We pass to the most interesting.
Make backup
Repository (pgbackrest user):
1. We carry out backup:
sudo -u pgbackrest pgbackrest --stanza=main backup
2. We make sure that the backup was created:
ls /var/lib/pgbackrest/backup/main/
Pgbackrest will create the first full backup. If you wish, you can run the backup command again and make sure that the system creates an incremental backup.
If you want to make a full backup again, then specify an additional flag:
sudo -u pgbackrest pgbackrest --stanza=main --type=full backup
If you want detailed output to the console, then also specify:
sudo -u pgbackrest pgbackrest --stanza=main --type=full --log-level-console=info backup
Restore backup
Postgres server (sudo user or root):
1. Stop the working cluster:
sudo pg_ctlcluster 11 main stop
2. We recover from the backup:
sudo -u postgres pgbackrest --stanza=main --delta restore
3. Run the cluster:
sudo pg_ctlcluster 11 main start
After restoring the backup, we need to perform a second backup:
Repository (pgbackrest user):
sudo pgbackrest --stanza=main backup
That's all. In conclusion, I want to remind you that in no case am I trying to build myself a senior dba and, at the slightest opportunity, I will use clouds. Currently, I myself am starting to study various topics such as backup, replication, monitoring, etc. and I write small reports on the results in order to make a small contribution to the community and leave small cribs for myself.
In the following articles I will try to talk about additional features - data recovery on a clean cluster, encryption of backups and publication on S3, backups via rsync.