Kopia zapasowa i odzyskiwanie baz danych PostgreSQL: Kompletny przewodnik dla użytkowników AlexHost

Dlaczego strategia kopii zapasowych PostgreSQL jest ważniejsza niż myślisz

Utrata danych to nie hipotetyczne ryzyko — to pewność operacyjna, z którą każdy administrator bazy danych będzie się mierzyć w pewnym momencie. Awarie sprzętu, przypadkowe usunięcia, uszkodzone transakcje i ataki ransomware mogą doprowadzić środowisko produkcyjne do upadku w ciągu sekund. Dla użytkowników PostgreSQL posiadanie solidnej, przetestowanej i zautomatyzowanej strategii kopii zapasowych to różnica między drobnym incydentem a katastrofalną porażką biznesu.

Serwery dedykowane AlexHost zapewniają idealną podstawę do hostowania i ochrony baz danych PostgreSQL. Dzięki magazynowaniu NVMe SSD klasy korporacyjnej zapewniającemu wyjątkową przepustowość I/O, pełnemu dostępowi root do pełnej kontroli konfiguracji i wbudowanej ochronie przed DDoS, AlexHost zapewnia wydajność infrastruktury i postawę bezpieczeństwa, które wymagają poważne obciążenia baz danych.

Niezależnie od tego, czy prowadzisz platformę e-commerce o dużym ruchu, aplikację SaaS, instalację WordPress wspieraną przez relacyjną bazę danych, czy niestandardowy system korporacyjny, ten przewodnik przeprowadzi Cię przez każdą główną metodę tworzenia kopii zapasowych i odzyskiwania PostgreSQL — od prostych zrzutów SQL do zaawansowanego odzyskiwania do punktu w czasie (PITR) — wszystko zoptymalizowane dla środowisk produkcyjnych.

Spis treści

1. Zrozumienie opcji kopii zapasowych PostgreSQL
2. Wymagania wstępne i uprawnienia
3. Metoda 1 — Zrzut SQL z pg_dump
4. Metoda 2 — Tworzenie kopii zapasowych wszystkich baz danych z pg_dumpall
5. Metoda 3 — Kopie zapasowe w formacie niestandardowym dla dużych baz danych
6. Przywracanie ze zrzutów SQL
7. Przywracanie ze zrzutów w formacie niestandardowym
8. Metoda 4 — Ciągłe archiwizowanie i odzyskiwanie do punktu w czasie (PITR)
9. Automatyzacja kopii zapasowych za pomocą Cron
10. Zabezpieczanie i przechowywanie kopii zapasowych poza siedzibą
11. Podsumowanie najlepszych praktyk

1. Zrozumienie opcji kopii zapasowych PostgreSQL

PostgreSQL jest dostarczany z kilkoma dojrzałymi, dobrze udokumentowanymi mechanizmami tworzenia kopii zapasowych. Wybór właściwego zależy od rozmiaru bazy danych, celów czasu odzyskiwania (RTO), celów punktu odzyskiwania (RPO) i tolerancji złożoności operacyjnej.

Zrozumienie tych kompromisów przed rozpoczęciem jest niezbędne. Większość środowisk produkcyjnych korzysta z połączenia co najmniej dwóch podejść — na przykład nocnych zrzutów w formacie niestandardowym wraz z ciągłym archiwizowaniem WAL dla szczegółowej możliwości odzyskiwania.

2. Wymagania wstępne i uprawnienia

Przed wykonaniem jakiejkolwiek operacji tworzenia kopii zapasowej potwierdź, że spełnione są następujące wymagania wstępne:

Uprawnienia użytkownika:

• Musisz być superużytkownikiem PostgreSQL lub właścicielem docelowej bazy danych, aby wykonać pełną kopię zapasową.
• Dla pg_dumpall wymagane są uprawnienia superużytkownika.

Sprawdź wersję PostgreSQL:

psql --version

Sprawdź dostępne miejsce na dysku przed wykonaniem kopii zapasowej:

df -h /var/lib/postgresql/

Upewnij się, że miejsce docelowe kopii zapasowej ma wystarczającą wolną przestrzeń — co najmniej 1,5× rozmiar bazy danych, której kopia zapasowa jest tworzona, aby uwzględnić pliki tymczasowe i narzut kompresji.

Połącz się z serwerem za pośrednictwem SSH:

ssh root@your-server-ip

Jeśli korzystasz z planu VPS Hosting, będziesz mieć pełny dostęp SSH i możliwość instalacji, konfiguracji i zarządzania PostgreSQL bez ograniczeń.

3. Metoda 1 — Zrzut SQL z pg_dump

Narzędzie pg_dump jest najczęściej używanym narzędziem do tworzenia kopii zapasowych PostgreSQL. Tworzy spójną migawkę pojedynczej bazy danych, nawet gdy baza danych jest aktywnie używana. Wynik to zwykły skrypt SQL, który można przejrzeć, edytować i odtworzyć na dowolnej kompatybilnej instalacji PostgreSQL.

Krok 1: Otwórz terminal i uzyskaj dostęp do serwera

ssh root@your-alexhost-server-ip

Krok 2: Uruchom polecenie pg_dump

pg_dump -U username -W -F p database_name > /backups/backup_file.sql

Rozbicie parametrów:

Praktyczny przykład:

pg_dump -U postgres -W -F p my_production_db > /backups/my_production_db_$(date +%Y%m%d_%H%M%S).sql

> Pro Tip: Dołączenie sygnatury czasowej za pomocą $(date +%Y%m%d_%H%M%S) do nazwy pliku kopii zapasowej zapewnia, że nigdy nie przypadkowo nie nadpiszesz poprzedniej kopii zapasowej i tworzysz naturalny chronologiczny archiwum.

Krok 3: Sprawdź plik kopii zapasowej

ls -lh /backups/
head -50 /backups/my_production_db_*.sql

Plik powinien zaczynać się od komentarzy nagłówka PostgreSQL i instrukcji SET, potwierdzając, że została utworzona prawidłowa kopia zapasowa.

4. Metoda 2 — Tworzenie kopii zapasowych wszystkich baz danych z pg_dumpall

Gdy musisz wykonać kopię zapasową każdej bazy danych w instancji PostgreSQL — w tym obiektów globalnych, takich jak role i przestrzenie tabel — pg_dumpall jest właściwym narzędziem.

pg_dumpall -U postgres -W > /backups/all_databases_$(date +%Y%m%d).sql

To polecenie eksportuje:

• Wszystkie bazy danych
• Wszystkie role (użytkownicy i grupy)
• Wszystkie przestrzenie tabel
• Całą konfigurację globalną

Ważne: Plik wyjściowy z pg_dumpall może być bardzo duży na zajętych serwerach. Upewnij się, że partycja kopii zapasowej ma wystarczającą przestrzeń i rozważ natychmiastową kompresję wyjścia:

pg_dumpall -U postgres | gzip > /backups/all_databases_$(date +%Y%m%d).sql.gz

5. Metoda 3 — Kopie zapasowe w formacie niestandardowym dla dużych baz danych

W przypadku produkcyjnych baz danych przekraczających kilka gigabajtów, format niestandardowy (-F c) jest zdecydowanie zalecany zamiast zwykłych zrzutów SQL. Kopie zapasowe w formacie niestandardowym to:

• Domyślnie skompresowane — znacznie zmniejszające wymagania dotyczące przechowywania
• Szybsze do przywrócenia — obsługujące równoległe operacje przywracania za pomocą flagi -j
• Selektywnie przywracalne — umożliwiające przywrócenie poszczególnych tabel lub schematów

Tworzenie kopii zapasowej w formacie niestandardowym

pg_dump -U postgres -W -F c my_production_db > /backups/my_production_db_$(date +%Y%m%d).dump

Tworzenie skompresowanej kopii zapasowej w formacie katalogu (obsługuje równoległy proces)

pg_dump -U postgres -W -F d -j 4 -f /backups/my_production_db_dir my_production_db

To podejście dramatycznie skraca czas tworzenia kopii zapasowej na serwerach wielordzeniowych, co czyni go idealnym dla środowisk serwerów dedykowanych o wysokiej wydajności dostępnych w AlexHost.

6. Przywracanie ze zrzutów SQL

Przywróć pojedynczą bazę danych ze zwykłego zrzutu SQL

Najpierw upewnij się, że docelowa baza danych istnieje. Jeśli nie, utwórz ją:

psql -U postgres -c "CREATE DATABASE my_restored_db;"

Następnie przywróć:

psql -U postgres -d my_restored_db -f /backups/my_production_db_backup.sql

Rozbicie parametrów:

Monitoruj postęp przywracania

W przypadku dużych plików SQL możesz monitorować postęp za pomocą pv:

pv /backups/my_production_db_backup.sql | psql -U postgres -d my_restored_db

7. Przywracanie ze zrzutów w formacie niestandardowym

Zrzuty w formacie niestandardowym wymagają narzędzia pg_restore zamiast psql.

Podstawowe przywrócenie

pg_restore -U postgres -d my_restored_db /backups/my_production_db.dump

Przywróć i automatycznie utwórz bazę danych

Użyj flagi -C aby poinstruować pg_restore aby utworzyć bazę danych przed jej wypełnieniem:

pg_restore -U postgres -C -d postgres /backups/my_production_db.dump

Równoległa restauracja dla szybszego odzyskiwania

pg_restore -U postgres -d my_restored_db -j 4 /backups/my_production_db_dir/

Użycie -j 4 z kopią zapasową w formacie katalogu może zmniejszyć czas przywracania o do 75% na serwerze czterordzeniowym — znaczna przewaga przy minimalizowaniu przestojów podczas odzyskiwania po awarii.

Przywróć tylko określoną tabelę

pg_restore -U postgres -d my_restored_db -t orders /backups/my_production_db.dump

Ta szczegółowa możliwość jest jedną z kluczowych zalet formatu niestandardowego w stosunku do zwykłych zrzutów SQL.

8. Metoda 4 — Ciągłe archiwizowanie i odzyskiwanie do punktu w czasie (PITR)

PITR to złoty standard dla krytycznych wdrożeń PostgreSQL. Umożliwia przywrócenie bazy danych do dowolnego konkretnego momentu w czasie — nie tylko ostatniej kopii zapasowej — poprzez odtworzenie segmentów Write-Ahead Log (WAL) na górze kopii zapasowej. Jest to niezbędne w scenariuszach, w których musisz odzyskać się z błędu logicznego (takiego jak przypadkowy DROP TABLE), który miał miejsce w znanym znaczniku czasu.

Krok 1: Włącz archiwizowanie WAL w postgresql.conf

Zlokalizuj i edytuj plik konfiguracji PostgreSQL:

nano /etc/postgresql/15/main/postgresql.conf

Dodaj lub zmodyfikuj następujące dyrektywy:

# Enable WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Wyjaśnienie parametru:

Utwórz katalog archiwum i ustaw prawidłowe uprawnienia:

mkdir -p /var/lib/postgresql/wal_archive
chown postgres:postgres /var/lib/postgresql/wal_archive
chmod 700 /var/lib/postgresql/wal_archive

Uruchom ponownie PostgreSQL, aby zastosować zmiany:

systemctl restart postgresql

Krok 2: Utwórz kopię zapasową bazy za pomocą pg_basebackup

pg_basebackup -U postgres -D /backups/base_backup -Ft -z -P -Xs

Krok 3: Przywróć do określonego punktu w czasie

Aby przywrócić z kopii zapasowej bazy i archiwów WAL:

1. Zatrzymaj PostgreSQL:

systemctl stop postgresql

1. Wyczyść istniejący katalog danych:

rm -rf /var/lib/postgresql/15/main/*

1. Wyodrębnij kopię zapasową bazy:

tar -xzf /backups/base_backup/base.tar.gz -C /var/lib/postgresql/15/main/

1. Utwórz recovery.conf (PostgreSQL 11 i wcześniej) lub skonfiguruj postgresql.conf i utwórz plik recovery.signal (PostgreSQL 12+):

# For PostgreSQL 12+
touch /var/lib/postgresql/15/main/recovery.signal

Dodaj do postgresql.conf:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-01-15 14:30:00'
recovery_target_action = 'promote'

1. Ustaw prawidłową własność i uruchom PostgreSQL:

chown -R postgres:postgres /var/lib/postgresql/15/main/
systemctl start postgresql

PostgreSQL będzie odtwarzać segmenty WAL do określonego znacznika czasu, a następnie promować do normalnego stanu do odczytu i zapisu.

9. Automatyzacja kopii zapasowych za pomocą Cron

Ręczne kopie zapasowe są zawodne.