Postgres not working when I change the data directory in Ubuntu

Question

I am new in Linux and Postgres. I have been trying to connect my data to another directory for 2 days without success.

First I changed in my postgres.conf the data directory from:

data_directory = '/var/lib/postgresql/9.6/main'

To:

data_directory = '/media/cesar/My Book/data9.6'

When I try to connect to Postgres I get this error:

cesar@ubuntu:/$ sudo -u postgres psql postgres
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"?

I restarted the Postgres Server several times and changed the configuration also without success.

If I change back to the old directory it works fine.

cesar@ubuntu:/$ sudo /etc/init.d/postgresql restart
[sudo] password for cesar: 
[ ok ] Restarting postgresql (via systemctl): postgresql.service.
cesar@ubuntu:/$ sudo -u postgres psql postgres
psql (9.6.2)
Type "help" for help.
postgres=#

Any idea of what I am doing wrong?

ls -s response

postgres@ubuntu:~$ ls -l /media/cesar/My\ Book/data9.6
total 149
drwxrwxrwx 1 cesar cesar     0 Jan  7 22:04 base
drwxrwxrwx 1 cesar cesar 16384 Feb  6 19:40 global
drwxrwxrwx 1 cesar cesar     0 Jan  7 21:56 pg_clog
drwxrwxrwx 1 cesar cesar     0 Jan  7 21:56 pg_commit_ts
drwxrwxrwx 1 cesar cesar     0 Jan  7 21:56 pg_dynshmem
-rwxrwxrwx 2 cesar cesar  4118 Jan  8 14:46 pg_hba.conf
-rwxrwxrwx 2 cesar cesar  1678 Jan  7 21:56 pg_ident.conf
drwxrwxrwx 1 cesar cesar 65536 Feb  6 00:16 pg_log
drwxrwxrwx 1 cesar cesar     0 Jan  7 21:56 pg_logical
drwxrwxrwx 1 cesar cesar     0 Jan  7 21:56 pg_multixact
drwxrwxrwx 1 cesar cesar     0 Feb  6 00:16 pg_notify
drwxrwxrwx 1 cesar cesar     0 Jan  7 21:56 pg_replslot
drwxrwxrwx 1 cesar cesar     0 Jan  7 21:56 pg_serial
drwxrwxrwx 1 cesar cesar     0 Jan  7 21:56 pg_snapshots
drwxrwxrwx 1 cesar cesar     0 Feb  6 00:16 pg_stat
drwxrwxrwx 1 cesar cesar     0 Feb  7 21:15 pg_stat_tmp
drwxrwxrwx 1 cesar cesar     0 Jan  7 21:56 pg_subtrans
drwxrwxrwx 1 cesar cesar     0 Jan  7 21:56 pg_tblspc
drwxrwxrwx 1 cesar cesar     0 Jan  7 21:56 pg_twophase
-rwxrwxrwx 2 cesar cesar     4 Jan  7 21:56 PG_VERSION
drwxrwxrwx 1 cesar cesar 32768 Feb  6 00:15 pg_xlog
-rwxrwxrwx 2 cesar cesar    90 Jan  7 21:56 postgresql.auto.conf
-rwxrwxrwx 1 cesar cesar 22267 Feb 12 14:25 postgresql.conf
-rwxrwxrwx 2 cesar cesar    67 Feb  6 00:16 postmaster.opts
-rwxrwxrwx 2 cesar cesar    35 Feb  6 00:16 postmaster.pid
-rwxrwxrwx 1 cesar cesar     3 Feb 12 14:52 test.txt

What are permissions and owner of old directory and new directory? — Sergius
– Sergius, Commented Feb 12, 2017 at 18:32
FATAL: data directory "/media/cesar/My Book/dataGE" has group or world access DETAIL: Permissions should be u=rwx (0700). — CesarD
– CesarD, Commented Feb 12, 2017 at 18:39
I get that error when I try: cesar@ubuntu:/$ /usr/lib/postgresql/9.6/bin/pg_ctl -D /media/cesar/My\ Book/dataGE -l /tmp/pgsql.log start server starting — CesarD
– CesarD, Commented Feb 12, 2017 at 18:43
I did this chmod -R 700 /media/cesar/My\ Book/dataGE but I am still having the same error — CesarD
– CesarD, Commented Feb 12, 2017 at 18:59

Andomar · Accepted Answer · 2017-02-12 20:05:49Z

8

User postgres should have read, write and execute permissions on the data directory. No other user should have write access. You can set these permissions with:

chown -R postgres:postgres '/media/cesar/My Book/dataGE'
chmod -R u+rwx,g-rwx,o-rwx '/media/cesar/My Book/dataGE'

After changing the data directory, you should run initdb to set up the database files:

su - postgres
initdb

edited Feb 12, 2017 at 20:05

answered Feb 12, 2017 at 19:38

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

CesarD Over a year ago

If I use sudo -su postgres I can become postgres user but if I try to open the server as postgres gives me the same error. Thank you!

Andomar Over a year ago

After becoming the postgres user, can you read & write to the data directory? Like ls /path/to/data and echo hi > /path/to/data/test.txt

CesarD Over a year ago

this is what I tried postgres@ubuntu:/media/cesar/My Book/data9.6$ and I accessed it

CesarD Over a year ago

echo hi says hi as ubuntu user in that directory

CesarD Over a year ago

I didn't know that was important to mention but this is an external disk connected trough a USB SS cable. It is not changing the owner and the group because is an external disk so I am trying to use gksu gedit /etc/fstab to map disk and change the owner. I still need to keep working on this and I will test the change of directory soon.

|

Jameel · Accepted Answer · 2018-11-29 13:36:59Z

0

I would like to share my issue basically was with permission. DB Cluster was not starting when I check with this >> pg_lsclusters when I check the Log file >> sudo nano /var/log/postgresql/postgresql-9.6-main.log "FATAL: data directory "/var/lib/postgresql/9.6/main/" has group or world access DETAIL: Permissions should be u=rwx (0700)"

The solution is already mentioned so I just executed >> sudo chmod -R u=rwx '/var/lib/postgresql/9.6/main/' and restarted postgresql service {notable took some time to start which was good indication of doing something} after that I check with >> pg_lsclusters and YES it worked!!

answered Nov 29, 2018 at 13:36

Jameel

536 bronze badges

Comments

D V Santhosh Kiran · Accepted Answer · 2018-12-18 16:50:33Z

Adding to @Jameel's & @Andomar's answers (as i had this issue on my local postgres db),

My setup is a local Postgres DB. And I've enabled db logging to see what queries are running on the db and their errors (Just FYI... I've a hibernate application persisting to db on backend and to debug hibernate issues, I felt its easy to check postgres logs instead of juggling with logger configuration).

postgres logs are in /9.6/Data/logs. To open the log with an editor (e.g: sublime text) I added read-only permission to everyone. though it didn't readily stopped my postgres server, upon subsequent restart attempts (for e.g restart laptop) postgres server was failing to start.

so removing all the permissions to everyone recursively on /9.6/Data resolved my issue. I mean it should be read & write to postgres user only and its a must that no other permission.

Collectives™ on Stack Overflow

Postgres not working when I change the data directory in Ubuntu

3 Answers 3

13 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related