Loss of access to the database after restarting vm

Question

I have this database in a VM, could restart the VM and it could access correctly

But now if I restart the VM when trying to access the DB by pgAdmin III I get the message below

Server doesn't listen

The server doesn't accept connections: the connection library reports

could not connect to server: Connection refused Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432?

If you encounter this message, please check if the server you're trying to contact is actually running PostgreSQL on the given port. Test if you have network connectivity from your client to the server host using ping or equivalent tools. Is your network / VPN / SSH tunnel / firewall configured correctly?

For security reasons, PostgreSQL does not listen on all available IP addresses on the server machine initially. In order to access the server over the network, you need to enable listening on the address first. For PostgreSQL servers starting with version 8.0, this is controlled using the "listen_addresses" parameter in the postgresql.conf file. Here, you can enter a list of IP addresses the server should listen on, or simply use '*' to listen on all available IP addresses. For earlier servers (Version 7.3 or 7.4), you'll need to set the "tcpip_socket" parameter to 'true'.

You can use the postgresql.conf editor that is built into pgAdmin III to edit the postgresql.conf configuration file. After changing this file, you need to restart the server process to make the setting effective.

If you double-checked your configuration but still get this error message, it's still unlikely that you encounter a fatal PostgreSQL misbehaviour. You probably have some low level network connectivity problems (e.g. firewall configuration). Please check this thoroughly before reporting a bug to the PostgreSQL community.

I did some testing through VM snapshots and noticed that the behavior occurs after the command

sudo a2enmod rewrite

But I did not find anything that could indicate some link or how to solve, since I need to run sudo a2enmod rewrite

postgresql.conf connection settings:

# - Connection Settings -

listen_addresses = '*'      # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost'; use '*' for all
                    # (change requires restart)
port = 5432             # (change requires restart)
max_connections = 100           # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                    # (change requires restart)
#unix_socket_group = ''         # (change requires restart)
#unix_socket_permissions = 0777     # begin with 0 to use octal notation
                    # (change requires restart)
#bonjour = off              # advertise server via Bonjour
                    # (change requires restart)
#bonjour_name = ''          # defaults to the computer name
                    # (change requires restart)

I already tried to restart the postgres service, and it did not work

Answer 1

This is part of my general procedure for debugging access to any database.

Basic tests

Intended to determine whether or not the services are running properly.

Check if PostgreSQL is running:

pgrep -fl postgres
service postgresql status

(If status is available in your Linux then you should see something like this):

mortiz@florida:~/.mozilla/firefox/hlmpduzp.default$ sudo service postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Mon 2018-06-11 14:05:04 -03; 3s ago
  Process: 17522 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 17522 (code=exited, status=0/SUCCESS)

Jun 11 14:05:04 florida systemd[1]: Starting PostgreSQL RDBMS...
Jun 11 14:05:04 florida systemd[1]: Started PostgreSQL RDBMS.

If the service is running, check the basic connectivity to the port:

telnet 127.0.0.1 5432

If you aren't able to connect try with localhost or the ip address (not the loopback):

telnet localhost 5432
telnet <your_ip_address> 5432

• If the service isn't running try restoring a postgresql.conf backup.
• If the service is running but you aren't able to connect to the port, it could be a configuration problem, try restoring the configuration from a backup.

If you were able to connect to the port using telnet, then the service is up and the port is open, anyway there could be configuration problems with the instance or your client.

If telnet didn't work with 127.0.0.1 but it did with localhost or -(your_ip_address / not loopback) then change that in your client settings.

If the service is running, check the basic connectivity to the port:

telnet 127.0.0.1 5432

If you aren't able to connect try with localhost or the ip address (not the loopback):

telnet localhost 5432
telnet <your_ip_address> 5432

• If the service isn't running try restoring a postgresql.conf backup.
• If the service is running but you aren't able to connect to the port, it could be a configuration problem, try restoring the configuration from a backup.

If you were able to connect to the port using telnet, then the service is up and the port is open, anyway there could be configuration problems with the instance or your client.

If the telnet didn't work with 127.0.0.1 but it did with localhost or then change that in your client settings.

Testing functionality

To check the expected behavior of the service. In Linux some services start even with errors and produce undesired behaviors.

Now it's time to see if the service is working properly, we'll use the CLI. Run this command:

psql -h localhost --username=postgres --list

And you should see the list of databases:

mortiz@florida:~/.mozilla/firefox/hlmpduzp.default$ psql -h localhost --username=postgres --list
Password for user postgres: 
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

If this works then your problem may be in the client pgAdmin. Try installing another client or version and try connecting to the database.

Reviewing the configuration of the server * Maybe someone changed the method of accessing the database.

Be careful, backup your file first. Check the PostgreSQL Client Authentication Configuration File hosts allowed to connect / methods and users -> /etc/postgresql/9.6/main/pg_hba.conf

# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "ident", "peer", "pam", "ldap", "radius" or "cert".  Note that
# "password" sends passwords in clear text; "md5" is preferred since
# it sends encrypted passwords.

Tell me your results of each test if any problem.