Use esta tarefa para criar um banco de dados no CentOS pelo Ansible
- name: Create mydb database
command: createdb -E UNICODE -l en_US.UTF-8 -T template0 mydb -O mydbuser
become_user: postgres
Ficará pendente. Resultado:
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": []}
Às vezes, executar o comando diretamente no servidor também ficará pendente.
Se reiniciar o serviço PostgreSQL, o problema será corrigido. Qual é o problema?
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)
O único bloqueio interessante que a transação conflitante 568 mantém é um
ROW EXCLUSIVEbloqueio empg_authid, portanto, deve haver um conflito de bloqueio de linha.Isso prova que não é o
createdbque trava, mas simcreateusera instrução SQLCREATE/ALTER/DROP ROLE.Deve ser que a transação 568 esteja atualmente adicionando ou modificando um usuário, e sua transação bloqueada 569 deve estar tentando modificar o mesmo usuário simultaneamente.
Solução: certifique-se de que a transação de bloqueio 568 esteja fechada ou a sessão encerrada.