PostgreSQL 다른 서버 DB 테이블 복사 - PostgreSQL daleun seobeo DB teibeul bogsa

티스토리 뷰

웹개발자/DB

PostgreSQL 테이블 복사

wlsufld 2019. 9. 18. 13:21

구조 복사
CREATE TABLE 테이블명 LIKE 원본테이블명;

구조 + 데이터 복사
SELECT * INTO 테이블명 FROM 원본테이블명;

'웹개발자 > DB' 카테고리의 다른 글

공유하기 링크

댓글

비밀글

첫 번째 방법

쿼리로 수행

기본형식

INSERT INTO COMPANY_PRO.table_name
SELECT *
FROM COMPANY_DEV.table_name ;

조건 추가가 가능하다

INSERT INTO COMPANY_PRO.table_name
SELECT *
FROM COMPANY_DEV.table_name
WHERE USE_YN = 'Y' ;

*개발디비에서 컬럼 USE_YN = 'Y' 경우만 운영에 넣음

INSERT INTO COMPANY_PRO.table_name
SELECT *
FROM COMPANY_DEV.table_name
WHERE EXISTS (SELECT ID FROM COMPANY_PRO.table_name2);

*종속적으로 영향받는 테이블 때문에 저러한 조건이 나왔음

이미 운영디비 table_name테이블에 데이터를 넣었고

table_name2 테이블에 table_name 테이블 ID가 존재하는데이터만 넣고 싶을 때 저러한 조건문을 썼음

두 번째 방법

DBMS를 사용

이번에 사용했던 툴은 DBeaver였음. 이 툴을 사용해서 클릭 몇 번으로 덤프를 뜨는게 가능함

1. 마우스 오른쪽을 클릭하여 'Export Data'를 클릭

1. Target을 Database로 설정
   

   

2. Target에 해당하는 테이블을 선택함
   

   

Amazon RDS에 대해 PostgreSQL 전송 가능 데이터베이스를 사용하여 두 DB 인스턴스 간에 PostgreSQL 데이터베이스를 전송할 수 있습니다. 이 방법은 서로 다른 DB 인스턴스 간에 대규모 데이터베이스를 마이그레이션하는 매우 빠른 방법입니다. 이 방법을 사용하여 DB 인스턴스가 모두 동일한 메이저 버전의 PostgreSQL을 실행해야 합니다.

이 기능을 사용하려면 소스와 대상 DB 인스턴스 모두에 pg_transport 확장을 설치해야 합니다. pg_transport 확장은 최소한의 처리로 데이터베이스 파일을 이동하는 물리적 전송 메커니즘을 제공합니다. 이 메커니즘은 기존의 덤프 및 로드 프로세스보다 적은 가동 중지로 훨씬 빠르게 데이터를 이동합니다.

PostgreSQL 전송 가능 데이터베이스는 RDS for PostgreSQL 11.5 이상 및 RDS for PostgreSQL 버전 10.10 이상에서 사용할 수 있습니다.

RDS for PostgreSQL DB 인스턴스에서 다른 RDS로 PostgreSQL DB 인스턴스를 전송하려면 먼저 전송을 위해 DB 인스턴스 설정에 설명된 대로 소스 및 대상 인스턴스를 설정합니다. 그런 다음 PostgreSQL 데이터베이스 전송에 설명된 함수를 사용하여 데이터베이스를 전송할 수 있습니다.

주제

• PostgreSQL 전송 가능 데이터베이스 사용에 대한 제한 사항
• PostgreSQL 데이터베이스를 전송하도록 설정
• PostgreSQL 데이터베이스를 소스에서 대상으로 전송
• 데이터베이스 전송 중 발생하는 사항
• 전송 가능한 데이터베이스 함수 참조
• 전송 가능한 데이터베이스 파라미터 참조

PostgreSQL 전송 가능 데이터베이스 사용에 대한 제한 사항

전송 가능 데이터베이스에는 다음과 같은 제한 사항이 있습니다.

• 읽기 전용 복제본 – 읽기 전용 복제본 또는 읽기 전용 복제본의 상위 인스턴스에서 전송 가능 데이터베이스를 사용할 수 없습니다.

• 지원되지 않는 열 유형 – 이 방법으로 전송하려는 데이터베이스 테이블에서는 reg 데이터 유형을 사용할 수 없습니다. 이러한 유형은 전송 중에 자주 변경되는 시스템 카탈로그 객체 ID(OID)에 따라 다릅니다.

• 테이블스페이스 – 모든 소스 데이터베이스 객체는 기본 pg_default 테이블스페이스에 있어야 합니다.

• 호환성 – 소스 및 대상 DB 인스턴스는 모두 동일한 메이저 버전의 PostgreSQL을 실행해야 합니다.

• 확장 - 소스 DB 인스턴스에는 pg_transport만 설치할 수 있습니다.

• 역할 및 ACL – 소스 데이터베이스의 액세스 권한 및 소유권 정보는 대상 데이터베이스로 전달되지 않습니다. 모든 데이터베이스 객체는 전송의 로컬 대상 사용자가 생성하고 소유합니다.

• 동시 전송 - 작업자 프로세스가 올바르게 구성된 경우 단일 DB 인스턴스에서 가져오기와 내보내기를 포함하여 최대 32개의 동시 전송을 지원할 수 있습니다.

• RDS for PostgreSQL DB 인스턴스 전용 - RDS for PostgreSQL DB 인스턴스에 대해 PostgreSQL 전송 가능 데이터베이스만 지원할 수 있습니다. Amazon EC2에서 실행되는 온프레미스 데이터베이스 또는 데이터베이스에는 사용할 수 없습니다.

PostgreSQL 데이터베이스를 전송하도록 설정

시작하기 전에 RDS for PostgreSQL DB 인스턴스가 다음 요구 사항을 충족하는지 확인하세요.

• 소스 및 대상 RDS for PostgreSQL DB 인스턴스는 동일한 버전의 PostgreSQL을 실행해야 합니다.

• 대상 DB에는 전송할 소스 DB와 같은 이름의 데이터베이스를 가질 수 없습니다.

• 전송을 실행하는 데 사용하는 계정은 소스 DB와 대상 DB 모두에 대한 rds_superuser 권한이 필요합니다.

• 소스 DB 인스턴스의 보안 그룹은 대상 DB 인스턴스의 인바운드 액세스를 허용해야 합니다. 소스 및 대상 DB 인스턴스가 VPC에 있는 경우 이미 해당 경우일 수 있습니다. 보안 그룹에 대한 자세한 내용은 보안 그룹을 통한 액세스 제어 섹션을 참조하세요.

소스 DB 인스턴스에서 대상 DB 인스턴스로 데이터베이스를 전송하려면 각 인스턴스와 연결된 DB 파라미터 그룹을 몇 번 변경해야 합니다. 즉, 소스 DB 인스턴스에 대한 사용자 지정 DB 파라미터 그룹을 생성하고 대상 DB 인스턴스에 대한 사용자 지정 DB 파라미터 그룹을 생성해야 합니다.

DB 인스턴스가 이미 사용자 지정 DB 파라미터 그룹을 사용하여 구성된 경우 다음 절차의 2단계부터 시작할 수 있습니다.

데이터베이스 전송을 위한 사용자 지정 DB 그룹 파라미터 구성

다음 단계에서는 rds_superuser 권한을 가진 계정을 사용하세요.

1. 소스 및 대상 DB 인스턴스가 기본 DB 파라미터 그룹을 사용하는 경우 인스턴스에 적합한 버전을 사용하여 사용자 지정 DB 파라미터를 생성해야 합니다. 이렇게 하면 여러 파라미터의 값을 변경할 수 있습니다. 자세한 정보는 파라미터 그룹 작업을 참조하십시오.

2. 사용자 지정 DB 파라미터 그룹에서 다음 파라미터 값을 변경합니다.

   • shared_preload_libraries - 라이브러리 목록에 pg_transport를 추가합니다.

   • pg_transport.num_workers - 기본값은 3입니다. 데이터베이스에 필요한 경우 이 값을 늘리거나 줄입니다. 200GB 데이터베이스의 경우 8이하의 값이 좋습니다. 이 파라미터의 기본값을 늘리면 max_worker_processes 값도 늘려야 합니다.

   • pg_transport.work_mem - 기본값은 PostgreSQL 버전에 따라 128MB 또는 256MB입니다. 기본 설정은 일반적으로 변경되지 않을 수 있습니다.

   • max_worker_processes - 이 파라미터의 값은 다음 계산을 사용하여 설정해야 합니다.

     3 * pg_transport.num_workers) + 9

     전송과 관련된 다양한 백그라운드 작업자 프로세스를 처리하기 위해 대상에서 필요한 값입니다. max_worker_processes,에 관한 자세한 내용은 PostgreSQL 문서에서 리소스 소비를 참조하세요.

   pg_transport 파라미터에 대한 자세한 내용은 전송 가능한 데이터베이스 파라미터 참조 단원을 참조하십시오.

3. 소스 RDS for PostgreSQL DB 인스턴스 및 대상 인스턴스를 재부팅하면 파라미터 설정이 적용됩니다.

4. RDS for PostgreSQL 소스 DB 인스턴스에 연결합니다.

   psql --host=source-instance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

5. DB 인스턴스의 퍼블릭 스키마에서 불필요한 확장을 제거합니다. 실제 전송 작업 중에만 pg_transport 확장이 허용됩니다.

6. 다음과 같이 pg_transport 확장을 설치합니다.

   postgres=> CREATE EXTENSION pg_transport;
   CREATE EXTENSION

7. RDS for PostgreSQL 대상 DB 인스턴스에 연결합니다. 불필요한 확장을 제거한 다음 pg_transport 확장을 설치합니다.

   postgres=> CREATE EXTENSION pg_transport;
   CREATE EXTENSION

PostgreSQL 데이터베이스를 소스에서 대상으로 전송

PostgreSQL 데이터베이스를 전송하도록 설정에 설명된 프로세스를 완료한 후에는 전송을 시작할 수 있습니다. 이렇게 하려면 대상 DB 인스턴스에서 transport.import_from_server 함수를 실행합니다. 다음 구문에서는 함수 파라미터를 찾을 수 있습니다.

SELECT transport.import_from_server( 
   'source-db-instance-endpoint', 
    source-db-instance-port, 
   'source-db-instance-user', 
   'source-user-password', 
   'source-database-name', 
   'destination-user-password', 
   false);

예에 표시된 false 값은 함수에 이것이 모의 실습이 아님을 알려줍니다. 전송 설정을 테스트하려면 다음에서 처럼 함수를 호출할 때 dry_run 옵션에서 true를 지정할 수 있습니다.

postgres=> SELECT transport.import_from_server(
    'docs-lab-source-db.666666666666aws-region.rds.amazonaws.com', 5432,
    'postgres', '********', 'labdb', '******', true);
INFO:  Starting dry-run of import of database "labdb".
INFO:  Created connections to remote database        (took 0.03 seconds).
INFO:  Checked remote cluster compatibility          (took 0.05 seconds).
INFO:  Dry-run complete                         (took 0.08 seconds total).
 import_from_server
--------------------

(1 row)

INFO 행은 pg_transport.timing 파라미터가 기본값인 true로 설정하므로 출력됩니다. 다음과 같이 명령을 실행하고 소스 데이터베이스를 대상으로 가져올 때 dry_run을 false로 설정합니다.

INFO:  Starting import of database "labdb".
INFO:  Created connections to remote database        (took 0.02 seconds).
INFO:  Marked remote database as read only           (took 0.13 seconds).
INFO:  Checked remote cluster compatibility          (took 0.03 seconds).
INFO:  Signaled creation of PITR blackout window     (took 2.01 seconds).
INFO:  Applied remote database schema pre-data       (took 0.50 seconds).
INFO:  Created connections to local cluster          (took 0.01 seconds).
INFO:  Locked down destination database              (took 0.00 seconds).
INFO:  Completed transfer of database files          (took 0.24 seconds).
INFO:  Completed clean up                            (took 1.02 seconds).
INFO:  Physical transport complete              (took 3.97 seconds total).
import_from_server
--------------------
(1 row)

이 함수를 사용하려면 데이터베이스 사용자 암호를 제공해야 합니다. 따라서 전송이 완료된 후 사용한 사용자 역할의 암호를 변경하는 것이 좋습니다. 또는 SQL 바인드 변수를 사용하여 임시 사용자 역할을 생성할 수 있습니다. 전송에 이러한 임시 역할을 사용한 후 나중에 해당 역할을 삭제하십시오.

전송에 성공하지 못하면 다음과 같은 오류 메시지가 표시될 수 있습니다.

pg_transport.num_workers=8 25% of files transported failed to download file data

"파일 데이터를 다운로드 실패" 오류 메시지는 작업자 프로세스 수가 데이터베이스 크기에 맞게 올바르게 설정되지 않았음을 나타냅니다. pg_transport.num_workers에 대한 값 세트를 늘리거나 줄여야 할 수 있습니다. 각 실패는 완료 비율을 보고하므로 변경 사항이 미치는 영향을 확인할 수 있습니다. 예를 들어 한 경우에 8에서 4로 설정을 변경하면 다음과 같은 결과가 발생합니다.

pg_transport.num_workers=4 75% of files transported failed to download file data

max_worker_processes 파라미터는 전송 프로세스 중에도 고려됩니다. 다시 말해 데이터베이스를 성공적으로 전송하려면 pg_transport.num_workers와 max_worker_processes 두 가지를 모두 수정해야 할 수 있습니다. 표시된 예제는 pg_transport.num_workers가 2로 설정되었을 때 마침내 작동했습니다.

pg_transport.num_workers=2 100% of files transported

transport.import_from_server 함수와 해당 파라미터에 대한 자세한 내용은 전송 가능한 데이터베이스 함수 참조 섹션을 참조하세요.

데이터베이스 전송 중 발생하는 사항

PostgreSQL 전송 가능 데이터베이스 기능은 소스 DB 인스턴스에서 대상으로 데이터베이스를 가져오는 풀링 모델을 사용합니다. transport.import_from_server 함수는 대상 DB 인스턴스에서 전송 중 데이터베이스를 생성합니다. 전송 기간 동안에는 대상 DB 인스턴스에서 전송 중 데이터베이스에 액세스할 수 없습니다.

전송이 시작되면 소스 데이터베이스의 모든 현재 세션이 종료됩니다. 소스 DB 인스턴스의 소스 데이터베이스 이외의 모든 데이터베이스는 전송의 영향을 받지 않습니다.

소스 데이터베이스는 특수한 읽기 전용 모드로 설정됩니다. 이 모드에 있는 동안 소스 데이터베이스에 연결하고 읽기 전용 쿼리를 실행할 수 있습니다. 그러나 쓰기 가능 쿼리 및 일부 다른 유형의 명령은 차단됩니다. 전송되는 특정 소스 데이터베이스만 이러한 제한의 영향을 받습니다.

전송 중에는 대상 DB 인스턴스를 특정 시점으로 복원할 수 없습니다. 전송은 트랜잭션이 아니며 PostgreSQL 미리 쓰기 로그를 사용하여 변경 사항을 기록하지 않기 때문입니다. 대상 DB 인스턴스에 자동 백업이 활성화되어 있으면 전송이 완료된 후 백업이 자동으로 수행됩니다. 특정 시점으로 복원은 백업이 완료된 후 일정 시간 동안 사용할 수 있습니다.

전송이 실패하면 pg_transport 확장은 소스 및 대상 DB 인스턴스에 대한 모든 변경을 취소하려고 시도합니다. 여기에는 대상에 부분적으로 전송된 데이터베이스 제거가 포함됩니다. 실패 유형에 따라 소스 데이터베이스는 쓰기 가능 쿼리를 계속 거부할 수 있습니다. 이 경우 다음 명령을 사용하여 쓰기 가능 쿼리를 허용하십시오.

ALTER DATABASE db-name SET default_transaction_read_only = false;

전송 가능한 데이터베이스 함수 참조

transport.import_from_server 함수는 PostgreSQL 데이터베이스를 소스 DB 인스턴스에서 대상 DB 인스턴스로 가져와서 전송합니다. 물리적 데이터베이스 연결 전송 메커니즘을 사용하여 이를 수행합니다.

전송을 시작하기 전에 이 함수는 소스 및 대상 DB 인스턴스가 동일한 버전이며 마이그레이션을 위해 호환되는지 확인합니다. 또한 대상 DB 인스턴스 소스에 충분한 공간이 있는지 확인합니다.

구문

transport.import_from_server(
   host text,
   port int,
   username text,
   password text,
   database text,
   local_password text,
   dry_run bool
)

반환 값

없음.

파라미터

다음 표에서 transport.import_from_server 함수 파라미터에 대한 설명을 확인할 수 있습니다.

예

관련 예제는 PostgreSQL 데이터베이스를 소스에서 대상으로 전송 섹션을 참조하세요

전송 가능한 데이터베이스 파라미터 참조

pg_transport 확장 동작을 제어하는 몇 가지 파라미터입니다. 다음에서 이러한 파라미터에 대한 설명을 확인할 수 있습니다.

전송 프로세스에 사용할 작업자 수입니다. 기본값은 3입니다. 유효한 값은 1 – 32입니다. 대용량 데이터베이스 전송에서도 일반적으로 8명 미만 작업자가 필요합니다. 전송 중에 대상 DB 인스턴스의 이 설정 값은 대상 및 소스 모두에서 사용됩니다.

전송 중 타이밍 정보를 보고할지 여부를 지정합니다. 기본값은 true로, 타이밍 정보가 보고됩니다. 이 파라미터는 설정은 true로 두어 진행 상황을 모니터링할 수 있을 것을 권장합니다. 예제 출력은 PostgreSQL 데이터베이스를 소스에서 대상으로 전송을 참조하세요.

각 작업자에게 할당할 최대 메모리 양입니다. 기본값은 PostgreSQL 버전에 따라 131072킬로바이트(KB) 또는 262144KB(256MB)입니다. 최소값은 64MB(65,536KB)입니다. 유효한 값은 이진 base-2 단위로 나타낸 킬로바이트(KB)입니다(1KB = 1024바이트).

전송 시 이 파라미터에 지정된 것보다 적은 메모리를 사용할 수 있습니다. 대용량 데이터베이스 전송에서도 일반적으로 작업자당 256MB(262144KB) 미만의 메모리가 필요합니다.