From: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Tory M Blue <tmblue@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PG_XLOG 27028 files running out of space
Date: Thu, 14 Feb 2013 13:08:58 +0200
Message-ID: <511CC5CA.2000403@vmware.com> (raw)
In-Reply-To: <CAEaSS0Ywu2gc5M1aowPxmnb_jfdjdicv0QGPmFdeDwnqV-zHGA@mail.gmail.com>
References: <CAEaSS0Ywu2gc5M1aowPxmnb_jfdjdicv0QGPmFdeDwnqV-zHGA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 14.02.2013 12:49, Tory M Blue wrote:
> My postgres db ran out of space. I have 27028 files in the pg_xlog
> directory. I'm unclear what happened this has been running flawless for
> years. I do have archiving turned on and run an archive command every 10
> minutes.
>
> I'm not sure how to go about cleaning this up, I got the DB back up, but
> I've only got 6gb free on this drive and it's going to blow up, if I can't
> relieve some of the stress from this directory over 220gb.
>
> What are my options?
You'll need to delete some of the oldest xlog files to release disk
space. But first you need to make sure you don't delete any files that
are still needed, and what got you into this situation in the first place.
You say that you "run an archive command every 10 minutes". What do you
mean by that? archive_command specified in postgresql.conf is executed
automatically by the system, so you don't need to and should not run
that manually. After archive_command has run successfully, and the
system doesn't need the WAL file for recovery anymore (ie. after the
next checkpoint), the system will delete the archived file to release
disk space. Clearly that hasn't been working in your system for some
reason. If archive_command doesn't succeed, ie. it returns a non-zero
return code, the system will keep retrying forever until it succeeds,
without deleting the file. Have you checked the logs for any
archive_command errors?
To get out of the immediate trouble, run "pg_controldata", and make note
of this line:
Latest checkpoint's REDO WAL file: 000000010000000000000001
Anything older than that file is not needed for recovery. You can delete
those, if you have them safely archived.
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hlinnakangas@vmware.com, tmblue@gmail.com
Subject: Re: PG_XLOG 27028 files running out of space
In-Reply-To: <511CC5CA.2000403@vmware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox