Elijah Robison | GIS Blog

Long time no see. Scroll down for the answer, or start here for the story..

Today, by happenstance while testing a backup/restore strategy, my coworker discovered that our CentOS 7 installation from ISO/DVD used a default disk partitioning strategy that allocated a majority of disk space to the /home mountpoint, and a dearth of space to /, the root mountpoint. In our example, this meant about 2.1 TB available to the home mount and appx 50 GB to the root mount.

Meanwhile, our default PostGRESql 9.x install on this platform established the database instance to a location on the root mountpoint, which naturally limited it to 50 GB of consumption. While 50 GB might be enough for our purposes relative to the database instance, there is some danger in that, if it eventually consumed all 50 GB, it could potentially hose the system. Believe it or not, but it’s possible for log files to burgeon to huge values like this, particularly on systems that get stood up for simple missions and run for a decade trouble-free, all the while their log files collecting information about every db query, every web request, every user login, etc.

This system is not yet in production, so one option available to us was to start entirely from scratch and customize the disk partitioning so more space would be available to the database, and who knows what else. We considered resizing the partitions, stealing some space from the rich home mount and giving it to the less affluent root mount, but that option looked like a mess we didn’t want to step in. So ultimately we decided to try moving the database instance to the preferred mountpoint. But, having reached this decision, and being generally sure of what needed to happen for it to work, we didn’t find a definitive start to finish “how to” that addressed our confusions or a few of the gotchas we ran into. I’ll refrain from discussing what we expected and why we expected it, or a few missteps we made in the process, because in the end that’s just brain pollution.

While I suppose this approach works for most CentOS 7.x and PostGRESql 9.x combos, since versions, repositories, and packager managers are things, your mileage may vary.

No warranty implied! 

If you follow these instructions to the letter, you should be able to undo everything by working backwards, which is why we kept the original data directory intact, only renamed. But tread very carefully if you’re considering moving a production database.

<TL;DR>

CentOS 7 and PostGRESql: How to move the PostGRESql data_directory:

1. To get started, you need your current data_directory value. If you don’t know it you can get it from your database client (pgAdmin, DBeaver, etc) by connecting with the postgres user and querying:

SHOW data_directory;

Alternatively, you can get it from psql, here’s how that looks in my case:

cd /home
su postgres
psql
%TYPE_YOUR_PASSWORD_AT_THE_PROMPT%
show data_directory;
## /var/lib/pgsql/9.5/data

It probably goes without saying, but everywhere you see 9.5, your service names and file paths may be a little different, so be aware to adjust accordingly.

2. Next, create a new home for the PostGRESql instance. You’ll want to stop the database to release any locks. We created a location at /home/data/pg_data, like this.

service postgresql-9.5 stop		## Stop the database.
cd /home				## Make your new containing directory structure..
mkdir data
cd data
mkdir pg_data
chown -R postgres:postgres pg_data	## Establish postgres as the owner of the new directory.

3. Now deep-copy the contents of your original data directory to the new location, then re-name your original data directory so that when you restart the instance, you’ll know for certain it’s working out of the new location. Here’s how:

cd /var/lib/pgsql/9.5/data		## Browse into the original data dir
su postgres				## Switch to the postgres user for permission safety
cp -a * /home/data/pg_data/		## Make an archive copy (-a) of the contents, preserving permissions and symlinks
cd ..					## Browse back up..
mv data/ ORIGDATADIR			## And rename (mv) the original data folder

4. Now PostGRESql is moved, but you have to correct how the system instantiates its service interface. We surmised this from the ServerFault question asked here, and this is how we did it.

Note that nomenclature like this [i], or this [ESC], [:], etc.. means literally press those keys to communicate with the VI interface. Hopefully that makes sense. Also, please see the WARNING discussed in the closing thoughts.

cd /usr/lib/systemd/system/			## Browse here..
vi postgresql-9.5.service			## Open the postgres service file in VI
[i]						## Toggle INSERT mode in VI by hitting "i"
#Environment=PGDATA=/var/lib/pgsql/9.5/data/	## Find and comment-out this line with a pound sign (#)
Environment=PGDATA=/home/data/pg_data/		## And replace it with a line using your new data_directory path
[ESC]:x!					## Finally save your edits and close the file.

5. In the final step, you need to impose a change on SELinux as described here (lines 2 and 3), ensure proper access permissions on the new data_directory location (lines 4 and 5), force reconfigure your service instantiation manager ..I think, and then restart the PostGRESql instance.

cd /home/data
semanage fcontext --add --equal /var/lib/pgsql /home/data/pg_data
restorecon -rv /home/data/pg_data
chmod -R 700 /home/data/pg_data
chown -R postgres /home/data/pg_data
systemctl daemon-reload
service postgresql-9.5 start

That should do it—the start command should bring your database back online, operating out of a new location. If it fails to start, you might learn something about the problem by running journalctl -xe, which we did a few times while troubleshooting the approach.

WARNING. As alluded to in Step 4, when you open the open the postgresql service file, notice the counsel at the top of the file instructing you not to modify the file, but to create a different file elsewhere, in /etc, and modify it, because changes to the systemd/system file will be overwritten in the event of a package upgrade or something. We couldn’t get that to work for some reason, so we made good notes in our system documentation and hope to keep this concern in mind this going forward. I encourage you to pursue the recommended approach, but at the very least be mindful of this potential reversal down the road and any poor soul who may be following in your administrative footsteps without your knowledge of the problem! At minimum put a descriptive README file in a few key locations, like the root user’s home directory and the database’s default installation path.

Looking back, there are a few changes to this approach that would save a few steps, but this worked for us so I decided to post it as-built. (i.e. Rather than create pg_data, then cp -a the contents of data, just cp -a the entire data directory, which might avoid the chmod and chown steps later on..). I may decide to go back and modify the postgresql-9.5.service startup to use the development-recommended approach, in which case I’ll hopefully update this post, but please don’t hesitate to leave a comment if you know how to get that working.