[database] 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 developmnent_db_name

그것이 제가 실제로하는 방법입니다.

답변

실제로 일반 텍스트 .sql 스크립트 파일 형식으로 디스크에 덤프 된 데이터베이스 백업이 필요하지 않은 경우 파이프를 통해 직접 연결 pg_dump하여 연결할 수 있습니다 pg_restore.

테이블을 삭제 및 재 작성하기 위해 --clean명령 행 옵션을 사용하여 pg_dump데이터베이스 명령을 작성하기 전에 데이터베이스 오브젝트를 정리 (삭제)하는 SQL 명령을 생성 할 수 있습니다. (이것은 전체 데이터베이스를 삭제하지 않고 각 테이블 / 시퀀스 / 인덱스 등을 다시 만들기 전에 삭제합니다.)

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

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

답변

다음 줄은 Windows 배치 스크립트에서 가져 왔지만 명령은 매우 비슷해야합니다.

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

이 명령은 실제로 삭제함으로써 전체 데이터베이스를 지우는 데 사용됩니다. 명령의 $DATABASE(Windows에 있어야 함 %DATABASE%)은 데이터베이스 이름으로 평가되는 Windows 스타일 환경 변수입니다. 로 대체해야합니다 development_db_name.

답변

덤프하려면 :

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”라는 데이터베이스를 정리하려면 다음을 수행하십시오.

1) 다른 db (예 : ‘postgres’)에 로그인하십시오.

psql postgres

2) 데이터베이스를 제거하십시오.

DROP DATABASE example_db;

3) 데이터베이스를 다시 작성하십시오.

CREATE DATABASE example_db;

답변

나는 사용했다 :

pg_restore -c -d database_name filename.dump

답변

참고 : 내 대답은 실제로 테이블과 다른 데이터베이스 객체를 삭제하는 것입니다. 에 대한 모든 데이터를 삭제 에 테이블, 즉 모든 테이블을 절단 , Endre를 두 달 후에 이와 비슷하게 잘 실행 (직접 실행) 문을 제공하고 있습니다.

그냥 할 수없는 경우에 DROP SCHEMA public CASCADE;, DROP OWNED BY current_user;여기에 거래 안전 (당신이 사이를 넣을 수 있습니다 예이다 내가 쓴 독립 실행 형 SQL 스크립트, 또는 뭔가 BEGIN;및 중 ROLLBACK;단지 시험에 밖으로 나 COMMIT;실제로으로 행동 할) “모든”데이터베이스 개체를 정리합니다. 응용 프로그램에서 사용하는 데이터베이스에 사용 된 모든 개체는 다음과 같이 추가 할 수 있습니다.

테이블의 트리거
테이블에 대한 제약 조건 (FK, PK CHECK,, UNIQUE)
지수
VIEWs (정상 또는 구체화)
테이블
시퀀스
루틴 (집합 기능, 기능, 절차)
public“우리”가 소유 한 모든 디폴트 (즉 , DB- 내부 또는 DB- 내부) 스키마 : “데이터베이스 수퍼 유저 아님”으로 실행할 때 스크립트가 유용합니다. 수퍼 유저는 모든 스키마 를 삭제할 수 있습니다 (실제로 중요한 스키마는 여전히 명시 적으로 제외됨)
확장 프로그램 (사용자가 제공하지만 일반적으로 고의적으로 남겨 두는 경우)

삭제되지 않습니다 (일부 고의적; 일부는 DB에 예가 없었기 때문에) :

그만큼 public스키마 (그들 확장 제공하는 물건에 대한 예)
데이터 정렬 및 기타 로케일
이벤트 트리거
텍스트 검색 물건,… ( 여기 참조 내가 놓친 것 다른 것들에 대한)
역할 또는 기타 보안 설정
복합 유형
토스트 테이블
FDW 및 외래 테이블

이것은 복원하려는 덤프가 다른 데이터베이스 스키마 버전 인 경우에 매우 유용합니다 (예 : 데비안)dbconfig-common , 이동 경로 또는 Liquibase / DB-MANUL) 당신이로 복원 할 데이터베이스보다.

또한 누군가 관심있는 경우를 대비하여“두 테이블을 제외한 모든 것 및 그 테이블에 속하는 것”(수동으로 테스트, 미안, 지루함)을 삭제하는 버전이 있습니다. diff는 작습니다. 저에게 연락 하거나이 repo를 확인하십시오관심 .

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; $$;

PostgreSQL 9.6 ( ) 에서 이후 추가 ( Clément Prévost 제공 )를 제외하고 테스트 extensions되었습니다 . 9.6 및 12.2에서 집계 제거 테스트, 12.2에서 절차 제거 테스트. 버그 수정 및 추가 개선을 환영합니다!jessie-backports