CentOS 7, PostgreSQL failed to read permission data directory in mounted partition

Question

I've a problem with start PostgreSQL 9.4 on Centos 7 Server. Because the partition /var/lib/pgsql/9.4/data/ (which contains PostgreSQL data) is full so I want to change to another partition.

/dev/sdb1 154685904 322500 146482752 1% /run/media/root/Data

I've used this command to copy all the old data to new data folder:

cp -a /var/lib/pgsql/9.4/data/ /run/media/root/Data/pgsql_data

Then I changed the directory to new data directory in postgresql.conf (/var/lib/pgsql/9.4/data)

data_directory = '/run/media/root/Data/pgsql_data/' # use data in another directory

And then I try to restart PostgreSQL, here are errors:

postgresql-9.4.service - PostgreSQL 9.4 database server 
Loaded: loaded (/usr/lib/systemd/system/postgresql-9.4.service; enabled) 
Active: failed (Result: exit-code) since Wed 2015年03月11日 09:17:21 ICT; 4s ago 
Process: 54975 ExecStop=/usr/pgsql-9.4/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS) 
Process: 56133 ExecStart=/usr/pgsql-9.4/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=1/FAILURE) 
Process: 56127 ExecStartPre=/usr/pgsql-9.4/bin/postgresql94-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) 
Main PID: 54580 (code=exited, status=0/SUCCESS)
Mar 11 09:17:16 localhost.localdomain pg_ctl[56133]: LOG: skipping missing configuration file "/run/media/root/Data/pgsql_data/postgresql.auto.conf" 
Mar 11 09:17:16 localhost.localdomain pg_ctl[56133]: < 2015年03月10日 22:17:16.187 EDT FATAL: could not read permissions of directory "/run/media/root/Data/pgsql_data": Permission denied 
Mar 11 09:17:21 localhost.localdomain pg_ctl[56133]: pg_ctl: could not start server 
Mar 11 09:17:21 localhost.localdomain pg_ctl[56133]: Examine the log output. 
Mar 11 09:17:21 localhost.localdomain systemd[1]: postgresql-9.4.service: control process exited, code=exited status=1 
Mar 11 09:17:21 localhost.localdomain systemd[1]: Failed to start PostgreSQL 9.4 database server. 
Mar 11 09:17:21 localhost.localdomain systemd[1]: Unit postgresql-9.4.service entered failed state.

Please help!

• Pham Huu Bang: So that you mean, I should add permission for postgres user rwx to /run/ and old children folders?

Answer 1

You are obviously missing some permissions in the new location.

I have tried to reproduce your error on my local test cluster. I could get several different errors along the way but never the one you got. However, here are the permissions you need on your directories to run the cluster:

• the data_directory (in your case, pgsql_data) must be owned by postgres (the group seems making no difference)
• it must have 0700 as permissions (no group and world level permissions are allowed)
• all directories under this need 07XX, where XX refers to the group and world permissions - I haven't found any restrictions to these (but obviously didn't try all possibilities)

Based on my experiments, these should be enough for starting PostgreSQL.

• I'm Root James: I thought I would add comment also that when moving PostgreSQL data directory on a CentOS that I could NOT move the data dir into /home/username/ location. I kept getting the same permission errors, even though all the permissions matched the original data directory and SELinux was configured to give the new data dir the same permissions. In the end, moving the new data dir to /home/pg_data instead of /home/username/pg_data worked perfectly.