Re: restoring from dump - Mailing list pgsql-admin

Hi Jeff,

Thanks for getting back to me.

> Do the databases you are trying to restore already exist?
Yes.

> If so, restoring from a dumpall file won't work out very
> well for you.
Mmm...

> What exactly are you trying to restore?
> Perhaps you only want to restore a few tables?
No.  Please read on.

> If you want to restore all the databases that were in this
> postgresql installation at the time of the backup, then the
> best thing to do is drop all those DBs before attempting the
> restore.
Yes, the database in question is built and updated continuously
from a several sources.  There was a massive power failure,
a series of them, and things got to be a very inconsistent
state and therefore we need to go back to a reliable, trustworthy
backup and then rebuild from there.

What I gather, from your comments, all I have to do would to issue
a psql command:
  drop database <dbname>
then repeat what I did from shell prompt, ie.,
  zcat <compressed_file> | psql postgres > restore.out 2 > restore.err

Would you mind confirming if I am understanding you correctly?

Many thanks for your assistance.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu



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

On Fri, 22 Aug 2008, Tena Sakai wrote:

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

Tena,

Do the databases you are trying to restore already exist?  If so, restoring
from a dumpall file won't work out very well for you.  What exactly are you
trying to restore?  Perhaps you only want to restore a few tables?  If you
want to restore all the databases that were in this postgresql installation at
the time of the backup, then the best thing to do is drop all those DBs before
attempting the restore.  If you just want to get a few tables out of the
dumpall file, then you'll need to uncompress it and open it in an editor to
pull out the COPY statements you need, then feed those through psql.

I think we need more info about what you're trying to accomplish though.




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