# CWDB

<div class="page group" id="bkmrk-dns-ip-address-loc-o"><div class="level1"><div class="table sectionedit2"><table class="inline"><thead><tr class="row0"><th class="col0"><abbr title="Domain Name System">DNS</abbr></th><th class="col1">IP Address</th><th class="col2">Loc</th><th class="col3"><abbr title="Operating System">OS</abbr></th><th class="col4">Ver</th><th class="col5">Services</th></tr></thead><tbody><tr class="row1"><td class="col0">cwdb</td><td class="col1">172.16.1.128</td><td class="col2">Internal VM</td><td class="col3">SLES</td><td class="col4">12</td><td class="col5">postgresql</td></tr></tbody></table>

</div></div></div>## Installation

### SLE Modules

<div class="page group" id="bkmrk-software-development"><div class="level3">- <div class="li">Software Development Kit</div>
- <div class="li">Web and Scripting</div>

</div></div>### Installed Packages

<div class="page group" id="bkmrk-postgresql"><div class="level3">- <div class="li">`postgresql`</div>

</div></div>### Users

<div class="page group" id="bkmrk-postgres%C2%A0%28created-wh"><div class="level3">- <div class="li">`postgres` (created when installing the `postgresql` package)</div>

</div></div>## 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.

<div class="page group" id="bkmrk-you-will-need-to-tel"><div class="level2">- <div class="li">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</div>
- <div class="li">When you make changes to the custom rules, you will need to run the `SuSEfirewall2` command as `root` (pay attention to any error messages)</div>

</div></div>### Custom Rules File

Add the rules within the `fw_custom_before_masq()` area

<div class="page group" id="bkmrk-susefirewall2-custom"><div class="level3"><dl class="code"><dt>[SuSEfirewall2-custom](https://kb.mlc-wels.edu/_export/code/disaster/servers/cwdb?codeblock=4 "Download Snippet")</dt><dd>```
# 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
```

</dd></dl></div></div>## Backup

WAL archives and `/data` directory backups are housed on the [archive](https://kb.mlc-wels.edu/disaster/servers/cwdb-archive "disaster:servers:cwdb-archive") server.

<div class="page group" id="bkmrk-pg_binary_backup.sh-"><div class="level2"><dl class="code"><dt>[pg\_binary\_backup.sh](https://kb.mlc-wels.edu/_export/code/disaster/servers/cwdb?codeblock=5 "Download Snippet")</dt><dd>```
#!/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"
```

</dd></dl></div></div>