Re: Can't get postmaster to restart after removing WAL files - Mailing list pgsql-admin

From Robert.Farrugia@go.com.mt
Subject Re: Can't get postmaster to restart after removing WAL files
Date
Msg-id OF8A7973CB.24FFC359-ONC1256AC5.0059DD83@go.com.mt
Whole thread Raw
In response to Can't get postmaster to restart after removing WAL files  ("Nick Fankhauser" <nickf@ontko.com>)
List pgsql-admin
This depends on the type of update.  If you commit the transaction
regularly, files will be re-used.  On the other hand, if you commit the
transaction at the end like what the COPY command (pg_dump) does, extra WAL
files will be created as needed (even though the limit has been reached).

Regards
Robert




                    "Nick
                    Fankhauser"          To:     <Robert.Farrugia@go.com.mt>
                    <nickf@ontko.        cc:     <pgsql-admin@postgresql.org>
                    com>                 Subject:     RE: Can't get postmaster to restart after removing WAL files

                    12/09/2001
                    16:12
                    Please
                    respond to
                    nickf






> You can try running pg_resetxlog (situated in the contrib directory in
the
> source).  It will reset the wal files and will allow you to restart the
> postmaster.

I just did an initdb as suggested by Randy since the data was easy to
recreate, but this is good to know as a fallback for future situations!

> Try to upgrade
> to 7.1.3 since it solves some issues where long transactions take up an
> unordinated amount of space unecessary.

I was running v7.1.3 & only had two 41Mb databases running in the cluster,
which was why I was kinda' caught off guard by having 48Mb used in WAL
files.

> As regards to the space required by the WAL files this depends on the
> configuration (postgresql.conf).  There is a paramter which controls the
> amount of WAL files to create (each file takes 16MB).

This helps, but I'm a bit confused- What if I'm runnning an enormous update
& I really need 4 WAL files, but I've set the limit at 3? Will an fsync be
forced so that the first file will be deleted & the space reused?

It sounds like I can limit the space used to match what I have available,
but I'm not sure if this will cause a problem if I'm too stingy on my space
allocation.

Thanks Robert & others for your help with this!

-Nick

---------------------------------------------------------------------
Nick Fankhauser

Business:
nickf@doxpop.com  Phone 1.765.965.7363  Fax 1.765.962.9788
doxpop  - Court records at your fingertips - http://www.doxpop.com/

Personal:
nick@fankhausers.com   http://www.fankhausers.com






pgsql-admin by date:

Previous
From: "Nick Fankhauser"
Date:
Subject: Another WAL question
Next
From: Bruce Momjian
Date:
Subject: Re: Another WAL question