Postgresql
Postgresql 10 創建訂閱掛起
在我的本地機器上，我剛剛升級到最新的 postgres.app 以獲取版本 10。在此之前，我在 9.6 數據庫上執行 pg_dumpall，然後在安裝版本 10 後，我執行腳本以重新創建數據庫、使用者、等等…在從 dumpall 執行腳本並更新了一些日誌記錄設置後，我還將 wal_level 更改為邏輯。
我首先在
dot數據庫中創建了一個訂閱（作為超級使用者）bill=# \connect dot; You are now connected to database "dot" as user "bill". dot=# create publication foo for table dot.item; CREATE PUBLICATIONitem 表中大約有 39k 行，因此同步數據應該不會花費那麼長時間。
接下來，我創建了一個新數據庫，該數據庫
dot2由與數據庫相同的使用者擁有dot，並在與數據庫中存在的相同架構中創建了一個空項目表dot，然後我嘗試訂閱另一個數據庫中的發布（也作為超級使用者）。bill=# \connect dot2; You are now connected to database "dot2" as user "bill". dot2=# create subscription bar connection 'host=localhost port=5432 dbname=dot connect_timeout=10' PUBLICATION foo;但是，這只是掛起。它不返回。
在我的日誌中，我看到的只有這個……
2017-11-26 21:36:52.828 EST bill@dot2 54567 [local] STATEMENT: create subscription bar connection 'host=localhost port=5432 dbname=dot connect_timeout=10' PUBLICATION foo; 2017-11-26 21:36:55.122 EST bill@dot 54978 ::1(61990) LOG: duration: 9.328 ms statement: SELECT DISTINCT t.schemaname, t.tablename FROM pg_catalog.pg_publication_tables t WHERE t.pubname IN ('foo') 2017-11-26 21:36:55.126 EST bill@dot 54978 ::1(61990) LOG: logical decoding found initial starting point at 0/4584A5A8 2017-11-26 21:36:55.126 EST bill@dot 54978 ::1(61990) DETAIL: Waiting for transactions (approximately 1) older than 1613 to end.試圖查看 pg_stat_activity （免責聲明——我不知道我在這裡做什麼），我在發布數據庫中看到了這個……
編輯從 pg_state 添加更多細節。為了（某些）簡潔而省略了空值。
dot=# select * from pg_stat_activity; -[ RECORD 1 ]----+------------------------------------------------------------------------------------------------------------- pid | 54323 backend_start | 2017-11-26 21:04:40.467211-05 wait_event_type | Activity wait_event | AutoVacuumMain backend_type | autovacuum launcher -[ RECORD 2 ]----+------------------------------------------------------------------------------------------------------------- pid | 54325 usesysid | 10 usename | postgres backend_start | 2017-11-26 21:04:40.467857-05 wait_event_type | Activity wait_event | LogicalLauncherMain backend_type | background worker -[ RECORD 3 ]----+------------------------------------------------------------------------------------------------------------- datid | 16391 datname | dot pid | 54978 usesysid | 16384 usename | bill application_name | bar client_addr | ::1 client_port | 61990 backend_start | 2017-11-26 21:36:55.110362-05 xact_start | query_start | 2017-11-26 21:36:55.113552-05 state_change | 2017-11-26 21:36:55.122976-05 wait_event_type | Lock wait_event | transactionid state | idle query | SELECT DISTINCT t.schemaname, t.tablename + | FROM pg_catalog.pg_publication_tables t + | WHERE t.pubname IN ('foo') backend_type | walsender -[ RECORD 4 ]----+------------------------------------------------------------------------------------------------------------- datid | 16391 datname | dot pid | 54533 usesysid | 16384 usename | bill application_name | psql client_port | -1 backend_start | 2017-11-26 21:07:00.309111-05 xact_start | 2017-11-26 22:16:11.140318-05 query_start | 2017-11-26 22:16:11.140318-05 state_change | 2017-11-26 22:16:11.140321-05 state | active backend_xmin | 1612 query | select * from pg_stat_activity; backend_type | client backend -[ RECORD 5 ]----+------------------------------------------------------------------------------------------------------------- datid | 18611 datname | dot2 pid | 54567 usesysid | 16384 usename | bill application_name | psql client_port | -1 backend_start | 2017-11-26 21:09:12.969232-05 xact_start | 2017-11-26 21:36:55.106831-05 query_start | 2017-11-26 21:36:55.106831-05 state_change | 2017-11-26 21:36:55.106835-05 wait_event_type | Client wait_event | LibPQWalReceiverReceive state | active backend_xid | 1612 backend_xmin | 1612 query | create subscription bar connection 'host=localhost port=5432 dbname=dot connect_timeout=10' PUBLICATION foo; backend_type | client backend -[ RECORD 6 ]----+------------------------------------------------------------------------------------------------------------- pid | 54321 backend_start | 2017-11-26 21:04:40.466525-05 wait_event_type | Activity wait_event | BgWriterMain backend_type | background writer -[ RECORD 7 ]----+------------------------------------------------------------------------------------------------------------- pid | 54320 backend_start | 2017-11-26 21:04:40.466302-05 wait_event_type | Activity wait_event | CheckpointerMain backend_type | checkpointer -[ RECORD 8 ]----+------------------------------------------------------------------------------------------------------------- pid | 54322 backend_start | 2017-11-26 21:04:40.466712-05 wait_event_type | Activity wait_event | WalWriterMain backend_type | walwriter根據要求添加 pg_locks。同樣，空值被省略。
dot=# select * from pg_locks; -[ RECORD 1 ]------+----------------- locktype | relation database | 18611 relation | 6102 virtualtransaction | 5/16 pid | 54567 mode | RowExclusiveLock granted | t fastpath | t -[ RECORD 2 ]------+----------------- locktype | relation database | 18611 relation | 18635 virtualtransaction | 5/16 pid | 54567 mode | AccessShareLock granted | t fastpath | t -[ RECORD 3 ]------+----------------- locktype | virtualxid virtualxid | 5/16 virtualtransaction | 5/16 pid | 54567 mode | ExclusiveLock granted | t fastpath | t -[ RECORD 4 ]------+----------------- locktype | relation database | 16391 relation | 11577 virtualtransaction | 4/21 pid | 54533 mode | AccessShareLock granted | t fastpath | t -[ RECORD 5 ]------+----------------- locktype | virtualxid virtualxid | 4/21 virtualtransaction | 4/21 pid | 54533 mode | ExclusiveLock granted | t fastpath | t -[ RECORD 6 ]------+----------------- locktype | transactionid transactionid | 1612 virtualtransaction | 5/16 pid | 54567 mode | ExclusiveLock granted | t fastpath | f -[ RECORD 7 ]------+----------------- locktype | object database | 0 classid | 6100 objid | 18661 objsubid | 0 virtualtransaction | 5/16 pid | 54567 mode | AccessShareLock granted | t fastpath | f -[ RECORD 8 ]------+----------------- locktype | transactionid transactionid | 1612 virtualtransaction | 3/0 pid | 54978 mode | ShareLock granted | f fastpath | f -[ RECORD 9 ]------+----------------- locktype | relation database | 0 relation | 6100 virtualtransaction | 5/16 pid | 54567 mode | RowExclusiveLock granted | t fastpath | f -[ RECORD 10 ]-----+----------------- locktype | object database | 0 classid | 1260 objid | 16384 objsubid | 0 virtualtransaction | 5/16 pid | 54567 mode | AccessShareLock granted | t fastpath | f我已經嘗試重新啟動數據庫，並且我已經關閉了任何可能連接到它的程序。我使用的唯一可以連接到它的是我用來連接到 pub 和 sub 數據庫的兩個 psql 實例。
知道為什麼會發生這種情況，或者我應該看什麼來弄清楚它為什麼在等待？
從文件：
僅當複制槽不是作為同一命令的一部分創建時，創建連接到同一數據庫集群的訂閱（例如，在同一集群中的數據庫之間複製或在同一數據庫內複製）才會成功。否則，CREATE SUBSCRIPTION 呼叫將掛起。為此，請單獨創建複製槽（使用函式 pg_create_logical_replication_slot 和外掛名稱 pgoutput）並使用參數 create_slot = false 創建訂閱。這是一個實施限制，可能會在未來的版本中取消。