wake-up-neo.net

Abschneiden aller Tabellen in einer Postgres-Datenbank

Ich muss regelmäßig alle Daten aus meiner PostgreSQL-Datenbank löschen, bevor ich sie neu aufbaue. Wie würde ich das direkt in SQL machen?

Im Moment habe ich es geschafft, eine SQL-Anweisung zu finden, die alle Befehle zurückgibt, die ich ausführen muss:

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

Aber ich kann keine Möglichkeit finden, sie programmgesteuert auszuführen, wenn ich sie habe.

137
Sig

FrustratedWithFormsDesigner ist korrekt, PL/pgSQL kann dies tun. Hier ist das Skript:

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;

Dadurch wird eine gespeicherte Funktion erstellt (Sie müssen dies nur einmal tun), die Sie anschließend wie folgt verwenden können:

SELECT truncate_tables('MYUSER');
198
Henning

Explizite Cursor werden in plpgsql selten benötigt. Verwenden Sie den einfacheren und schnelleren impliziten Cursor eines FOR Schleife:

Hinweis: Da Tabellennamen nicht pro Datenbank eindeutig sind, müssen Sie die Tabellennamen mit einem Schema qualifizieren, um sicherzugehen. Außerdem beschränke ich die Funktion auf das Standardschema 'public'. Passen Sie sich Ihren Bedürfnissen an, aber schließen Sie unbedingt die Systemschemata pg_* Und information_schema Aus.

Seien Sie mit diesen Funktionen sehr vorsichtig . Sie zerstören Ihre Datenbank. Ich habe eine Kindersicherung eingebaut. Kommentieren Sie die Zeile RAISE NOTICE Und entfernen Sie das Kommentarzeichen EXECUTE, um die Bombe zu zünden ...

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() benötigt Postgres 9.1 oder höher. In älteren Versionen verketten Sie die Abfragezeichenfolge folgendermaßen:

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

Einzelbefehl, keine Schleife

Da wir TRUNCATE mehrere Tabellen auf einmal können, brauchen wir keinen Cursor oder überhaupt eine Schleife:

Aggregieren Sie alle Tabellennamen und führen Sie eine einzelne Anweisung aus. Einfacher, schneller:

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;

Anruf:

SELECT truncate_tables('postgres');

Verfeinerte Abfrage

Sie brauchen nicht einmal eine Funktion. In Postgres 9.0+ können Sie dynamische Befehle in einer DO -Anweisung ausführen. Und in Postgres 9.5+ kann die Syntax noch einfacher sein:

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$;

Über den Unterschied zwischen pg_class, pg_tables Und information_schema.tables:

Informationen zu regclass und Tabellennamen in Anführungszeichen:

Zur mehrmaligen Anwendung

Erstellen Sie eine "Template" -Datenbank (nennen wir es my_template) Mit Ihrer Vanilla-Struktur und allen leeren Tabellen. Dann durchlaufen Sie einen DROP/ CREATE DATABASE Zyklus:

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

Dies ist extrem schnell , da Postgres die gesamte Struktur auf Dateiebene kopiert. Keine Nebenläufigkeitsprobleme oder andere Probleme, die Sie verlangsamen.

Wenn Sie durch gleichzeitige Verbindungen die Datenbank nicht löschen können, beachten Sie Folgendes:

82

Wenn ich das tun muss, erstelle ich einfach ein Schema-SQL der aktuellen Datenbank, lege und erstelle eine Datenbank und lade dann die Datenbank mit dem Schema-SQL.

Nachfolgend sind die Schritte aufgeführt:

1) Schemadump der Datenbank erstellen (--schema-only)

pg_dump mydb -s > schema.sql

2) Datenbank löschen

drop database mydb;

3) Datenbank erstellen

create database mydb;

4) Schema importieren

psql mydb < schema.sql

33
Sandip Ransing

In diesem Fall ist es wahrscheinlich besser, nur eine leere Datenbank als Vorlage zu verwenden. Wenn Sie eine Aktualisierung durchführen müssen, löschen Sie die vorhandene Datenbank und erstellen Sie eine neue aus der Vorlage.

9
Scott Bailey

Reinigung AUTO_INCREMENT Ausführung:

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;
3
RomanGorbatko

Könnten Sie dynamisches SQL verwenden, um jede Anweisung der Reihe nach auszuführen? Dazu müssten Sie wahrscheinlich ein PL/pgSQL-Skript schreiben.

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html (Abschnitt 38.5.4. Ausführen dynamischer Befehle)

Sie können dies auch mit bash tun:

#!/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 "{}"

Sie müssen die Schemanamen, Kennwörter und Benutzernamen an Ihre Schemata anpassen.

3
simao

Jungs, der bessere und sauberere Weg ist:

1) Schemadump der Datenbank erstellen (nur --schema) pg_dump mydb -s> schema.sql

2) Datenbank löschen Datenbank löschen mydb;

3) Datenbank erstellen Datenbank erstellen mydb;

4) Importieren Sie Schema psql mydb <schema.sql

Es ist Arbeit für mich!

Einen schönen Tag noch. Hiram Walker

2
Hiram Walker

Um die Daten zu entfernen und die Tabellenstrukturen in pgAdmin beizubehalten, können Sie Folgendes tun:

  • Klicken Sie mit der rechten Maustaste auf die Datenbank -> Sicherung und wählen Sie "Nur Schema".
  • Löschen Sie die Datenbank
  • Erstellen Sie eine neue Datenbank und benennen Sie sie wie die vorherige
  • Klicken Sie mit der rechten Maustaste auf die neue Datenbank -> Wiederherstellen -> wählen Sie die Sicherung aus und wählen Sie "Nur Schema".
2
mYnDstrEAm