Re: restoring from dump - Mailing list pgsql-admin

Hi,

I am in a fix...

I have executed the following command being a user postgres:
zcat /var/postgres/backup/20080812.2.gz | psql postgres > restore.out 2 > restore.err

It asked me a password, which I complied.  It worked silently
for a couple of hours and told me:
  Segmentation fault

As I look at the stdout and stderr redirected files, restore.out
has a bunch of lines like:
  You are now connected to database "postgres".
  SET
  SET
  SET
  ALTER ROLE
  ALTER ROLE
    .
    .
  GRANT ROLE
  GRANT ROLE
    .
    .
  You are now connected to database "canon".
  SET
  SET
    .
    .
  ALTER SCHEMA
  ALTER SCHEMA
    .
    .
  ALTER SEQUENCE
   setval
  ---------
   4398404
  (1 row)

  ALTER TABLE
  ALTER SEQUENCE
   setval
  --------
     1379
  (1 row)
    .
    .
  ALTER TABLE
  ALTER TABLE
  SET

where two dots mean ommision by me.  The stderr output
is way too large (6,899,669 lines), but here's a glimpse of it:

cat restore.err | awk '{print $1}' | sort | uniq
  ^
  CONTEXT:
  ERROR:
  invalid
  LINE
  NOTICE:

I am willing to get rid of the database as exists now
and do "createdb" and run zcat + psql.

Does anybody have a better idea/suggestions?

Please advise.

Thank you.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu



-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Tena Sakai
Sent: Fri 8/22/2008 3:56 PM
To: Jeff Frost
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] restoring from dump

Many thanks, Jeff.  I appreciate it.  (And I will take
this opportunity to look at the dump file, if nothing
else, to satisfy my curiosity.)

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Jeff Frost [mailto:jeff@frostconsultingllc.com]
Sent: Fri 8/22/2008 3:54 PM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] restoring from dump

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@frostconsultingllc.com]
> Sent: Fri 8/22/2008 3:18 PM
> To: Tena Sakai
> Cc: pgsql-admin@postgresql.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@frostconsultingllc.com>
Frost Consulting, LLC   http://www.frostconsultingllc.com/
Phone: 916-647-6411     FAX: 916-405-4032