You are here: Home / Debian GNU/Linux / Servers / Databases / Install and setup PostgreSQL on Debian

Install and setup PostgreSQL on Debian

by Pierre-Yves Landuré last modified Aug 09, 2013 01:40

PostgreSQL is a powerfull database management system. It can be seen as an free and open-source Oracle. One of its functionalities is the geolocation data storage. This howto ease the installation of PostgreSQL server 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:

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:

Thanks