Обсуждение: What to do when pg_xlog directory has filled up the filesystem

Hi all,

Just curious about this issue as it has occurred on my 9.4.0 sandbox primary server. The link to the directory
specifiedin archive_command had disappeared, so the command  
specified in archive_command kept failing overnight. This resulted in many WAL segments piling up in the pg_xlog
directoryand a bunch of <wal name>.ready files appearing in the  
pg_xlog/archive.status directory. The filesystem had reached 100% full so the database quit and would not restart. I've
thoughtabout just moving some of the WAL segments  
temporarily to another filesystem, but which ones, oldest or newest? And is that really the best way to deal with this
orwhat is the best way to deal with this kind of issue? 
--
Jay

> On Jan 23, 2015, at 08:00, John Scalia <jayknowsunix@gmail.com> wrote:
>
> Hi all,
>
> Just curious about this issue as it has occurred on my 9.4.0 sandbox primary server. The link to the directory
specifiedin archive_command had disappeared, so the command specified in archive_command kept failing overnight. This
resultedin many WAL segments piling up in the pg_xlog directory and a bunch of <wal name>.ready files appearing in the
pg_xlog/archive.statusdirectory. The filesystem had reached 100% full so the database quit and would not restart. I've
thoughtabout just moving some of the WAL segments temporarily to another filesystem, but which ones, oldest or newest?
Andis that really the best way to deal with this or what is the best way to deal with this kind of issue? 

If you are on ext4, you can just:

tune2fs -m 0 /dev/sda1

Or whatever device you're talking about.

That will reset the reserved space to 0.

Then you can start up PostgreSQL and let it do the archiving. After it's done:

tune2fs -m 5 /dev/sda1

To put it back to the default.

On 1/23/2015 11:33 AM, Jeff Frost wrote:
>> On Jan 23, 2015, at 08:00, John Scalia <jayknowsunix@gmail.com> wrote:
>>
>> Hi all,
>>
>> Just curious about this issue as it has occurred on my 9.4.0 sandbox primary server. The link to the directory
specifiedin archive_command had disappeared, so the command specified in archive_command kept failing overnight. This
resultedin many WAL segments piling up in the pg_xlog directory and a bunch of <wal name>.ready files appearing in the
pg_xlog/archive.statusdirectory. The filesystem had reached 100% full so the database quit and would not restart. I've
thoughtabout just moving some of the WAL segments temporarily to another filesystem, but which ones, oldest or newest?
Andis that really the best way to deal with this or what is the best way to deal with this kind of issue? 
> If you are on ext4, you can just:
>
> tune2fs -m 0 /dev/sda1
>
> Or whatever device you're talking about.
>
> That will reset the reserved space to 0.
>
> Then you can start up PostgreSQL and let it do the archiving. After it's done:
>
> tune2fs -m 5 /dev/sda1
>
> To put it back to the default.
>
Thanks, Jeff!