Moving Postgres 10 data dir on Ubuntu 18

Question

I am trying to move the initial Postgres data dir from

/var/lib/postgresql/10/main

to another harddrive. The harddrive is accessable within the terminal via

/media/chris/Datenspeicher

I used this guide to switch my data dir. Postgres is starting up after modifying the postgres.conf file but im not able anymore to access the database through pgadmin or the terminal.

When trying to access the database its asking if the database is running on port 5432 and accepting connections.

Why? What am I doing wrong?

Update: After clean install of ubuntu and postgres all errors still remain. The postgres log output is:

2018年07月06日 14:16:04.958 CEST [5664] LOG: listening on IPv4 address "127.0.0.1", port 5432

2018年07月06日 14:16:04.998 CEST [5664] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

2018年07月06日 14:16:05.124 CEST [5665] LOG: database system was shut down at 2018年07月06日 14:00:21 CEST

2018年07月06日 14:16:05.167 CEST [5664] LOG: database system is ready to accept connections

2018年07月06日 14:16:05.714 CEST [5672] [unknown]@[unknown] LOG: incomplete startup packet

• user1822: What is the error you get?
• Trevirius: psql: cannot connect to the server: File or Directory not found. Is the server running local and accepting connections on unix-domain-socket >>/var/run/postgresql/.s.PGSQL.5432?
• user1822: Did you check the many, many questions with that error message: stackoverflow.com/…
• Trevirius: yes i did. The systemctl status tells me the server is running but the netstat -na | grep postgres prints no answer I also purged all postgres and reainstalled
• jjanes: Look in the server log file, probably in /var/log/postgres

Answer 1

Please check the socket file is in the /tmp. If it is there then create a link to /var/run.

OR, I prefer this method to move the data directory on Ubuntu.

I hope you have the backup copy of the old directory which is in /var/lib/postgresql/10/main.

service postgresql stop
rm -rf /media/chris/Datenspeicher/*
chown -R postgres:postgres /media/chris/Datenspeicher/
chmod 700 /media/chris/Datenspeicher
/usr/lib/postgresql/10/bin/initdb -D /media/chris/Datenspeicher
rm -rf /media/chris/Datenspeicher/*
sudo rsync -av /var/lib/postgresql/10/main/* /media/chris/Datenspeicher
in postgresql.conf file, data_directory = '/media/chris/Datenspeicher'
service postgresql start

• Trevirius: inside /tmp is no file related to postgres your provided tasks are sadly not helping either. its resulting in same error message when trying to connect with psql
• TheDataGuy: Ok, try to find the file find / -name s.PGSQL.5432 then create the link to /var/run/postgresql/.s.PGSQL.5432
• Trevirius: sudo find / -name s.PGSQL.5432 --> find / -name s.PGSQL.5432 no access rights so the file cannot be found ?
• TheDataGuy: run this from root user.
• Trevirius: even su -c 'find / -name s.PGSQL.5432' didn't help to find the file --> resulted in same message as running with sudo

Answer 2

after a clean ubuntu installation (clean all partiotions), auto mounting my second drive and using the answer from here everything works!

1. installed postgresql-common via:

   sudo apt install postgresql-common

2. open createcluster.conf with nano

   sudo nano /etc/postgresql-common/createcluster.conf

3. uncomment line create_main_cluster = true

4. change line data_directory = '/var/lib/postgresql/%v/%c' to new/dir/%v/%c

5. save file

6. install postgresql via sudo apt install -y postgresql-10