Disclaimer
I am a developer. I write code and interact with the database only as a user. I do not aspire to be a system administrator, nor a DBA. Butā¦
It just so happened that I needed to set up a backup for the PostgreSQL database. No cloudsājust keep SSH and make sure everything works without asking for money. What do we do in such cases? Right, we shove pg_dump into cron, back everything up to an archive every day, and if things go really awry, we send that archive somewhere far away.
This time, the challenge was that the database was expected to grow by about 100 MB a day. Naturally, after a couple of weeks, the desire to back everything up with pg_dump would disappear. Here's where incremental backups come to the rescue.
Interested? Welcome under the cut.
An incremental backup is a type of backup where not all source files are copied, but only the new and modified ones since the last copy was created.
Like any developer who did NOT want to dive into the intricacies of Postgres at that time, I wanted to find a green button. You know, like in AWS, DigitalOcean: press one buttonāget replication, press anotherāset up backups, press a thirdārollback a couple of hours ago. I didnāt find such a button or a nice GUI tool. If you know of one (free or inexpensive), please share it in the comments.
After a bit of Googling, I found two tools. pgbarman and pgbackrest. I simply couldn't get the first one to work (very sparse documentation, I tried to set everything up using old manuals), but the documentation for the second was at a decent level, though not without flaws. This article was written to simplify the work for those who encounter a similar task.
By the end of this article, you will learn how to create incremental backups, store them on a remote server (a backup repository), and restore them in case of data loss or other issues on the main server.
Preparation
To replicate the manual, you will need two VPS. The first will be the storage (repository where the backups will be kept), and the second will be the server with PostgreSQL (in my case, PostgreSQL version 11).
It is assumed that on the server with postgres you have a root, sudo user, the postgres user, and postgres itself installed (the postgres user is automatically created when installing postgresql), and on the repository server, there is a root and a sudo user (the username pgbackrest will be used in the manual).
To help reduce issues when following the instructions ā I will indicate in italics where, with which user, and with what permissions I executed the command while writing and reviewing the article.
Installing pgbackrest
Repository (user pgbackrest):
1. Download the pgbackrest archive and move 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 /build2. Install the necessary dependencies for building:
sudo apt-get update
sudo apt-get install build-essential libssl-dev libxml2-dev libperl-dev zlib1g-dev
libpq-dev3. Build pgbackrest:
cd /build/pgbackrest-release-2.18/src && sudo ./configure
sudo make -s -C /build/pgbackrest-release-2.18/src4. 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/pgbackrest5. Pgbackrest requires perl. Install it:
sudo apt-get install perl6. Create directories for logs and set their permissions:
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.conf7. Check:
pgbackrest versionPostgres server (sudo user or root):
The installation process of pgbackrest on the postgres server is similar to the installation process on the repository (yes, pgbackrest should be installed on both servers), but in step 6, the second and last commands:
sudo chown pgbackrest:pgbackrest /var/log/pgbackrest
sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.confshould be replaced with:
sudo chown postgres:postgres /var/log/pgbackrest
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.confSetting up communication between servers via passwordless SSH
For pgbackrest to work correctly, it is necessary to configure the communication between the postgres server and the repository using a 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! The above commands should be executed 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 from 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@ cat /var/lib/postgresql/.ssh/id_rsa.pub) |
sudo -u pgbackrest tee -a /home/pgbackrest/.ssh/authorized_keysAt this step, it will ask for the root user's password. You need to enter the root password of the PostgreSQL server!
Postgres server (sudo user):
Copy the repository's public key to the server with PostgreSQL:
(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' &&
echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' &&
sudo ssh root@ 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 root user's password. You need to enter the root password of the repository!
Checking:
Repository (root user, for the sake of the experiment):
sudo -u pgbackrest ssh postgres@Postgres server (root user, for the sake of the experiment):
sudo -u postgres ssh pgbackrest@Make sure we can access without any issues.
Configuring the PostgreSQL server
Postgres server (sudo user or root):
1. Allow external IPs to connect to the PostgreSQL server. To do this, edit the file postgresql.conf (located in the /etc/postgresql/11/main folder), adding the following line:
listen_addresses = '*'If such a line already exists, either uncomment it or set the parameter value to '*'.
In the file pg_hba.conf (also located in the folder /etc/postgresql/11/main) add the following lines:
hostssl all all 0.0.0.0/0 md5
host all all 0.0.0.0/0 md5where:
hostssl/host - connect via SSL (or not)
all - allow connection to all databases
all - username allowed to connect (everyone)
0.0.0.0/0 - network mask from which connections are allowed
md5 - password encryption method2. Make the necessary configurations in postgresql.conf (located in the folder /etc/postgresql/11/main) for pgbackrest to work:
archive_command = 'pgbackrest --stanza=main archive-push %p' # Where main is the cluster name. When PostgreSQL is installed, it automatically creates the main cluster.
archive_mode = on
max_wal_senders = 3
wal_level = replica3. 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=4. Restart PostgreSQL:
sudo service postgresql restartConfiguring the repository server
Repository (pgbackrest user):
Make the necessary configurations in the configuration file pgbackrest
(/etc/pgbackrest/pgbackrest.conf):
[main]
pg1-host=
pg1-path=/var/lib/postgresql/11/main
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2 # Parameter indicating how many full backups to keep. That is, if you have two full backups and create a third one, the first two will be deleted along with the increments.
start-fast=y # Starts the backup immediately, you can read about this parameter here https://postgrespro.ru/docs/postgrespro/9.5/continuous-archivingCreating a Repository
Repository (pgbackrest user):
We are creating a new repository for the cluster main:
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 checkRepository (pgbackrest user):
Check on the repository server:
sudo -u pgbackrest pgbackrest --stanza=main --log-level-console=info checkEnsure that we see the line "check command end: completed successfully" in the output.
Tired? Let's move on to the most interesting part.
Creating a Backup
Repository (pgbackrest user):
1. Perform the backup:
sudo -u pgbackrest pgbackrest --stanza=main backup
2. Ensure that the backup was created:
ls /var/lib/pgbackrest/backup/main/Pgbackrest will create the first full backup. If desired, you can run the backup command again and ensure that the system creates an incremental backup.
If you want to redo the full backup, you should specify an additional flag:
sudo -u pgbackrest pgbackrest --stanza=main --type=full backupIf you want detailed output in the console, also specify:
sudo -u pgbackrest pgbackrest --stanza=main --type=full --log-level-console=info backupRestoring a Backup
Postgres server (sudo user or root):
1. Stop the running cluster:
sudo pg_ctlcluster 11 main stop2. Restore from the backup:
sudo -u postgres pgbackrest --stanza=main --delta restore3. Start the cluster:
sudo pg_ctlcluster 11 main startAfter restoring the backup, we need to perform another backup:
Repository (pgbackrest user):
sudo pgbackrest --stanza=main backupThat's all. In conclusion, I want to remind you that I am by no means trying to pose as a senior DBA, and at any chance, I will use cloud services. Currently, I am starting to study various topics like backup, replication, monitoring, etc., and I write small reports on the results to make a small contribution to the community and keep some handy notes for myself.
In the next articles, I will try to talk about additional featuresārestoring data to a clean cluster, backup encryption, and publishing on S3, backups via rsync.
Source: habr.com
