From: Ray Stell <stellr@vt.edu>
To: pgsql-admin@postgresql.org
Subject: WAL archive space planning?
Date: Fri, 17 Feb 2017 13:30:32 -0500
Message-ID: <f757ab05-c6d8-07f4-dac7-4394dde2bf85@vt.edu> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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!
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: stellr@vt.edu
Subject: Re: WAL archive space planning?
In-Reply-To: <f757ab05-c6d8-07f4-dac7-4394dde2bf85@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