Can I restore just one schema from a pg_dump of the entire database?

Question

I have backups of my postgres database - the entire database instance in a single nightly backup. Is it possible to restore just one of the database from within that backup? Or if I want to access individual databases (for migration or restoring), do I need to change my database backup scheme to do individual dumps?

Answer 1

Yes, if your backup is "an archive created by pg_dump in one of the non-plain-text formats." - use the "--schema=" option to pg_restore.

On the other hand I'm not sure you're using the correct terminology here - you refer to a DB cluster as "entire database instance"; in the explanation you ask about "database" but in the title you wrote "schema", etc.

Edit: Now, after some deliberation, I believe you have a cluster backup, created with "pg_dumpall". "pg_dumpall" creates only plain-text (SQL-commands) backups. In that case it's not possible to restore only one database (or only one schema from a database).

Then yes, you need to change your backup procedure to backup individual databases using non-plain-text format (--format=custom is the recommended one).

Actually the backup procedure I use on my DB servers does just that.

Answer 2

You can access individual databases from a full database cluster dump in text format (pg_dumpall) by using some text processing like this:

awk '/^\\connect database_name/ {flag=1;print;next}
     /^\\connect/ {flag=0}
     flag { print }' \
   < all_databases.sql \
   > database_name.sql

This would get from pg_dumpall file everything between "\connect database_name" and next "\connect". But it is not very efficient.

But I'd recommend dumping every database separately like this:

# Dumping global data (for example roles)
pg_dumpall -g > /var/lib/pgsql/backups/globals.sql

#Dumping indidual databases in tar (uncompressed binary) format
for dbname in
  `
    psql -qXtc "
      select datname from pg_catalog.pg_database
      where datname<>'template0'" template1
  `
do
  pg_dump -b -F t "$dbname" > "/var/lib/pgsql/backups/$dbname.dump"
done

I'm assuming you mean "Can I restore just one database from a pg_dumpall of the entire database cluster?"