使用此任务通过 Ansible 在 CentOS 上创建数据库
- name: Create mydb database
command: createdb -E UNICODE -l en_US.UTF-8 -T template0 mydb -O mydbuser
become_user: postgres
它将悬而未决。输出：
TASK [db_primary : Create mydb database] *********************************************************************************************************
fatal: [stg-master]: FAILED! => {"changed": true, "cmd": ["createdb", "-E", "UNICODE", "-l", "en_US.UTF-8", "-T", "template0", "mydb", "-O", "mydbuser"], "delta": "0:00:44.323037", "end": "2020-09-17 18:44:48.873994", "msg": "non-zero return code", "rc": 1, "start": "2020-09-17 18:44:04.550957", "stderr": "WARNING: canceling the wait for synchronous replication and terminating connection due to administrator command\nDETAIL: The transaction has already committed locally, but might not have been replicated to the standby.\ncreatedb: database creation failed: server closed the connection unexpectedly\n\tThis probably means the server terminated abnormally\n\tbefore or while processing the request.", "stderr_lines": ["WARNING: canceling the wait for synchronous replication and terminating connection due to administrator command", "DETAIL: The transaction has already committed locally, but might not have been replicated to the standby.", "createdb: database creation failed: server closed the connection unexpectedly", "\tThis probably means the server terminated abnormally", "\tbefore or while processing the request."], "stdout": "", "stdout_lines": []}
有时直接在服务器上运行命令也会被挂起。
如果重新启动 PostgreSQL 服务将解决此问题。有什么问题？
postgres=# SELECT * FROM pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
virtualxid | | | | | 7/10 | | | | | 7/10 | 30451 | ExclusiveLock | t | t
virtualxid | | | | | 6/3 | | | | | 6/3 | 30290 | ExclusiveLock | t | t
relation | 13808 | 11577 | | | | | | | | 3/3 | 29004 | AccessShareLock | t | t
virtualxid | | | | | 3/3 | | | | | 3/3 | 29004 | ExclusiveLock | t | t
relation | 0 | 2676 | | | | | | | | 7/10 | 30451 | RowExclusiveLock | t | f
transactionid | | | | | | 569 | | | | 7/10 | 30451 | ExclusiveLock | t | f
relation | 0 | 1260 | | | | | | | | 7/10 | 30451 | RowExclusiveLock | t | f
transactionid | | | | | | 568 | | | | 7/10 | 30451 | ShareLock | f | f
transactionid | | | | | | 568 | | | | 6/3 | 30290 | ExclusiveLock | t | f
relation | 0 | 2677 | | | | | | | | 7/10 | 30451 | RowExclusiveLock | t | f
relation | 0 | 1260 | | | | | | | | 6/3 | 30290 | RowExclusiveLock | t | f
(11 rows)
冲突事务 568 持有的唯一有趣的锁是
ROW EXCLUSIVElock onpg_authid，因此必须存在行锁定冲突。那证明不是
createdb挂createuser了，而是SQL语句CREATE/ALTER/DROP ROLE。必须是事务 568 当前正在添加或修改用户，而您的被阻止事务 569 必须同时尝试修改同一用户。
解决方法：确保阻塞事务 568 已关闭或会话终止。