Install and setup PostgreSQL on Debian

This howto is tested on:

• Debian 5.0 Lenny
• Debian 6.0 Squeeze
• Debian 7.0 Wheezy

Installation

Install the server software and a password generation tool:

command apt-get install postgresql apg

Generate a random password for the administration account "postgres" (equivalent to the MySQL "root account"):

PGSQL_PWD="$(command apg -q -a  0 -n 1 -M NCL)"

Display the generated password, and keep it preciously:

command echo "The password for PostgreSQL postgres account is: '${PGSQL_PWD}'."

Assign the password to the PostgreSQL postgres account:

command su postgres -c "command psql -c \"ALTER USER postgres PASSWORD '${PGSQL_PWD}';\""

Create the PLPGSQL language:

command su postgres -c "command createlang 'plpgsql' 'template1'"

The PostgreSQL server is ready.

Simple administration tool

This howto recommends:

• Ease PostgreSQL server administration with pgsql-tools.
• Install PHPPgAdmin on Debian.

Allow network access to PostgreSQL databases

Warning: The following setting can be a security breach. Use it only if your server is on a local network, or if you use a strong firewall.

Open PostgreSQL to the network:

command find '/etc/postgresql' -name 'postgresql.conf' -print0 \
    | command xargs -0 -iFILE \
        sed -i -e "s/^[# ]*\\(listen_addresses[ ]*=[ ]*'\\)[^']*\\('.*\\)$/\\1*\\2/" FILE

Restart the server:

test -x /etc/init.d/postgresql-8.2 && /etc/init.d/postgresql-8.2 restart
test -x /etc/init.d/postgresql-8.3 && /etc/init.d/postgresql-8.3 restart
test -x /etc/init.d/postgresql && /etc/init.d/postgresql restart

Backup

Backup the installation with Backup Manager (see Install and setup Backup Manager on Debian).

Setup Backup Manager to backup the PostgreSQL databases. Create a custom pipe backuping the output of "pgsql_dumpall" command:

CURRENT_PIPE_NUMBER=$(command grep -e "^[\t ]*BM_PIPE_COMMAND\[" "/etc/backup-manager.conf" \
    | command sed -e 's/.*\[\([0-9]*\)\].*/\1/')
PIPE_NUMBER=$((${CURRENT_PIPE_NUMBER}+1))
command test -z "${CURRENT_PIPE_NUMBER}" && PIPE_NUMBER=0
command sed -i -e "/export BM_PIPE_COMMAND/i\\
\\
# Pipe backuping PostgreSQL databases.\\
BM_PIPE_COMMAND[${PIPE_NUMBER}]='/bin/su postgres -c /usr/bin/pg_dumpall'\\
BM_PIPE_NAME[${PIPE_NUMBER}]='postgresql'\\
BM_PIPE_FILETYPE[${PIPE_NUMBER}]='sql'\\
BM_PIPE_COMPRESS[${PIPE_NUMBER}]='bzip2'\\
" "/etc/backup-manager.conf"

Enable "Pipes" backup:

command sed -i -e 's/[#]*\(.*BM_ARCHIVE_METHOD=.*".*\)"$/\1 pipe"/' \
    "/etc/backup-manager.conf"

References

These books can help you:

• PostgreSQL (2nd Edition)
• Beginning Databases with PostgreSQL: From Novice to Professional
• PostgreSQL 9 Admin Cookbook
• PostgreSQL 9.0 High Performance
• PostGIS in action

Thanks

• Thanks to PostgreSQL developers.