After upgrading the temBoard agent from version 7.6 to version 7.7, I have noticed an issue in regards to parsing of the dsn of the parameter primary_conninfo. I have described the issue here:
https://github.com/dalibo/temboard-agent/issues/531
And this is the error which pops up in the Agent log file.
2021-06-07 14:52:38,583 temboardagent[2030906]: [probes] ERROR: File "/usr/lib64/python3.6/site-packages/psycopg2/extensions.py", line 155, in make_dsn 2021-06-07 14:52:38,583 temboardagent[2030906]: [probes] ERROR: parse_dsn(dsn) 2021-06-07 14:52:38,583 temboardagent[2030906]: [probes] ERROR: psycopg2.ProgrammingError: invalid dsn: missing "=" after "/home/postgres/.pgpass''" in connection info string 2021-06-07 14:52:38,597 temboardagent[2030906]: [probes] ERROR: invalid dsn: missing "=" after "/home/postgres/.pgpass''" in connection info string 2021-06-07 14:52:38,597 temboardagent[2030906]: [probes] ERROR: Traceback (most recent call last): 2021-06-07 14:52:38,597 temboardagent[2030906]: [probes] ERROR: File "/usr/lib/python3.6/site-packages/temboardagent/plugins/monitoring/probes.py", line 865, in run 2021-06-07 14:52:38,597 temboardagent[2030906]: [probes] ERROR: dsn = parse_dsn(get_primary_conninfo(conn)) 2021-06-07 14:52:38,597 temboardagent[2030906]: [probes] ERROR: psycopg2.ProgrammingError: invalid dsn: missing "=" after "/home/postgres/.pgpass''" in connection info string
The issue applies on Red Hat 7 and Red Hat 8 on the PostgreSQL Standby side. Let’s take a look first on Red Hat 8.
$ cat /etc/redhat-release Red Hat Enterprise Linux release 8.3 (Ootpa) $ uname -a Linux host01 4.18.0-240.15.1.el8_3.x86_64 #1 SMP Wed Feb 3 03:12:15 EST 2021 x86_64 x86_64 x86_64 GNU/Linux
Red Hat 8 comes with Python 3 per default, and also with a quite recent libpq version.
$ ldd /usr/lib64/python3.6/site-packages/psycopg2/_psycopg.cpython-36m-x86_64-linux-gnu.so | grep libpq
libpq.so.5 => /lib64/libpq.so.5 (0x000014ef0d124000)
$ ls -l /lib64/libpq.so.5
lrwxrwxrwx. 1 root root 13 Dec 1 2020 /lib64/libpq.so.5 -> libpq.so.5.12
$ ls -l /lib64/libpq.so.5.12
-rwxr-xr-x. 1 root root 332608 Dec 1 2020 /lib64/libpq.so.5.12
However, the Agent has issues with parsing the primary_conninfo if it looks like this one (which is perfectly valid from the PostgreSQL point of view):
$ cat postgresql.auto.conf.20210607a # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo = 'host=host00 port=55041 user=repmgr application_name=pgx connect_timeout=2 passfile=''/home/postgres/.pgpass''' recovery_target_timeline = 'latest'
After removing the single quotes from the passfile parameter it works. 🙂
$ cat postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo = 'host=host00 port=55041 user=repmgr application_name=pgx connect_timeout=2 passfile=/home/postgres/.pgpass' recovery_target_timeline = 'latest'
On Red Hat 7 it is a different story, because it is shipped per default with a quite old libpq.
$ cd /usr/lib64 $ ls -l | grep libpq lrwxrwxrwx. 1 root root 12 Jul 10 2020 libpq.so.5 -> libpq.so.5.5 -rwxr-xr-x. 1 root root 197544 Feb 24 2020 libpq.so.5.5 $ rpm -qa | grep postgres postgresql-libs-9.2.24-4.el7_8.x86_64
To fix the issue, we must find a way to tell psycopg to use a different libpq shared object, or to upgrade psycopg to a more recent version.
$ ldd /usr/lib64/python3.6/site-packages/psycopg2/_psycopg.cpython-36m-x86_64-linux-gnu.so | grep libpq
libpq.so.5 => /lib64/libpq.so.5 (0x00002b464425e000)
There a different ways to upgrade to a more recent version on Red Hat 7 or to fix it in a different way.
- You could install a more recent version from the postgresql.org yum repository.
- You could compile psycopg yourself.
- You could change the parameter passfile to password.
- Or you change the temBoard Agent systemd service (LD_LIBRARY_PATH)
Let’s get through the different options.
You could install a more recent version from the postgresql.org yum repository. However, this comes with it’s one issues. You could easily mess up your rpm repository.
# Install the repository RPM: sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
You could compile psycopg yourself. For more information, take a look at the following links.
https://www.psycopg.org/
https://www.psycopg.org/docs/install.html
https://github.com/psycopg/psycopg2
You could change the parameter passfile to password. For example from
primary_conninfo = 'user=repmgr connect_timeout=2 host=host00 port=55004 user=repmgr application_name=pgx ''passfile=/home/postgres/.pgpass'''
to
primary_conninfo = 'user=repmgr connect_timeout=2 host=host00 port=55004 user=repmgr application_name=pgx connect_timeout=2 password=***************'
Or you could change the temBoard Agent systemd service, to point to a different library path, which is the most elegant solution from my point of view.
To do so, you just need to add the LD_LIBRARY_PATH to you systemd service file.
$ cat temboard-agent@12-pg55000.service [Unit] Description=PostgreSQL Remote Control Agent %I After=network.target postgresql@%i.service AssertPathExists=/etc/temboard-agent/%I/temboard-agent.conf [Service] Type=simple User=postgres Group=postgres Environment="LD_LIBRARY_PATH=/app/lib/postgres/pgproduct/pg-12.6/lib" ExecStart=/usr/bin/env SYSTEMD=1 temboard-agent -c /etc/temboard-agent/%I/temboard-agent.conf [Install] WantedBy=multi-user.target
Afterwards reload the daemon and restart the agent.
$ sudo systemctl daemon-reload $ sudo systemctl stop temboard-agent@12-pg55000.service $ sudo systemctl start temboard-agent@12-pg55000.service
Ready.
Conclusion
Fixing the issue is not too complicated. On Red Hat 8 you need just to remove the single quotes from the passfile parameter. On Red Hat 7 you need to remove the single quotes as well, and additionally add one environment variable to your systemd service.