Sample Header Ad - 728x90

How to create database by Ansible with PostgreSQL?

0 votes
1 answer
497 views
Use this task to create a database on CentOS by Ansible
- name: Create mydb database
  command: createdb -E UNICODE -l en_US.UTF-8 -T template0 mydb -O mydbuser
  become_user: postgres
It will pending. Output:
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": []}
Sometimes run the command on the server directly will also got pending. If restart PostgreSQL service will fix the issue. What's the problem? ---
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)
Asked by rawmain (359 rep)
Sep 16, 2020, 07:00 AM
Last activity: Sep 17, 2020, 10:01 AM