Skip to main content

CWDB

DNS IP Address Loc OS Ver Services
cwdb 172.16.1.128 Internal VM SLES 12 postgresql

Installation

SLE Modules

  • Software Development Kit
  • Web and Scripting

Installed Packages

  • postgresql

Users

  • postgres (created when installing the postgresql package)

Useful Incantations

Managing PostgreSQL Process

rcpostgresql start|stop|restart|reload

Load Firewall Rules

SuSEfirewall2

Cron Jobs

Root

Copies custom firewall rules into area where normal backups can grab a copy and changes the ownership so that it can be copied over easily.

0 0 * * * cp bin/SuSEfirewall2-custom /var/lib/pgsql/data/ | chown postgres:postgres /var/lib/pgsql/data/SuSEfirewall2-custom

Postgres

Runs the backup script that copies the /data directory via rsync.

15 3 * * * /var/lib/pgsql/bin/pg_binary_backup.sh >/dev/null 2>&1

Firewall

There is a need for custom rules for the firewall to handle PostgreSQL and SSH connections. They are stored in /root/bin/SuSEfirewall2-custom. You can find a copy of this file within the binary backup of the /data directory for cwdb stored on archive.

  • You will need to tell SUSE to load these custom rules by going to YaST > System > /etc/sysconfig Editor > Network > Firewall > SuSEfirewall2 > FW_CUSTOMRULES and then adding /root/bin/SuSEfirewall2-custom into the settings
  • When you make changes to the custom rules, you will need to run the SuSEfirewall2 command as root (pay attention to any error messages)

Custom Rules File

Add the rules within the fw_custom_before_masq() area

SuSEfirewall2-custom
# list each host IP address on a new line
SSH_HOSTS="
172.16.0.1
"
for SSH_HOST in $SSH_HOSTS; do
iptables -A input_ext -p tcp -s $SSH_HOST --dport 22 -j ACCEPT
done
 
# list each host IP address on a new line
PG_HOSTS="
172.16.0.1
"
for PG_HOST in $PG_HOSTS; do
iptables -A input_ext -p tcp -s $PG_HOST --dport 5432 -j ACCEPT
done

Backup

WAL archives and /data directory backups are housed on the archive server.

pg_binary_backup.sh
#!/bin/bash
 
CURRENT_DATE=$(date +%y-%m-%d)
DATA_PATH=/var/lib/pgsql/data/
ARCHIVE_DATA_PATH=/home/archive/cwdb/data/$CURRENT_DATE
 
psql -c "select pg_start_backup('backup for $CURRENT_DATE')"
rsync -cva --inplace --exclude=*pg_xlog* $DATA_PATH archive@172.16.1.130:$ARCHIVE_DATA_PATH
psql -c "select pg_stop_backup(), current_timestamp"
Back to top