PostgreSQL databases are not working after apt-get upgrade on Debian 7

Created:

2016-11-16 12:45:57 UTC

Modified:

2017-08-16 16:38:06 UTC

0

Was this article helpful?


Have more questions?

Submit a request

PostgreSQL databases are not working after apt-get upgrade on Debian 7

Applicable to:

  • Plesk 11.x for Linux

Symptoms

Click "Tools & Utilities" -> "Services Management". See that PostgreSQL is down. Click green start icon to start it and you can see that the following error appears:

Error: Unable to make action: Unable to manage service by pg_manage: Error: Unable to detect posgres version () or cluster name ()
3
('--start', 'postgresql')

According to apt-get history new version of postgresql-9.1 been installed during 'apt-get upgrade'

less /var/log/apt/history.log


Start-Date: 2014-03-07 01:07:28
Commandline: apt-get upgrade
Upgrade: postgresql-9.1:amd64

And the old version removed automatically:
Start-Date: 2014-03-07 02:22:04
Commandline: apt-get autoremove
Remove: postgresql-8.4:amd64

So only the latest version is present on the server:

# dpkg -l | grep -i postgres
ii libpq-dev 9.1.12-0wheezy1 amd64 header files for libpq5 (PostgreSQL library)
rc libpq4 8.1.18-0etch1 amd64 PostgreSQL C client library
ii libpq5 9.1.12-0wheezy1 amd64 PostgreSQL C client library
ii php5-pgsql 5.4.25-1~dotdeb.1 amd64 PostgreSQL module for php5
ii postgresql 9.1+134wheezy4 all object-relational SQL database (supported version)
rc postgresql-7.4 1:7.4.26-0etch1 amd64 object-relational SQL database, version 7.4 server
rc postgresql-8.3 8.3.16-0lenny1 amd64 object-relational SQL database, version 8.3 server
rc postgresql-8.4 8.4.17-0squeeze1 amd64 object-relational SQL database, version 8.4 server
=== > ii postgresql-9.1 9.1.12-0wheezy1 amd64 object-relational SQL database, version 9.1 server

It is not possible to manage the service by pg_manage:

# /usr/local/psa/admin/bin/pg_manage --start
Error: Unable to detect posgres version () or cluster name ()
3

Cause

PostgreSQL daemon had been running on the port 5433 instead of 5432. Plesk is able to work with PostgreSQL cluster on the port 5432 only. In PostgreSQL's case it's typical to use port 5432 if it is available. If it isn't, most installers will choose the next free port, usually 5433. It is probably because you already have a PostgreSQL cluster on port 5432 or you've removed it, but the configuration remains unchanged (this is expected).

You can see what is actually running using the netstat tool.

netstat -nlp | grep 543
tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN 25025/postgres
unix 2 [ ACC ] STREAM LISTENING 23235729 25025/postgres /var/run/postgresql/.s.PGSQL.5432

/usr/bin/pg_lsclusters
Version Cluster Port Status Owner Data directory Log file
9.1 main 5433 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log

Resolution

Change port to 5432.

grep ^port /etc/postgresql/9.1/main/postgresql.conf
port = 5432 # (change requires restart)

Check also that configured paths are correct:

cat /etc/psa/psa.conf | grep PGSQL
PGSQL_DATA_D /var/lib/postgresql/9.1/main
PGSQL_CONF_D /var/lib/postgresql/9.1/main
PGSQL_BIN_D /var/lib/postgresql/9.1/bin
Have more questions? Submit a request
Please sign in to leave a comment.