Postgresql – Why does Postgresql Data directory revert to default after updates/reboots

bashpostgresql

I have something that is pretty simple to explain. We have certain server builds as it relates to directories in our CentOS boxes at my company and so to accommodate those, I have a script that changes the PostgreSQL data directory. However, there seem to be some occasions where it reverts back to the default value. I know one such instance was after our IT department did some upgrades and then rebooted. I can't say for sure what exactly does this, but let me show you the code that I use to change the data directory and maybe someone can figure out what I am doing wrong.

sed -i "s/Environment=PGDATA=\/var\/lib\/pgsql\/11\/data/Environment=PGDATA=\/usr\/local\/pgsql\/data/" /usr/lib/systemd/system/postgresql-11.service

systemctl daemon-reload

systemctl restart postgresql-11

There is more, but that is what changes the data directory. Any ideas why this randomly will switch back to /var/lib/pgsql/11/data and how I can prevent this from changing back? My guess is that I need to change it somewhere other than just the service file, but I have yet to find out where else I need to change it.

I guess it could help if I provided some information. This is on a CentOS 7 box, with Postgresql 11.

Best Answer

After looking up some more details. It seems that the better options is create an override file in /etc/systemd .

Though, I cannot vouch for if this is best practice, I believe that it is a better option since I do not have to edit the default file.

dir=/etc/systemd/system/postgresql-11.service.d
if [ ! -d $dir ]
then
    mkdir $dir
    chmod 755 $dir
fi
cd $dir
touch override.conf
echo '[Service]' >> override.conf
echo 'Environment=PGDATA=/usr/local/pgsql/data' >> override.conf
systemctl daemon-reload
systemctl restart postgresql-11

I know normally this is done via systemctl edit, but from what i can tell, this does the same thing, just without using systemctl. This DID work and it allowed postgres to work even though the data directory in /usr/lib/systemd/system/postgresql-11.service does not have a working data directory in it. If someone has a better answer, I can gladly accept that though.

I arrived at this solution based off https://pgstef.github.io/2018/02/28/custom_pgdata_with_systemd.html

Related Question