Re: WAL archive space planning?

Keith <keith@xxxxxxxxxxx> · Tue, 21 Feb 2017 12:26:49 -0500

On Tue, Feb 21, 2017 at 12:22 PM, Ray Stell <stellr@xxxxxx> 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

        2017-02-18 2:30 GMT+08:00 Ray Stell <stellr@xxxxxx>:

          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@xxxxxxxxxxxxxx)

                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 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? 