我使用ansible 角色安装了 PostgreSQL。它的默认版本是10.6. 如何设置另一个版本来安装？比如12.4。
$ psql --version
psql (PostgreSQL) 10.6
使用此任务通过 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)