Postgres Pro
Downloads
Home   >   mailing lists

Thread: wrong objects order using pg_dump

wrong objects order using pg_dump

From
Oleg
Date:
Dear All,
I am trying to copy the PostGIS Database from Linux computer to Windows.
Under Linux Debian we have PostgreSQL 7.4.6, under Windows XP we have
PostgreSQL 8.1.0-2 .
I am creating dump under Linux with pg_dump myDB > myDBdump.sql
Under windows I am creating new DB (it is automatically PostGIS db) and
then using
psql -f myDBdump.sql -d myWinDB -U postgres
After accomplishing this request WinDB missing some Views. The problem
is that myDBdump.sql has in some places wrong order of CREATE VIEW and
CREATE TABLE statements.
For example:
CREATE VIEW myView AS SELECT .... myTable...;
is located before the:
CREATE TABLE myTable ....

while trying to create view the "relation "myTable" does not exist"
ERROR arised.

What is the right way to copy Database from one computer to another?
Thanks a lot in advance
Oleg

Re: wrong objects order using pg_dump

From
Jaime Casanova
Date:
On 1/30/06, Oleg <evdakov@iwk.uka.de> wrote:
> Dear All,
> I am trying to copy the PostGIS Database from Linux computer to Windows.
> Under Linux Debian we have PostgreSQL 7.4.6, under Windows XP we have
> PostgreSQL 8.1.0-2 .
> I am creating dump under Linux with pg_dump myDB > myDBdump.sql
> Under windows I am creating new DB (it is automatically PostGIS db) and
> then using
> psql -f myDBdump.sql -d myWinDB -U postgres
> After accomplishing this request WinDB missing some Views. The problem
> is that myDBdump.sql has in some places wrong order of CREATE VIEW and
> CREATE TABLE statements.
> For example:
> CREATE VIEW myView AS SELECT .... myTable...;
> is located before the:
> CREATE TABLE myTable ....
>
> while trying to create view the "relation "myTable" does not exist"
> ERROR arised.
>
> What is the right way to copy Database from one computer to another?
> Thanks a lot in advance
> Oleg
>
>

use the pg_dump from the the 8.1 to make the backup:
pg_dump --host <host> --port <port> <your options> base > base.sql

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Re: wrong objects order using pg_dump

From
Tom Lane
Date:
Oleg <evdakov@iwk.uka.de> writes:
> I am trying to copy the PostGIS Database from Linux computer to Windows.
> Under Linux Debian we have PostgreSQL 7.4.6, under Windows XP we have
> PostgreSQL 8.1.0-2 .
> I am creating dump under Linux with pg_dump myDB > myDBdump.sql

Try using the 8.1 pg_dump to suck data from the 7.4 database.  7.4's
pg_dump is not very smart about inter-object dependencies, especially
not if you ALTERed any objects after initially creating them.  8.0 or
8.1 pg_dump should get this right though.

            regards, tom lane

Re: wrong objects order using pg_dump

From
Oleg
Date:
Hi Jaime
Thank you very much. It works fine now.
Best regards
Oleg


Jaime Casanova schrieb:

>On 1/30/06, Oleg <evdakov@iwk.uka.de> wrote:
>
>
>>Dear All,
>>I am trying to copy the PostGIS Database from Linux computer to Windows.
>>Under Linux Debian we have PostgreSQL 7.4.6, under Windows XP we have
>>PostgreSQL 8.1.0-2 .
>>I am creating dump under Linux with pg_dump myDB > myDBdump.sql
>>Under windows I am creating new DB (it is automatically PostGIS db) and
>>then using
>>psql -f myDBdump.sql -d myWinDB -U postgres
>>After accomplishing this request WinDB missing some Views. The problem
>>is that myDBdump.sql has in some places wrong order of CREATE VIEW and
>>CREATE TABLE statements.
>>For example:
>>CREATE VIEW myView AS SELECT .... myTable...;
>>is located before the:
>>CREATE TABLE myTable ....
>>
>>while trying to create view the "relation "myTable" does not exist"
>>ERROR arised.
>>
>>What is the right way to copy Database from one computer to another?
>>Thanks a lot in advance
>>Oleg
>>
>>
>>
>>
>
>use the pg_dump from the the 8.1 to make the backup:
>pg_dump --host <host> --port <port> <your options> base > base.sql
>
>--
>regards,
>Jaime Casanova
>(DBA: DataBase Aniquilator ;)
>
>
>