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

Re: restoring from dump

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: Tena Sakai <tsakai(at)gallo(dot)ucsf(dot)edu>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: restoring from dump
Date: 2008-08-22 22:54:00
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
On Fri, 22 Aug 2008, Tena Sakai wrote:

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

No, if you look at the dumpall file, it contains all the 'create database' 
commands to recreate the DBs that were dumped.

> -----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?
> Tena,
> 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: Tena SakaiDate: 2008-08-22 22:56:37
Subject: Re: restoring from dump
Previous:From: Tena SakaiDate: 2008-08-22 22:51:28
Subject: Re: restoring from dump

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