Backup einer DB mit pg_dump: keine Daten, sonst alles

Hallo,

ich habe auf einem postgreSQL 8.4 Server mehrere Datenbanken laufen. Ich muss eine davon ohne die Daten aber ansonstem in voll einsatzbereitem Zustand auf mehreren Entwicklugsservern bereitstellen. Ich brauche also die Benutzer, alle Tabellen, Funktionen, Sequences, Constraints etc aber eine keine Datensätze in den Tabellen auf dem neuen Server.

Das Tool der Wahl scheint laut meinen Recherchen pg_dump zu sein. Ich habe die manpage gelesen und folgendes gemacht:

  1. Auf dem Server, auf dem die DB momentan läuft ein dump erstellt:
postgres@srv> pg_dump --schema-only --format=custom --file=mydb_schema_only.pg_dump mydb
  1. Zum Wiederherstellen auf den Entwicklugsservern:

2.1) Manuell die Benutzer und DB erstellt:

postgres@srv> psql
  psql> CREATE ROLE mydb WITH LOGIN;
  psql> CREATE DATABASE mydb OWNER mydb;
  psql> \q

2.2) Die DB eingespielt:

postgres@srv> pg_restore --dbname=mydb mydb_schema_only.pg_dump

Das sah erst alles ganz gut aus, aber die DB ist unvollständig und nicht lauffähig, denn es fehlen alle contraints in den Tabellen und alle Funktionen. :frowning:

Wie macht man es richtig? In der Dokumentation von pg_dump finde ich weder eine Option, wie man Funktionen oder Constraints ignoriert, noch wie man sie explizit einbezieht. Mir ist nicht klar, warum sie ohne Warnung ignoriert wurden.

Falls das von Interesse ist: auf dem Produktionsserver läuft SuSE Linux, die Development Server laufen unter Win32 (XP), SuSE und Debian Linux.

Vielen Dank für jede Antwort,

pg_dump macht doch genau das, was Du gesagt hast :wink:

–schema-only macht genau das: es werden nur die Schema-Objekte gedumpt, sonst nichts.

Na schön, wenn also eine Funktion und ein Constraint nicht zum Schema gehören sondern zu den Datensätzen (?!): wie erreiche ich mein Ziel?

Das Ziel ist wie gesagt die DB voll funktionsfähig auf den anderen Servern zu haben.

Ich kann nicht Dein Problem nachvollziehen:

kretschmer@tux:~$ dropdb test2
kretschmer@tux:~$ createdb test2
kretschmer@tux:~$ psql test2 -c 'create function f() returns int as $$begin return 1; end;$$language plpgsql; commit;'
WARNING:  there is no transaction in progress
COMMIT
kretschmer@tux:~$ pg_dump --schema-only test2 | grep -i "f()"
-- Name: f(); Type: FUNCTION; Schema: public; Owner: kretschmer
CREATE FUNCTION f() RETURNS integer
ALTER FUNCTION public.f() OWNER TO kretschmer;

Da ist die Funktion im Dump. Das würde sicher auch pasieren, wenn das im Fc - Format wäre. Mach mal ein Listing vom Dump (pg_dump Option -l) und schau, was da alles steht.

Andreas

Hm, du hast recht. Ein paar der Constraints sind im Dump, allerdings fehlen auch viele:

postgres@dev:~> pg_restore -l ptgl_schema_only_ts.pg_dump
;
; Archive created at Mon Feb 21 21:09:48 2011
;     dbname: postgres
;     TOC Entries: 91
;     Compression: -1
;     Dump Version: 1.11-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 8.4.7
;     Dumped by pg_dump version: 8.4.7
;
;
; Selected TOC Entries:
;
6; 2615 2200 SCHEMA - public postgres
2023; 0 0 COMMENT - SCHEMA public postgres
2024; 0 0 ACL - public postgres
402; 2612 23172 PROCEDURAL LANGUAGE - plpgsql postgres
21; 1255 22290 FUNCTION public plpgsql_call_handler() postgres
1595; 1259 22327 TABLE public albe ptgl
// many lines omitted here
2017; 2606 23155 FK CONSTRAINT public procpdb_albe ptgl
2019; 2606 23165 FK CONSTRAINT public procpdb_be ptgl
2016; 2606 23150 FK CONSTRAINT public procpdb_sse ptgl
2015; 2606 23145 FK CONSTRAINT public procpdb_stat ptgl
// many lines omitted here
postgres@dev:~>

Ich mache das erstmal nochmal von vorne, falls ich ein Dump einer falschen Version der DB gezogen habe oder sowas komisches. Ich sage bescheid, was rausgekommen ist…

Danke schonmal.

Oh nein! Es sieht so aus, als würde mein Problem schon früher beginnen:

Die Original-Datenbank, die auf die Development-Server kopiert muss, wurde aus einem alten Komplett-SQL-Dump wiederhergestellt und in ihr fehlen bereits die Constraints! (Ich habe den “Schema-only”-Dump von dieser DB gezogen, daher fehlen natürlich auch in meinem Dump die Constraints). Damit ist klar, dass pg_dump nicht das Problem ist. Es gibt aber leider ein neues Problem. :frowning:

Ich habe gerade das alte Komplett-SQL-Dump benutzt, um die DB nochmal auf dem primären Server wiederherzustellen, dabei kommen bereits Fehlermeldungen, dass die Constraints nicht erstellt werden können:

postgres@ptgl20:/home/ptgl/develop/ptgl_database> psql -U postgres -d ptgl -f ptgl.sql 
psql :p tgl.sql:19: ERROR:  permission denied for language c
psql :p tgl.sql:29: NOTICE:  using pg_pltemplate information instead of CREATE LANGUAGE psql :p tgl.sql:39: WARNING:  no privileges could be revoked for "public"
psql :p tgl.sql:40: WARNING:  no privileges were granted for "public"
GRANT
SET
CREATE FUNCTION
CREATE SEQUENCE
CREATE TABLE
CREATE TABLE
// ***** many lines omitted here ***** 
psql :p tgl.sql:27806742: ERROR:  permission denied for relation protein
psql :p tgl.sql:27806751: ERROR:  permission denied for relation protein
SET
psql :p tgl.sql:27806766: NOTICE:  ignoring incomplete trigger group for constraint "fun_pro_fk" FOREIGN KEY protein(fun_id) REFERENCES funct(function_id)
DETAIL:  Found referencing table's trigger.
CREATE TRIGGER
psql :p tgl.sql:27806779: NOTICE:  ignoring incomplete trigger group for constraint "fun_pro_fk" FOREIGN KEY protein(fun_id) REFERENCES funct(function_id)
DETAIL:  Found referenced table's DELETE trigger.
CREATE TRIGGER
psql :p tgl.sql:27806792: NOTICE:  converting trigger group into constraint "fun_pro_fk" FOREIGN KEY protein(fun_id) REFERENCES funct(function_id)
DETAIL:  Found referenced table's UPDATE trigger.
psql :p tgl.sql:27806792: ERROR:  foreign key constraint "fun_pro_fk" cannot be implemented
DETAIL:  Key columns "fun_id" and "function_id" are of incompatible types: numeric and integer.
// ***** many lines omitted here ***** 
setval 
--------
   3521
(1 row)

SET
psql :p tgl.sql:27807083: ERROR:  must be owner of schema public
SET
COMMENT
postgres@ptgl20:/home/ptgl/develop/ptgl_database>

Das SQL-Dump hat irgendjemand vor mehreren Jahren erstellt, wahrscheinlich mit einer älteren postgreSQL-Version und beim Import scheint etwas schiefzulaufen. Was kann ich da tun?!

Allerdings ist mir bereits rätselhaft, warum ich als DB-admin (postgres user) für irgendwelche der SQL-Befehle im Dump ‘permission denied’-Fehler bekomme…

EDIT: Laut diesem Post soll es mit postgresql <= 8.2 gehen, aber es muss ja wohl auch anders als mit Downgrade machbar sein, alte Dumps einzuspielen…

Ich habe die beiden Probleme wie folgt gelöst (das zweite eher umgangen, aber vielleicht weiss da ja noch jemand was).

Zu dem ersten Problem mit den Fehlern beim Einspielen des SQL-Dumps: ich habe das File mal näher angesehen (ist ja Plaintext) und da standen solche Befehle drin:

SET SESSION AUTHORIZATION [I]<db_user>[/I]

Also wird das Skript anscheinend ab der Stelle als dieser User <db_user> ausgeführt. Habe ihn also als DB-Superuser angelegt und dann ging die Sache. :slight_smile:

Das zweite Problem mit den nicht korrekt importierten Constraints habe ich durch Installation von postgresql 8.2 umgangen. (Habe es erstmal zu Testzwecken selbst kompiliert und neben dem anderen Server laufen lassen, das geht mit ./configure --prefix=/home/<my_unix_user>/software/pgsql82 --with-pgport=6543 vor dem make && make install-Befehl.

Nun kannst Du ein Backup mit den Tools deiner neuen Version von der alten DB machen.

Andreas

Jo, den Plan hatte ich auch schon gefasst.

Hab’s gerade gemacht und es hat anscheinend alles geklappt. Damit funktioniert jetzt auch alles mit Postgres >= 8.4. Wow, was für ein Akt.

Danke für die Hilfe, Problem gelöst.