Bug: postgresql_db with state: restore executes SQL file twice via --file and stdin #882
Description
Issue Type:
[x] Bug Report
Component Name:
community.postgresql.postgresql_db
Ansible Version:
ansible [core 2.17.9]
config file = /home/github/ansible/ansible.cfg
configured module search path = ['/home/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
ansible python module location = /usr/lib/python3/dist-packages/ansible
ansible collection location = /home/.ansible/collections:/usr/share/ansible/collections
executable location = /usr/bin/ansible
python version = 3.10.12 (main, Aug 15 2025, 14:32:43) [GCC 11.4.0] (/usr/bin/python3)
jinja version = 3.0.3
libyaml = True
Collection Version:
community.postgresql: 3.10.2
(and 3.9.0 is also installed in another location)
/usr/share/ansible/collections/ansible_collections
community.postgresql 3.10.2
/usr/lib/python3/dist-packages/ansible_collections
community.postgresql 3.9.0
Bug Description:
When using the community.postgresql.postgresql_db module with state: restore and a target file, the module constructs a faulty psql command in the background. It incorrectly uses both the --file argument and a standard input redirection (<) for the same target file.
This causes the entire SQL script to be executed twice. If the script contains statements like CREATE TABLE or CREATE SCHEMA without IF NOT EXISTS, the task fails during the second execution with "already exists" errors. This makes the state: restore feature unreliable for standard SQL dumps.
Steps to Reproduce:
-
Prepare a PostgreSQL database.
-
Create a simple SQL file, e.g., /tmp/test.sql, with content that is not idempotent:
SQL
CREATE TABLE my_test_table (id SERIAL PRIMARY KEY);
Execute the following Ansible task to "restore" this file into the database.
YAML
name: Restore database from SQL file (this will fail)
community.postgresql.postgresql_db:
state: restore
name: my_database
target: /tmp/test.sql
login_user: postgres
login_password: your_password
Expected Behavior:
The SQL script should be executed exactly once. The task should complete successfully with a changed status.
Actual Behavior:
The task fails with a FAILED! status. The error message from psql indicates that objects from the script already exist.
The verbose output from Ansible reveals the incorrectly generated command, which is the root cause of the problem:
JSON
"cmd": "/usr/bin/psql --dbname=my_database --host=127.0.0.1 --port=5432 --username=postgres --file=/tmp/test.sql < /tmp/test.sql",
"msg": "psql:/tmp/test.sql:1: ERROR: relation "my_test_table" already exists",
Note: The command and error are based on the minimal example. The full error log from a complex Keycloak import can be seen below.
Full Production Log Snippet:
TASK [k8s_app : restore keycloak DB] ***************************************************************************************************************************************************************************************************************************************************************************************************************************************************
Tuesday 02 September 2025 07:06:12 +0000 (0:00:01.445) 0:18:21.500 *****
fatal: [mycluster-m1]: FAILED! => {"changed": false, "cmd": "/usr/bin/psql --dbname=keycloak --host=127.0.0.1 --port=5432 --username=postgres --file=/tmp/keycloak.sql < /tmp/keycloak.sql", "msg": "psql:/tmp/keycloak.sql:26: ERROR: schema "metric_helpers" already exists\npsql:/tmp/keycloak.sql:46: ERROR: schema "user_management" already exists ...", "rc": 2, ...}
Analysis:
The module should choose only one method to pass the file to psql: either via the --file argument or via stdin redirection, but not both simultaneously. The current implementation guarantees failure for any standard SQL dump that is not perfectly idempotent.
The recommended workaround is to bypass this module's restore logic and use ansible.builtin.command or ansible.builtin.shell to execute a well-formed psql command.