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 [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] -----Original Message----- From: Jeff Frost [mailto:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] > 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 <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032