Re: Backing up multiple databases

Jacob Atzen wrote:
> Hello list,
>
> I have a setup with multiple databases running on one Postgres. Say,
> db1, db2 and db3.
>
> I have two problems with this setup, the first is how to restore one of
> the databases and leave the other two intact. If for example somebody
> accidentally deletes data from db1 which needs to be restored I would
> need to restore db1 but not db2 and db3. As far as I can tell there is
> no easy way to do this with the current tools.

Eh? pg_dump -U my_user my_db > dump_file

I could make a script to
> clean out the unneeded parts of the dump but before I do that I want to
> make sure, there's no easier way to do this.

Course there is - you can restore a single table, or a single schema, or
even (with the --list option) a selected list of objects.

> The second problem is a matter of database ownership. Apparently
> pg_dumpall will dump the owners of the database along with the data.
> This is causing trouble when I try to restore the dump on a server where
> the owner doesn't exist. At the moment I have the server running on a
> machine where the default owner is "pgsql" but on my local machine the
> name is "postgres". How do I get around this? Should I just abandon
> pg_dumpall and use pg_dump instead or is there some other way?

What's the problem with creating a superuser called "postgres" on both
machines? Or you could choose not to dump (or restore) ownership
information (--no-owner). The section of the manuals you want is
"PostgreSQL Client Applications" - it covers all the options.

I'd use pg_dump anyway - unless you have hundreds of databases, it makes
it easier to keep by backups separate.

--
Richard Huxton
Archonet Ltd