RE: pg_xlog piling
| От | Paula Price |
|---|---|
| Тема | RE: pg_xlog piling |
| Дата | |
| Msg-id | DM5PR0801MB3720F10BD8A50376137A14B2FBFB0@DM5PR0801MB3720.namprd08.prod.outlook.com обсуждение исходный текст |
| Ответ на | Re: pg_xlog piling (Venkata B Nagothi <nag1010@gmail.com>) |
| Список | pgsql-admin |
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@gmail.com]
Sent: Tuesday, January 30, 2018 3:25 PM
To: chris <chrisk@pgsqlrocket.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>; Jorge Torralba <jorge.torralba@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: Re: pg_xlog piling
On Wed, 31 Jan 2018 at 8:32 am, chris <chrisk@pgsqlrocket.com> 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_location | 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
В списке pgsql-admin по дате отправления: