Skip site navigation (1) Skip section navigation (2)

Re: restoring from dump

From: "Tena Sakai" <tsakai(at)gallo(dot)ucsf(dot)edu>
To: "Jeff Frost" <jeff(at)frostconsultingllc(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: restoring from dump
Date: 2008-08-22 22:51:28
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
Many thanks, Jeff.

I am now clued in as to what "postgres" means in the
context of "psql -f infile postgres".  Also, thanks
for a tip for stdout and stderr redirection.

What I am still a bit shaky is whether or not I should
issue "createdb" prior to restore.  I would appreciate
it if you could elucidate.


Tena Sakai

-----Original Message-----
From: Jeff Frost [mailto:jeff(at)frostconsultingllc(dot)com]
Sent: Fri 8/22/2008 3:18 PM
To: Tena Sakai
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] restoring from dump
On Fri, 22 Aug 2008, Tena Sakai wrote:

> Hi Everybody,
> I had a machine crash a couple of days ago, from which
> I have recovered (ie., postgres is running now), but I
> want to restore from a dump I had made.  The dump is
> made by pg_dumpall and I have a gzipped file.
> I am a bit unclear as to how to proceed.
> (A) I get rid of what I have now and use createdb to
>    make a "new" database and use psql.
> (B) Don't bother with createdb and let the tables be
>    overwritten.
> Also the manual tells me (in section 24.1.2):
>  psql -f infile postgres
> The last parameter: "postgres" is this the database
> super-user?  If so, do I need to spell it out if I am
> logged in as user postgres?  If not, what is this?


If you have a gzipped dumpall file, then you would restore it like so:

zcat dumpall.gz | psql postgres

I generally redirect the stdout and stderr output to files for later review 
like this:

zcat dumpall.gz | psql postgres > /tmp/restore.out 2> /tmp/restore.err

In this case 'postgres' is the initial database that you're connecting to with 
psql.  This is sometimes called the maintenance database. The dumpall file 
will contain all the create database commands necessary for restoring every 
database that was dumped.

Jeff Frost, Owner 	<jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC
Phone: 916-647-6411	FAX: 916-405-4032

In response to


pgsql-admin by date

Next:From: Jeff FrostDate: 2008-08-22 22:54:00
Subject: Re: restoring from dump
Previous:From: Jeff FrostDate: 2008-08-22 22:18:38
Subject: Re: restoring from dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group