[GENERAL] HELP!!! The WAL Archive is taking up all space
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
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
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 <j...@commandprompt.com> 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
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 <ssoor...@gmail.com> 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 f*&%*$%%$#%$#