On Tue, Oct 20, 2015 at 4:34 AM, Drazen Kacar <drazen.kacar@xxxxxxxxxxx> wrote:
Hi,
I have Postgres 9.3.9 primary and 9.3.9 hot standby which is used for
read-only queries. This is the configuration for WAL files and checkpoints
(it's the same on primary and standby):
....Several days ago I had an application upgrade which caused huge write
activity. Some of it was caused by "alter table ... add column ... default
..." which caused rewriting of the large table(s). After that there was a
pretty large write activity from reshuffling the data from the old to the
new data model. All in all, a large number of WAL files were generated.
All that caused standby's pg_xlog partition to fill up. This is an excerpt
from its logs....
This is a situation where your application has to be Postgres aware. There's not much Postgres itself can do, because your app is exceeding the network bandwidth available to Postgres and the archiving system.
We use this in our maintenance script. Its job is more-or-less to install new supplier catalogs every week. After each catalog is installed, the Perl script does this:
# Is the Postgres XLOG directory getting full?
my $pct = 0;
my $thishost = hostname();
my $df_command = ($thishost eq $dbhost) ? "df" : "ssh $dbhost df";
do {
$pct = (split(/\s+/, `$df_command | grep postgres_xlog | tail -1`))[4];
$pct = 0 if !defined($pct);
$pct =~ s/\%//;
if ($pct > 75) {
print "WAITING: $schema, ${dbhost}'s Postgres XLOG is too full: $pct\% at " . `date`;
sleep 300; # 5min
}
} until $pct <= 75;
Craig
