postgresql
Kopia zapasowa i przywracanie
Szukaj…
Uwagi
Tworzenie kopii zapasowej systemu plików zamiast używania pg_dumpall i pg_dump
Bardzo ważne jest, aby użyć tej funkcji przed wywołaniem funkcji pg_start_backup() a następnie funkcji pg_stop_backup() . W przeciwnym razie wykonywanie kopii zapasowych systemu plików nie jest bezpieczne; nawet migawka ZFS lub FreeBSD systemu plików, którego kopię zapasową utworzono bez tych wywołań funkcji, spowoduje przejście bazy danych w tryb odzyskiwania i może utracić transakcje.
Unikałbym wykonywania kopii zapasowych systemu plików zamiast zwykłych kopii zapasowych Postgres, zarówno z tego powodu, jak i dlatego, że pliki kopii zapasowych Postgres (szczególnie w niestandardowym formacie) są niezwykle wszechstronne, jeśli chodzi o obsługę alternatywnych przywracania. Ponieważ są to pojedyncze pliki, łatwiej nimi zarządzać.
Tworzenie kopii zapasowej jednej bazy danych
pg_dump -Fc -f DATABASE.pgsql DATABASE
-Fc wybiera „niestandardowy format kopii zapasowej”, który daje więcej mocy niż surowy SQL; więcej szczegółów znajdziesz na pg_restore . Jeśli chcesz waniliowy plik SQL, możesz to zrobić w zamian:
pg_dump -f DATABASE.sql DATABASE
lub nawet
pg_dump DATABASE > DATABASE.sql
Przywracanie kopii zapasowych
psql < backup.sql
Bezpieczniejsza alternatywa używa -1 do zawijania operacji przywracania. -f określa nazwę pliku zamiast używania przekierowania powłoki.
psql -1f backup.sql
Pliki w formacie niestandardowym należy przywrócić za pomocą pg_restore z opcją -d , aby określić bazę danych:
pg_restore -d DATABASE DATABASE.pgsql
Format niestandardowy można również przekonwertować z powrotem na SQL:
pg_restore backup.pgsql > backup.sql
Zalecane jest użycie niestandardowego formatu, ponieważ można wybrać rzeczy do przywrócenia i opcjonalnie włączyć przetwarzanie równoległe.
W przypadku aktualizacji z jednej wersji postgresql do nowszej może być konieczne wykonanie pg_dump, a następnie pg_restore.
Tworzenie kopii zapasowej całego klastra
$ pg_dumpall -f backup.sql
Działa to za kulisami, tworząc wiele połączeń z serwerem raz dla każdej bazy danych i wykonując na nim pg_dump .
Czasami może pojawić się pokusa, aby ustawić to jako zadanie CRON, więc chcesz zobaczyć datę utworzenia kopii zapasowej jako część nazwy pliku:
$ postgres-backup-$(date +%Y-%m-%d).sql
Należy jednak pamiętać, że może to generować duże pliki codziennie. Postgresql ma znacznie lepszy mechanizm regularnych kopii zapasowych - archiwa WAL
Dane wyjściowe z pg_dumpall są wystarczające do przywrócenia do identycznie skonfigurowanej instancji Postgres, ale pliki konfiguracyjne w $PGDATA ( pg_hba.conf i postgresql.conf ) nie są częścią kopii zapasowej, więc będziesz musiał wykonać ich kopię zapasową osobno.
postgres=# SELECT pg_start_backup('my-backup');
postgres=# SELECT pg_stop_backup();
Aby wykonać kopię zapasową systemu plików, musisz użyć tych funkcji, aby upewnić się, że Postgres jest w spójnym stanie podczas przygotowywania kopii zapasowej.
Za pomocą Kopiuj do importowania
Aby skopiować dane z pliku CSV do tabeli
COPY <tablename> FROM '<filename with path>';
Aby wstawić do tabeli user z pliku o nazwie user_data.csv umieszczonego wewnątrz /home/user/ :
COPY user FROM '/home/user/user_data.csv';
Aby skopiować dane z pliku oddzielonego potokiem do tabeli
COPY user FROM '/home/user/user_data' WITH DELIMITER '|';
Uwaga: W przypadku braku opcji with delimiter domyślnym ogranicznikiem jest przecinek ,
Aby zignorować wiersz nagłówka podczas importowania pliku
Użyj opcji nagłówka:
COPY user FROM '/home/user/user_data' WITH DELIMITER '|' HEADER;
Uwaga: Jeśli dane są cytowane, domyślnie znaki cudzysłowu są podwójnie cytowane. Jeśli dane są cytowane przy użyciu dowolnego innego znaku, skorzystaj z opcji QUOTE ; jednak ta opcja jest dozwolona tylko przy użyciu formatu CSV.
Za pomocą Kopiuj do eksportu
Aby skopiować tabelę do standardowego o / p
KOPIUJ <nazwa_tablicy> DO STDOUT (DELIMITER '|');
Aby wyeksportować użytkownika tabeli do standardowego wyjścia:
KOPIUJ użytkownika DO STDOUT (DELIMITER '|');
Aby skopiować tabelę do pliku
KOPIUJ użytkownika Z '/ home / user / user_data' WITH DELIMITER '|';
Aby skopiować dane wyjściowe instrukcji SQL do pliku
COPY (instrukcja sql) TO '<nazwa pliku ze ścieżką>';
KOPIUJ (WYBIERZ * OD użytkownika, GDZIE nazwa_użytkownika JAK 'A%') DO '/ home / user / user_data';
Aby skopiować do skompresowanego pliku
KOPIUJ użytkownika DO PROGRAMU 'gzip> /home/user/user_data.gz';
Tutaj wykonywany jest program gzip do kompresji danych tabeli użytkowników.
Używanie psql do eksportowania danych
Dane można eksportować za pomocą polecenia kopiowania lub przy użyciu opcji wiersza polecenia polecenia psql.
Aby wyeksportować dane csv z użytkownika tabeli do pliku csv:
psql -p \<port> -U \<username> -d \<database> -A -F<delimiter> -c\<sql to execute> \> \<output filename with path>
psql -p 5432 -U postgres -d test_database -A -F, -c "select * from user" > /home/user/user_data.csv
Tutaj kombinacja -A i -F załatwia sprawę.
-F określa delimiter
-A or --no-align
Przełącza na tryb wyrównywania wyjścia. (Domyślny tryb wyjściowy jest w inny sposób wyrównany).