One of the first things I realized I needed when I started using PostgreSQL was a way to automate backups. Originally I created a script that backed up the databases I specified but later I realized I didn't want to have to remember to add new databases to the list. Using Google I found a generic script, after some custom tweaks for FreeBSD I had the script I wanted. 

#!/bin/sh
# Location of binaries
bin="/usr/local/bin"
# Location of the backup logfile.
logfile="/data/backup/postgres/postgres.log"
# Location to place backups.
backup_dir="/data/backup/postgres"
username="pgsql"
database="template1"
touch $logfile
timeslot=`date +%H-%M`
databases=`$bin/psql -h localhost -d $database -U $username -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`

for i in $databases; do
        echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile
        $bin/vacuumdb -z -h localhost -U $username $i >/dev/null 2>&1
        j="60"
        while [ $j -ge 0 ]
        do
                temp0=`expr $j - 1`
                temp1=$j
                j=`expr $j - 1`
                if [ $temp0 -lt 0 ]
                then
                        suffix0=""
                        suffix1=".0"
                else
                        suffix0=".$temp0"
                        suffix1=".$temp1"
                fi
                if [ -f $backup_dir/postgresql-$i-database.gz$suffix0 ]
                then
                        echo "Renaming postgresql-$i-database.gz$suffix0 to postgresql-$i-database.gz$suffix1"
                        mv $backup_dir/postgresql-$i-database.gz$suffix0 $backup_dir/postgresql-$i-database.gz$suffix1
                fi
        done
        if [ $i != 'template0' ]
        then
                $bin/pg_dump -U $username $i -h 127.0.0.1 | gzip > "$backup_dir/postgresql-$i-database.gz"
        fi
done

This script creates 60 backups and when used in conjunction with a cron job you can make that weekly, daily, hourly, etc. I use this to create a daily backup. Now for some people 60 is probably more than they want to store. But that can be changed by changing the start value of the variable j. To make this work for your postgresql database you will most likely want to change the backup_dir and logfile variables. Once completed add a cron job and your done.

0 0 * * * /root/dbBackup.sh >> /dev/null 2>&1

This cron job will run daily at 12:00am and automatically backup all databases with the exception of the template0 database as I have yet to find a way to back this schema up.

 

 

By default PostgreSQL installed configuration only allows connections from the localhost. This is a good thing because the average user most likely has a LAMP style setup where all services are contained within the same server and this will limit its vulnerability to the outside.

The install I am using will need to support multiple connections from many different locations simultaneously. For example we have a couple ASP.NET applications that will access different databases on the server as well as a few Network appliances, for example snort, that will also access databases on the server.

First lets setup the server to start listening on the external interfaces. To do this we will need to access the posgres data directory. As I explained before I moved my data directory from the FreeBSD default location (/usr/local/pgsql/data) to /data/pgsql. In the postgres data directory you will need to edit the file postgresql.conf. Find the setting for listen_addresses and change it from localhost to * or a specific IP address or list of ip addresses.

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 80                    # (change requires restart)

You will also notice that I changed the max_connections from 40 to 80, you could also change the port that postgres listens on if you chose but for this I will leave the default.

Next we need to edit the pg_hba.conf file to allow connections from an IP space other than the localhost (127.0.0.1). There may be more secure ways to setup this but between the firewall, ip restrictions, user accounts and md5 I feel pretty confident in my setup (until someone tells me otherwise). Anyway say I have a .NET application on my windows server(10.3.0.100), a database named testdb and the user i will be connection to the database with is named testuser if I wanted to set postgresql to allow this connection I would add the following line to the pg_hba.conf.

host    testdb       testuser 10.3.0.100/32   md5

Once I have made my changes to the configuration files I need to restart postgresql. To do this I run /usr/local/etc/rc.d/postgresql restart and in a few seconds I should be able to test the connection in my .NET application.

I my previous post I talked about rebuilding the kernel on a AMD64 machine with dual processors, what I didn't know at the time is the default installed kernel enables SMP but the GENERIC kernel profile does not. Enabling SMP in the FreeBSD kernel is quite easy all thats necessary is to add the SMP option prior to rebuilding the kernel.

options         SMP                     # Enable SMP kernel

Once this is done just follow the same steps I have described in previous posts. After you have completed the kernel install you can verify that the operating system sees all the processors by running one of the following commands.

[root@test] [/root]> cat /var/log/messages | grep cpu
Jan 30 10:33:11 test kernel: cpu0: <ACPI CPU> on acpi0
Jan 31 07:48:42 test kernel: cpu0 (BSP): APIC ID:  0
Jan 31 07:48:42 test kernel: cpu1 (AP): APIC ID:  1
Jan 31 07:48:42 test kernel: cpu0: <ACPI CPU> on acpi0
Jan 31 07:48:42 test kernel: cpu1: <ACPI CPU> on acpi0

[root@test] [/root]> ps -aux | grep cpu
root      10 99.0  0.0     0    16  ??  RL    7:48AM 783:07.85 [idle: cpu1]
root      11 99.0  0.0     0    16  ??  RL    7:48AM 781:42.93 [idle: cpu0]
root      42  0.0  0.0     0    16  ??  DL    7:48AM   0:00.60 [schedcpu]
kkikta 16847  0.0  0.1  5920  1256  p0  S+    8:52PM   0:00.00 grep cpu

[root@test] [/root]> top -n
last pid: 16900;  load averages:  0.00,  0.00,  0.00  up 0+13:05:43    20:53:44
27 processes:  1 running, 26 sleeping

Mem: 12M Active, 13M Inact, 31M Wired, 20M Buf, 1912M Free
Swap: 512M Total, 512M Free


  PID USERNAME  THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU COMMAND
  635 pgsql       1   8    0 53580K  5776K nanslp 0   0:02  0.00% postgres
  624 pgsql       1  96    0 53580K  5480K select 1   0:02  0.00% postgres

As you probably noticed I colored the text in red that distinguishes between processors.