patternsqlMinor
Changing data directory for postgres on CentOS
Viewed 0 times
directorycentospostgresforchangingdata
Problem
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:
So, I'd like to move the data directory to my home repository (it's a test-DB only for myself)
For doing so, I've tried:
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/SU
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% /homeFor 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 postgresqlStarting 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/SU
Solution
SELinux blocking access? Check your /var/log/audit/audit.log.
It might be necessary to fix the SELinux context for the new dir:
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/
Context
StackExchange Database Administrators Q#197809, answer score: 4
Revisions (0)
No revisions yet.