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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of SunWuKung Sent: Monday, December 11, 2006 3:56 PM To: pgsql-general@xxxxxxxxxxxxxx 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
