2

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
  • "doesn't works" what does that mean. – Evan Carroll Feb 13 '18 at 17:18
  • I've added the error message that I get by doing "service postgresql.service status". Hope this helps. – Mayak Feb 13 '18 at 17:37
1

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.
  • postgres is owner of /home/mlu/postgres-data and /var/lib/pgsql but still has no permission to copy files between both folders – Mayak Feb 13 '18 at 19:06
  • @MayaK doubt it. unless the files in those directories are owned by another which is weird. and cause for concern. – Evan Carroll Feb 13 '18 at 19:14
  • 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. – Mayak Feb 13 '18 at 20:02
  • do you have u+x on the dir? – Evan Carroll Feb 13 '18 at 20:03
  • $ ls -ld says: drwxrwxr-x. 2 postgres postgres – Mayak Feb 13 '18 at 20:11
0

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.