HOWTO install and run a private PostgreSQL server instance on Opalstack

sean

Opalstack's shared PostgreSQL instance is optimized for multi-tenant shared hosting, and individual customers aren't able to adjust the DB server configuration. If you do need that kind of control, or just want to learn more about running your own database server instance, then the following procedure is for you!

Here's how to install and run your own private instance of PostgreSQL on Opalstack:

Create a new shell user. The rest of this procedure will use myuser as the shell user name - be sure to replace that with your own shell user name.
Create a new "proxy port" application for the new shell user. Make a note of the app name and port assignment. The rest of this procedure will use pgapp as the name and 55555 as the port - be sure to replace them with your own app name and port.
SSH to your server as the shell user you created in step 1.

Create a file named ~/pwfile in at the top of your home directory and secure its permissions:

touch ~/pwfile
chmod 600 ~/pwfile

Edit ~/pwfile and put your desired PostgreSQL superuser password in it.

Run the following commands as your shell user to install PostgreSQL into the app directory:

export APPNAME=pgapp
export APPDIR=$HOME/apps/$APPNAME
export APPPORT=55555
export PATH=/usr/pgsql-11/bin/:$PATH
echo "export PATH=/usr/pgsql-11/bin/:\$PATH" >> ~/.bashrc
mkdir -p $APPDIR/{bin,data,dumps,run}
initdb -D $APPDIR/data -E UTF8 --pwfile ~/pwfile -A password
sed -i "s|#port = 5432|port = $APPPORT|g" $APPDIR/data/postgresql.conf
sed -i "s|#unix_socket_directories = '/var/run/postgresql, /tmp'|unix_socket_directories = '$HOME\/apps\/$APPNAME\/run'|g" $APPDIR/data/postgresql.conf

Finally, delete the password file:

rm ~/pwfile

PostgreSQL is now installed and you can access it using your shell user name as the PostgreSQL superuser name with the password you specified earlier in the password file.

The commands to work with the private DB instance are:

Start: pg_ctl -D ~/apps/pgapp/data start
Stop: pg_ctl -D ~/apps/pgapp/data stop
Connect: psql -U myuser -h localhost -p 55555 postgres

Be sure to replace the username and port in the above command with your own.

That's it! If you need a private PostgreSQL instance then please give this a try and let me know how it goes here in the comments. :-)

oceanlove

sean

Everything worked like a charm, with absolutely no issue! Thanks Sean.

P/s: Just a note in case it'd help someone:
The "pg_ctl" command is not available by default, so if we log into a new shell, we need to do the following export again:
export PATH=/usr/pgsql-11/bin/:$PATH

The better way is probably to add it to ~/.bashrc so that it'd now always be available for every shell login.

sean

oceanlove I've added a bashrc thing to the procedure, thanks!

kurt

I'm running into this error when I try to execute initdb:

$ initdb -D $APPDIR/data -E UTF8 --pwfile ~/pwfile -A password
The files belonging to this database system will be owned by user "myuser".
This user must also own the server process.

initdb: invalid locale settings; check LANG and LC_* environment variables

Explicitly setting the --locale flag seems to work:

$ initdb -D $APPDIR/data -E UTF8 --locale "en_US.UTF-8" --pwfile ~/pwfile -A password

Here's what I get for locale, btw:

$ locale
locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_ALL to default locale: No such file or directory
LANG=en_US.UTF-8
LC_CTYPE=UTF-8
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

sean

kurt I can't reproduce any of those problems. Are you doing anything weird with locale settings in your shells locally or on the server?

kurt

sean I don't think I'm doing anything weird with locale settings. I haven't done any manual shell configuration yet. Up until now, I've just migrated some applications over from webfaction, rsynced some tar.bz2 files from there to here and set up private postgres and mysql instances.

I see a locale-related warning when I SSH into Opalstack:

ssh x@opal1.opalstack.com
Last login: Sun Dec  6 08:33:52 2020 from x.x.x.x
-bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory

I just noticed I'm getting a similar error when I SSH into my account at WF, so it may be something related to my local setup (MacBook Pro, macOS Big Sur version 11.0.1):

x@web503.webfaction.com's password:
Last login: Sun Dec  6 08:34:36 2020 from x.x.x.x
-bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory
manpath: can't set the locale; make sure $LC_* and $LANG are correct

I'll do some googling.

kurt

This seems to be a macOS issue. I added a couple of lines to my ~/.[bash,zsh]_profile, following the suggestion here, then ran exec zsh, and the problem seems to be fixed. Sorry for the bother!

The lines I added:

export LC_ALL=en_US.UTF-8
export LANG=en_US.UTF-8

sean

kurt using bash on Catalina locally here, no issues at all.

kurt

sean Digging deeper, it seems to be related to my exotic AppleLocale:

➜  ~ defaults read -g AppleLocale
en_AT

It's part of the price I have to pay as an American living in Austria 😃

Apologies for the bother! It seems adding the two lines from my previous message does the trick, though.

herohat

It works! but just adding these two steps before running "initdb -D $APPDIR/data -E UTF8 --pwfile ~/pwfile -A password"

export LC_ALL=en_US.UTF-8
export LANG=en_US.UTF-8

stephan0h

Hi Sean,
Thanks, setting up the PostgreSQL instance worked like a charm.
But when I try to connect remotely I always get a timeout - although I tried to configure postgresql.conf and pg_hba.conf. I'm wondering now: is it my configuration that is not correct or is it some opalstack-setting that I have to tweak?
Thanks & kind regards,
Stephan

sean

stephan0h Your app's port is closed in the firewall by default. If you'd like to have the port opened up then please email support @ opalstack.com from your main account email address to request it. Thanks!

stephan0h

Thanks, this explains, why it wouldn't work. I come back on this, when I need it, thank you!

anjanesh

If I would want to connect to a PostgresSQL database via TablePlus, I would need the port open ?

sean

anjanesh If I would want to connect to a PostgresSQL database via TablePlus, I would need the port open ?

TablePlus supports connections via SSH tunnel so it is not necessary to have an open port. You can connect through the tunnel instead.