데이터 - SQL로 postgres 테이블을 완전히 복사하기



postgresql data-migration (4)

면책 조항 : 이 질문은 여기에 스택 오버플로 질문과 유사하지만, 나중에 설명하겠습니다.

큰 열 (~ 40M 행, 100 + 열) 많은 열이 인덱싱되는 포스트 그레스에서 복사하려면 노력하고있어. 현재 SQL의이 비트를 사용합니다.

CREATE TABLE <tablename>_copy (LIKE <tablename> INCLUDING ALL);
INSERT INTO <tablename>_copy SELECT * FROM <tablename>;

이 방법에는 두 가지 문제가 있습니다.

  1. 데이터 수집 전에 인덱스를 추가하므로 인덱스가없는 테이블을 만든 다음 모든 데이터를 복사 한 후 인덱싱하는 것보다 훨씬 오래 걸립니다.
  2. 이것은`SERIAL '스타일 열을 올바르게 복사하지 않습니다. 새 테이블에 새 '카운터'를 설정하는 대신 새 테이블의 열의 기본값을 이전 테이블의 카운터로 설정합니다. 즉, 행이 추가 될 때 증가하지 않습니다.

테이블 크기는 인덱싱을 실시간으로 처리합니다. 또한 파일로 덤프하여 다시 가져 오는 것이 불가능합니다. 나는 또한 커맨드 라인의 이점이 없다. SQL에서이 작업을 수행해야합니다.

내가하고 싶은 것은 어떤 기적의 명령으로 똑바로 정확한 사본을 만들거나 그것이 가능하지 않다면 색인없이 모든 조건으로 테이블을 복사하고 그들이 '정신적으로'제약이 있는지 확인하는 것입니다 (일명 SERIAL 열에 대한 새 카운터). 그런 다음 모든 데이터를 SELECT * 로 복사 한 다음 모든 색인을 복사하십시오.

출처

  1. 데이터베이스 복사에 대한 스택 오버플로 질문 : 이것은 내가 왜 세 가지 이유에 대해 묻는거야

    • 커맨드 라인 옵션 pg_dump -t x2 | sed 's/x2/x3/g' | psql pg_dump -t x2 | sed 's/x2/x3/g' | psql pg_dump -t x2 | sed 's/x2/x3/g' | psql 그리고이 설정에서는 커맨드 라인에 접근 할 수 없습니다.
    • 느린 인덱스 인제 스트를 생성합니다.
    • default nextval('x1_id_seq'::regclass) 으로 직렬 열을 증거로 올바르게 업데이트하지 않습니다. default nextval('x1_id_seq'::regclass)
  2. 포스트그레스 테이블의 시퀀스 값을 재설정하는 방법 : 이것은 훌륭하지만 불행히도 매우 수동적입니다.


Answer #1

가장 가까운 "기적의 명령"은 다음과 같습니다.

pg_dump -t tablename | sed -r 's/\btablename\b/tablename_copy/' | psql -f -

특히 이것은 테이블 데이터를로드 한 후 인덱스를 작성하는 것을 담당합니다.

하지만 그것은 시퀀스를 재설정하지 않습니다. 당신은 자신을 스크립트해야합니다.


Answer #2

불행하게도이 물건들을 직접 손으로해야 할 것입니다. 그러나 그것은 psql과 같은 것으로부터 모두 할 수 있습니다. 첫 번째 명령은 충분히 간단합니다.

select * into newtable from oldtable

그러면 oldtable의 데이터는 있지만 인덱스는 아닌 newtable이 생성됩니다. 그런 다음 인덱스와 시퀀스 등을 직접 만들어야합니다. 다음 명령을 사용하여 테이블의 모든 인덱스 목록을 얻을 수 있습니다.

select indexdef from pg_indexes where tablename='oldtable';

그런 다음 psql -E를 실행하여 db에 액세스하고 \ d를 사용하여 이전 테이블을 봅니다. 그런 다음 시퀀스에 대한 정보를 얻기 위해이 두 쿼리를 조작 할 수 있습니다.

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(oldtable)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '74359' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

위의 74359를 이전 쿼리에서 얻은 oid로 바꿉니다.


Answer #3

경고:

pg_dump와 어떤 종류의 정규식을 사용하여 소스 테이블의 이름을 대체하는 모든 대답은 정말로 위험합니다. 데이터에 대체하려는 하위 문자열이 포함되어 있으면 어떻게 될까요? 결국 데이터가 변경 될 것입니다.

나는 two-pass 해결책을 제안한다.

  1. 일부 데이터 특정 정규 표현식을 사용하여 덤프에서 데이터 라인 제거
  2. 나머지 라인을 검색하고 교체한다.

다음은 Ruby로 작성된 예제입니다.

ruby -pe 'gsub(/(members?)/, "\\1_copy_20130320") unless $_ =~ /^\d+\t.*(?:t|f)$/' < members-production-20130320.sql > copy_members_table-20130320.sql

위의 "members_copy_20130320"에 "members"테이블을 복사하려고합니다. 내 데이터 별 정규 표현식은 /^\d+\t.*(?ttf )$/입니다.

위의 유형의 솔루션이 저에게 효과적입니다. 경고 emptor ...

편집하다:

좋습니다, 정규식 혐오 사람들을위한 가상 쉘 구문의 또 다른 방법은 다음과 같습니다.

  1. pg_dump -s -t mytable mydb> mytable_schema.sql
  2. mytable_schema.sql> mytable_copy_schema.sql에서 검색 및 교체 테이블 이름
  3. psql -f mytable_copy_schema.sql mydb

  4. pg_dump -a -t mytable mydb> mytable_data.sql

  5. 데이터 섹션 앞의 몇 가지 SQL 문에서 "mytable"바꾸기
  6. psql -f mytable_data.sql mydb

Answer #4

PostgreSQL의 create table as 기능이 이제 OP가 찾고 있던 해답이 될 수 있습니다.

https://www.postgresql.org/docs/9.5/static/sql-createtableas.html

create table my_table_copy as
  select * from my_table

이렇게하면 데이터가있는 동일한 테이블이 만들어집니다.

with no data 없이 추가하면 데이터없이 스키마가 복사됩니다.

create table my_table_copy as
  select * from my_table
with no data

이렇게하면 모든 데이터가 있지만 인덱스 및 트리거 등이없는 테이블이 생성됩니다.

create table my_table_copy (like my_table including all)

create table like syntax에는 모든 트리거, 인덱스, 제약 조건 등이 포함되지만 데이터는 포함하지 않습니다.





data-migration