Re: HELP!!! The WAL Archive is taking up all space

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: FattahRozzaq <ssoorruu(at)gmail(dot)com>, John R Pierce <pierce(at)hogranch(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: HELP!!! The WAL Archive is taking up all space
Date: 2015-12-10 01:02:31
Message-ID: 5668CF27.6020907@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 12/09/2015 04:27 PM, FattahRozzaq wrote:
> Hi John,
>
> I really don't know why I should keep the wal archives.

So who set up the archiving and why?

Is archive recovery set up on the standby?:

http://www.postgresql.org/docs/9.4/interactive/archive-recovery-settings.html

> I implement streaming replication into 1 server (standby server).

Is that the only standby or is there another set up previously?

Per another recent thread having a WAL archive to fall back on is handy
if the streaming replication falls behind and wal_keep_segments is not
high enough:

http://www.postgresql.org/docs/9.4/interactive/warm-standby.html#STREAMING-REPLICATION

"If you use streaming replication without file-based continuous
archiving, the server might recycle old WAL segments before the standby
has received them. If this occurs, the standby will need to be
reinitialized from a new base backup. You can avoid this by setting
wal_keep_segments to a value large enough to ensure that WAL segments
are not recycled too early, or by configuring a replication slot for the
standby. If you set up a WAL archive that's accessible from the standby,
these solutions are not required, since the standby can always use the
archive to catch up provided it retains enough segments."

> I'm really newbie to PostgreSQL but the boss pushed me to handle it
> and implement it in production f*&%*$%%$#%$#&# (forgive me)
> They don't hire a database expert, I don't know why.
>
> Hi Alan,
> I have no problem with pg_xlog size which is only 67GB.
> My issue is with the archive folder size.
> I've read in this blog:
> http://blog.endpoint.com/2014/09/pgxlog-disk-space-problem-on-postgres.html
> And I change the archive_command into: archive_command = '/bin/true'
> Is it a bad decision?
>
> Hi Adrian,
> Thank you very much for the suggestion. is pgarchivecleanup harmful?
> Do you have a pgarchivecleanup example for my case?
> How to run a dry-run?

Before you do any of the below make sure you know what your
replication/archiving set up is, per the questions above.

On the standby do:

select pg_last_xlog_receive_location();

This will give you the last WAL synced to the standby disk.

You can use that to see what WAL files are before this in the WAL
archive. Then you can do this:

pg_archivecleanup -d -n <archive_dir> <some_wal_file_before_the one you
found_above>

>
>
> Thank you,
> Fattah
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2015-12-10 01:05:06 Re: HELP!!! The WAL Archive is taking up all space
Previous Message FattahRozzaq 2015-12-10 00:59:54 Re: HELP!!! The WAL Archive is taking up all space