Re: Restoring a database

[Jeff Frost <jeff@xxxxxxxxxxxxxxxxxxxxxx>]

Jeff Frost wrote:

Chris Henderson 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.
  
    

Chris,

pg_restore is used to restore backups that were saved in the custom
format by pg_dump (option -Fc).  You don't need pg_restore to restore a
  

Whoops, that should say, in the custom or tar format (options -Fc or -Ft).    

pg_dumpall archive.  If you look at the backup file, you'll find that
it's just straight SQL.  If you want to restore a particular database
out of it and not all of them, then you will need to edit the sql file
to include only what you want to restore.  Then you simply pass it
through psql like so:

psql -f /tmp/postgres.backup.`hostname` postgres

Hint: the above will require that the CREATE DATABASE and \connect
commands for the database you want to restore are still in the file.

  

-- 
Jeff Frost, Owner 	<jeff@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 916-647-6411	FAX: 916-405-4032