Unable to connect to PostgreSQL server although psql works fine

[oldtechaa]

I know plenty of questions have been submitted before on various forums about inability of Bareos to connect to PostgreSQL, but none of the solutions seem to work in this case. Here's the last portion of the log (as bareos user) of

bareos-dir -t -f -d 500

bareos-dir (50): postgresql.c:239-0 pg_real_connect done

bareos-dir (50): postgresql.c:241-0 db_user=bareos db_name=bareos db_password=Redacted
bareos-dir: dird.c:1157-0 Could not open Catalog "Catalog", database "bareos".
bareos-dir: dird.c:1162-0 postgresql.c:246 Unable to connect to PostgreSQL server. Database=bareos User=bareos
Possible causes: SQL server not running; password incorrect; max_connections exceeded.
00 FVbareos-dir ERROR TERMINATION
Please correct the configuration in /etc/bareos/bareos-dir.conf

This started happening after an upgrade from Debian Stretch to Buster. I've tried restarting IPv6, which I also changed at the same time, recreating the catalog, which works just fine, testing psql, which can connect fine as either postgres or bareos users, using a Unix socket connection or TCP/IP, checking the PSQL connection allowances, and connecting with or without a password. I don't have SELinux enabled, which was the solution for one person on a different forum.

Does anyone have any ideas what I should try next? Thank you.

Avery

[Christoph Haas]

Hi Avery,

Am 12.07.20 um 21:23 schrieb oldtechaa:

I know plenty of questions have been submitted before on various forums about inability of Bareos to connect to PostgreSQL, but none of the solutions seem to work in this case. Here's the last portion of the log (as bareos user) of

bareos-dir -t -f -d 500

bareos-dir (50): postgresql.c:239-0 pg_real_connect done

bareos-dir (50): postgresql.c:241-0 db_user=bareos db_name=bareos db_password=Redacted
bareos-dir: dird.c:1157-0 Could not open Catalog "Catalog", database "bareos".
bareos-dir: dird.c:1162-0 postgresql.c:246 Unable to connect to PostgreSQL server. Database=bareos User=bareos
Possible causes: SQL server not running; password incorrect; max_connections exceeded.
00 FVbareos-dir ERROR TERMINATION
Please correct the configuration in /etc/bareos/bareos-dir.conf

By default on Debian PostgreSQL listens to the localhost interface only IIRC. Is Bareos maybe not trying localhost but the hostname/IP that binds to the ethernet interface?

Anything in the /var/log/*postgresql.log?

What does your database configuration in Bareos look like?

…Christoph

[oldtechaa]

Here's the output of bareos-dbcheck -B:

catalog=Catalog
db_name=bareos
db_driver=postgresql
db_user=bareos
db_password=Redacted
db_address=
db_port=0
db_socket=
db_type=PostgreSQL
working_dir=/var/lib/bareos

And here's the whole catalog section of bareos-dir.conf:

Catalog {
  Name = Catalog
  # Uncomment the following lines if you want the dbi driver
  # dbdriver = "dbi:postgresql"; dbaddress = 127.0.0.1; dbport =
  #dbdriver = "postgresql"
  dbdriver = "postgresql"
  dbname = "bareos"
  dbuser = "bareos"
  dbpassword = "Redacted"
}

Here is the startup portion of postgresql-11.log after a restart of the service:

2020-07-13 18:05:59.609 EDT [30258] LOG:  listening on IPv6 address "::1", port 5434
2020-07-13 18:05:59.609 EDT [30258] LOG:  listening on IPv4 address "127.0.0.1", port 5434
2020-07-13 18:05:59.639 EDT [30258] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5434"
2020-07-13 18:05:59.750 EDT [30259] LOG:  database system was shut down at 2020-07-13 18:05:59 EDT
2020-07-13 18:05:59.796 EDT [30258] LOG:  database system is ready to accept connections
2020-07-13 18:06:00.363 EDT [30266] [unknown]@[unknown] LOG:  incomplete startup packet

 And here's my netstat -luntp showing open servers for PostgreSQL:

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      425/sshd
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      890/exim4
tcp        0      0 127.0.0.1:5434          0.0.0.0:*               LISTEN      30258/postgres
tcp        0      0 0.0.0.0:9102            0.0.0.0:*               LISTEN      422/bareos-fd
tcp        0      0 0.0.0.0:9103            0.0.0.0:*               LISTEN      424/bareos-sd
tcp6       0      0 :::22                   :::*                    LISTEN      425/sshd
tcp6       0      0 ::1:25                  :::*                    LISTEN      890/exim4
tcp6       0      0 ::1:5434                :::*                    LISTEN      30258/postgres
udp        0      0 0.0.0.0:68              0.0.0.0:*                           365/dhcpcd
udp6       0      0 :::546                  :::*                                365/dhcpcd

Finally, here's my pg_hba.conf:

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

I'm not sure where the problem could be here. Thanks for your help.

Avery

[Andreas Haase]

Hello,

Am 14.07.2020 um 00:11 schrieb oldtechaa <oldt...@gmail.com>:

tcp        0      0 127.0.0.1:5434          0.0.0.0:*               LISTEN      30258/postgres

Your Postgres listens to port 5434, standard would be 5432. So you have to change the port in your catalog configuration to 5434.

Regards,

Andreas

[oldtechaa]

Thanks a lot for your help. I kind of wondered about 5434, but I assumed the configuration wouldn't have changed the default port with an upgrade of stock Debian packages. I learned though that if a server is already running on 5432, the new version installed will be configured to run on a higher port, so the upgrade from 9.6 to 11 changed the port it was running on. I changed the port in postgresql.conf, used "pg_ctlcluster 11 main restart", and bareos-dir is now working fine. That solves my issue, so many thanks again.

Avery