Re: Restoring a database

On Wed, Oct 15, 2008 at 3:19 PM, Chris Henderson <henders254(at)gmail(dot)com> wrote:
> I backup all my databases by using pg_dumpall - pg_dumpall >
> /tmp/postgres.backup.`hostname`. It should backup four DBs: analyze,
> postgres, template0 and template1
> I guess this backs up the schemas as well.
>
> Now I want to restore one of the databases and schema from this backup
> dump file onto a different server. The databases is call "analyze".
> Does anyone know how to do that with pg_restore? Thanks.

This is a common mistake. Regular pg_dump / pg_dumpall without custom
format are just plain text dumps and are designed to be restored with
psql. You can take a text editor (or tail / head etc) to the text
file and chop out the parts you want, or you can make a compressed
backup and use pg_restore to do things like what you want.

I generally do a pg_dumpall --globals, and then pg_dump for each
database into a separate file.