RE: pg_xlog piling

[Paula Price <paula.price@xxxxxxxxxxxxxxx>]

We had a similar issue with 9.2. Managed to track it enough to figure out it was happening when streaming replication falls behind.

 

We log ship to a dir, log_archive, and have an archive_command.sh file in that dir.

 

Added a line to the archive_command.sh to call pg_archivecleanup for the xlog partition in addition to the existing pg_archivecleanup for log_archive dir.  For some reason (have not had time to research) we have to fully path the location of the pg_archivecleanup.

 

If this is helpful, I can show the lines of the archive_command.sh file.

 

Paula Price

Senior Database Administrator

 

From: Venkata B Nagothi [mailto:nag1010@xxxxxxxxx]
Sent: Tuesday, January 30, 2018 3:25 PM
To: chris <chrisk@xxxxxxxxxxxxxxx>
Cc: David G. Johnston <david.g.johnston@xxxxxxxxx>; Jorge Torralba <jorge.torralba@xxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: Re: pg_xlog piling

 

 

On Wed, 31 Jan 2018 at 8:32 am, chris <chrisk@xxxxxxxxxxxxxxx> wrote:

postgres=# select * from pg_stat_replication;

 pid  | usesysid | usename  | application_name |  client_addr   |

client_hostname | client_port |         backend_start         |   state   |

sent_location | write_location | flush_location | replay_locatio

n | sync_priority | sync_state 

------+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+-----------+---------------+----------------+----------------+---------------

--+---------------+------------

 5303 |       10 | postgres | walreceiver      | 198.161.184.74 |               

 |       57049 | 2017-12-14 11:13:16.339948-07 | streaming | 2FD3/41A38140 |

2FD3/41A38140  | 2FD3/41A38140  | 2F47/7015D490 

  |             0 | async

(1 row)

postgres=# 

# - Archiving -

archive_mode = on        # allows archiving to be done

archive_command = 'cp %p /ComplianceDB/pitr/walstage/%f; mv /ComplianceDB/pitr/walstage/%f /ComplianceDB/pitr/wal/%f'                # (change requires restart)
#archive_command = ''        # command to use to archive a logfile segment
                # placeholders: %p = path of file to archive
                #               %f = file name only
                # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0        # force a logfile segment switch after this
                # number of seconds; 0 disables
Thanks

 

 

Interesting. Do you see any files called *.ready in pg_xlog/archive_status directory ? Normally pg_xlog fills up when archive_command is not successful.

 

Do you have parameter wal_keep_segments configured to higher value ? 

 

Regards,

Venkata B

--

 

Regards,

 

Venkata B N

Database Consultant