Unable to connect to postgres using postgesql_db ansible module

Here is the playbook im using

- hosts: all
  become: true
  tasks:
    - name: create db
      become_user: postgres
      postgresql_db:
        name: test_db

Output after executing the play

The full traceback is:
Traceback (most recent call last):
  File "/tmp/ansible_postgresql_db_payload_fwv6hxn3/ansible_postgresql_db_payload.zip/ansible/modules/database/postgresql/postgresql_db.py", line 541, in main
  File "/usr/lib64/python3.6/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

fatal: [***********************.net]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "conn_limit": "",
            "db": "test_db",
            "encoding": "UTF8",
            "lc_collate": "en_US.UTF-8",
            "lc_ctype": "en_US.UTF-8",
            "login_host": "",
            "login_password": "",
            "login_unix_socket": "",
            "login_user": "postgres",
            "maintenance_db": "postgres",
            "name": "test_db",
            "owner": "",
            "port": 5432,
            "session_role": null,
            "ssl_mode": "prefer",
            "state": "present",
            "tablespace": "",
            "target": "",
            "target_opts": "",
            "template": ""
        }
    },
    "msg": "unable to connect to database: connection to server on socket \"/var/run/postgresql/.s.PGSQL.5432\" failed: No such file or directory\n\tIs the server running locally and accepting connections on that socket?\n"
}

Note: python3-psycopg2 is already installed in managed nodes.

Ansible 2.9.27
Postgres version: 15

Check the PostgreSQL socket path?

1 Like

Hey folks,

@Mukesh1328 It’s very old, could you upgrade Ansible or install the latest collection version using the ansible-galaxy command and try again if the socket investigation won’t help?

@chris your help is much appreciated in all of those topics, thanks for the contribution!

1 Like

In the managed node it shows
/tmp/.s.PGSQL.5432

How can we tell ansible to pick this path?

Sure, We are planning to do the upgrade. thanks you for response @Andersson007

1 Like

Perhaps you would need to write a task to find the socket path, however I’d suggest upgrading Ansible first — hopefully this won’t be needed.

1 Like

You should check the postgresql collection version you are using too.

The current one has a ‘login_unix_socket’ keyword to specify a socket, this is perhaps what you are looking for :

https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_query_module.html

2 Likes