SQL을 사용하여 postgres 테이블을 완전히 복사

SQL을 사용하여 postgres 테이블을 완전히 복사

---

면책 조항 : 이 질문은 여기 의 스택 오버플로 질문과 유사 하지만 나중에 설명 하겠지만 답변 중 어느 것도 내 문제에 대해 작동하지.

많은 열이 인덱싱 된 postgres에서 큰 테이블 (~ 40M 행, 100+ 열)을 염색합니다. 현재 저는이 SQL 비트를 사용합니다.

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

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

1. 다음 모든 데이터를 수집하여 생성하는 추가 데이터없이 테이블을 생성 한 다음 모든 데이터를 복사합니다.
2. 이`SERIAL '스타일 열을 제대로 복사하지 않습니다. 새 테이블에 새 '카운터'를 설정하는 새 테이블의 열을 이전 테이블의 카운터로 설정합니다. 즉, 행이 추가 될 때 증가하지 않습니다.

테이블 크기로 인해 인덱싱이 실시간 문제가 발생합니다. 또한 파일에 보관 된 다음 다시 수집하는 것도 불가능합니다. 또한 명령 줄의 이점도 없습니다. SQL 에서이 작업을 수행해야합니다.

내가하고 싶은 것은 기적 명령으로 정확한 사본을 만들거나, 가능하지 않은 경우 모든 금기 사항이 색인없이 테이블을 복사하여 '정신적'제약 조건인지 확인하는 것입니다 (일명 SERIAL 열에 대한 새 카운터). 그런 다음 a를 사용하여 모든 데이터 SELECT *를 복사합니다.

출처

1. 복사에 대한 스택 오버플로 질문 : 이것은 세 가지 요구하는 것이 아닙니다.

   • 명령 줄 옵션을 사용 하며이 pg_dump -t x2 | sed 's/x2/x3/g' | psql설정에서는 명령 줄에 액세스 할 수 없습니다.
   • 느린 수준의 사전 데이터 수집을 생성합니다.
   • 에 의해 문서로 문서 열을 문서화하지 않습니다. default nextval('x1_id_seq'::regclass)

2. postgres 테이블에 대한 시퀀스 값을 그대로 유지하는 방법 : 훌륭하지만 안타깝게도 매우 수동적입니다.

---

글쎄요, 안타깝게도 손 으로이 일을해야합니다. 그러나 그것은 모두 psql과 같은 것입니다. 첫 번째 명령은 간단합니다.

select * into newtable from oldtable

새롭게 강화가 아닌 이전 테이블의 데이터로 새 테이블이 생성됩니다. 그런 다음 투표와 시퀀스 등을 직접 결정합니다. 다음 명령을 사용하여 테이블의 모든 고급 목록을 사용할 수 있습니다.

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로 바꿉니다.

---

create table asPostgreSQL 의 기능은 이제 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)

구문과 같은 생성 테이블에는 모든 트리거, 수준, 제약 조건 등이 포함되어 있습니다.

---

가장 가까운 "기적의 명령"은 다음과 달라집니다.

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

특히 테이블 데이터를로드 한 후 고급 생성을 처리합니다.

그러나 그것은 시퀀스를 존재하지 않습니다. 직접 펼쳐서 작성해야합니다.

---

테이블 구조와 데이터를 모두 포함하여 테이블을 완전히 제거한 다음 문을 사용합니다.

CREATE TABLE new_table AS 
TABLE existing_table;

데이터없이 테이블 구조를 복사하려면 다음과 같이 CREATE TABLE 문에 WITH NO DATA 절을 추가합니다.

CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;

기존 테이블에서 부분 데이터가있는 테이블을 복사하려면 다음 문을 사용합니다.

CREATE TABLE new_table AS 
SELECT
*
FROM
    existing_table
WHERE
    condition;

---

경고:

소스 테이블의 이름을 대체하기 위해 pg_dump와 모든 종류의 정규식을 사용하는 모든 답변은 정말 위험합니다. 데이터에 바꾸려는 부분 문자열이 포함되어 있으면 어떻게됩니까? 데이터를 변경하게됩니다!

2 단계 솔루션을 제안합니다.

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"테이블을 "members_copy_20130320"에 복사하려고합니다. 내 데이터 별 정규식은 /^\d+\t.*(?:t|f)$/입니다.

위 유형의 솔루션이 저에게 효과적입니다. 주의 사항 ...

편집하다:

좋아요, 여기 정규식을 싫어하는 사람들을위한 의사 쉘 구문의 또 다른 방법이 있습니다 :

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

---

분명히 당신은 테이블을 "재 구축"하고 싶습니다. 테이블을 복사하지 않고 재 빌드하려는 경우 대신 CLUSTER를 사용해야합니다.

SELECT count(*) FROM table; -- make a seq scan to make sure the table is at least
                            -- decently cached
CLUSTER someindex ON table;

인덱스를 선택하고 쿼리에 맞는 인덱스를 선택하십시오. 다른 색인이 적합하지 않은 경우 항상 기본 키를 사용할 수 있습니다.

테이블이 너무 커서 캐시 할 수없는 경우 CLUSTER가 느려질 수 있습니다.

---

newTableName 테이블을 만듭니다 (인덱스를 포함하는 oldTableName과 유사). newTableName에 삽입 select * from oldTableName

이것은 나를 위해 일했습니다 9.3

참고 URL : https://stackoverflow.com/questions/6601978/completely-copying-a-postgres-table-with-sql