PostgreSQL

1. Log in to a virtual server using SSH

Establishing an SSH connection.

2. Create a PostgreSQL database

initdb -D $HOME/postgresql
echo "listen_addresses = '$(vs-loopback-ip -4)'" >> $HOME/postgresql/postgresql.conf
echo "unix_socket_directories = '$HOME/tmp'" >> $HOME/postgresql/postgresql.conf

3. Create PM2 configuration file

The PM2 file is needed to start the PostgreSQL service automatically. In the filename, the .config.js ending is important!

Open the file ~/postgresql/postgresql.config.js and add:

module.exports = {
  apps: [
    {
      name: "postgresql",
      script: "/opt/zse/bin/postgres",
      args: "-D " + process.env.HOME + "/postgresql/",
      exec_mode: "fork", // Ensures it runs in the background
      autorestart: true, // Restart if it crashes
      watch: false, // PM2 will not watch for file changes
      max_restarts: 10, // Maximum number of restarts before giving up
    },
  ],
};

4. Add PM2 app to My Zone control panel

In the web-hosting management of the My Zone control panel, select PM2 and Node.js and then Add New Application.
Enter Postgresql in the Application Name field.
In the script or PM2 .JSON field enter: postgresql/postgresql.config.js.
Maximum memory usage: drag the slider to the maximum.
Click Save.

Wait 3-4 minutes until pm2 list in shell displays postgresql application status: online.

5. Set the root user password!

The root user name is your SSH user name in the form virtXXX, which is what you see when you run the shell: whoami

The PostgreSQL server host is your loopback IP address, which you can see if you run the shell: vs-loopback-ip -4

To set a password, log in to the PostgreSQL server:

psql -h $(vs-loopback-ip -4) -U $(whoami) -d postgres

Replace new_password with a secure password and run:

ALTER USER SESSION_USER WITH PASSWORD 'new_password';

Then remove the “trust” method from the pg_hba.conf file (without it the password will not be prompted):

sed -i 's/trust$/md5/g' ~/postgresql/pg_hba.conf

Restart the postgresql server:

pm2 restart postgresql

Make sure you are now prompted for a password when you connect!

psql -h $(vs-loopback-ip -4) -U $(whoami) -d postgres

6. Notes

• NB! There is only one version of PostgreSQL available on the ZoneOS server (currently 14.5) and this may change with a ZoneOS update without notice!
• It is a good idea to regularly back up the database yourself, as the Zone default file system backup may not be perfect.
• If you want to use PostgreSQL with PHP, you need to activate the PostgreSQL extension under PHP extensions in the My Zone control panel.