====== List of netcup Servers for PostgreSQL / v5 ======


  * 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:

<code>
openvpn --config config.ovpn --log openvpn.log  --daemon
</code>
As of now, both machines psql0{1,2} each have a certificate and are able to access the VPN.


===== SSH =====

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

<code>
schnauzer0{1,2}
</code>
depending on the machine.

The protection is minimal, since the keys are cached with

<code>
ssh-add
</code>
manually and then are stored in plaintext in memory. The password only prevents that stolen keys are too easily used for logins.


===== Ports =====

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


===== Connection Strings =====


  * live:
<code>
postgresql://docker:live_sfHjZ0i6GYKc2hIh@v220201062212128885.bestsrv.de:5401/products
</code>
  * prelive:
<code>
postgresql://docker:prelive_wLHUeLUk9tbcTLKl@v220201062212128886.quicksrv.de:5401/products
</code>


===== Data =====

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


===== Monitoring Resources =====

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:

<code>
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
</code>
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


===== Docker =====

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


==== Known Issues ====

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


<code>
tls-version-min 1.0
</code>

=== shared memory to small ===

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%%''