community.postgresql
community.postgresql copied to clipboard
ansible-collections
Using is_available return variable to determine if PG Ping is success or failure
I am running the postgresql_ping module in my playbook, and the postgresql server is not running on the target host. So I expect a failed result. Instead I just get a warning followed by an OK. How can I treat the warning as an error or how do I reference the return value, "is_available" which returns false?
ansible-playbook playbooks/check_postgres_roles.yml -->
PLAY [PostgreSQL ping dbsrv server using not default credentials and ssl] *******************************************************************************************************************************************************************
TASK [Gathering Facts] **********************************************************************************************************************************************************************************************************************
ok: [192.168.1.133]
TASK [pgdb : PostgreSQL ping dbsrv server using not default credentials and ssl] ************************************************************************************************************************************************************
[WARNING]: PostgreSQL server is unavailable: 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?
ok: [192.168.1.133]
PLAY RECAP **********************************************************************************************************************************************************************************************************************************
192.168.1.133 : ok=2 changed=0 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
@MichaelDBA thanks for the question! You can do something like
- name: Check if the server is available
postgresql_ping:
... # conn params here if needed
register: result
- name: Assert that the server is available
assert:
that:
- result.is_available == yes # or no. If the assert fails, the playbook will fail
# Or you can use this with another task
- name: Another task which depends on server availability
...
when: result.is_available == yes # or no, or true/false
@MichaelDBA please let me know if the solution works.
Also I'm thinking of adding a bool option fail_on_error.
@MichaelDBA @tcraxs @hunleyd @klando @kostiantyn-nemchenko @andytom what do you think?
Seems that there's a demand from users for that https://andreas.scherbaum.la/blog/archives/1093-Make-Ansible-postgresql_ping-fail-if-the-database-does-not-exist.html
PQping reports the status of the server. It accepts connection parameters identical to those of PQconnectdb, described above. It is not necessary to supply correct user name, password, or database name values to obtain the server status; however, if incorrect values are provided, the server will log a failed connection attempt.
So I'm tempted to say that people are wrong not only on Ansible but on their usage of PostgreSQL feature: this PG ping is not to evaluate existence of a DB, it's wrong by design. dot.
I'm more tempted by a doc patch.
As for the ERROR vs WARNING. For OP, the answer from Andersson looks correct to me. Maybe adding a boolean option as proposed. On my side I would just wait a bit more that PostgreSQL startup is completed not error out but I cannot judge others requirements. Maybe what we really want is (just a raw, not working example):
wait_for:
service: postgresql
timeout: 120
wait_for:
service: postgresql
database: mynewdb
timeout: 120
And eventually we may want to instruct ansible postgresql_ping with those returned values:
PQPING_OK
The server is running and appears to be accepting connections.
PQPING_REJECT
The server is running but is in a state that disallows connections (startup, shutdown, or crash recovery).
PQPING_NO_RESPONSE
The server could not be contacted. This might indicate that the server is not running, or that there is something wrong with the given connection parameters (for example, wrong port number), or that there is a network connectivity problem (for example, a firewall blocking the connection request).
PQPING_NO_ATTEMPT
No attempt was made to contact the server, because the supplied parameters were obviously incorrect or there was some client-side problem (for example, out of memory).
PQPING_OK
The server is running and appears to be accepting connections.
PQPING_REJECT
The server is running but is in a state that disallows connections (startup, shutdown, or crash recovery).
PQPING_NO_RESPONSE
The server could not be contacted. This might indicate that the server is not running, or that there is something wrong with the given connection parameters (for example, wrong port number), or that there is a network connectivity problem (for example, a firewall blocking the connection request).
PQPING_NO_ATTEMPT
No attempt was made to contact the server, because the supplied parameters were obviously incorrect or there was some client-side problem (for example, out of memory).
I like this idea. Maybe a new return value called, say, status or something with OK, REJECT, etc. but we could see what's going on based on error messages returned by psycopg2 connector.
So I'm tempted to say that people are wrong not only on Ansible but on their usage of PostgreSQL feature:
I think this is my bad because i haven't provided good and clear documentation. I'll start with improving the doc today.
Added the examples https://github.com/ansible-collections/community.postgresql/pull/176
@Andersson007, adding this info to my playbook did the trick. But if I have a role that invokes this task, is there something I can do when my playbook consists of a role instead of a task to do the same kinda check?
register: results
failed_when: results.is_available == false
Perhaps we can update the example in the documentation for postgresql_ping to add the check for is_available return value:
# In the example below you need to generate certificates previously.
# See https://www.postgresql.org/docs/current/libpq-ssl.html for more information.
- name: PostgreSQL ping dbsrv server using not default credentials and ssl
# optionally check for return variable, is_available, to determine if we succeed or fail
#register: results
#failed_when: results.is_available == false
community.postgresql.postgresql_ping:
db: protected_db
login_host: dbsrv
login_user: secret
login_password: secret_pass
ca_cert: /root/root.crt
ssl_mode: verify-full
@klando copied documentation related to this discussion that basically said that the purpose of the PG ping is to evaluate the status of a PG cluster, not a specific database within the cluster. I would state that the purpose is to evaluate the connection to a cluster regardless of parameters provided or the reason it fails. By using this definition of a "connection", we actually add more flexibility to the purpose of this call: to validate connectivity to a cluster and/or database.
- It can validate that the server exists by not providing a database name defaulting to postgres as the target database.
- It can validate a specific database exists within that server by providing a user-defined database as input.
Let the user of this module determine the reasoning for it. Let us not force our definition of a PG ping on the user.
So I would simply modify postgresql_ping,py to return a failure for any connection error:
db_connection = connect_to_db(module, conn_params, fail_on_conn=True)
And by doing this I don't have to access the is_available return parameter to determine success or failure.
I changed the title of this issue to something more relevant to the evolving focus here: Using is_available return variable to determine if PG Ping is success or failure
It was not very clear, but I pasted the PostgreSQL documentation related to the output values.
Perhaps we can update the example in the documentation for postgresql_ping to add the check for is_available return value:
# In the example below you need to generate certificates previously. # See https://www.postgresql.org/docs/current/libpq-ssl.html for more information. - name: PostgreSQL ping dbsrv server using not default credentials and ssl # optionally check for return variable, is_available, to determine if we succeed or fail #register: results #failed_when: results.is_available == false community.postgresql.postgresql_ping: db: protected_db login_host: dbsrv login_user: secret login_password: secret_pass ca_cert: /root/root.crt ssl_mode: verify-full
@MichaelDBA great idea, added, PTAL, thanks!
By using this definition of a "connection", we actually add more flexibility to the purpose of this call: to validate connectivity to a cluster and/or database.
1. It can validate that the server exists by not providing a database name defaulting to **postgres** as the target database. 2. It can validate a specific database exists within that server by providing a user-defined database as input.
No, really, PostgreSQL ping connection is not a database existence check and should not be diverted to achieved such goal.
You can also have a DB which exists and you cannot connect to.
-
To know if a DB exists you need to connect to any DB and lookup PostgreSQL catalogs.
-
To know if you can connect to a DB you need to try to connect to the DB and check the result of the connection to evaluate if the DB is absent or is just forbidden, or...
OK, then, I reckon @klando is saying take out the database name option from the ping call just to see if we can connect or not. I'm for that. We can use the postgresql_query task to see if a database exists or not.