WAL archive space planning?

From: Ray Stell <stellr(at)vt(dot)edu>
To: pgsql-admin(at)postgresql(dot)org
Subject: WAL archive space planning?
Date: 2017-02-17 18:30:32
Message-ID: f757ab05-c6d8-07f4-dac7-4394dde2bf85@vt.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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!

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Keith 2017-02-19 07:34:56 Re: WAL archive space planning?
Previous Message stevenchang1213 2017-02-17 15:52:53 Re: Can I create multiple schema for one user