Thread: Can't get postmaster to restart after removing WAL files

Can't get postmaster to restart after removing WAL files

From
"Nick Fankhauser"
Date:
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/


Re: Can't get postmaster to restart after removing WAL files

From
"Randy Hall"
Date:
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
>


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


Re: Can't get postmaster to restart after removing WAL files

From
Robert.Farrugia@go.com.mt
Date:
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



Re: Can't get postmaster to restart after removing WAL files

From
"Nick Fankhauser"
Date:
> 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


Re: Can't get postmaster to restart after removing WAL files

From
Robert.Farrugia@go.com.mt
Date:
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