Hi I have some strange issues with a postgresql read replica that seems to stop replicating under certain circumstances. Whenever we have changes to our views we have script that drops all views and reload them from scratch with the new definitions. The reloading of the views happens in a transaction to avoid confusion for everyone using the database. When this update gets to the slave it seems there is a chance for a deadlock to occur that doesn't get detected. As I was trying to reproduce this behavior, I ran into another weird situation that I don't entirely understand. The symptom is the same that replication stops, but it looks quite different. This example won't reproduce the issue reliably, but after a few hours I get a slave that won't continue to replicate until I restart it. The queries in the example won't make much sense, and I don't know if they can be simplified further and still cause the "desired" effect. Setup: Launch a new RDS psql instance (9.6.2) on AWS (will be referred to as db-master) and create a read replica (will be referred to as db-slave). The following options are changed from AWS default: max_standby_streaming_delay=-1 hot_standby_feedback=1 On the master create 2 dummy tables: create table a (id serial primary key); create table b (id serial primary key); Setup thread 1 to do work on master: while true; do psql -h db-master -U postgres db -c 'begin; drop view if exists view_a cascade; drop view if exists view_b; drop view if exists view_c; create view view_a as select * from a; create view view_b as select * from b; create view view_c as select * from view_a join view_b using (id); insert into a values (default); insert into b values (default); commit;'; done Setup thread 2 to do work on Slave: while true; do psql -h db-slave -U postgres db -c 'begin; select * from view_c order by random() limit 10; select * from view_a order by random() limit 10;'; done Setup thread 3 to do more work on slave: while true; do psql -h db-slave -U postgres db -c 'begin; select * from view_b order by random() limit 10; select * from view_a order by random() limit 10;'; done Every now and then a deadlock is detected and one connection is aborted, this works as expected. But After a while(serveral hours) it becomes impossible to connect to db on db-slave and thread 2 and 3 stops producing output. When trying to connect the psql client just hangs. However it is possible connect to template1 database to get a look on what is going on. template1=> select * from pg_stat_activity; -[ RECORD 1 ]----+-------------------------------- datid | 16384 datname | rdsadmin pid | 7891 usesysid | 10 usename | rdsadmin application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | <insufficient privilege> -[ RECORD 2 ]----+-------------------------------- datid | 1 datname | template1 pid | 11949 usesysid | 16388 usename | hiper application_name | psql client_addr | 192.168.10.166 client_hostname | client_port | 41002 backend_start | 2017-10-20 16:30:26.032745+02 xact_start | 2017-10-20 16:30:34.306418+02 query_start | 2017-10-20 16:30:34.306418+02 state_change | 2017-10-20 16:30:34.306421+02 wait_event_type | wait_event | state | active backend_xid | backend_xmin | 26891 query | select * from pg_stat_activity; There are no active connection except rdsadmin from aws. template1=> select * from pg_locks; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath ------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+---------- virtualxid | | | | | 3/929 | | | | | 3/929 | 9640 | ExclusiveLock | t | t relation | 16390 | 2659 | | | | | | | | 4/829 | 9639 | AccessShareLock | t | t relation | 16390 | 1249 | | | | | | | | 4/829 | 9639 | AccessShareLock | t | t virtualxid | | | | | 4/829 | | | | | 4/829 | 9639 | ExclusiveLock | t | t relation | 1 | 11695 | | | | | | | | 5/148 | 11949 | AccessShareLock | t | t virtualxid | | | | | 5/148 | | | | | 5/148 | 11949 | ExclusiveLock | t | t virtualxid | | | | | 1/1 | | | | | 1/0 | 7593 | ExclusiveLock | t | t object | 0 | | | | | | 1262 | 16390 | 0 | 4/829 | 9639 | RowExclusiveLock | t | f relation | 16390 | 1259 | | | | | | | | 4/829 | 9639 | AccessShareLock | f | f relation | 16390 | 1259 | | | | | | | | 1/0 | 7593 | AccessExclusiveLock | t | f object | 0 | | | | | | 1262 | 16390 | 0 | 3/929 | 9640 | RowExclusiveLock | t | f relation | 16390 | 1259 | | | | | | | | 3/929 | 9640 | AccessShareLock | f | f (12 rows) Here there seems to be many threads that all wait for a lock on relation 1259, however I have no idea what the process that has the lock waits for (pid: 7593). I can't use pg_terminate_backend(7593) to free the lock, so my only option is to restart it. I don't know if anyone can reproduce this effect? why are the pids still holding locks even after the have been disconnected in pg_stat_activity? I assume that one of the pids is the slave thread applying updates from the master? This is not the exact problem Im trying to reproduce, in that example all connection end up in LOCKED state and there appear to be a deadlock when inspecting the pg_locks table that don't get discovered. I hope I can reproduce that example also at some time. But have anyone else experienced problem with slave stopping to replicate because everything ends up being locked? Cheers Kim Carlsen -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
