Thread: Can't get postmaster to restart after removing WAL files
Hi all-
I caused a crash by filling up the filesystem that pg_xlog lives on whilst
doing a vacuum analyze. When I looked at the remains, there were 3 WAL files
out there & no space left.
I tried simply restarting (/etc/init.d/postgresql start) in the hopes that
the mess would get cleaned, but with no luck. The startup script reported
success, but no postmaster was running.
I tried removing the WAL files & restarting again with the same results- the
startup script reports success, but postmaster isn't running.
I'm not worried about the data- this is on a demo system, so I can
regenerate it quickly, but I can't even get the postmaster started so I can
begin the restore. I guess I've learned that removing a WAL file is a Bad
Idea (tm)... but now I need a good idea to get me back to the starting
point.
Any ideas?
TIA-
-Nick
---------------------------------------------------------------------
Nick Fankhauser
nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
Since you can recreate or restore the data, erase everything in your $PGDATA directory and reinitialize the database. While you are at it, you should consider putting $PGDATA on a filesystem with more space. -- Randy Hall - Red Hat Certified Engineer - Ex-Great Bridge PostgreSQL Expert Resume: http://members.home.net/rthall3 ----- Original Message ----- From: "Nick Fankhauser" <nickf@ontko.com> To: "pgsql-admin" <pgsql-admin@postgresql.org> Sent: Tuesday, September 11, 2001 7:16 AM Subject: [ADMIN] Can't get postmaster to restart after removing WAL files > Hi all- > > I caused a crash by filling up the filesystem that pg_xlog lives on whilst > doing a vacuum analyze. When I looked at the remains, there were 3 WAL files > out there & no space left. > > I tried simply restarting (/etc/init.d/postgresql start) in the hopes that > the mess would get cleaned, but with no luck. The startup script reported > success, but no postmaster was running. > > I tried removing the WAL files & restarting again with the same results- the > startup script reports success, but postmaster isn't running. > > I'm not worried about the data- this is on a demo system, so I can > regenerate it quickly, but I can't even get the postmaster started so I can > begin the restore. I guess I've learned that removing a WAL file is a Bad > Idea (tm)... but now I need a good idea to get me back to the starting > point. > > Any ideas? > > TIA- > > -Nick > > > --------------------------------------------------------------------- > Nick Fankhauser > > nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788 > doxpop - Court records at your fingertips - http://www.doxpop.com/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl >
How much space should I allocate for WAL files? (was: RE: Can't get postmaster to restart after removing WAL files)
From
"Nick Fankhauser"
Date:
> Since you can recreate or restore the data, erase everything in > your $PGDATA directory and reinitialize the database. Thanks! I'll give that a try. > While you are at it, you should > consider putting $PGDATA on a filesystem with more space. Doing that has just become a priority for me! <g> I'd like to approach this logically instead of just taking a shot in the dark. Is there any way to predict in advance how much space I'll need? -Nick > -- > Randy Hall - Red Hat Certified Engineer - Ex-Great Bridge > PostgreSQL Expert > Resume: http://members.home.net/rthall3 > > ----- Original Message ----- > From: "Nick Fankhauser" <nickf@ontko.com> > To: "pgsql-admin" <pgsql-admin@postgresql.org> > Sent: Tuesday, September 11, 2001 7:16 AM > Subject: [ADMIN] Can't get postmaster to restart after removing WAL files > > > > Hi all- > > > > I caused a crash by filling up the filesystem that pg_xlog > lives on whilst > > doing a vacuum analyze. When I looked at the remains, there were 3 WAL > files > > out there & no space left. > > > > I tried simply restarting (/etc/init.d/postgresql start) in the > hopes that > > the mess would get cleaned, but with no luck. The startup > script reported > > success, but no postmaster was running. > > > > I tried removing the WAL files & restarting again with the same results- > the > > startup script reports success, but postmaster isn't running. > > > > I'm not worried about the data- this is on a demo system, so I can > > regenerate it quickly, but I can't even get the postmaster started so I > can > > begin the restore. I guess I've learned that removing a WAL > file is a Bad > > Idea (tm)... but now I need a good idea to get me back to the starting > > point. > > > > Any ideas? > > > > TIA- > > > > -Nick > > > > > > --------------------------------------------------------------------- > > Nick Fankhauser > > > > nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788 > > doxpop - Court records at your fingertips - http://www.doxpop.com/ > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
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. It is not the recommended way but I used in in dire emergencies and it worked. Normally, when you fill up the drive because of WALs, you should try to free up 16MB. If the drive is the same as the database, you can move one file from the database directory to another partition, create a soft link to the file, and restart the database. Once the WAL files are removed by the postmaster, you should stop it, and copy back the file. Try to upgrade to 7.1.3 since it solves some issues where long transactions take up an unordinated amount of space unecessary. 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). Hope it helps Robert
> 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
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