Thread: restoring from dump

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?

Please advise.  I would appreciate it.

Thank you.

Tena Sakai
tsakai@gallo.ucsf.edu

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@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 916-647-6411    FAX: 916-405-4032

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.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


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

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@frostconsultingllc.com]
> 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     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 916-647-6411    FAX: 916-405-4032

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@gallo.ucsf.edu


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

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
tsakai@gallo.ucsf.edu



-----Original Message-----
From: pgsql-admin-owner@postgresql.org 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
tsakai@gallo.ucsf.edu


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

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@postgresql.org 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
> tsakai@gallo.ucsf.edu
>
>
> -----Original Message-----
> From: Jeff Frost [mailto:jeff@frostconsultingllc.com]
> 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:jeff@frostconsultingllc.com]
>> 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     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 916-647-6411    FAX: 916-405-4032

Hi Jeff,

Thanks for getting back to me.

> Do the databases you are trying to restore already exist?
Yes.

> If so, restoring from a dumpall file won't work out very
> well for you.
Mmm...

> What exactly are you trying to restore?
> Perhaps you only want to restore a few tables?
No.  Please read on.

> 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?

Many thanks for your assistance.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu



-----Original Message-----
From: Jeff Frost [mailto:jeff@frostconsultingllc.com]
Sent: Fri 8/22/2008 9:39 PM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] restoring from dump

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@postgresql.org 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
> tsakai@gallo.ucsf.edu
>
>
> -----Original Message-----
> From: Jeff Frost [mailto:jeff@frostconsultingllc.com]
> 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:jeff@frostconsultingllc.com]
>> 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       <jeff@frostconsultingllc.com>
Frost Consulting, LLC   http://www.frostconsultingllc.com/
Phone: 916-647-6411     FAX: 916-405-4032

> 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.

-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 916-647-6411	FAX: 916-405-4032

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

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.

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

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

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

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
>
>

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
> >
> >
>
>
>

Tena Sakai wrote:
> 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?

They are not created by autovacuum.  They are a very central part of the
system.  Autovacuum can cause them to be removed when they are no longer
needed (manual vacuuming can cause them to be deleted too).  If they are
removed before every last bit that depends on them is, then we have a
bug.

This is very strange:

> > > 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.

When a file is deleted, all files prior to it must be deleted too.  If
files prior to it remained, what it suggests is that the deletion did
not happen because of vacuum or autovacuum, but because of something
else that's not related to Postgres.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Hi Alvaro,

I just looked in pg_clog directory and there is
only one file:
  -rw-------  1 postgres postgres 24576 Aug 25 20:18 0000
I saw the same file about 10:30 am and I believe
it was roughly the same size.  This is a brand new
installation of 8.3.3.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Mon 8/25/2008 7:34 PM
To: Tena Sakai
Cc: Marcelo Martins; jeff@frostconsultingllc.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] restoring from dump

Tena Sakai wrote:
> 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?

They are not created by autovacuum.  They are a very central part of the
system.  Autovacuum can cause them to be removed when they are no longer
needed (manual vacuuming can cause them to be deleted too).  If they are
removed before every last bit that depends on them is, then we have a
bug.

This is very strange:

> > > 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.

When a file is deleted, all files prior to it must be deleted too.  If
files prior to it remained, what it suggests is that the deletion did
not happen because of vacuum or autovacuum, but because of something
else that's not related to Postgres.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Tena Sakai wrote:
> Hi Alvaro,
>
> I just looked in pg_clog directory and there is
> only one file:
>   -rw-------  1 postgres postgres 24576 Aug 25 20:18 0000
> I saw the same file about 10:30 am and I believe
> it was roughly the same size.  This is a brand new
> installation of 8.3.3.

Maybe initdb was executed?  That would cause the files to disappear.
Of course, so would the data; it would have to be restored from a
backup.

Note that these files use only 2 bits per transaction, so in 24756 bytes
you have enough room to cover 99024 transactions.  Furthermore, they
always grow in 8192-byte increments.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Hi Alvaro,

> Maybe initdb was executed?

Yes.  I installed 8.3.3 afresh and as a part of
such initdb was executed.  I have saved (renamed)
the previous distribution (8.3.0) and there are
many files in pg_clog directory there.  The oldest
one is named 00, and consecutively named in hex
2 digit name as 01, 02, .. , 09, 0A, 0B, .. , 84.
Then it skipped to 88 (no 85, 86, 87).  The size
of each file is 262144 bytes, with exception of
88, which is 163840.

I just looked at the one in new (8.3.3) directory:
  -bash-3.00$ date
  Tue Aug 26 08:40:35 PDT 2008
  -bash-3.00$ pwd
  /usr/local/pgsql/data/pg_clog
  -bash-3.00$ ll
  total 24
  -rw-------  1 postgres postgres 24576 Aug 26 02:33 0000

As I compare the size with what's in last night's email,
there is no difference in size.  The timestamp is in the
middle of night.

Fascinating...

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Tue 8/26/2008 7:29 AM
To: Tena Sakai
Cc: Marcelo Martins; jeff@frostconsultingllc.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] restoring from dump

Tena Sakai wrote:
> Hi Alvaro,
>
> I just looked in pg_clog directory and there is
> only one file:
>   -rw-------  1 postgres postgres 24576 Aug 25 20:18 0000
> I saw the same file about 10:30 am and I believe
> it was roughly the same size.  This is a brand new
> installation of 8.3.3.

Maybe initdb was executed?  That would cause the files to disappear.
Of course, so would the data; it would have to be restored from a
backup.

Note that these files use only 2 bits per transaction, so in 24756 bytes
you have enough room to cover 99024 transactions.  Furthermore, they
always grow in 8192-byte increments.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Hi Alvaro,

> Note that these files use only 2 bits per transaction,
> so in 24756 bytes you have enough room to cover 99024
> transactions.  Furthermore, they always grow in 8192-
> byte increments.

I just did:
  -bash-3.00$ pwd; date; ll
and it told me:
  /usr/local/pgsql/data/pg_clog
  Wed Aug 27 09:09:04 PDT 2008
  total 32
  -rw-------  1 postgres postgres 32768 Aug 27 02:33 0000

Yesterday the byte count was 24576.
Voila!  24576 + 8192 -> 32768

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu



-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Tue 8/26/2008 7:29 AM
To: Tena Sakai
Cc: Marcelo Martins; jeff@frostconsultingllc.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] restoring from dump

Tena Sakai wrote:
> Hi Alvaro,
>
> I just looked in pg_clog directory and there is
> only one file:
>   -rw-------  1 postgres postgres 24576 Aug 25 20:18 0000
> I saw the same file about 10:30 am and I believe
> it was roughly the same size.  This is a brand new
> installation of 8.3.3.

Maybe initdb was executed?  That would cause the files to disappear.
Of course, so would the data; it would have to be restored from a
backup.

Note that these files use only 2 bits per transaction, so in 24756 bytes
you have enough room to cover 99024 transactions.  Furthermore, they
always grow in 8192-byte increments.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.