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 May 05, 2014 12:47 PM

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

Kamala says:
Feb 11, 2013 01:24 PM
I used pgFouine with PostgreSQL 8.3 under Debian without pbmolers. For my Postgresql 8.4 under Ubuntu 10.10 i changed redirect_stderr = onto logging_collector = onother settings are: log_destination = stderr' logging_collector = on log_duration = off log_statement = none' log_min_duration_statement = 0When i use any SQL statement e.g.:postgres psql -ltit logs the duration together with the stament on stderr. In 8.3 this was only in the logfile.LOG: duration: 1.672 ms statement: SELECT d.datname as Name , pg_catalog.pg_get_userbyid(d.datdba) as Owner , pg_catalog.pg_encoding_to_char(d.encoding) as Encoding , d.datcollate as Collation , d.datctype as Ctype , pg_catalog.array_to_string(d.datacl, E'\n') AS Access privileges FROM pg_catalog.pg_database dORDER BY 1;The problem is that it appears in psql interactive mode during tab completion:postgres=# select * from pg_LOG: duration: 4.161 ms statement: SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ( r', S', v') AND substring(pg_catalog.quote_ident(c.relname),1,3)='pg_' AND pg_catalog.pg_table_is_visible(c.oid)UNIONSELECT pg_catalog.quote_ident(n.nspname) || .' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || .',1,3)='pg_' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || .',1,3) = substring( pg_',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1UNIONSELECT pg_catalog.quote_ident(n.nspname) || .' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ( r', S', v') AND substring(pg_catalog.quote_ident(n.nspname) || .' || pg_catalog.quote_ident(c.relname),1,3)='pg_' AND substring(pg_catalog.quote_ident(n.nspname) || .',1,3) = substring( pg_',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || .',1,3) = substring( pg_',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1LIMIT 1000Even using syslog as you described did not fix the problem.Can you help me?
Add comment

You can add a comment by filling out the form below. Plain text formatting. Web and email addresses are transformed into clickable links. Comments are moderated.