Loss of access to the database after restarting the virtual machine

0

I have a database in a VM, could restart the VM and access the database after this normally

But now if I restart the VM, when I try to access the database with pgAdmin III I get the following message:

  

Server does not listen

     

The server does not accept connections: the connection library

     

Could not connect to server: Connection declined 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 the IP addresses list   server should listen on, or simply use '*' to listen to 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   misbehavior. You probably have low level network connectivity   problems (e.g. firewall configuration). Please check it thoroughly   before reporting a bug to the PostgreSQL community.

I made some tests through the VM snapshots and noticed that the problem happens after the command

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've tried restarting the postgres service and it did not work.

    
asked by anonymous 30.05.2018 / 22:22

1 answer

0

This is most often due to the presence of the lock postmaster.pid file due to a sudden stop of PostgreSQL, either by a server crash or a kill -9 .

The error message already gives the hint of the first thing to be checked: is the PostgreSQL server up-to-date? First check if there are postgres user processes running:

$ ps -ef | grep postgres

If there is not, try to start the service and check both the system log file and the postgres file. It is possible that when stopping the VM the database was not closed properly, in these cases the postmaster.pid lock file may not have been removed, avoiding for security reasons that the same Postgres cluster is started in duplicate. If this is the case, your start attempt will result in the following error message:

  

FATAL: lock file "postmaster.pid" already exists

Most likely the error will appear in your syslog or messages , or directly on your terminal if you try start using

pg_ctl start -D [diretório raíz do cluster]

It is safe to delete the postmaster.pid file as long as you are sure that there is no more running Postgres process . Without the lock file the database will go up by redoing any transactions not yet dumped into data files by the checkpoint process and then go into mode This will allow you to log in to the server using the following command:

  

LOG: database system is ready to accept connections

Now, if the result of your ps -ef | grep postgres acknowledges the existence of processes still running, try doing a stop of the service before anything else. If they stay on the air, make a kill (do not use kill -9 !) And wait for them to add up. Then it is safe to delete the postmaster.pid and restart the database.

To avoid such occurrences, always stop PostgreSQL before reboot the server. If the bank is being started as an init service, systemd etc., it is possible that such a process is not correctly configured, or that your VM has been abruptly disconnected, such as "pulling from the socket", rather than performing the entire shutdown safe.

    
31.05.2018 / 06:31