move postgres database to mnt/database instead of default location

[5Reprise]

Hi,

I have the unique situation of burning through OS disks every 5-6 months because of high I/O with zabbix. (the root disk is an SD card. Failure signs show up after 3 months with high latency.) I set up a solution to have the database run on an externally mounted medium more suited to higher writes, and plan to run backups for the database in cron with rsync to my storage node to easily change the storage out and restore the zabbix database easier. I'm currently Re-imaging the entire disk every 6 months, which requires at least 60 minutes of downtime.

• The new location for postgres is: /mnt/database
• The file /etc/postgresql/11/main/postgresql.conf was updated to change to the following:

data_directory = '/mnt/database/postgresql/11/main/'
hba_file = '/mnt/database/postgresql/11/main/pg_hba.conf'
ident_file = '/mnt/database/postgresql/11/main/pg_ident.conf'

• The permissions were preserved using rsync -av, but here they are anyway:

root@bishop# ls -la /mnt/database/ 
total 28
drwxr-xr-x 4 postgres postgres 4096 Mar 26 18:29 .
drwxr-xr-x 3 root root 4096 Mar 26 17:11 ..
drwxr-xr-x 2 postgres postgres 16384 Mar 26 17:01 lost+found
drwxr-xr-x 3 postgres postgres 4096 Mar 6 2020 postgresql

• I've restarted postgresql, which comes up as active(exited), and of course, zabbix cannot run as a result.

I consulted many pages about "how to move a postgres database" which all match what I've done. I also can't see the data_directory for zabbix to double check I moved it.

root@bishop# sudo -u zabbix psql -U zabbix -tA -c "SHOW data_directory;" 
ERROR: must be superuser or a member of pg_read_all_settings to examine "data_directory"

Thank you all for your time.

[5Reprise]

Allow me to answer my own question.
I stole the execute command from ps to execute it on my own, and then see for myself in the terminal that the socket was opened, and the database is ready to accept connections.
That command is:

sudo -u postgres /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf -c hba_file=/etc/postgresql/11/main/pg_hba.conf -c ident_file=/etc/postgresql/11/main/pg_ident.conf -c external_pid_file=/var/run/postgresql/11-main.pid

Then I changed the config to my test dir in /tmp. I could see I mis-spelled a directory name. It then started working from the test.

Then I ran this command to make absolutely certain the postgres user can see the files I want to use. indeed, there were no errors.

sudo -u postgres ls -la /mnt/database/postgresql/11/main/

So, now I stop postgres again, I uncomment the real line in the config to the external drive /mnt/database, and did it again. start postgres with the command manually. It worked.
I did not change anything in the config from yesterday and today. I just started using the command from ps from vanilla postgres. I ctrl+c out, and now I start it with service postgresql start.
And I kid you not, it didn't complain this time. I checked everywhere:

┌─────────────────────────────────────────────────────────────────────────────── 05:27:39
└──root@bishop─> sudo -u postgres psql -l 130 
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
zabbix | zabbix | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

┌─────────────────────────────────────────────────────────────────────────────── 05:27:45
└──root@bishop─>  sudo -u postgres psql -c "SHOW data_directory;" 
data_directory
----------------------------------
/mnt/database/postgresql/11/main
(1 row)

Maybe someone will see this thread and get some helpful information. I honestly don't know what the hubbub was about. This is a straightforward database configuration.