Postgres: bash 스크립트에서 다시 생성/재채우기 전에 전체 데이터베이스 지우기

Postgres: bash 스크립트에서 다시 생성/재채우기 전에 전체 데이터베이스 지우기

저는 다음과 같은 셸 스크립트(cronjob이 될 것)를 작성하고 있습니다.

1: 프로덕션 데이터베이스 덤프

2: 덤프를 내 개발 데이터베이스로 가져오기

1단계와 2단계 사이에 개발 데이터베이스를 삭제해야 합니다(모든 테이블 삭제?).셸 스크립트에서 이 작업을 가장 잘 수행하는 방법은 무엇입니까?지금까지는 다음과 같습니다.

#!/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

데이터베이스를 삭제한 후 다시 만들 수 있습니다.UNIX 또는 Linux 시스템에서는 다음 작업을 수행해야 합니다.

$ dropdb development_db_name
$ createdb development_db_name

그게 사실 제가 하는 방식입니다.

일반 텍스트 .sql 스크립트 파일 형식으로 디스크에 덤프된 데이터베이스의 백업이 실제로 필요하지 않은 경우 연결할 수 있습니다.pg_dump그리고.pg_restore파이프를 타고 바로 함께.

테이블을 삭제하고 다시 만들려면 다음을 사용할 수 있습니다.--clean명령줄 옵션pg_dump데이터베이스 개체를 만들기 전에 SQL 명령을 실행하여 데이터베이스 개체를 정리(삭제)합니다. 이렇게 하면 데이터베이스 전체가 삭제되는 것이 아니라 각 테이블/시퀀스/인덱스/등이 삭제됩니다.

위의 두 가지는 다음과 같습니다.

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

덤프:

pg_dump -Fc mydb > db.dump

복원 방법:

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

"example_db"라는 이름의 데이터베이스를 정리하려면 다음을 수행합니다.

다른 DB에 로그인합니다(예: 'postgres').

psql postgres

데이터베이스 제거:

DROP DATABASE example_db;

데이터베이스 재생성:

CREATE DATABASE example_db;

다음 행은 윈도우즈 배치 스크립트에서 가져온 것이지만 명령은 매우 유사해야 합니다.

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

이 명령은 전체 데이터베이스를 삭제하여 삭제하는 데 사용됩니다.그$DATABASE(Windows에서는 다음과 같아야 합니다.)%DATABASE%명령의 )은 데이터베이스 이름을 평가하는 윈도우즈 스타일 환경 변수입니다.당신은 그것을 당신의 것으로 대체해야 할 것입니다.development_db_name.

참고: 제 대답은 테이블과 다른 데이터베이스 개체를 정말로 삭제하는 것입니다. 테이블의 모든 데이터를 삭제하는 것, 즉 모든 테이블을 잘라내는 것에 대해 Endre Both는 한 달 후 유사하게 잘 실행된(직접 실행) 문을 제공했습니다.

당신이 그냥 할 수 없는 경우를 위해.DROP SCHEMA public CASCADE;,DROP OWNED BY current_user;또는 뭔가, 여기 제가 작성한 독립 실행형 SQL 스크립트가 있는데, 트랜잭션 안전합니다(즉, 이 스크립트를 사이에 둘 수 있습니다).BEGIN;어느 쪽이든ROLLBACK;그냥 테스트를 해보거나.COMMIT;실제로 수행) 및 "모든" 데이터베이스 개체를 정리합니다. 음, 애플리케이션에서 사용하거나 제가 현명하게 추가할 수 있는 데이터베이스에서 사용되는 모든 개체는 다음과 같습니다.

• 테이블의 트리거
• 테이블의 조건(, 테이의제조건약블f((FK, PK,CHECK,UNIQUE)
• 인디케이터
• VIEWs 또는 s (정상또구됨화체는)됨▁material(
• 테이블들
• 순서들
• 루틴(기능, 기능, 프로시저 포함
• 이 아닌 모든 non-default(기본값 아님)public또는 DB-internal) 스키마 "우리" 소유: 스크립트는 "데이터베이스 수퍼유저가 아님"으로 실행될 때 유용합니다. 슈퍼유저는 모든 스키마를 삭제할 수 있습니다(그러나 정말 중요한 스키마는 여전히 명시적으로 제외됨).
• 확장(사용자가 제공하지만 일반적으로 의도적으로 사용자에게 제공함)

삭제되지 않음(일부는 고의적이며 일부는 DB에 예가 없었기 때문에):

• 그자리의 public예되는 항목의 경우) 파일 (파일: 파일 이름: 파일 이름)
• 데이터 수집 및 기타 로케일 정보
• 이벤트 트리거
• 텍스트 검색 항목, …(다른 항목은 여기를 참조하십시오.)
• 역할 또는 기타 보안 설정
• 복합 활자
• 토스트 테이블
• FDW 및 외부 테이블

복원하려는 덤프가 데이터베이스 스키마 버전이 다른 경우(예: Debian)에 매우 유용합니다.dbconfig-commonFlyway 또는 Liquibase/DB-Manul)이 복원할 데이터베이스보다 큽니다.

저는 또한 누군가가 관심을 가질 경우를 대비하여 "두 개의 테이블과 테이블에 속하는 것을 제외한 모든 것"(수동으로 테스트한 시퀀스, 미안, 알아, 지루함)을 삭제하는 버전이 있습니다. 차이는 작습니다.관심이 있으면 저에게 연락하거나 이 보고서를 확인하십시오.

SQL

-- Copyright © 2019, 2020
--      mirabilos <t.glaser@tarent.de>
--
-- 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 person’s immediate fault when using the work as intended.
-- -
-- Drop everything from the PostgreSQL database.

DO $$
DECLARE
        q TEXT;
        r RECORD;
BEGIN
        -- triggers
        FOR r IN (SELECT pns.nspname, pc.relname, pt.tgname
                FROM pg_catalog.pg_trigger pt, pg_catalog.pg_class pc, pg_catalog.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_catalog.pg_constraint pcon, pg_catalog.pg_class pc, pg_catalog.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_catalog.pg_constraint pcon, pg_catalog.pg_class pc, pg_catalog.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_catalog.pg_class pc, pg_catalog.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_catalog.pg_class pc, pg_catalog.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_catalog.pg_class pc, pg_catalog.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_catalog.pg_class pc, pg_catalog.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 (only if necessary; keep them normally)
        FOR r IN (SELECT pns.nspname, pe.extname
                FROM pg_catalog.pg_extension pe, pg_catalog.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;
        -- aggregate functions first (because they depend on other functions)
        FOR r IN (SELECT pns.nspname, pp.proname, pp.oid
                FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pns, pg_catalog.pg_aggregate pagg
                WHERE pns.oid=pp.pronamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pagg.aggfnoid=pp.oid
            ) LOOP
                EXECUTE format('DROP AGGREGATE %I.%I(%s);',
                    r.nspname, r.proname,
                    pg_get_function_identity_arguments(r.oid));
        END LOOP;
        -- routines (functions, aggregate functions, procedures, window functions)
        IF EXISTS (SELECT * FROM pg_catalog.pg_attribute
                WHERE attrelid='pg_catalog.pg_proc'::regclass
                    AND attname='prokind' -- PostgreSQL 11+
            ) THEN
                q := 'CASE pp.prokind
                        WHEN ''p'' THEN ''PROCEDURE''
                        WHEN ''a'' THEN ''AGGREGATE''
                        ELSE ''FUNCTION''
                    END';
        ELSIF EXISTS (SELECT * FROM pg_catalog.pg_attribute
                WHERE attrelid='pg_catalog.pg_proc'::regclass
                    AND attname='proisagg' -- PostgreSQL ≤10
            ) THEN
                q := 'CASE pp.proisagg
                        WHEN true THEN ''AGGREGATE''
                        ELSE ''FUNCTION''
                    END';
        ELSE
                q := '''FUNCTION''';
        END IF;
        FOR r IN EXECUTE 'SELECT pns.nspname, pp.proname, pp.oid, ' || q || ' AS pt
                FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pns
                WHERE pns.oid=pp.pronamespace
                    AND pns.nspname NOT IN (''information_schema'', ''pg_catalog'', ''pg_toast'')
            ' LOOP
                EXECUTE format('DROP %s %I.%I(%s);', r.pt,
                    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_catalog.pg_namespace pns, pg_catalog.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; $$;

추가사항을 완료(테스이트됨사제추외항가후이(▁tested제테extensionsClément Prevost에 의해 기여됨), Postgre에서.SQL 9.6(jessie-backports 및 를 거쳤으며,를 거쳤습니다. 9.6 § 122에서 골재 제거 테스트를 거쳤으며, 12.2에서도 절차 제거 테스트를 거쳤습니다.버그 수정 및 추가 개선을 환영합니다!

사용한 적:

pg_restore -c -d database_name filename.dump

데이터베이스를 완전히 지웁니다.

참고: pg_dump & pg_restore는 전체 데이터베이스를 완전히 지우지 않습니다.

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Postgre를 사용하는 경우SQL 9.3 이상에서는 기본 권한 부여를 복원해야 할 수도 있습니다.

GRANT ALL ON SCHEMA public TO <your_db_username>;
GRANT ALL ON SCHEMA public TO public;

언급URL : https://stackoverflow.com/questions/2056876/postgres-clear-entire-database-before-re-creating-re-populating-from-bash-scr