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@xxxxxxxxxxxxxx on behalf of Tena Sakai
Sent: Fri 8/22/2008 3:56 PM
To: Jeff Frost
Cc: pgsql-admin@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxx
-----Original Message-----
From: Jeff Frost [mailto:jeff@xxxxxxxxxxxxxxxxxxxxxx]
Sent: Fri 8/22/2008 3:54 PM
To: Tena Sakai
Cc: pgsql-admin@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxx]
Sent: Fri 8/22/2008 3:18 PM
To: Tena Sakai
Cc: pgsql-admin@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032
