Backup/Restore PostgreSQL database

Backup and restore Postgresql database easy

Last updated on 20 kwietnia, 2021

logo postgresql

Dziś szybki wpis na temat automatycznego backupu bazy danych PostgreSQL. Opiszę jak łatwo i szybko można zautomatyzować robienie backupów baz danych, tak samo jak miało to miejsce przy MySQL. Jeśli jeszcze nie czytałeś tego artykułu to zachęcam, znajdziesz go pod linkiem https://lepczynski.it/gitlab/backup-restore-mysql-database

Backup PostgreSQL database:

Ręczny Backup PostgreSQL database:

Na początku zacznijmy od ręcznego wykonania backupu. Po pierwsze trzeba zainstalować klienta postgresql-client, na komputerze który będzie robił backup. Gdy już go mamy określamy parametry połączenia, podajemy IP address serwera i port jeśli jest niestandardowy. Będzie nam także potrzebna nazwa użytkownika i jego hasło. W przykładzie użyłem użytkownika admin i jego hasła. Na końcu zapisujemy backup we wskazanej lokalizacji, u mnie jest to /mnt/cifs/backup_alldb_$DATA1.dump. Lokalizacja zawiera zmienną $DATA1 która dopisuje w pliku aktualną datę.

apt-get install postgresql-client
DATA1=$(date +"%Y%m%d")

# backup jednej bazy:
psql  -U admin -h 10.10.10.10 -p 30123 NAZWA_BAZY  > /mnt/cifs/backup_singledb_$DATA1.dump;

# backup wszystkich baz:
pg_dumpall -U admin -h 10.10.10.10 -p 30123 -o -v  > /mnt/cifs/backup_alldb_$DATA1.dump;

Automatyzacja Backup PostgreSQL database – Sposób 1:

Automatyzacja tego procesu jest prosta, ale zabieramy się za to dopiero gdy sprawdziliśmy, że działa nam ręczne wykonanie backupu. Taki automatyczny backup można zamontować np w runnerze GitLaba, który może wykonać backup według schedulera. Tutaj używam obrazu postgres:11.6 który jest kompatybilny z backupowanym serwerem. Następnie montowany jest dysk sieciowy, na którym będą zapisywały się backupy. Sam backup wygląda niemal identycznie jak przy manualnej operacji. Dodatkowo na końcu dopisałem fragment, który usuwa pliki starsze niż 30 dni. To czego tutaj nie napisałem, a co jeszcze trzeba zrobić, to zdefiniować zmienne, loginy i hasła oraz dodać schedulera.

backup_postgres_X :
   stage: deploy
   image: postgres:11.6
   script:
     - DATA1=$(date +"%Y%m%d")
       # tworzenie folderów i montowanie dysków
     - mkdir -p /mnt/cifs
     - mount -t cifs -o username=$backup_user,password=$backup_pass //11.11.11.11/backup/db_postgres_nazwa1 /mnt/cifs
     - ls /mnt/cifs/
     
     # wykonywanie backupu wszystkich baz
     - export PGPASSWORD=$postgrese_password
     - pg_dumpall -h 10.10.10.10 -o -U admin -p 30123 -v  > /mnt/cifs/backup_postgres_$DATA1.dump
     
     #usuwanie starszych plików niż 30 dni
     - find /mnt/cifs/ -maxdepth 1 -type f -mtime +30 -name 'backup_postgres*'
     - find /mnt/cifs/ -maxdepth 1 -type f -mtime +30 -name 'backup_postgres*' -exec rm {} \;
   only:
     refs:
       - schedules
   only:
     variables: 
       - $ACTION == "backup-postgres"

Automatyzacja Backup PostgreSQL database– Sposób 2:

Zamiast tworzyć runnery można wykonać backup na serwerze postgresql za pomocą crona. Wystarczy zalogować się jako postgres i dopisać do crontaba(crontab -e) polecenie:

0 2 * * * pg_dump -U postgres dbname > /mnt/cifs/db_backups/dbname-$(date +\%Y\%m\%d).dump
0 3 * * * find /mnt/cifs/db_backups/ -type f -name "*.dump" -mtime +30 -delete

A jeśli chcemy zaoszczędzić trochę miejsca to można pliki dodatkowo spakować:

0 2 * * * pg_dump -U postgres dbname > | gzip > /mnt/cifs/db_backups/dbname-$(date +\%Y\%m\%d).dump.gz
0 3 * * * find /mnt/cifs/db_backups/ -type f -name "*.dump.gz" -mtime +30 -delete

Restore PostgreSQL database:

Często przydatna w przypadku awarii jest opcja restore, by odtworzyć bazę danych z wykonanego wcześniej backupu. Najłatwiej jest to zrobić za pomocą polecenia:

# lokalnie:
psql -U username -d dbname < filename.dump

# zdalnie:
psql  -U username -h 10.10.10.10 -p 30123 -d NAZWA_BAZY < /mnt/db/file.dump

Poniżej kilka prostych dodatkowych poleceń dla PostgreSQL, które też czasem są przydatne by sprawdzić czy baza albo użytkownik jest na serwerze. Na serwer logujemy się lokalnie albo np za pomocą ‘psql -h 10.10.10.10 -U admin –port=30123 -d mydb

# spis baz danych:
\l

# usuwa bazę danych:
# DROP DATABASE database_name;
# wyświetla tabele z bazy danych 'database_name':
\d database_name;
\dt;
# wyświetla użytkowników i ich role:
\du
# wychodzimy wpisując:
\q

Więcej informacji na temat samego postgresql znajdziecie pod adresem https://www.postgresqltutorial.com/psql-commands/

Jeśli chcesz się dowiedzieć jak automatyczny backup może wyglądać w MySQL zapraszam do zapoznania się z moim poprzednim artykułem na ten temat:

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *