Skip site navigation (1) Skip section navigation (2)

Re: restoring from dump

From: "Tena Sakai" <tsakai(at)gallo(dot)ucsf(dot)edu>
To: "Marcelo Martins" <pglists(at)zeroaccess(dot)org>,<pgsql-admin(at)postgresql(dot)org>
Subject: Re: restoring from dump
Date: 2008-08-24 21:24:14
Message-ID: FE44E0D7EAD2ED4BB2165071DB8E328C0378F41A@egcrc-ex01.egcrc.org (view raw or flat)
Thread:
Lists: pgsql-admin
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
>
>


In response to

Responses

pgsql-admin by date

Next:From: Bob LunneyDate: 2008-08-25 05:17:12
Subject: PITR wrm-standby startup fails
Previous:From: Tena SakaiDate: 2008-08-24 17:07:32
Subject: Re: restoring from dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group