pg.ddx.io pgsql-admin@postgresql.org mailing list archive help / color / mirror / Atom feed From: Andrea Moretto <moretto.andrea@gmail.com>
To: pgsql-admin@postgresql.org
Subject: WAL replay stuck in hot standby when performing the backup
Date: Tue, 16 Jul 2019 15:14:56 +0200
Message-ID: <92CEF44C-A3DD-433D-8CD7-B96EA01A2AA5@gmail.com> (raw )
Hi there,
I have noticed that WAL replay blocks in a hot standby slave when performing the backup.
I am using on both PostgreSQL 9.4.23 on Redhat.
During the whole backup procedure (which takes more than 24 hours), the data onto the hot standby node is not updated.
Is this behaviour correct?
My idea is to confine batch workloads, backups and less critical OLTP queries on the hot standby.
If during the backup live data is not updated in the hot standby node, I have to re-plan the environment.
Details:
- Replication statistics show that replay location is lagging far behind the flush location (on master side):
select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
15477 | 10 | postgres | walreceiver | 127.0.0.1 | | 50612 | 2019-07-15 17:34:36.759306+00 | 1152223785 | streaming | 274D/804CBCB8 | 274D/804CBCB8 | 274D/804CBCB8 | 2744/30F84028 | 0 | async
(1 row)
- Replay lag is about 19 hours and running (on hot standby side):
select now()-pg_last_xact_replay_timestamp() as replication_lag;
replication_lag
-----------------
19:12:11.2289
(1 row)
SELECT
pg_last_xlog_receive_location() receive,
pg_last_xlog_replay_location() replay,
(
extract(epoch FROM now()) -
extract(epoch FROM pg_last_xact_replay_timestamp())
)::int lag;
receive | replay | lag
---------------+---------------+-------
274E/BAF0B610 | 2744/30F84028 | 69157
(1 row)
The only query currently running (not in idle state) on the slave is a COPY:
select * from pg_stat_activity;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | state | backend_xid | backend_xmin | query
-------+-------------+-------+----------+----------------+------------------------+--------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+-------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16993 | mydb | 15604 | 3226933 | rousr | PostgreSQL JDBC Driver | 10.192.87.91 | | 41491 | 2019-07-15 17:35:57.01458+00 | | 2019-07-16 12:55:58.21023+00 | 2019-07-16 12:55:58.210251+00 | f | idle | | | COMMIT
16993 | mydb | 18868 | 3226603 | amoretto | psql93 | 10.174.62.66 | | 54106 | 2019-07-16 12:59:30.122865+00 | 2019-07-16 13:00:18.807889+00 | 2019-07-16 13:00:18.807889+00 | 2019-07-16 13:00:18.807893+00 | f | active | | 1152301732 | select * from pg_stat_activity;
16993 | mydb | 2559 | 3226603 | amoretto | psql93 | 10.174.62.66 | | 52338 | 2019-07-16 10:57:03.118782+00 | | 2019-07-16 10:58:27.278095+00 | 2019-07-16 10:58:27.279752+00 | f | idle | | | select * from pg_stat_replication;
16993 | mydb | 15736 | 3226603 | amoretto | pg_dump | 10.192.87.90 | | 4281 | 2019-07-15 17:36:42.540157+00 | 2019-07-15 17:36:42.566704+00 | 2019-07-16 12:51:04.488305+00 | 2019-07-16 12:51:04.488307+00 | f | active | | 1152223785 | COPY public.c_box_hw_dev_app_hist (box_id, hardware_id, device_id, application_id, sequence, history_time, history_info, usr_id, usr_info, archived, is_history_info_zip, description, parent_application_name, iid) TO stdout;
(4 rows)
select * from pg_stat_database_conflicts;
datid | datname | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock
-------+-------------+------------------+------------+----------------+-----------------+----------------
1 | template1 | 0 | 0 | 0 | 0 | 0
12998 | template0 | 0 | 0 | 0 | 0 | 0
13003 | postgres | 0 | 0 | 0 | 0 | 0
16993 | mydb | 0 | 0 | 0 | 0 | 0
(4 rows)
select * from pg_stat_database;
datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_read_time | blk_write_time | stats_reset
-------+-------------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+---------------+----------------+-------------------------------
1 | template1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
12998 | template0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
13003 | postgres | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
16993 | mydb | 3 | 2710 | 2 | 2499 | 333071 | 213588 | 72658 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2019-07-15 17:31:51.600354+00
(4 rows)
select * from pg_stat_bgwriter;
checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_backend_fsync | buffers_alloc | stats_reset
-------------------+-----------------+-----------------------+----------------------+--------------------+---------------+------------------+-----------------+-----------------------+---------------+-------------------------------
4636 | 1 | 1107064 | 9 | 378395 | 0 | 0 | 460498 | 0 | 1432887 | 2019-07-15 17:31:22.319099+00
(1 row)
I hope that someone can give me some hint on this topic, it would be appreciated!
Thanks in advance,
Andrea
Andrea Moretto
moretto.andrea@gmail.com
-------------------------------------------------------
CONFIDENTIALITY NOTICE
This message and its attachments are addressed solely to the persons
above and may contain confidential information. If you have received
the message in error, be informed that any use of the content hereof
is prohibited. Please return it immediately to the sender and delete
the message.
-------------------------------------------------------
Message-ID: <92CEF44C-A3DD-433D-8CD7-B96EA01A2AA5@gmail.com>
Permalink: ../92CEF44C-A3DD-433D-8CD7-B96EA01A2AA5@gmail.com/
Also on: postgresql.org/message-id/92CEF44C-A3DD-433D-8CD7-B96EA01A2AA5@gmail.com
copy link · copy postgr.es
reply 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: moretto.andrea@gmail.com
Subject: Re: WAL replay stuck in hot standby when performing the backup
In-Reply-To: <92CEF44C-A3DD-433D-8CD7-B96EA01A2AA5@gmail.com>
* 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