Thread: Cannot rename file pg_xlog\0000.....

Hi,

I had someone pull the plug on my database about a week ago.  When it was restarted
postgres would not start because of the postmaster.pid file existed already.  I deleted
the pid file and attempt to restart the postmaster but it again failed.  The error this
time was that Log could not rename the file pg_xlog\something to pg_xlog\something new.
 I then rebooted the machine again, the postmaster service started up, and everything
seemed to be ok.

I am now having problems where everything on the database seems to be working fine but
then it starts going slower and slower.  Simple COPY table X FROM File statements that
normally take 10 seconds at the most can now take 20 minutes (1000 rows) if they ever
finish.

Rebooting the system seems to help for a little while but sometimes the postmaster
service starts immediately and other times it hangs while trying to start.

I have noticed that in the event logs that I am again seeing errors about the renaming:
LOG: could not rename file
"pg_xlog/000000010000006900000021" to "pg_xlog/000000010000006900000030" continuing to try

Windows 2003 Server SP1 - Postgres 8.1.5.  Original install from 8.1 and upgraded using
standard windows installer through each version.

I have manually vacuumed the databases, including the postgres db, to check for errors
(I normally use autovacuum), rebuilt all indexes using the force option on all databases
including the postgres db.

My next attempt to "fix" the problem was to create a new database and restore it using
the latest backup I have or can make.

Any other suggestions??

Mike

--
Open WebMail Project (http://openwebmail.org)


--
Open WebMail Project (http://openwebmail.org)

Mike G wrote:
> Hi,
>
> I had someone pull the plug on my database about a week ago.  When it was restarted
> postgres would not start because of the postmaster.pid file existed already.  I deleted
> the pid file and attempt to restart the postmaster but it again failed.  The error this
> time was that Log could not rename the file pg_xlog\something to pg_xlog\something new.
>  I then rebooted the machine again, the postmaster service started up, and everything
> seemed to be ok.
>
> I am now having problems where everything on the database seems to be working fine but
> then it starts going slower and slower.  Simple COPY table X FROM File statements that
> normally take 10 seconds at the most can now take 20 minutes (1000 rows) if they ever
> finish.
>
> Rebooting the system seems to help for a little while but sometimes the postmaster
> service starts immediately and other times it hangs while trying to start.
>
> I have noticed that in the event logs that I am again seeing errors about the renaming:
> LOG: could not rename file
> "pg_xlog/000000010000006900000021" to "pg_xlog/000000010000006900000030" continuing to try

Sounds to me like it might be a Windows issue with the file - probably
damaged when the plug was pulled.

First I would try and manually do the rename postgres attempted then try
and delete pg_xlog/000000010000006900000021 or at least move it out of
the xlog dir.

If you can't delete or rename the file one possibility is permissions on
the file.

If you can move it but not delete it then move it somewhere and forget
about it.

I have seen a few examples of bad files that windows (2000 server) can't
delete and have had to sit there and be ignored.

One I haven't tried is booting the machine with something like knoppix
live cd and rename/delete from there. This can recover files from a disk
that windows won't boot from but I'm not sure if writing to NTFS has
progressed since I last looked.

> Windows 2003 Server SP1 - Postgres 8.1.5.  Original install from 8.1 and upgraded using
> standard windows installer through each version.
>
> I have manually vacuumed the databases, including the postgres db, to check for errors
> (I normally use autovacuum), rebuilt all indexes using the force option on all databases
> including the postgres db.
>
> My next attempt to "fix" the problem was to create a new database and restore it using
> the latest backup I have or can make.
>
> Any other suggestions??
>
> Mike
>
> --
> Open WebMail Project (http://openwebmail.org)
>
>
> --
> Open WebMail Project (http://openwebmail.org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>
>


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

On 11/3/06, Shane Ambler <pgsql@007marketing.com> wrote:

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

hi mike

you seem to have run into the same (or similar) bug as i did with 8.2 (also
w2k3 here)

> I have noticed that in the event logs that I am again seeing errors about
> the renaming:
> LOG: could not rename file
> "pg_xlog/000000010000006900000021" to "pg_xlog/000000010000006900000030"
> continuing to try

there is some discussion of it in [BUGS]. and there is a patch by tom /
magnus for the win32 code that fixed the problem for me, but it is not yet
in the 8.2beta3 win32 executable.

- thomas