pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ray Stell <stellr@vt.edu>
To: Scott Whitney <scott@journyx.com>
To: Steven Chang <stevenchang1213@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Cc: Keith <keith@keithf4.com>
Subject: Re: WAL archive space planning?
Date: Tue, 14 Mar 2017 13:16:06 -0400
Message-ID: <48d1bacb-071c-deda-5f04-adc125af5627@vt.edu> (raw)
In-Reply-To: <fa27582a-f3a0-4df8-9a68-1b9da8f2479d@email.android.com>
References: <fa27582a-f3a0-4df8-9a68-1b9da8f2479d@email.android.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>

On 3/14/17 12:17 AM, Scott Whitney wrote:

> 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 NEEDS PITR.
>
> I have 2 "production" servers. One is paid customers, and one is 
> customer-facing test and training. Let us call the one that 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 Linux Distribution.
>     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
>     <mailto:keith@keithf4.com>>:
>
>
>
>         On Tue, Feb 21, 2017 at 2:33 PM, Ray Stell <stellr@vt.edu
>         <mailto: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 <mailto: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
>                         retention and wal archive related parameters.
>                         https://www.itenlight.com/blog/2016/05/19/PostgreSQL+HA+with+pgpool-II+-+Part+2
>                         <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 <mailto:stellr@vt.edu>>:
>
>                             I was "planning" to turn on WAL archiving
>                             on a postgresql 9.4.11 server that
>                             currently is running with
>                             "wal_level=hot_standby" and streaming to a
>                             standby. I thought there would be a
>                             relationship between the rate of pg_xlog
>                             files and archive generation.  When I
>                             turned up the archive_command/mode I found
>                             the scale of the archive target was wrong
>                             as I had based it on the pg_xlog file
>                             creation rate.
>
>                             When I turned on the archive command for a
>                             few minutes, pg_xlog dir contained these
>                             files for the 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
>                             <mailto:pgsql-admin@postgresql.org>)
>                             To make changes to your subscription:
>                             http://www.postgresql.org/mailpref/pgsql-admin
>                             <http://www.postgresql.org/mailpref/pgsql-admin;
>
>
>                     I'm interested in why I might observed a dramatic
>                     difference between pg_xlog files and the archive
>                     target 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 few
>                     pg_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 as
>             observed, does it?
>
>             " When this parameter is greater than zero, the server
>             will switch to a new segment file whenever this many
>             seconds have elapsed since the last segment file switch,
>             and there has been any database activity, including a
>             single checkpoint. (Increasing checkpoint_timeout will
>             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 and see 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 never been 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/

"There is nowhere in the world where I am responsible for rolling back 
to anything."

Logical failures.  Suppose duhveloper trashes table on production when 
he meant to make the change to pre-prod.  I might want to roll forward 
to to a PIT just before.

view thread (14+ messages)  latest in thread

Message-ID: <48d1bacb-071c-deda-5f04-adc125af5627@vt.edu>
Permalink:  ../48d1bacb-071c-deda-5f04-adc125af5627@vt.edu/
Also on:    postgresql.org/message-id/48d1bacb-071c-deda-5f04-adc125af5627@vt.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: stellr@vt.edu, scott@journyx.com, stevenchang1213@gmail.com, keith@keithf4.com
  Subject: Re: WAL archive space planning?
  In-Reply-To: <48d1bacb-071c-deda-5f04-adc125af5627@vt.edu>
  References: <fa27582a-f3a0-4df8-9a68-1b9da8f2479d@email.android.com> <48d1bacb-071c-deda-5f04-adc125af5627@vt.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox