Thread: restore a dump file (with postgis tables): errors!!

restore a dump file (with postgis tables): errors!!

From
Pedro Salazar
Date:
Greetings,

I have a pgsql 7.3 database where I created postgis tables and my schema
with my object model database. I want to dump my database and restore it
in another machine with pgsql (also 7.3.2).

what I did:

1- pg_dump -U postgres locawadb | gzip > locawa1.dmp.gz

2- createdb -U postgres locawadb
   (create users in database)
   gunzip -c locawa1.dmp.gz | psql -U postgres locawadb

But I got a few errors on the 2 step.

With -a option (echo) in psql I noticed that some errors where about:

- the user owner from my schema could not create a schema (well I
supposed that this operation should be done through super user
postgres);
- that functions like srid() and others did not exist!? (shouldn't
postgres warrant the order of dependences?);
- that some user tables from my schema with geom data types failed to
restore;
- and a few class errors (?) and invalid namespace of my schema!!

My question is: are my procedures of dump and restore database well
done?  (I already saw that exist a pg_restore command line, ...is it
equal to the psql command line or it's better?)

thanks,
Pedro Salazar.
--
PS
pedro-b-salazar@ptinovacao.pt
PGP:0E129E31D803BC61


Re: restore a dump file (with postgis tables): errors!!

From
Robert Treat
Date:
On Fri, 2003-04-04 at 04:56, Pedro Salazar wrote:
> Greetings,
>
> I have a pgsql 7.3 database where I created postgis tables and my schema
> with my object model database. I want to dump my database and restore it
> in another machine with pgsql (also 7.3.2).
>
> what I did:
>
> 1- pg_dump -U postgres locawadb | gzip > locawa1.dmp.gz
>
> 2- createdb -U postgres locawadb
>    (create users in database)
>    gunzip -c locawa1.dmp.gz | psql -U postgres locawadb
>
> But I got a few errors on the 2 step.
>
> With -a option (echo) in psql I noticed that some errors where about:
>
> - the user owner from my schema could not create a schema (well I
> supposed that this operation should be done through super user
> postgres);
> - that functions like srid() and others did not exist!? (shouldn't
> postgres warrant the order of dependences?);
> - that some user tables from my schema with geom data types failed to
> restore;
> - and a few class errors (?) and invalid namespace of my schema!!
>

I'm wondering if your second database has postgis properly set up? I'm
not familiar with it, but it sounds like the postgis support isn't
meshing.

> My question is: are my procedures of dump and restore database well
> done?  (I already saw that exist a pg_restore command line, ...is it
> equal to the psql command line or it's better?)

It's better in some cases worse in others. Probably worth a try. You
might also try and dump only your schema and restore that to make sure
that the schema is being transferred correctly before you worry about
the data.

Robert Treat