• v220201062212128885.bestsrv.de: psql02 [live]
• v220201062212128886.quicksrv.de: psql01[pre-live]

The setup was done identically to the frontend servers with a helper script.

But in contrast to the front-ends, the VPN certificate is located in /root/.cert

The VPN command is as follows:

openvpn --config config.ovpn --log openvpn.log  --daemon

As of now, both machines psql0{1,2} each have a certificate and are able to access the VPN.

For minimal security, the generated keys are protected with a simple password:

schnauzer0{1,2}

depending on the machine.

The protection is minimal, since the keys are cached with

ssh-add

manually and then are stored in plaintext in memory. The password only prevents that stolen keys are too easily used for logins.

We have two back-end servers, one for live, one for pre-live, both use port 5401.

• live:

postgresql://docker:live_sfHjZ0i6GYKc2hIh@v220201062212128885.bestsrv.de:5401/products

• prelive:

postgresql://docker:prelive_wLHUeLUk9tbcTLKl@v220201062212128886.quicksrv.de:5401/products

All the database files are mounted into the docker and located at /home/picalike/psql

To keep track of the load + memory on the servers, a simple script 'proc_stat.py' [1] is used. It is triggered via crontab to periodically track the resource usage. The output looks like this:

cat v5/logs/psql_usage.log 
[2020-12-14 15:35] (postgres) rss=52462 Pages, vss=8479 MB | load average: 4.74, 4.79, 4.79

The load is the same as in htop / uptime and vss means the (virtual) allocated memory. At psql01/psql02 the script also contains an alerting that sends a notification to the slack channel.

The threshold is set to 10, but this is arbitrary and probably needs adjustments.

Since the pid is hardcoded, the cron needs to be manually adjusted after a docker restart

[1] /home/picalike/bin/proc_stat.py

The psql image is deployed on psql01/02 and can be started/restarted there.

The openvpn version needs a modification in the config file to work:
In the file config.opvn:

tls-version-min 1.0

ERROR: could not resize shared memory segment “/PostgreSQL.71071463” to 50438144 bytes: No space left on device
SOLUTION: add –shm-size 12G (or another than default 64MB) when executing docker run