Re: [ADMIN] WAL archive space planning?

Scott Whitney <scott@journyx.com> writes:

> Forgive me if this has been explained well in the past. You can probably search my name for discussions on this...
>
> I fail to see why WAL archiving is either required or desired in a streaming replication scenario WHERE ONE NEVER
NEEDSPITR. 

If you don't mind losing sync from standby to master and having to
refresh the standby from scratch after for any reason streaming
connectivity is lost (and/or standby falls out of sync due to a too
heavy volume)... then fine.

In practice you need both to allow standbys to catch up again after an a
break.

A busy master can overrun a streaming standby at times just due to
volume, perhaps complicated by IO throughput on the standby.

Your standby may go down  undetected...  it may lag beyond what the
master still has in pg_xlog to satisfy simply due to configured in
replication lag tolerance to support reporting workloads.

To name a few :-)

True in an uncomplicated setup being able to fetch WALs from a remote
archive site is not strictly necessary.

It's also the case with newer Pg releases, that if you use a binary rep
slot, your master will hold WAL forever in its own pg_xlog directory
until a down standby comes alive and reads the data but then your
production master is at risk of disk filling if the unresponsive standby
isn't dealt with eventually.

HTH

> I have 2 "production" servers. One is paid customers, and one is customer-facing test and training. Let us call the
onethat I care about bigDb and the other littleDb. 
>
> littleDb replicates bigDb in real-time. They both back up nightly. They are in the same data center.
>
> At my home office, I have otherDb which replicates BOTH and runs independent backups nightly.
>
> I have to have a secondary data center. Let's call that otherCityBigDb and otherCityLittleDb. They stream
replication. 
>
> There is nowhere in the world where I am responsible for rolling back to anything.
>
> Why would one EVER want WAL archiving? It seems like you are planning for failure.
>
> On Mar 13, 2017 10:55 PM, Steven Chang <stevenchang1213@gmail.com> wrote:
>
>     Dears,
>
>          Just read a posgres monitoring moudule named check-postres,  which is a ready enhance package on Debian
LinuxDistribution. 
>
>        https://bucardo.org/check_postgres/check_postgres.pl.html
>
>        here is its readme about wal_files
>        Maybe you can also use this module to help plan your  space usage of WAL files.
>
>      wal_files
>            ("symlink: check_postgres_wal_files") Checks how many WAL files exist in the
>            pg_xlog directory, which is found off of your data_directory, sometimes as a
>            symlink to another physical disk for performance reasons. This action must be
>            run as a superuser, in order to access the contents of the pg_xlog directory.
>            The minimum version to use this action is Postgres 8.1. The --warning and
>            --critical options are simply the number of files in the pg_xlog directory. What
>            number to set this to will vary, but a general guideline is to put a number
>            slightly higher than what is normally there, to catch problems early.
>
>            Normally, WAL files are closed and then re-used, but a long-running open
>            transaction, or a faulty archive_command script, may cause Postgres to create
>            too many files. Ultimately, this will cause the disk they are on to run out of
>            space, at which point Postgres will shut down.
>
>     2017-02-22 3:45 GMT+08:00 Keith <keith@keithf4.com>:
>
>         On Tue, Feb 21, 2017 at 2:33 PM, Ray Stell <stellr@vt.edu> wrote:
>
>             On 2/21/17 12:26 PM, Keith wrote:
>
>                 On Tue, Feb 21, 2017 at 12:22 PM, Ray Stell <stellr@vt.edu> wrote:
>
>                     On 2/21/17 12:09 AM, Steven Chang wrote:
>
>                         check this, his 2nd part introduces Stream Replication Implementation and tell you px_log
retentionand wal archive related parameters. 
>                         https://www.itenlight.com/blog/2016/05/19/PostgreSQL+HA+with+pgpool-II+-+Part+2
>
>                         2017-02-18 2:30 GMT+08:00 Ray Stell <stellr@vt.edu>:
>
>                             I was "planning" to turn on WAL archiving on a postgresql 9.4.11 server that currently is
runningwith "wal_level=hot_standby" and streaming 
>                             to a standby.  I thought there would be a relationship between the rate of pg_xlog files
andarchive generation.   When I turned up the 
>                             archive_command/mode I found the scale of the archive target was wrong as I had based it
onthe pg_xlog file creation rate. 
>
>                             When I turned on the archive command for a few minutes, pg_xlog dir contained these files
forthe time period: 
>
>                             -rw------- 1 postgres postgres 16777216 Feb 17 06:47 00000001000023AC0000007F
>                             -rw------- 1 postgres postgres 16777216 Feb 17 06:45 00000001000023AC0000007E
>
>                             The archive command wrote 126, 16MB files:
>
>                             -rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000056
>                             -rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000055
>                             -rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000054
>                             -rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000053
>                             ...
>
>                             -rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000DC
>                             -rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000DB
>                             -rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000DA
>                             -rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000D9
>
>                             On servers that are not nearly as busy, I observe a one-to-one relationship between these
files/rates.
>
>                             Is there a good WAL archive space planning guide?
>
>                             Is the way to collect planning data for this to turn on wal_debug?
>
>                             TIA!
>
>                             --
>                             Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>                             To make changes to your subscription:
>                             http://www.postgresql.org/mailpref/pgsql-admin
>
>                     I'm interested in why I might observed a dramatic difference between pg_xlog files and the
archivetarget files. Other dbs I see have them pretty 
>                     much one-to-one, but not in this case here.  What might cause the large variation?  I got only a
fewpg_xlogs files each minute and 42 archive files 
>                     /min.
>
>                 Ray,
>
>                 Did you see my previous response about the archive_timeout setting?
>
>             Lost your post somehow, but I see it in the list archive.  Thanks, Keith.
>
>             There was a 30 minute timeout set, but I would not think that would increase the archive file generation
asobserved, does it?  
>
>             " When this parameter is greater than zero, the server will switch to a new segment file whenever this
manyseconds have elapsed since the last segment file 
>             switch, and there has been any database activity, including a single checkpoint. (Increasing
checkpoint_timeoutwill reduce unnecessary checkpoints on an 
>             idle system.) "
>
>             Your post seems to indicate having a non-zero value might increase the number of archive files, is that
so? Maybe it is in milliseconds which might make 
>             sense with the observed archive rate, but the doc says seconds and it was set to 1800.  I can set it to 0
andsee if it changes things, but I'm a little 
>             confused.
>
>         If it was set to 30 minutes, then no, it wouldn't explain what you saw. But we didn't know what it was set
to,so that's why I asked. Make sure you're checking 
>         the value actually set in the database, and not just what's in postgresql.conf, since it could've changed but
neverbeen reloaded to put in place (type "show 
>         archive_timeout" in psql).
>
>         If that's not it, not sure what may be causing what you're seeing.
>
>         Keith
>
> Journyx, Inc.
> 7600 Burnet Road #300
> Austin, TX 78757
> www.journyx.com
>
> p 512.834.8888
> f 512-834-8858
>
> Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at
http://go.journyx.com/emailPreference/e/4932/714/ 
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800