Postgres - backup i restore do określonego punktu w czasie (Postgresql PITR)

Baza danych postgres podobnie jak większość komercyjnych rozwiazań pozwala na robienie backupów binarnych oraz na ich odtwarzanie do określonego punktu w czasie.


W celu prostego wyjaśnienia jak cały mechanizm działa posłużę się prostym diagramem:

O godzinie 1 w nocy zrobiony został pełny backup narzędziem pg_basebackup. Następnie baza pracowała poprawnie aż do godziny 11:15. Z całego tego czasu czyli od godziny 01:00 do 12:00 do dedykowanego katalog odkładały się również Wal_logi. Wal log zawiera wektor zmian bazodanowych który pozwala na odtworzenie każdej transacji. (można to porównać do SQLi uruchamianych na bazie ale w rzeczywistości jest za zapis wartości które się pozmianiały np. jak zmieniły się rekordy w tabeli). Nie wchodząć głębiej w szczegóły mając pełny backup można uzupełnić go zapisami z wal_logów tak aby otrzymać spójny stan bazy na godzinę np. 11:00 - w sumie to na dowolną godzinę z zakresu normalnego funkcjonowania bazy.

Czyli backup zrobiony narzędziem pg_basebackup + wal_logi (archive) = spójne odtworzenie bazy danych do określonego punktu w czasie.

W bazie postgres można zdefiniować kilka poziomów szczegółowości Wal_logów (poniżej kilka jest opisanych ale to nie są wszystkie typy).
minimum - domyślna wartość - zawiera minimalną ilość wpisów niezbędną do funkcjonowania bazy danych
archive - zawiera odpowiednią ilość wpisów aby można było użyć wal_logów do odtworzenia bazy do określonego punktu w czasie.
replica - zawiera wszystko to co archive oraz dodatkowo zawiera informacje ktore są wykorzystywane przy replikacji

Przystępujemy do konfiguracji Postgresa 10

Tworzymy odpowiednią strukturę katalogów:


mkdir /opt/pg_backup
mkdir /opt/pg_backup_archive
mkdir /opt/wal_archive
mkdir /opt/manage_postgres
chown -R postgres: /opt/pg_backup
chown -R postgres: /opt/pg_backup_archive
chown -R postgres: /opt/wal_archive

Następnie zmieniamy w pliku  /etc/postgresql/10/main/postgresql.conf parametry:


archive_mode = on                                           
archive_command = 'test ! -f /opt/wal_archive/%f && cp %p /opt/wal_archive/%f'                           
wal_level = archive     # minimal, replica, or logical


Modyfikujemy plik pg_hba.conf - dodajemy następującą linię aby można było się połączyć za pomocą pg_databasebackup. Gdzie pg_bkp to nazwa użytkownika bazodanowego używanego do backupu

/etc/postgresql/10/main/pg_hba.conf
host    replication     pg_bkp      127.0.0.1/32               md5

Tworzymy użytkownika do backupu:

su - postgres
psql
CREATE USER pg_bkp REPLICATION LOGIN ENCRYPTED PASSWORD 'Backup321';
\q
exit;

Restartujemy postgresa

systemctl postgresql restart

W kolejnym kroku konfigurujemy podłączenie się do bazy z użyciem hasła z pliku .pgpass:
Tworzymy plik .pgpass o zawartości takiej jak poniżej gdzie:
 127.0.0.1 nazwa hosta
* oznacza port
* oznacza nazwę bazy danych (* oznacza wszystkie)
pg_bkp - nazwa usera którym się łączymy do bazy
Backup321 - nasze hasło

su - postgres
127.0.0.1:*:*:pg_bkp:Backup321


I finalnie tworzymy pierwszy pełny backup bazy danych:

pg_basebackup --checkpoint=fast -Upg_bkp -h127.0.0.1 --progress -D /opt/pg_backup/

W celu automatyzacji procesu backupu można użyć następującego skryptu:
backup.sh

POSTGRES_DB_DIR=/var/lib/postgresql/10/main
PG_DB_BACKUP_DIR=/opt/pg_backup
PG_WAL_BACKUP_DIR=/opt/wal_archive
PG_BACKUP_ARCHIVE=/opt/pg_backup_archive
PG_REPLICATION_USER=replica

echo "Tar current full backup to archive $PG_BACKUP_ARCHIVE"
tar --remove-files -czf $PG_BACKUP_ARCHIVE/wal_$(hostname)_$(date +%Y-%m-%d_%H%M).tgz $PG_WAL_BACKUP_DIR/*
tar --remove-files -czf $PG_BACKUP_ARCHIVE/pg_db_full_$(hostname)_$(date +%Y-%m-%d_%H%M).tgz $PG_DB_BACKUP_DIR/*


echo "Creating full backup to $PG_DB_BACKUP_DIR"
pg_basebackup --checkpoint=fast -U$PG_REPLICATION_USER -h127.0.0.1 --progress -D $PG_DB_BACKUP_DIR


Powyższy skrypt robi tar z ostatniego full backupu oraz z plików z wal logami.

Proces odtworzenia bazy danych sprowadza się do:
1. Usunięcia istniejącej bazy danych
2. Odtworzenie pełnego backupu bazy danych - na tym przykładzie za pomocą polecenia cp
3. Utworzenia pliku recovery.conf który to spowoduje że do pełnego backupu zostaną dograne zmiany z wal logów - aż do określonego punktu w czasie - czyli do recovery_target_time.

Sam proces odtwarzania bazy danych również można zautomatyzować i wykorzystać następujący skrypt. W skrypcie należy ustawić tylko datę do której się odtwarzamy - oraz oczywiście poprawne ścieżki

restore.sh

#SET VARIABLES
POSTGRES_DB_DIR=/var/lib/postgresql/10/main
PG_DB_BACKUP_DIR=/opt/pg_backup
PG_WAL_BACKUP_DIR=/opt/wal_archive
#SET POINT IN TIME TO RECOVER DATABASE
RECOVER_DATE="2018-10-04 08:00:00"


systemctl stop postgresql
echo "Postgress stopped"
sleep 3
echo "Cleaning old postgres directory"
rm -rf $POSTGRES_DB_DIR/*
cp -r $PG_DB_BACKUP_DIR/* $POSTGRES_DB_DIR/
rm -rf $POSTGRES_DB_DIR/pg_wal/*

#create restore file
echo "Creating restore file"

echo "restore_command = 'cp $PG_WAL_BACKUP_DIR/%f \"%p\"'
#recovery_target = 'immediate'
recovery_target_time='$RECOVER_DATE'
recovery_target_action = 'promote'
" > $POSTGRES_DB_DIR/recovery.conf


chown -R postgres: $POSTGRES_DB_DIR/*
echo "start postgres"
sleep 3

systemctl start postgresql