Re: Postgres dump & restore madness

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.

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

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

On Thu, 4 Sep 2003, Marcus Andree S. Magalhaes wrote:

> I need to transfer a database from postgres 7.3.3 to another server, managed
> by other people, running version 7.2.3.
>
> 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.

Can you give some examples of the lines that errored and what the errors
were to help us narrow it down a bit.