Re: Why is my pg_xlog directory so huge?

Okay, thanks. It' seems you were right! Now I have fixed the issue (it was an ssh key).
So I started a:
SELECT pg_start_backup('backup', true);

And when done, I executed a:
sudo -u postgres rsync -av --exclude postmaster.pid --exclude pg_xlog /var/lib/postgresql/9.2/main/ postgres(at)192(dot)168(dot)0(dot)2:/var/lib/postgresql/9.2/main/

Then I tried to finish off the backup by doing a:
SELECT pg_stop_backup();

But It keeps on telling me:
WARNING: pg_stop_backup still waiting for all required WAL segments to be archived (480 seconds elapsed)
HINT: Check that your archive_command is executing properly. pg_stop_backup can be canceled safely, but the database backup will not be usable without all the WAL segments.

And I could see in the log that it's some kind of permission issue. So I canceled it, and started the streaming replication on my slave, and it seems to work fine. However the pg_xlog dir on the master is still HUGE 153G - so how can I get this mess sorted, and cleaned up that directory?

Den 18/03/2013 kl. 10.26 skrev Magnus Hagander <magnus(at)hagander(dot)net>:

> On Mon, Mar 18, 2013 at 10:14 AM, Niels Kristian Schjødt
> <nielskristian(at)autouncle(dot)com> wrote:
>> After installing my new server I just discovered something that doesn't seem right:
>>
>> sudo du -h /var/lib/postgresql/9.2/main
>
> <snip>
>
>> As you can see the pg_xlog folder is 202G, which is more than my entire database - this seems wrong to me, however I have no clue why this would happen.
>
> My first guess would be that your archive_command is failing - so
> check your logs for that. If that command fails, no xlog files will
> ever be rotated (since it would invalidate your backups).
>
> --
> Magnus Hagander
> Me: http://www.hagander.net/
> Work: http://www.redpill-linpro.com/