PostgreSQL Server – Won’t Start After Moving Data Directory

postgresqlpostgresql-9.5postgresql-9.6

I am running a local postgresql server (9.5 & 9.6) on my Ubuntu 17.04 machine. After moving my data directories to another hard drive following this guide the server can't start.

I.e. with

sudo service postgresql restart && sudo service postgresql status 

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Mon 2017-11-06 10:31:32 CET; 1min 42s ago
  Process: 8962 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 8962 (code=exited, status=0/SUCCESS)

Nov 06 10:31:32 HERCULES systemd[1]: Starting PostgreSQL RDBMS...
Nov 06 10:31:32 HERCULES systemd[1]: Started PostgreSQL RDBMS.

The logs for both versions then contain the following:

pg_ctl: could not access directory "/media/daveknave/BIGFS/PGSQL/9.5/main": Keine Berechtigung

"Keine Berechtigung" translates to "Permission denied".

ls -l /media/daveknave/BIGFS/PGSQL/9.5/main

gives

drwxrwxrwx 5 postgres postgres 4096 Sep 25 21:42 base
drwxrwxrwx 2 postgres postgres 4096 Nov  3 21:07 global
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_clog
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_commit_ts
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_dynshmem
drwxrwxrwx 4 postgres postgres 4096 Sep 25 21:42 pg_logical
drwxrwxrwx 4 postgres postgres 4096 Sep 25 21:42 pg_multixact
drwxrwxrwx 2 postgres postgres 4096 Nov  3 21:07 pg_notify
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_replslot
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_serial
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_snapshots
drwxrwxrwx 2 postgres postgres 4096 Nov  3 21:07 pg_stat
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_stat_tmp
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_subtrans
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_tblspc
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_twophase
-rwxrwxrwx 1 postgres postgres    4 Sep 25 21:42 PG_VERSION
drwxrwxrwx 3 postgres postgres 4096 Nov  2 10:19 pg_xlog
-rwxrwxrwx 1 postgres postgres   88 Sep 25 21:42 postgresql.auto.conf
-rwxrwxrwx 1 postgres postgres  133 Nov  3 21:07 postmaster.opts
-rwxrwxrwx 1 postgres postgres  100 Nov  3 21:07 postmaster.pid

I've set the file permissions in an attempt to solve this, however no result.

Trying to access the server using

sudo -u postgres psql -W

prompts

Password: 
psql: could not connect to server: Datei oder Verzeichnis nicht gefunden
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Can anybody please help? Have any of you encountered that same issue? I've searched using google, but couldn't find any hints to solve my problem.

Best Answer

Verify the permissions on /main with 

ls -l /media/daveknave/BIGFS/PGSQL/9.5/

Does postgres still have access to main?

Switch user to postgres with su - postgres and then navigate through the directory structure /media/daveknave/BIGFS/PGSQL/9.5/main/. Can you navigate past main into base?

Verify the file permissions beneath ..../base with the following comamnds:

ls -lR /media/daveknave/BIGFS/PGSQL/9.5/main/base > ~/postrgres_main_perm.log

and then 

vi ~/postrgres_main_perm.log

If you find that the permissions are not correct in the log file, then run the following command to grant postgres all permissions to the directory ...main/base and all underlying sub-directories:

chown -Rv postgres:postgres /media/daveknave/BIGFS/PGSQL/9.5/main/base/

Caution:
Before you modify all permissions ensure the command works by replacing chown -Rv with something less destructive like ls -R or navigate down to the .../main directory and issue the shorter command: chown -Rv postgres:postgres base/. This will ensure you are applying the command to the correct directory.

Try restarting your PostgreSQL instance again.

Related Question