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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
