It has long been known that making backups in SQL dumps (using pg_dump or pg_dumpall) is not the best idea. For backing up PostgreSQL databases, it's better to use the command pg_basebackup, which creates a binary copy of the WAL logs. But when you start studying the entire process of creating backups and restoration, you'll realize that you need to build at least a couple of three-wheeled bicycles to make everything work without causing pain both above and below. To alleviate the suffering, WAL-G was developed.
is a tool written in Go for backing up and restoring PostgreSQL databases (and recently MySQL/MariaDB, MongoDB, and FoundationDB). It works with storage solutions such as Amazon S3 (and analogs, for instance, Yandex Object Storage), as well as Google Cloud Storage, Azure Storage, Swift Object Storage, and even the file system. All setup boils down to a few simple steps, but due to the fragmented articles about it online, there is no comprehensive how-to manual that includes all the steps from start to finish (there are several posts on Habr, but many aspects are missed there).
This article is primarily written to systematize my knowledge. I am not a DBA, so I might express myself in a somewhat layman's developer language, hence any corrections are welcome!
I should note that everything mentioned below is relevant and verified for PostgreSQL 12.3 on Ubuntu 18.04; all commands should be executed by a privileged user.
Installation
At the time of writing this article, the stable version of WAL-G is . This is what we will use (but if you want to compile it yourself from the master branch, there are instructions for that in the repository on GitHub). To download and install, you need to execute:
#!/bin/bash
curl -L "https://github.com/wal-g/wal-g/releases/download/v0.2.15/wal-g.linux-amd64.tar.gz" -o "wal-g.linux-amd64.tar.gz"
tar -xzf wal-g.linux-amd64.tar.gz
mv wal-g /usr/local/bin/
After this, you need to configure WAL-G first, and then PostgreSQL itself.
Configuring WAL-G
For the example of backup storage, Amazon S3 will be used (because it is closer to my servers and its use is very cheap). To work with it, you need an 's3 bucket' and access keys.
In all previous articles about WAL-G, configuration was done using environment variables, but from this release, settings can be placed in in the home directory of the postgres user. To create it, execute the following bash script:
#!/bin/bash
cat > /var/lib/postgresql/.walg.json << EOF
{
"WALG_S3_PREFIX": "s3://your_bucket/path",
"AWS_ACCESS_KEY_ID": "key_id",
"AWS_SECRET_ACCESS_KEY": "secret_key",
"WALG_COMPRESSION_METHOD": "brotli",
"WALG_DELTA_MAX_STEPS": "5",
"PGDATA": "/var/lib/postgresql/12/main",
"PGHOST": "/var/run/postgresql/.s.PGSQL.5432"
}
EOF
# обязательно меняем владельца файла:
chown postgres: /var/lib/postgresql/.walg.json
Let me explain all the parameters:
- WALG_S3_PREFIX – the path to your S3 bucket where backups will be uploaded (can be to the root or a folder);
- AWS_ACCESS_KEY_ID – access key in S3 (in case of recovery on a test server – these keys must have a ReadOnly Policy! More details are provided in the recovery section);
- AWS_SECRET_ACCESS_KEY – secret key in the S3 storage;
- WALG_COMPRESSION_METHOD – compression method, it’s best to use Brotli (as it is a good balance between final size and compression/decompression speed);
- WALG_DELTA_MAX_STEPS – the number of "deltas" before creating a full backup (this can save time and the size of uploaded data, but might slightly slow down the recovery process, so it's not advisable to use large values);
- PGDATA – the path to the directory containing your database data (you can find this out by executing the command pg_lsclusters);
- PGHOST – connection to the database, for local backup, it’s better to use a unix-socket as in this example.
The other parameters can be found in the documentation: .
Configuring PostgreSQL
To allow the archiver within the database to upload WAL logs to the cloud and recover from them (if necessary) – you need to set several parameters in the configuration file /etc/postgresql/12/main/postgresql.conf. First, you need to ensure, that none of the settings listed below are set to any other values, so that when the configuration is reloaded – the DBMS does not crash. You can add these parameters using:
#!/bin/bash
echo "wal_level=replica" >> /etc/postgresql/12/main/postgresql.conf
echo "archive_mode=on" >> /etc/postgresql/12/main/postgresql.conf
echo "archive_command='/usr/local/bin/wal-g wal-push "%p" >> /var/log/postgresql/archive_command.log 2>&1' " >> /etc/postgresql/12/main/postgresql.conf
echo “archive_timeout=60” >> /etc/postgresql/12/main/postgresql.conf
echo "restore_command='/usr/local/bin/wal-g wal-fetch "%f" "%p" >> /var/log/postgresql/restore_command.log 2>&1' " >> /etc/postgresql/12/main/postgresql.conf
# перезагружаем конфиг через отправку SIGHUP сигнала всем процессам БД
killall -s HUP postgres
Description of the set parameters:
- wal_level – how much information to write to WAL logs, "replica" – to write everything;
- archive_mode – enabling the uploading of WAL logs using the command from the parameter archive_command;
- archive_command – command for archiving the finished WAL log;
- archive_timeout – logs are archived only when they are finished, but if your server rarely modifies/adds data to the DB, it makes sense to set a limit in seconds here, after which the archiving command will be triggered forcibly (I have intensive writes to the database every second, so I opted out of setting this parameter in production);
- restore_command – command for restoring a WAL log from backup, will be used if the "full backup" (base backup) is missing the latest changes in the DB.
You can read more about all these parameters in the translated official documentation: .
Setting up the backup schedule
No matter how you look at it, the most convenient way to initiate is with cron. This is what we will set up for creating backups. Let's start with the command to create a full backup: in wal-g this is the launch argument. backup-push. But first, it's best to run this command manually as the postgres user to ensure everything is working well (and there are no access errors):
#!/bin/bash
su - postgres -c '/usr/local/bin/wal-g backup-push /var/lib/postgresql/12/main'
The launch arguments specify the path to the data directory – remember that you can find this out by executing pg_lsclusters.
If everything went smoothly and the data was uploaded to the S3 storage, you can now set up periodic execution in crontab:
#!/bin/bash
echo "15 4 * * * /usr/local/bin/wal-g backup-push /var/lib/postgresql/12/main >> /var/log/postgresql/walg_backup.log 2>&1" >> /var/spool/cron/crontabs/postgres
# задаем владельца и выставляем правильные права файлу
chown postgres: /var/spool/cron/crontabs/postgres
chmod 600 /var/spool/cron/crontabs/postgres
In this example, the backup process is initiated every day at 4:15 AM.
Deleting old backups
Chances are you don't need to keep all backups from the Mesozoic era, so it will be useful to periodically 'clean up' your storage (both 'full backups' and WAL logs). We will do this through a cron job as well:
#!/bin/bash
echo "30 6 * * * /usr/local/bin/wal-g delete before FIND_FULL $(date -d '-10 days' '+%FT%TZ') --confirm >> /var/log/postgresql/walg_delete.log 2>&1" >> /var/spool/cron/crontabs/postgres
# ещё раз задаем владельца и выставляем правильные права файлу (хоть это обычно это и не нужно повторно делать)
chown postgres: /var/spool/cron/crontabs/postgres
chmod 600 /var/spool/cron/crontabs/postgres
Cron will execute this task every day at 6:30 AM, deleting everything (full backups, deltas, and WALs) except for copies from the last 10 days, but it will retain at least one backup up to from the specified date, so that any point after in time can be included in PITR.
Recovering from a backup
It is no secret that the key to a healthy database is periodic recovery and integrity checks of the data inside. I will explain how to recover using WAL-G in this section, and we will discuss the checks afterward.
It is worth noting separately that for recovery in a test environment (everything that is not production) – you need to use a Read Only account in S3 to avoid accidentally overwriting backups. In the case of WAL-G, you need to set the following permissions for the S3 user in the Group Policy (Effect: Allow): s3:GetObject, s3:ListBucket, s3:GetBucketLocation. And, of course, do not forget to set archive_mode=off in the configuration file postgresql.conf, so that your test database doesn’t quietly back itself up.
Recovery is performed with a simple move of the hand by removing all PostgreSQL data (including users), so please be extremely careful when executing the following commands.
#!/bin/bash
# если есть балансировщик подключений (например, pgbouncer), то вначале отключаем его, чтобы он не нарыгал ошибок в лог
service pgbouncer stop
# если есть демон, который перезапускает упавшие процессы (например, monit), то останавливаем в нём процесс мониторинга базы (у меня это pgsql12)
monit stop pgsql12
# или останавливаем мониторинг полностью
service monit stop
# останавливаем саму базу данных
service postgresql stop
# удаляем все данные из текущей базы (!!!); лучше предварительно сделать их копию, если есть свободное место на диске
rm -rf /var/lib/postgresql/12/main
# скачиваем резервную копию и разархивируем её
su - postgres -c '/usr/local/bin/wal-g backup-fetch /var/lib/postgresql/12/main LATEST'
# помещаем рядом с базой специальный файл-сигнал для восстановления (см. https://postgrespro.ru/docs/postgresql/12/runtime-config-wal#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY ), он обязательно должен быть создан от пользователя postgres
su - postgres -c 'touch /var/lib/postgresql/12/main/recovery.signal'
# запускаем базу данных, чтобы она инициировала процесс восстановления
service postgresql start
For those who want to monitor the recovery process, here is a small piece of bash magic prepared so that in case of problems during recovery, the script will exit with a non-zero exit code. In this example, 120 checks are performed with a timeout of 5 seconds (totaling 10 minutes for recovery) to determine whether the signal file has been deleted (this will indicate that the recovery was successful):
#!/bin/bash
CHECK_RECOVERY_SIGNAL_ITER=0
while [ ${CHECK_RECOVERY_SIGNAL_ITER} -le 120 ]
do
if [ ! -f "/var/lib/postgresql/12/main/recovery.signal" ]
then
echo "recovery.signal removed"
break
fi
sleep 5
((CHECK_RECOVERY_SIGNAL_ITER+1))
done
# если после всех проверок файл всё равно существует, то падаем с ошибкой
if [ -f "/var/lib/postgresql/12/main/recovery.signal" ]
then
echo "recovery.signal still exists!"
exit 17
fi
After a successful recovery, don't forget to restart all processes (pgbouncer/monit, etc.).
Verification of data after recovery
It is essential to check the integrity of the database after recovery to avoid situations with corrupted/broken backups. It's best to do this with every created archive, but where and how depends only on your imagination (you can spin up separate servers on an hourly basis or run checks in CI). However, at a minimum, data and indexes in the database must be verified.
To check the data, it's enough to run them through a dump, but it's better to have checksums enabled when creating the database ():
#!/bin/bash
if ! su - postgres -c 'pg_dumpall > /dev/null'
then
echo 'pg_dumpall failed'
exit 125
fi
To check the indexes, there exists , the SQL query to it will be taken from and around it, we will build a small logic:
#!/bin/bash
# добавляем sql-запрос для проверки в файл во временной директории
cat > /tmp/amcheck.sql << EOF
CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check(c.oid), c.relname, c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree'
AND c.relpersistence != 't'
AND i.indisready AND i.indisvalid;
EOF
chown postgres: /tmp/amcheck.sql
# добавляем скрипт для запуска проверок всех доступных баз в кластере
# (обратите внимание что переменные и запуск команд – экранированы)
cat > /tmp/run_amcheck.sh << EOF
for DBNAME in $(su - postgres -c 'psql -q -A -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" ')
do
echo "Database: ${DBNAME}"
su - postgres -c "psql -f /tmp/amcheck.sql -v 'ON_ERROR_STOP=1' ${DBNAME}" && EXIT_STATUS=$? || EXIT_STATUS=$?
if [ "${EXIT_STATUS}" -ne 0 ]
then
echo "amcheck failed on DB: ${DBNAME}"
exit 125
fi
done
EOF
chmod +x /tmp/run_amcheck.sh
# запускаем скрипт
/tmp/run_amcheck.sh > /tmp/amcheck.log
# для проверки что всё прошло успешно можно проверить exit code или grep’нуть ошибку
if grep 'amcheck failed' "/tmp/amcheck.log"
then
echo 'amcheck failed: '
cat /tmp/amcheck.log
exit 125
fi
In summary
I would like to express my gratitude to Andrey Borodin for his help in preparing this publication and a special thank you for his contribution to the development of WAL-G!
This note has come to an end. I hope I have conveyed the ease of setup and the vast potential for applying this tool in your company. I have heard a lot about WAL-G, but I never had the time to sit down and figure it out. After I implemented it myself, this article emerged from me.
It's worth noting that WAL-G can also work with the following databases:
- ;
- ;
- ;
- And judging by the commits, several more are expected!
Source: habr.com
