Re: restoring from dump

Hi Marcelo,

> What happened to that clog file? was it deleted?
I assume you mean 0088. I think it got created as
a result of my executing (and failing) "dropdb"
command. Judging from the timestamp, to say otherwise
is not plausible.

> where is 0086, 0087?
I have no idea. We have had a series of power outages
and maybe they got lost as a result. I am not convinced,
however.

Instead of your dd suggestion, I thought of copying
0084 and calling it 0085. I have no idea what consiquence
there might be. Perhaps, you can comment?

Regards,

Tena Sakai
tsakai(at)gallo(dot)ucsf(dot)edu

-----Original Message-----
From: Marcelo Martins [mailto:pglists(at)zeroaccess(dot)org]
Sent: Sun 8/24/2008 12:58 PM
To: Tena Sakai
Subject: Re: [ADMIN] restoring from dump

What happened to that clog file ? was it deleted ?
You could try re-creating it with zero contents as a last resort
though .. the transaction will be lost too.

hmm where is 0086, 0087 ?

postgres~$ dd if=/dev/zero of=/var/lib/pgsql/data/pg_clog/0085 bs=256K
count=1

Marcelo
Linux/Solaris System Administrator
http://www.zeroaccess.org

On Aug 24, 2008, at 12:07 PM, Tena Sakai wrote:

> Hi Jeff,
>
> Quagmire deepens, it seems...
>
> I tried:
> dropdb <myDB>
> and it told me:
> dropdb: database removal failed: ERROR: could not access status
> of transaction 139602298
> DETAIL: Could not open file "pg_clog/0085": No such file or
> directory.
>
> I went into pg_clog directory and issued:
> ls -lt | head
> and it told me:
> -rw------- 1 postgres postgres 163840 Aug 24 09:57 0088
> drwx------ 11 postgres postgres 4096 Aug 22 13:56 ..
> drwx------ 2 postgres postgres 4096 Jun 30 16:03 .
> -rw------- 1 postgres postgres 262144 Jun 1 20:04 0084
> -rw------- 1 postgres postgres 262144 Apr 4 15:48 0083
> -rw------- 1 postgres postgres 262144 Mar 26 18:25 0082
> -rw------- 1 postgres postgres 262144 Mar 26 01:28 0081
> -rw------- 1 postgres postgres 262144 Mar 25 23:05 0080
> -rw------- 1 postgres postgres 262144 Mar 25 20:39 007F
>
> It seems that 0088 was generated at the time very close to
> my issuing dropdb.
>
> What are my options now?
>
> Thank you.
>
> Tena Sakai
> tsakai(at)gallo(dot)ucsf(dot)edu
>
>
>
> -----Original Message-----
> From: Jeff Frost [mailto:jeff(at)frostconsultingllc(dot)com]
> Sent: Sat 8/23/2008 10:29 PM
> To: Tena Sakai
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: RE: [ADMIN] restoring from dump
>
> On Sat, 23 Aug 2008, Tena Sakai wrote:
>
> > Hi,
> >
> > At psql prompt, I tried:
> > drop database myDB;
> > and it told me:
> > ERROR: cannot drop the currently open database
> >
> > Does this mean I have to issue
> > pg_ctl stop
> > before I issue
> > "drop database myDB;"?
> > But if I do so, then how would I get to psql prompt
> > at all?
> >
> > How would I get around this catch-22 situation?
> > Any advice appreciated.
>
> Just connect to a different database to do your drop. This is what
> the
> 'postgres' database is often used for. That's why it is sometimes
> referred to
> as the maintenance DB.
>
> Or you can use the dropdb command. Of course I'd still recommend
> you rename
> the DB till you're sure the restore was successful.
>
>
> >
> > Tena Sakai
> > tsakai(at)gallo(dot)ucsf(dot)edu
> >
> > -----Original Message-----
> > From: Jeff Frost [mailto:jeff(at)frostconsultingllc(dot)com]
> > Sent: Fri 8/22/2008 10:28 PM
> > To: Tena Sakai
> > Cc: pgsql-admin(at)postgresql(dot)org
> > Subject: Re: [ADMIN] restoring from dump
> >
> >
> >
> > Jeff Frost wrote:
> >> Tena Sakai wrote:
> >>>
> >>>> 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?
> >>>
> >> Yes, based on the information you've given us, you should be able
> to
> >> restore the entire database (and any other databases that were in
> the
> >> cluster) by first dropping those databases and then issuing the
> above
> >> command.
> >>
> >> BTW, if you find yourself with an older version of postgresql, this
> >> could be a good opportunity to upgrade. I'm not sure if you
> mentioned
> >> what version you were using in your original post.
> > I should also note that you could rename the database instead of
> > dropping it outright, to make sure your restore is effective before
> > dropping it.
> >
> >
>
> --
> Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 916-647-6411 FAX: 916-405-4032
>
>