Re: restoring from dump - Mailing list pgsql-admin

Hi,

I downloaded the latest release (8.3.3), followed the
instruction on the manual to install and restored the
file I had made by pg_dumpall, thereby bypassing the
trouble with files in pg_clog directory.

I just read a bit about these files.  They are made
as a result of autovacuum.  Does anybody have notion
as to how long they should be kept, what recycling
policy there ought to be, etc?

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Marcelo Martins [mailto:pglists@zeroaccess.org]
Sent: Mon 8/25/2008 7:06 AM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] restoring from dump

I never tried that and I would be reluctant on doing so since you will 
be getting status of another transactions.

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

On Aug 24, 2008, at 4:24 PM, Tena Sakai wrote:

> 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@gallo.ucsf.edu
>
>
>
>
> -----Original Message-----
> From: Marcelo Martins [mailto:pglists@zeroaccess.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@gallo.ucsf.edu
> >
> >
> >
> > -----Original Message-----
> > From: Jeff Frost [mailto:jeff@frostconsultingllc.com]
> > Sent: Sat 8/23/2008 10:29 PM
> > To: Tena Sakai
> > Cc: pgsql-admin@postgresql.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@gallo.ucsf.edu
> > >
> > > -----Original Message-----
> > > From: Jeff Frost [mailto:jeff@frostconsultingllc.com]
> > > Sent: Fri 8/22/2008 10:28 PM
> > > To: Tena Sakai
> > > Cc: pgsql-admin@postgresql.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@frostconsultingllc.com>
> > Frost Consulting, LLC   http://www.frostconsultingllc.com/
> > Phone: 916-647-6411     FAX: 916-405-4032
> >
> >
>
>
>