Changing data directory for postgres on CentOS

Question

I've got a postgres 9.2.18 installation on CentOS which used the standard installation path. Now I realised that on this particular partition, there isn't sufficient hard-disk space for some bigger queries.

The default data_directory is on:

/var/lib/pgsql/data
$ df -h /var/lib/pgsql
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 50G 39G 12G 77% /

So, I'd like to move the data directory to my home repository (it's a test-DB only for myself)

$ df -h /home/mlu
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-home 395G 171G 224G 44% /home

For doing so, I've tried:

1) Copying postgres data using rsync: 
sudo rsync -av /var/lib/pgsql /home/mlu/postgres-data
2) Addressing the new path within postgresql.conf:
sudo -u postgres nano /var/lib/pgsql/data/postgresql.conf
 data_directory = '/home/mlu/postgres-data/pgsql/'
3) starting postgres:
sudo systemctl start postgresql

Starting postgres service doesn't works now, because of the new data_directory path within the postgresql.conf. (It works, if I comment out the directory path again though)

EDIT: Ok, I've figured out, that it's a permission conflict. But shouldn't rsync just have copied the correct permission as well?

sudo service postgresql.service status
Redirecting to /bin/systemctl status postgresql.service.service
●くろまる postgresql.service - PostgreSQL database server
 Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
 Active: failed (Result: exit-code) since Tue 2018年02月13日 18:34:54 CET; 21s ago
 Process: 27416 ExecStop=/usr/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
 Process: 27484 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=1/FAILURE)
 Process: 27473 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 27290 (code=exited, status=0/SUCCESS)
Feb 13 18:34:53 axonlu-ws026.mappuls.int systemd[1]: Starting PostgreSQL database server...
Feb 13 18:34:53 axonlu-ws026.mappuls.int pg_ctl[27484]: FATAL: could not read permissions of directory "/home/mlu/postgres-data/pgsql": Permission denied
Feb 13 18:34:54 axonlu-ws026.mappuls.int pg_ctl[27484]: pg_ctl: could not start server
Feb 13 18:34:54 axonlu-ws026.mappuls.int pg_ctl[27484]: Examine the log output.
Feb 13 18:34:54 axonlu-ws026.mappuls.int systemd[1]: postgresql.service: control process exited, code=exited status=1
Feb 13 18:34:54 axonlu-ws026.mappuls.int systemd[1]: Failed to start PostgreSQL database server.
Feb 13 18:34:54 axonlu-ws026.mappuls.int systemd[1]: Unit postgresql.service entered failed state.
Feb 13 18:34:54 axonlu-ws026.mappuls.int systemd[1]: postgresql.service failed.

Not sure why I still get a permission denied, since postgres should have all necessary rights as owner of the folder:

$ ls -l
total 4
drwxrwxrwx. 6 postgres postgres 4096 Aug 3 2017 pgsq

• Evan Carroll: "doesn't works" what does that mean.
• Mayak: I've added the error message that I get by doing "service postgresql.service status". Hope this helps.
• I'm Root James: Were you able to migrate the database? If not, can you try to change the data directory to home /home/psql instead of home/mlu/...

Answer 1

SELinux blocking access? Check your /var/log/audit/audit.log.

It might be necessary to fix the SELinux context for the new dir:

semanage fcontext --add --equal /var/lib/pgsql /home/mlu/postgres-data/pgsql
restorecon -rv /home/mlu/postgres-data/pgsql/

Answer 2

Not sure why I still get a permission denied, since postgres should have all necessary rights as owner of the folder:

No sudo rsync -av does not preserve permissions. You need to use -p for that or,

1. create the folder and set the owner to postgres
2. switch to the postgres user
3. copy the files.

• Mayak: postgres is owner of /home/mlu/postgres-data and /var/lib/pgsql but still has no permission to copy files between both folders
• Evan Carroll: @MayaK doubt it. unless the files in those directories are owned by another which is weird. and cause for concern.
• Mayak: I did "sudo chown -R postgres:postgres /home/mlu/postgres-data", then I switched to postgres user and tried "cd /home/mlu/postgres-data" and all I get is "no permission". Not sure, what's the problem here.
• Evan Carroll: do you have u+x on the dir?
• Mayak: $ ls -ld says: drwxrwxr-x. 2 postgres postgres