Re: Postgres dump & restore madness

Am Do, 2003-09-04 um 18.12 schrieb Marcus Andree S. Magalhaes:
> Hello, all.
>
> I need to transfer a database from postgres 7.3.3 to another server, managed
> by other people, running version 7.2.3.

backup
pg_dump -D dbname > filename.sql

restore
createdb dbname
psql dbname -f filename.sql

That works fine, even with 7.2.

7.2 stores Foreign Keys as CONSTRAINT TRIGGERS (3 triggers for each FK)
They still work after dump/restore.

Since 7.3 there is 'nice' FK support where FK look like FK in dumps.

> So, I've pg_dump'ed the db with SQL output.
>
> While visually inspecting the file, I've noticed that were no reference
> to table constraints, like foreign keys. Going on with the process, I've
> tried to restore the database using psql -f <filename> and it generated
> tons of errors (one error per SQL command, I guess) related to SQL syntax.
>
> All tables and the database itself were created OK (except by the lack
> of constraints) but they weren't populated (certainly the number of
> SQL syntax errors can explain this very well).
>
> So, here go my questions (please forgive my dumbness) :
>
> 1 - How can a postgresql database be safely restored with all constraints, views,
> functions and other non-data related information?
>
> 2 - When I dump a db into SQL commands, they are supposed to be more or less
> portable, aren't they?
>
> 3 - Can anyone provide some ideas about how can I make this process work? I'm
> really begining to become frustrated about this.
>
> Any help is welcome.
>
> Thanks indeed.
>
> marcus.
>
>
>
>
> -------------------------------
> http://www.vlinfo.com.br
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de