PostgreSQL won't start after moving datadir on Centos 7

Question

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?

Answer 1

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.

Answer 2

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...

Answer 3

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.

Answer 4

for pg9.6 on method 2, you need to initdb after changing the directory setting

/usr/pgsql-9.6/bin/postgresql96-setup initdb

Answer 5

I ended up on this page because I ran into a similar situation. And I thank @Andreas Rogge in the previous post for the solution.

Indeed all I had to do is restorecon -Rv /var/lib/pgsql/data to make SElinux relabel the files according to the updated filecontext patterns. With ls -Z I could see they were not correct. I had a original data folder kept aside, to compare.

I moved the postgres data dir. Well, actually I copied it to another temporary folder, because I needed to migrate the current filesystem from ext2 to xfs. (The data dir is a separate mounted filesystem) So I used a tar copy to the temp filesystem, tar usually preserves all the file characteristics, but obviously not for selinux labels. I initialized the filesystem as xfs, and tar-copied the whole data dir back to this remounted location. But then postgres did not want to start, even though /var/lib/pgsql/data was available and filled, journalctl -xe output showed the error "/var/lib/pgsql/data" is missing or empty. With the knowledge from the Andreas I was able to check with ls -Z that the labels were not right. The needed to be restored from:

unconfined_u:object_r:unlabeled_t:s0 to unconfined_u:object_r:postgresql_db_t:s0

which restorecon -Rv /var/lib/pgsql/data solved!

So if anyone ever ends up here after googling "postgres does not start up after moving data folder, with error "/var/lib/pgsql/data" is missing or empty in journalctl -xe output" I hope it helps them to quickly recover with this knowledge.