Postgres 데이터베이스에서 모든 테이블 자르기

Postgres 데이터베이스에서 모든 테이블 자르기

---

재 구축하기 전에 PostgreSQL 데이터베이스에서 모든 데이터를 정기적으로 삭제해야합니다. SQL에서 직접 어떻게합니까?

현재 나는 실행해야 할 모든 명령을 반환하는 SQL 문을 만들었습니다.

SELECT 'TRUNCATE TABLE ' ||  tablename || ';' FROM pg_tables WHERE tableowner='MYUSER';

그러나 일단 프로그래밍 방식으로 실행하는 방법을 볼 수 없습니다.

---

FrustratedWithFormsDesigner가 정확하면 PL / pgSQL이이를 수행 할 수 있습니다. 스크립트는 다음과 같습니다.

CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public';
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

그러면 저장된 함수 (한 번만 수행하면 됨)가 생성되어 나중에 다음과 같이 사용할 수 있습니다.

SELECT truncate_tables('MYUSER');

---

plpgsql에서는 명시 적 커서가 거의 필요하지 않습니다. 루프 의 더 간단하고 빠른 암시 적 커서 를 사용하십시오 FOR.

참고 : 테이블 이름은 데이터베이스마다 고유하지 않으므로 테이블 이름을 스키마로 한정해야합니다. 또한 함수를 기본 스키마 'public'으로 제한합니다. 여러분의 필요에 적응하지만, 시스템 스키마를 제외해야 pg_*하고 information_schema.

이 기능들에 매우주의 하십시오 . 그들은 당신의 데이터베이스를 핵화합니다. 어린이 안전 장치를 추가했습니다. 폭탄을 프라이밍하기 위해 RAISE NOTICE라인을 주석 처리하고 주석 EXECUTE을 제거하십시오

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
  RETURNS void AS
$func$
DECLARE
   _tbl text;
   _sch text;
BEGIN
   FOR _sch, _tbl IN 
      SELECT schemaname, tablename
      FROM   pg_tables
      WHERE  tableowner = _username
      AND    schemaname = 'public'
   LOOP
      RAISE NOTICE '%',
      -- EXECUTE  -- dangerous, test before you execute!
         format('TRUNCATE TABLE %I.%I CASCADE', _sch, _tbl);
   END LOOP;
END
$func$ LANGUAGE plpgsql;

format()Postgres 9.1 이상이 필요합니다. 이전 버전에서는 다음과 같이 쿼리 문자열을 연결합니다.

'TRUNCATE TABLE ' || quote_ident(_sch) || '.' || quote_ident(_tbl)  || ' CASCADE';

단일 명령, 루프 없음

TRUNCATE한 번에 여러 테이블을 사용할 수 있으므로 커서 나 루프가 전혀 필요하지 않습니다.

• 배열에서 테이블 이름 전달

모든 테이블 이름을 집계하고 단일 명령문을 실행하십시오. 더 간단하고 빠르게 :

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
  RETURNS void AS
$func$
BEGIN
   RAISE NOTICE '%', 
   -- EXECUTE  -- dangerous, test before you execute!
  (SELECT 'TRUNCATE TABLE '
       || string_agg(format('%I.%I', schemaname, tablename), ', ')
       || ' CASCADE'
   FROM   pg_tables
   WHERE  tableowner = _username
   AND    schemaname = 'public'
   );
END
$func$ LANGUAGE plpgsql;

요구:

SELECT truncate_tables('postgres');

정제 된 쿼리

You don't even need a function. In Postgres 9.0+ you can execute dynamic commands in a DO statement. And in Postgres 9.5+ the syntax can be even simpler:

DO
$func$
BEGIN
   RAISE NOTICE '%', 
   -- EXECUTE
   (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE'
    FROM   pg_class
    WHERE  relkind = 'r'  -- only tables
    AND    relnamespace = 'public'::regnamespace
   );
END
$func$;

About the difference between pg_class, pg_tables and information_schema.tables:

• How to check if a table exists in a given schema

About regclass and quoted table names:

• Table name as a PostgreSQL function parameter

For repeated use

Create a "template" database (let's name it my_template) with your vanilla structure and all empty tables. Then go through a DROP / CREATE DATABASE cycle:

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

This is extremely fast, because Postgres copies the whole structure on the file level. No concurrency issues or other overhead slowing you down.

If concurrent connections keep you from dropping the DB, consider:

• Force drop db while others may be connected

---

If I have to do this, I will simply create a schema sql of current db, then drop & create db, then load db with schema sql.

Below are the steps involved:

1) Create Schema dump of database (--schema-only)

pg_dump mydb -s > schema.sql

2) Drop database

drop database mydb;

3) Create Database

create database mydb;

4) Import Schema

psql mydb < schema.sql

---

In this case it would probably be better to just have an empty database that you use as a template and when you need to refresh, drop the existing database and create a new one from the template.

---

Could you use dynamic SQL to execute each statement in turn? You would probably have to write a PL/pgSQL script to do this.

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html (section 38.5.4. Executing Dynamic Commands)

---

You can do this with bash also:

#!/bin/bash
PGPASSWORD='' psql -h 127.0.0.1 -Upostgres sng --tuples-only --command "SELECT 'TRUNCATE TABLE ' || schemaname || '.' ||  tablename || ';' FROM pg_tables WHERE schemaname in ('cms_test', 'ids_test', 'logs_test', 'sps_test');" | 
tr "\\n" " " | 
xargs -I{} psql -h 127.0.0.1 -Upostgres sng --command "{}"

You will need to adjust schema names, passwords and usernames to match your schemas.

---

Cleaning AUTO_INCREMENT version:

CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public';
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';

        IF EXISTS (
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_name=quote_ident(stmt.tablename) and column_name='id'
        ) THEN
           EXECUTE 'ALTER SEQUENCE ' || quote_ident(stmt.tablename) || '_id_seq RESTART WITH 1';
        END IF;

    END LOOP;
END;
$$ LANGUAGE plpgsql;

---

Guys the better and clean way is to :

1) Create Schema dump of database (--schema-only) pg_dump mydb -s > schema.sql

2) Drop database drop database mydb;

3) Create Database create database mydb;

4) Import Schema psql mydb < schema.sql

It´s work for me!

Have a nice day. Hiram Walker

---

For removing the data and preserving the table-structures in pgAdmin you can do:

• Right-click database -> backup, select "Schema only"
• Drop the database
• Create a new database and name it like the former
• Right-click the new database -> restore -> select the backup, select "Schema only"

참고URL : https://stackoverflow.com/questions/2829158/truncating-all-tables-in-a-postgres-database