Re: search_path when restoring to new db

PostgreSQL is simply very granular about what it lets you dump. The major point to realize is that pg_dump is useful for getting your data schema and data, and pg_dumpall will capture data schema, data, and most database instance configurations. Pg_dumpall has access to *global* objects, such as roles, tablespaces, and the default search_path.

Pg_dump assumes you're planning on using it to restore to an existing database that's already correctly configured. Pg_dumpall doesn't assume that. However, it does assume that the new database server will be the same version of PostgreSQL, IIRC. I believe it complains if you try to pg_dumpall a database with a different version of pg_dumpall.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of SunWuKung
Sent: Monday, December 11, 2006 3:56 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] search_path when restoring to new db

Thanks for your answers.

I guess it means that I have to issue Alter database after restore
than.
I find this a little scary because this is something that I just
realised now and wouldn't have thought beforehand. Seems to me that to
make an exact copy of a single db it isn't enough to use pg_backup and
pg_restore but need to issue other commands as well.

I think it would help a few users (it would certainly help me) if I
could find a complete example on how to backup a single database on one
server and restore it on another without having to worry about things
that might have been omitted - like this one.

I used to work with SQLServer and while I like Postgres a lot I don't
know enough to take advantage of the flexibility that this
backup-restore mechanism offers. In SQLServer if I had the proper
rights I could take a backup of a db, bring it to another server, say
restore and that's it (ok, I had to repopulate fulltext index) but I
could be sure that the db is in the same state than when I made the
backup. I must say that this was very convenient for me.

I think one should be able to create a backup and restore script that
would do the same with Postgres, but I didn't find one so far. Could
somebody show me an example? Or trying to simplify this is a wrong aim?

Thanks for your help.
Balázs

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match