[sql] Postgres에서 테이블 (인덱스 포함) 복사

Program / 글쓴이

postgres 테이블이 있습니다. 일부 데이터를 삭제해야합니다. 임시 테이블을 만들고 데이터를 복사하고 인덱스를 다시 만들고 필요한 행을 삭제하려고했습니다. 이 원본 테이블이 데이터 소스이기 때문에 원본 테이블에서 데이터를 삭제할 수 없습니다. 어떤 경우에는 X 삭제에 의존하는 결과를 얻어야하고, 다른 경우에는 Y를 삭제해야합니다. 따라서 항상 주변에 있고 사용할 수 있도록 모든 원본 데이터가 필요합니다.

그러나 테이블을 다시 만들고 다시 복사하여 인덱스를 다시 만드는 것은 약간 어리석은 것처럼 보입니다. 어쨌든 postgres에 “구조, 데이터 및 인덱스를 포함하여이 테이블의 완전한 개별 복사본을 원합니다”라고 말하는 것이 있습니까?

불행히도 PostgreSQL에는 “CREATE TABLE .. LIKE X INCLUDING INDEXES ‘가 없습니다.

답변

새 PostgreSQL (docs에 따르면 8.3 이후)은 “INCLUDING INDEXES”를 사용할 수 있습니다.

# select version();
                                             version
-------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.7 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)
(1 row)

보시다시피 8.3에서 테스트하고 있습니다.

이제 테이블을 생성 해 보겠습니다.

# create table x1 (id serial primary key, x text unique);
NOTICE:  CREATE TABLE will create implicit sequence "x1_id_seq" for serial column "x1.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x1_pkey" for table "x1"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x1_x_key" for table "x1"
CREATE TABLE

그리고 어떻게 보이는지보십시오 :

# \d x1
                         Table "public.x1"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('x1_id_seq'::regclass)
 x      | text    |
Indexes:
    "x1_pkey" PRIMARY KEY, btree (id)
    "x1_x_key" UNIQUE, btree (x)

이제 구조를 복사 할 수 있습니다.

# create table x2 ( like x1 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x2_pkey" for table "x2"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x2_x_key" for table "x2"
CREATE TABLE

그리고 구조를 확인하십시오.

# \d x2
                         Table "public.x2"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('x1_id_seq'::regclass)
 x      | text    |
Indexes:
    "x2_pkey" PRIMARY KEY, btree (id)
    "x2_x_key" UNIQUE, btree (x)

PostgreSQL 8.3 이전 버전을 사용하는 경우 “-t”옵션과 함께 pg_dump를 사용하여 테이블 1 개를 지정하고 덤프에서 테이블 이름을 변경 한 다음 다시로드 할 수 있습니다.

=> pg_dump -t x2 | sed 's/x2/x3/g' | psql
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

이제 테이블은 다음과 같습니다.

# \d x3
                         Table "public.x3"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('x1_id_seq'::regclass)
 x      | text    |
Indexes:
    "x3_pkey" PRIMARY KEY, btree (id)
    "x3_x_key" UNIQUE, btree (x)

답변

[CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace ]
    AS query][1]

다음은 예입니다.

CREATE TABLE films_recent AS
  SELECT * FROM films WHERE date_prod >= '2002-01-01';

첫 번째에서 새 테이블을 만드는 다른 방법은

    CREATE TABLE films_recent (LIKE films INCLUDING INDEXES);

    INSERT INTO films_recent
         SELECT *
           FROM books
          WHERE date_prod >= '2002-01-01';

Postgresql에는 두 번째 방법을 사용하는 경우 테이블 스페이스 문제를 해결하기 위한 패치 가 있습니다.

답변

postgres 테이블이 있습니다. 일부 데이터를 삭제해야합니다.

나는 추측한다 …

delete from yourtable
where <condition(s)>

… 어떤 이유로 작동하지 않습니다. (그 이유를 공유 하시겠습니까?)

임시 테이블을 만들고 데이터를 복사하고 인덱스를 다시 만들고 필요한 행을 삭제하려고했습니다.

pg_dump 및 pg_restore를 살펴보십시오. 몇 가지 영리한 옵션과 함께 pg_dump를 사용하고 아마도 pg_restoring 전에 출력을 편집하면 트릭을 수행 할 수 있습니다.

데이터에 대해 “what if”유형의 분석을 수행하고 있으므로 뷰를 사용하는 것이 더 나은지 궁금합니다.

제외하려는 항목의 부정을 기반으로 테스트하려는 각 시나리오에 대한보기를 정의 할 수 있습니다. 즉, 포함하려는 항목을 기반으로보기를 정의하십시오. 예를 들어, X = Y 인 행을 “삭제”한 데이터에 “창”을 원하면 (X! = Y) 인 행으로 뷰를 생성합니다.

뷰는 정의 쿼리로 데이터베이스 (시스템 카탈로그)에 저장됩니다. 뷰를 쿼리 할 때마다 데이터베이스 서버는 뷰를 정의하고 실행하는 기본 쿼리를 조회합니다 (그리고 사용한 다른 조건과 함께). 이 접근 방식에는 몇 가지 이점이 있습니다.

  1. 데이터의 어떤 부분도 복제하지 않습니다.
  2. 각 뷰 / 시나리오를 쿼리 할 때 기본 테이블 (원래의 “실제”테이블)에 이미 사용중인 인덱스가 사용됩니다 (쿼리 최적화 프로그램에 적합하다고 판단 됨). 재정의하거나 복사 할 필요가 없습니다.
  3. 뷰는 기본 테이블의 “실제”데이터에 대한 “창”(shapshot이 아님)이므로 기본 테이블에서 추가 / 업데이트 / 삭제하고 다음과 같이 아무것도 다시 만들 필요없이 간단히보기 시나리오를 다시 쿼리 할 수 ​​있습니다. 데이터는 시간이 지남에 따라 변경됩니다.

물론 절충안이 있습니다. 뷰는 “실제”(기본) 테이블이 아닌 가상 테이블이므로 실제로 액세스 할 때마다 (아마도 복잡한) 쿼리를 실행합니다. 이것은 약간의 속도를 늦출 수 있습니다. 그러나 그렇지 않을 수도 있습니다. 많은 문제 (데이터의 크기와 특성, 시스템 카탈로그의 통계 품질, 하드웨어 속도, 사용 부하 등)에 따라 다릅니다. 시도 할 때까지 알 수 없습니다. 실제로 성능이 용납 할 수 없을 정도로 느리다고 판단되는 경우에만 다른 옵션을 살펴볼 수 있습니다. (구체화 된 뷰, 테이블 사본, … 시간과 공간을 교환하는 모든 것)

답변

웹에는 많은 답변이 있으며 그중 하나는 여기 에서 찾을 수 있습니다 .

나는 다음과 같은 것을 끝내었다.

create table NEW ( like ORIGINAL including all);
insert into NEW select * from ORIGINAL

그러면 스키마와 인덱스를 포함한 데이터가 복사되지만 트리거 및 제약 조건은 포함되지 않습니다. 인덱스는 원래 테이블과 공유되므로 두 테이블에 새 행을 추가 할 때 카운터가 증가합니다.

답변

원하는 데이터를 가져 오기 위해 선택을 사용하여 새 테이블을 만듭니다. 그런 다음 이전 테이블을 새 테이블로 바꿉니다.

create table mynewone as select * from myoldone where ...
mess (re-create) with indexes after the table swap.

답변

간단한 방법은 다음을 모두 포함하는 것입니다.

CREATE TABLE new_table (LIKE original_table INCLUDING ALL);

답변