ilnarkz
ilnarkz

Reputation: 17

psql cannot connect to the server after changing data_directory

It became necessary to move the database from one directory to another. Tried on one computer as a test to transfer and everything was successful. Then I started to repeat the same steps on another computer. And after restarting the service, the database does not work. The steps I followed:

systemctl stop postgresql.service
mkdir /data/postgres_data
chown postgres:postgres /data/postgres_data
chmod 700 /data/postgres_data
sudo rsync -av /var/lib/postgresql/9.6/main /data/postgresql_data/

Modified postgresql.conf:

data_directory = '/data/postgres_data'

Changed /lib/systemd/system/postgresql.service:

...
Environment=PGDATA=/data/postgres_data
...

Then:

systemctl daemon-reload
systemctl start postgresql.service
systemctl status postgresql.service

The service works, but I can't connect to the database server. If I return the default path data_directory, everything works.

The error looks like this:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Output

sudo netstat -ltpn

...
tcp 0 0 0.0.0.0.0:5432 0.0.0.0.0:* LISTEN 15107/postgres      
...
tcp6 0 0 0 :::5432 :::* LISTEN 15107/postgres      
...

Also in the postgresql settings:

unix_socket_directories = /var/run/postgresql/
port = 5432

What do I need to correct?

Edit:

  1. Why was it necessary to move directories? - The database actively fills up free disk space, now it is enough, but in the future you will still have to do something.
  2. Where is /data/postgres_data located relative to where /var/lib/postgresql/9.6/main is located? - These are both absolute paths.
  3. Does /var/run/postgresql/.s.PGSQL.5432 exist? - Yes, it is

Upvotes: 0

Views: 70

Answers (0)

Related Questions