0

I've installed PostgreSQL 9.3 on Centos 7. After installing I could start the system service and login to psql. I want to move the PGDATA folder to another partition, so I tried several things to change DATADIR.

I copied the /var/lib/pgsql/data folder to /postgresdata/data, then created a symlink:

systemctl stop postgresql
cp -rp /var/lib/pgsql/data /postgresdata/data
mv /var/lib/pgsql/data /var/lib/pgsql/data.old
ln -s /postgresdata/data /var/lib/pgsql/data
systemctl start postgresql

The folder /postgresdata/ is set to 700 and owner postgres.

This results in the following error:

Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.

I tried two other methods as well, both resulting in the same error:

Method 1: Change postgresql.conf

data_directory = '/postgresdata/data'

Method 2: Change the system service setting:

vim /usr/lib/systemd/system/postgresql.service

Then change:

# Environment=PGDATA=/var/lib/pgsql/data
Environment=PGDATA=/postgresdata/data

Both result in the same error.

When I run journalctl -xe I get this:

-- Unit postgresql.service has begun starting up.
apr 25 15:08:03 srv001 pg_ctl[15517]: FATAL:  could not open file "/postgresdata/data/PG_VERSION": Permission denied
apr 25 15:08:04 srv001 systemd[1]: postgresql.service: control process exited, code=exited status=1
apr 25 15:08:04 srv001 pg_ctl[15517]: pg_ctl: could not start server
apr 25 15:08:04 srv001 pg_ctl[15517]: Examine the log output.
apr 25 15:08:04 srv001 systemd[1]: Failed to start PostgreSQL database server.
-- Subject: Unit postgresql.service has failed
-- Defined-By: systemd
-- Unit postgresql.service has failed.

Looking at /postgresdata/data/PG_VERSION I see no problems with rights. They're identical to the original it seems.

How can I move the data-folder for PostgreSQL on Centos 7?

4

The easiest thing to do is to mount your new disk at /var/lib/pgsql and save yourself a lot of trouble. This way you don't have to worry about altering SELinux policies or any of that.


If you insist on putting PostgreSQL's data directory in an unexpected location, you can make SELinux work correctly by setting a new file context for the new directory structure that is equivalent to the existing one. For example:

semanage fcontext --add --equal /var/lib/pgsql /postgresdata

This causes security contexts under /postgresdata to be applied as if they were under /var/lib/pgsql. You can then fix the contexts of any existing files with restorecon:

restorecon -rv /postgresdata

Be aware that people are not going to understand why you did this. That includes any admins that look at this server in the future, including yourself. There's no need to use a nonstandard directory simply because you are using a new disk. Just mount the new storage at /var/lib/pgsql.

  • Sometimes you enjoy to find a solution, bending the spoon and solving the problem. But on the long term, I agree, these solutions are a burden of pain. – Massimo Oct 2 '18 at 3:23
  • Do you mean to set a ln -s to mount a new disk/folder at var/lib/pgsql? Could you explain, please? – Michael Feb 22 at 7:31
  • @Michael No, I mean mount the disk at /var/lib/pgsql. – Michael Hampton Feb 22 at 14:18
0

The permissions of the PG_VERSION file mentioned should be simillar to this: (with user and group postgres)

-rw------- 1 postgres postgres

And also don't forget to not only allow access to /postgresdata/ but also to /postgresdata/data !

maybe try to use chown with -R (recrusive option).

Another problem could be the "other partition" if you use a different Filesystem for example like NTFS for moving data between multi boot systems you will run into those kind of permission problems especially since the executable permission bit is not supported from NTFS. Same goes of course for other non UNIX FS like fat ect.

best would be if you make the copy of the whole folder with rsync the the arcive option "-a or --archive" that try to keep the original permissions and symlinks ect. the same. of course only if its possible on the target Filesystem...

  • Thanks but the problem seems to be related to SELINUX, as mentioned by @Sven. – SPRBRN Apr 25 '17 at 14:08
0

The files & directories need to be labelled accordingly. You can have a look at the current labels with ls -Z.

The file contexts required for PostgreSQL can be determined using semanage fcontext --list | grep ^/var/lib/pgsql.

You'll need to adapt the policy to label files correctly. So you'll have to set this with semanage fcontext -a -t <type> <path-pattern> for each pattern listed in the output of the above command.

Having done that you just need to call restorecon -Rv /postgresdata to make SElinux relabel the files according to the updated filecontext patterns you just created.

Having done that your PostgreSQL instance should start again.

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.