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

[Jim Nasby]

On 12/9/15 7:05 PM, Andreas Kretschmer wrote:

I'm really newbie to PostgreSQL but the boss pushed me to handle it
>and implement it in production f*&%*$%%$#%$#

[GENERAL] HELP!!! The WAL Archive is taking up all space

[FattahRozzaq]

Hi all,

Please help...

I have 1 master PostgreSQL and 1 standby PostgreSQL.
Both servers has the same OS Linux Debian Wheezy, the same hardware.

Both server hardware:
CPU: 24 cores
RAM: 128GB
Disk-1: 800GB SAS (for OS, logs, WAL archive directory)
Disk-2: 330GB SSD (for PostgreSQL data directory, except WAL archive
and except pg_log)

The part of the configuration are as below:
checkpoint_segments = 64
checkpoint_completion_target = 0.9
default_statistics_target = 10
maintenance_work_mem = 1GB
effective_cache_size = 64GB
shared_buffers = 24GB
work_mem = 5MB
wal_buffers = 8MB
wal_keep_segments = 4096
wal_level = hot_standby
max_wal_senders = 10
archive_mode = on
archive_command = 'cp -i %p /home/postgres/archive/master/%f'


The WAL archive folder is at /home/postgres/archive/master/, right?
This directory consumes around 750GB of Disk-1.
Each segment in the /home/postgres/archive/master/ is 16MB each
There are currently 47443 files in this folder.

If I want to limit the total size use by WAL archive to around 200-400
GB, what value should I set for the wal_keep_segments,
checkpoint_segments?



Regards,
Fattah


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Alan Hodgson]

On Wednesday, December 09, 2015 07:55:09 AM FattahRozzaq wrote:
> archive_mode = on
> archive_command = 'cp -i %p /home/postgres/archive/master/%f'
> 
> 
> The WAL archive folder is at /home/postgres/archive/master/, right?
> This directory consumes around 750GB of Disk-1.
> Each segment in the /home/postgres/archive/master/ is 16MB each
> There are currently 47443 files in this folder.
> 
> If I want to limit the total size use by WAL archive to around 200-400
> GB, what value should I set for the wal_keep_segments,
> checkpoint_segments?

PostgreSQL doesn't clean up files copied by your archive_command. You need to 
have a separate task clean those out. PostgreSQL's active wal_keep_segments 
etc. are in the data/pg_xlog directory.


signature.asc
Description: This is a digitally signed message part.

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

[Adrian Klaver]

On 12/09/2015 11:15 AM, Alan Hodgson wrote:

On Wednesday, December 09, 2015 07:55:09 AM FattahRozzaq wrote:

archive_mode = on
archive_command = 'cp -i %p /home/postgres/archive/master/%f'


The WAL archive folder is at /home/postgres/archive/master/, right?
This directory consumes around 750GB of Disk-1.
Each segment in the /home/postgres/archive/master/ is 16MB each
There are currently 47443 files in this folder.

If I want to limit the total size use by WAL archive to around 200-400
GB, what value should I set for the wal_keep_segments,
checkpoint_segments?


PostgreSQL doesn't clean up files copied by your archive_command. You need to
have a separate task clean those out. PostgreSQL's active wal_keep_segments
etc. are in the data/pg_xlog directory.



The OP might want to take a look at:

http://www.postgresql.org/docs/9.4/interactive/pgarchivecleanup.html

To be safe I would use:

-n

Print the names of the files that would have been removed on stdout 
(performs a dry run).



at first.

--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Joshua D. Drake]

On 12/09/2015 04:38 PM, FattahRozzaq wrote:

Quick information,

After I realize, the line "archive_command=/bin/true" is a bad
decision, I have revert it back.
Now I'm really confused and panic.
I don't know what to do, and I don't really understand the postgresql.conf
I'm a network engineer, I should handle the network and also
postgresql database.
Oh man, the office is so good but this part is sucks :((


If the pg_xlog directory is growing it is likely that either:

* wal_keep_segments is set high and your slave is not correctly 
receiving updates.


* You are using a replication slot and the slave is not correctly 
receiving updates.


If your archive_command does not return a success, your pg_xlog will 
also grow but you don't need the archive_command *IF* your streaming 
replication is working *UNLESS* you are also doing archiving or PITR.


Sincerely,

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[FattahRozzaq]

Hi John,

I really don't know why I should keep the wal archives.
I implement streaming replication into 1 server (standby server).
I'm really newbie to PostgreSQL but the boss pushed me to handle it
and implement it in production 

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

[Adrian Klaver]

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*&%*$%%$#%$#

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

[Andreas Kretschmer]

> FattahRozzaq  hat am 10. Dezember 2015 um 01:27
> geschrieben:
> 
> 
> Hi John,
> 
> I really don't know why I should keep the wal archives.


That's the problem! But that's your part, not our. If you need a Backup with
PITR-capability you have to create a so called basebackup and continously WAL's.
If you create later, say the next day, a new Basebackup and your Backup-Policy
is hold one Backup, than you can delete all WAL's untill to the new Basebackup
and the old Backup.

If i where you i would use somethink like barman (see: http://www.pgbarman.org/
) for that. And yes: you should a extra Backup-Server. If you have both
(Database and Backup) on the same machine and the machine burns you will lost
both, data and backup.


Questions?



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


Streamin Replication can't replace a Backup!


> I'm really newbie to PostgreSQL but the boss pushed me to handle it
> and implement it in production 

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

[John R Pierce]

On 12/9/2015 4:27 PM, FattahRozzaq wrote:

I really don't know why I should keep the wal archives.
I implement streaming replication into 1 server (standby server).
I'm really newbie to PostgreSQL but the boss pushed me to handle it
and implement it in production f*&%*$%%$#%$#

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

[FattahRozzaq]

Hi John,

Really thanking you for spend time typing and responding my email.
I think the archive_command returns success, I can see the archive
directory piling up 16MB every 2 minutes.
Maybe the pgarchivecleanup is the solution to cleanup the contents of
archive folder?
How to properly do it?
What is the pgarchivecleanup example that I can use for this case?
How to run a dry-run for pgarchivecleanup?


Best Regards,
FR

On 10/12/2015, Joshua D. Drake  wrote:
> On 12/09/2015 04:38 PM, FattahRozzaq wrote:
>> Quick information,
>>
>> After I realize, the line "archive_command=/bin/true" is a bad
>> decision, I have revert it back.
>> Now I'm really confused and panic.
>> I don't know what to do, and I don't really understand the
>> postgresql.conf
>> I'm a network engineer, I should handle the network and also
>> postgresql database.
>> Oh man, the office is so good but this part is sucks :((
>
> If the pg_xlog directory is growing it is likely that either:
>
> * wal_keep_segments is set high and your slave is not correctly
> receiving updates.
>
> * You are using a replication slot and the slave is not correctly
> receiving updates.
>
> If your archive_command does not return a success, your pg_xlog will
> also grow but you don't need the archive_command *IF* your streaming
> replication is working *UNLESS* you are also doing archiving or PITR.
>
> Sincerely,
>
> JD
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Announcing "I'm offended" is basically telling the world you can't
> control your own emotions, so everyone else should do it for you.
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[FattahRozzaq]

Quick information,

After I realize, the line "archive_command=/bin/true" is a bad
decision, I have revert it back.
Now I'm really confused and panic.
I don't know what to do, and I don't really understand the postgresql.conf
I'm a network engineer, I should handle the network and also
postgresql database.
Oh man, the office is so good but this part is sucks :((

--
On 10/12/2015, FattahRozzaq  wrote:
> Hi John,
>
> I really don't know why I should keep the wal archives.
> I implement streaming replication into 1 server (standby server).
> I'm really newbie to PostgreSQL but the boss pushed me to handle it
> and implement it in production 

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

[John R Pierce]

On 12/8/2015 4:55 PM, FattahRozzaq wrote:

...I want to limit the total size use by WAL archive to around 200-400 GB...?


for what purpose are you keeping a wal archive ?

if its for PITR (point in time recovery), you need ALL WAL records since 
the start of a base backup up to the point in time at which you wish to 
recover.



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general