pg_dump locks database which causes hive sync to stale
In older versions of hivemind, pg_dump
(important for maintenance) used to be executed without locking hive sync
process.
pg_dump -Fc
takes roughly 90 minutes so having downtime for such long time isn't really viable in production environments.
Of course bigger service providers can mitigate that by using replicas for backup purposes, but it would be great to avoid locking if possible.
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
returns:
blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement | current_statement_in_blocking_process
-------------+--------------+--------------+---------------+--------------------------------------------------------------+---------------------------------------------------------------------------------
19117 | hive | 1498 | hive | SELECT update_account_reputations(50554016, 50554016, False) | COPY public.hive_post_data (id, title, preview, img_url, body, json) TO stdout;
and
SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;
gives:
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
-----------------------+----------+-----------+-----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
__new_reputation_data | relation | 170188638 | 185894290 | | | | | | | | 3/3283473 | 19117 | AccessExclusiveLock | f | f
potential suspect is:
TRUNCATE TABLE __new_reputation_data