Postgres: очистить всю базу данных до повторного создания/повторного заполнения из bash script

Я пишу оболочку script (станет cronjob), которая будет:

1: выгрузить мою производственную базу данных

2: импортировать дамп в мою базу данных разработки

Между шагами 1 и 2 мне нужно очистить базу данных разработки (удалить все таблицы?). Как это лучше всего сделать из оболочки script? Пока это выглядит так:

#!/bin/bash
time=`date '+%Y'-'%m'-'%d'`
# 1. export(dump) the current production database
pg_dump -U production_db_name > /backup/dir/backup-${time}.sql

# missing step: drop all tables from development database so it can be re-populated

# 2. load the backup into the development database
psql -U development_db_name < backup/dir/backup-${time}.sql

Ответы

Ответ 1

Я просто удаляю базу данных, а затем снова ее создаю. В системе UNIX или Linux это должно сделать:

$ dropdb development_db_name
$ createdb developmnent_db_name

То, как я это делаю, на самом деле.

Ответ 2

Если вам действительно не нужна резервная копия базы данных, сбрасываемой на диск в текстовом формате .sql script, вы можете подключить pg_dump и pg_restore непосредственно вместе по каналу.

Чтобы отбросить и воссоздать таблицы, вы можете использовать параметр командной строки --clean для pg_dump, чтобы испускать команды SQL для очистки (удаления) объектов базы данных до (создания команд) для их создания. (Это не приведет к потере всей базы данных, только каждой таблицы/последовательности/индекса/и т.д., Прежде чем воссоздать их.)

Вышеуказанные два будут выглядеть примерно так:

pg_dump -U username --clean | pg_restore -U username

Ответ 3

Хотя следующая строка взята из пакета windows script, команда должна быть очень похожей:

psql -U username -h localhost -d postgres -c "DROP DATABASE \"$DATABASE\";"

Эта команда используется для очистки всей базы данных, фактически отбрасывая ее. $DATABASE (в Windows должен быть %DATABASE%) в команде находится переменная среды стиля Windows, которая оценивает имя базы данных. Вам нужно будет заменить это на development_db_name.

Ответ 4

Сбрасывать:

pg_dump -Fc mydb > db.dump

Для восстановления:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d my_db db/latest.dump

Ответ 5

Я использовал:

pg_restore -c -d database_name filename.dump

Ответ 6

Примечание: мой ответ о действительно удалении таблиц и других объектов базы данных; для удаления всех данных в таблицах, т.е. усечения всех таблиц, Endre Both предоставил аналогично хорошо выполненный оператор (непосредственное выполнение) через месяц.

Для случаев, когда вы не можете просто DROP SCHEMA public CASCADE;, DROP OWNED BY current_user; или что-то еще, вот отдельный скрипт SQL, который я написал, который безопасен для транзакций (то есть вы можете поместить его между BEGIN; и ROLLBACK;, чтобы просто проверить это или COMMIT;, чтобы фактически сделать дело) и очищает "все" объекты базы данных… ну, все те, которые используются в базе данных, которую использует наше приложение, или я мог бы разумно добавить, что:

  • триггеры на столах
  • ограничения на таблицы (FK, PK, CHECK, UNIQUE)
  • ИНДЕКСЫ
  • VIEW (обычный или материализованный)
  • таблицы
  • последовательности
  • функции/процедуры (pg_proc.proisagg вероятно, следует соблюдать, хотя)
  • все схемы nōn-default (т.е. не public или DB-internal) "у нас": скрипт полезен, когда он запускается как "не суперпользователь базы данных"; суперпользователь может отбросить все схемы (хотя действительно важные из них явно исключены)
  • расширения (добавленные пользователем, но я обычно намеренно оставляю их в)

Не отброшены (некоторые умышленно; некоторые только потому, что у меня нет примера в нашей БД):

  • схема public (например, для предоставляемых расширений в них)
  • агрегатные функции
  • подборки и другие локализы
  • триггеры событий
  • материал для текстового поиска… (см. здесь, чтобы узнать о других вещах, которые я мог пропустить)
  • роли или другие настройки безопасности
  • составные типы
  • тосты
  • FDW и зарубежные таблицы

Это действительно полезно для случаев, когда дамп, который вы хотите восстановить, имеет другую версию схемы базы данных (например, с Debian dbconfig-common, Flyway или Liquibase/DB-Manul), чем база данных, которую вы хотите восстановить это в.

У меня также есть версия, которая удаляет "все, кроме двух таблиц и того, что им принадлежит", если кому-то это интересно; разница маленькая. Свяжитесь со мной, если это необходимо.

SQL

-- Copyright © 2019
--      mirabilos <[email protected]>
--
-- Provided that these terms and disclaimer and all copyright notices
-- are retained or reproduced in an accompanying document, permission
-- is granted to deal in this work without restriction, including un‐
-- limited rights to use, publicly perform, distribute, sell, modify,
-- merge, give away, or sublicence.
--
-- This work is provided 'AS IS' and WITHOUT WARRANTY of any kind, to
-- the utmost extent permitted by applicable law, neither express nor
-- implied; without malicious intent or gross negligence. In no event
-- may a licensor, author or contributor be held liable for indirect,
-- direct, other damage, loss, or other issues arising in any way out
-- of dealing in the work, even if advised of the possibility of such
-- damage or existence of a defect, except proven that it results out
-- of said persons immediate fault when using the work as intended.
-- -
-- Drop everything from the PostgreSQL database.

DO $$
DECLARE
        r RECORD;
BEGIN
        -- triggers
        FOR r IN (SELECT pns.nspname, pc.relname, pt.tgname
                FROM pg_trigger pt, pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace AND pc.oid=pt.tgrelid
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pt.tgisinternal=false
            ) LOOP
                EXECUTE format('DROP TRIGGER %I ON %I.%I;',
                    r.tgname, r.nspname, r.relname);
        END LOOP;
        -- constraints #1: foreign key
        FOR r IN (SELECT pns.nspname, pc.relname, pcon.conname
                FROM pg_constraint pcon, pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace AND pc.oid=pcon.conrelid
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pcon.contype='f'
            ) LOOP
                EXECUTE format('ALTER TABLE ONLY %I.%I DROP CONSTRAINT %I;',
                    r.nspname, r.relname, r.conname);
        END LOOP;
        -- constraints #2: the rest
        FOR r IN (SELECT pns.nspname, pc.relname, pcon.conname
                FROM pg_constraint pcon, pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace AND pc.oid=pcon.conrelid
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pcon.contype<>'f'
            ) LOOP
                EXECUTE format('ALTER TABLE ONLY %I.%I DROP CONSTRAINT %I;',
                    r.nspname, r.relname, r.conname);
        END LOOP;
        -- indicēs
        FOR r IN (SELECT pns.nspname, pc.relname
                FROM pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pc.relkind='i'
            ) LOOP
                EXECUTE format('DROP INDEX %I.%I;',
                    r.nspname, r.relname);
        END LOOP;
        -- normal and materialised views
        FOR r IN (SELECT pns.nspname, pc.relname
                FROM pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pc.relkind IN ('v', 'm')
            ) LOOP
                EXECUTE format('DROP VIEW %I.%I;',
                    r.nspname, r.relname);
        END LOOP;
        -- tables
        FOR r IN (SELECT pns.nspname, pc.relname
                FROM pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pc.relkind='r'
            ) LOOP
                EXECUTE format('DROP TABLE %I.%I;',
                    r.nspname, r.relname);
        END LOOP;
        -- sequences
        FOR r IN (SELECT pns.nspname, pc.relname
                FROM pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pc.relkind='S'
            ) LOOP
                EXECUTE format('DROP SEQUENCE %I.%I;',
                    r.nspname, r.relname);
        END LOOP;
        -- extensions (see below), only if necessary
        FOR r IN (SELECT pns.nspname, pe.extname
                FROM pg_extension pe, pg_namespace pns
                WHERE pns.oid=pe.extnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
            ) LOOP
                EXECUTE format('DROP EXTENSION %I;', r.extname);
        END LOOP;
        -- functions / procedures
        FOR r IN (SELECT pns.nspname, pp.proname, pp.oid
                FROM pg_proc pp, pg_namespace pns
                WHERE pns.oid=pp.pronamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
            ) LOOP
                EXECUTE format('DROP FUNCTION %I.%I(%s);',
                    r.nspname, r.proname,
                    pg_get_function_identity_arguments(r.oid));
        END LOOP;
        -- nōn-default schemata we own; assume to be run by a not-superuser
        FOR r IN (SELECT pns.nspname
                FROM pg_namespace pns, pg_roles pr
                WHERE pr.oid=pns.nspowner
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast', 'public')
                    AND pr.rolname=current_user
            ) LOOP
                EXECUTE format('DROP SCHEMA %I;', r.nspname);
        END LOOP;
        -- voilà
        RAISE NOTICE 'Database cleared!';
END; $$;

Протестировано, за исключением более поздних дополнений (extensions, предоставленных Clément Prévost), в PostgreSQL 9.6 (jessie-backports). Исправления и дальнейшие улучшения приветствуются!

Ответ 7

Если вы хотите очистить вашу базу данных с именем "example_db":

1) Войдите в другую базу данных (например, 'postgres'):

psql postgres

2) Удалить вашу базу данных:

DROP DATABASE example_db;

3) воссоздать вашу базу данных:

CREATE DATABASE example_db;