postgresql
Резервное копирование и восстановление
Поиск…
замечания
Резервное копирование файловой системы вместо использования pg_dumpall и pg_dump
Очень важно, что если вы используете это, вы вызываете pg_start_backup() перед и pg_stop_backup() после. Выполнение резервных копий файловой системы в противном случае небезопасно; даже моментальный снимок ZFS или FreeBSD из резервной копии файловой системы без этих вызовов функций поместит базу данных в режим восстановления и может потерять транзакции.
По этой причине я бы не стал делать резервные копии файловой системы вместо обычных резервных копий Postgres и потому, что файлы резервного копирования Postgres (особенно в пользовательском формате) чрезвычайно универсальны в поддержке альтернативных восстановлений. Поскольку они представляют собой одиночные файлы, они также менее трудны в управлении.
Резервное копирование одной базы данных
pg_dump -Fc -f DATABASE.pgsql DATABASE
-Fc выбирает «настраиваемый формат резервного копирования», который дает вам больше мощности, чем исходный SQL; см. pg_restore для более подробной информации. Если вам нужен файл vanilla SQL, вы можете сделать это вместо этого:
pg_dump -f DATABASE.sql DATABASE
или даже
pg_dump DATABASE > DATABASE.sql
Восстановление резервных копий
psql < backup.sql
Более безопасная альтернатива использует -1 для обертывания восстановления в транзакции. Параметр -f указывает имя файла, а не перенаправление оболочки.
psql -1f backup.sql
Файлы настраиваемого формата должны быть восстановлены с помощью pg_restore с опцией -d чтобы указать базу данных:
pg_restore -d DATABASE DATABASE.pgsql
Пользовательский формат также можно преобразовать обратно в SQL:
pg_restore backup.pgsql > backup.sql
Рекомендуется использовать пользовательский формат, поскольку вы можете выбрать, какие вещи нужно восстановить и, возможно, включить параллельную обработку.
Возможно, вам понадобится pg_dump, за которым следует pg_restore, если вы перейдете из одной версии postgresql в более новую.
Резервное копирование всего кластера
$ pg_dumpall -f backup.sql
Это работает за кулисами, делая несколько подключений к серверу один раз для каждой базы данных и выполняя pg_dump на нем.
Иногда может возникнуть соблазн установить это как задание cron, поэтому вы хотите увидеть дату, когда резервная копия была взята как часть имени файла:
$ postgres-backup-$(date +%Y-%m-%d).sql
Однако обратите внимание, что это может ежедневно создавать большие файлы. Postgresql имеет гораздо лучший механизм для регулярных резервных копий - архивы WAL
Вывод из pg_dumpall достаточен для восстановления экземпляра Postgres с идентичной конфигурацией, но файлы конфигурации в $PGDATA ( pg_hba.conf и postgresql.conf ) не являются частью резервной копии, поэтому вам придется резервировать их отдельно.
postgres=# SELECT pg_start_backup('my-backup');
postgres=# SELECT pg_stop_backup();
Чтобы выполнить резервное копирование файловой системы, вы должны использовать эти функции, чтобы гарантировать, что Postgres находится в согласованном состоянии, пока резервная копия подготовлена.
Использование копирования для импорта
Копировать данные из файла CSV в таблицу
COPY <tablename> FROM '<filename with path>';
Чтобы вставить в user таблицы из файла с именем user_data.csv помещенным внутри /home/user/ :
COPY user FROM '/home/user/user_data.csv';
Копировать данные из файла, разделенного на трубы, в таблицу
COPY user FROM '/home/user/user_data' WITH DELIMITER '|';
Примечание. В отсутствие опции with delimiter по умолчанию является запятая ,
Чтобы игнорировать строку заголовка при импорте файла
Используйте параметр заголовка:
COPY user FROM '/home/user/user_data' WITH DELIMITER '|' HEADER;
Примечание. Если данные цитируются, по умолчанию данные котировки символов являются двойными. Если данные цитируются с использованием любого другого символа, используйте опцию QUOTE ; однако эта опция разрешена только при использовании формата CSV.
Использование копирования для экспорта
Скопировать таблицу в стандартный файл o / p
COPY <имя_таблицы> TO STDOUT (DELIMITER '|');
Чтобы экспортировать пользователя таблицы в стандартный вывод:
Пользователь COPY TO STDOUT (DELIMITER '|');
Скопировать таблицу в файл
COPY user FROM '/ home / user / user_data' WITH DELIMITER '|';
Копировать вывод инструкции SQL в файл
COPY (оператор sql) TO '<имя_файла с контуром>';
COPY (SELECT * FROM user WHERE user_name LIKE 'A%') TO '/ home / user / user_data';
Скопировать в сжатый файл
COPY user TO PROGRAM 'gzip> /home/user/user_data.gz';
Здесь программа gzip выполняется для сжатия данных пользовательской таблицы.
Использование psql для экспорта данных
Данные могут быть экспортированы с помощью команды копирования или с использованием параметров командной строки команды psql.
Чтобы экспортировать данные csv из пользователя таблицы в файл 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
Здесь комбинация -A и -F делает трюк.
-F - указать разделитель
-A or --no-align
Переключает в режим выровненного вывода. (Режим вывода по умолчанию в противном случае выровнен.)