Re: Handling peak WAL data on the hot standby

On Tue, Oct 20, 2015 at 4:34 AM, Drazen Kacar <drazen(dot)kacar(at)oradian(dot)com>
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