How to Install PostgreSQL 9.3 on Debian 7 Wheezy

While MySQL considered as “The world’s most popular open source database”, PostgreSQL rightly considered as “the world’s most advanced open source database”.

PostgreSQL architecture is based on CLUSTERS (a group of databases managed by a common service), each containing one or more DATABASES, each containing one or more SCHEMATA.

Generally speaking, SCHEMA in PostgreSQL is the analog of MySQL database.

Excellent documentation is available here.

The following tutorial is based on a Debian 7 base system with systemd, Apache, php5. If you need more information about Debian Wheezy setup, you may read this and this article.

php5 PostgreSQL extension was also installed:

apt-get install php5-pgsql

Setup current Debian version

Version 9.1 is the current PostgreSQL version in Debian Wheezy (the time of writing this article). Installation is simple:

apt-get install postgresql

returns something like this

The following extra packages will be installed:
  postgresql-9.1 postgresql-client-9.1 postgresql-client-common postgresql-common

Setup latest PostgreSQL version on Debian

In case you need to install a newer (or the latest) PostgreSQL version in Debian, you have to use PostgreSQL Apt Repository. Detailed instructions are available here.

As root:

Add the PostgreSQL Apt Repository

create a Debian sources list file (e.g. pgdg.list)
```
nano /etc/apt/sources.list.d/pgdg.list
```
add the following line and save the file
```
deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main
```

Import the repository signing key

wget https://www.postgresql.org/media/keys/ACCC4CF8.asc
apt-key add ACCC4CF8.asc

Update the package lists
```
apt-get update
```
Finally, install PostgreSQL as usual
```
apt-get install postgresql
```
returns something like this
```
The following extra packages will be installed:
  libpq5 pgdg-keyring postgresql-9.3 postgresql-client-9.3 postgresql-client-common postgresql-common
```
Just press Y to continue.
This will install PostgreSQL 9.3.3 (latest PostgreSQL version at this time). You may install another version (e.g. 9.2), using:
```
apt-get install postgresql-9.2
```

Which method Should You Choose?

The answer is not easy:

Using the Debian default version of PostgreSQL is probably the safest method.

But, according to PostgreSQL

This repository will integrate with your normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL.

I use the latest version of PostgreSQL adding the PostgreSQL Apt Repository. I had no problems with Debian updates so far.

Additional Supplied Modules

If you need Additional Supplied Modules, install the contrib Debian package:

apt-get install postgresql-contrib

Usually, I use the following extensions:

fuzzystrmatch which offers functions like soundex and levenshtein
unaccent which offers function unaccent, useful for accent insensitive search.
citext which offers citext type, useful for case insensitive search.

More details here.

Enable Postgres extension

To enable a Postgres extension, e.g. fuzzystrmatch

su -l root
su - postgres
psql -U postgres -d mydb

mydb=# CREATE EXTENSION fuzzystrmatch;
mydb=# q

Then RESTART Postgres.

First Steps after Installation

After installation

postgresql client is available using: psql (more here).
default system user: Debian has created system user postgres. CAUTION: Do not set or change password for system user postgres
default postgresql user: is superuser postgres CAUTION: password must be set for database user postgres (see below)
default cluster: main
default database: template1
default schema: public

Set password to PostgreSQL user postgres

su -l root
su - postgres
psql

then

psql (9.3.3)
Type "help" for help.

postgres=# password postgres
Enter new password:
Enter it again:
postgres=#

Exit psql console using

postgres=# q

Remote connections

If remote connections needed

(1) postgresql.conf

su -l root
su - postgres
nano /etc/postgresql/9.3/main/postgresql.conf

change this

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

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

to something like

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

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

where 192.168.1.200=SERVER_IP

CAUTION: AVOID listen_addresses = '*' for security reasons.

(2) pg_hba.conf

su -l root
su - postgres
nano /etc/postgresql/9.3/main/pg_hba.conf

add

host    all         all         192.168.1.200/24          md5
host    all         all         192.168.1.51/24          md5

where 192.168.1.200=SERVER_IP and 192.168.1.51=MANAGEMENT_IP

(3) iptables

if iptables firewall is active

# Allow connection to PostgreSQL (e.g.) from 10.8.4.10
iptables -A INPUT -p tcp -m tcp -s 10.8.4.10 --dport 5432 -j ACCEPT

(4) restart postgresql server

(see below)

File locations

In Debian, multiple clusters can exist in the same server, even from different PostgreSQL versions.

Configuration files: /etc/postgresql/[version]/[cluster]/
Binaries: /usr/lib/postgresql/[version]
Data files: /var/lib/postgresql/[version]/[cluster]
Log file: /var/log/postgresql/postgresql-[version]-[cluster].log

So, for main cluster

Configuration files: /etc/postgresql/9.3/main/
Binaries: /usr/lib/postgresql/9.3
Data files: /var/lib/postgresql/9.3/main
Log file: /var/log/postgresql/postgresql-9.3-main.log

List existing database clusters

From command line

pg_lsclusters

returns

Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log

Start – stop – restart PostgreSQL on Debian

If you use systemd (recommended)

systemctl restart postgresql.service

else

service postgresql restart

How to log PostgreSQL queries

As user postgres, modify section ERROR REPORTING AND LOGGING of /etc/postgresql/9.3/main/postgresql.conf as follows and RESTART Postgres

ATTENTION:

Keep a backup of /etc/postgresql/9.3/main/postgresql.conf
DO NOT APPLY these settings in PRODUCTION servers (server load could be increased significantly)

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

log_destination = 'stderr'              # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on          # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log'                # directory where log files are written,
                                        # can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
#log_truncate_on_rotation = off         # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
#log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
#log_rotation_size = 10MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# This is only relevant when logging to eventlog (win32):
#event_source = 'PostgreSQL'

# - When to Log -

#client_min_messages = notice           # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   log
                                        #   notice
                                        #   warning
                                        #   error

#log_min_messages = warning             # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic

#log_min_error_statement = error        # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic (effectively off)

log_min_duration_statement = 0  # -1 is disabled, 0 logs all statements
                                        # and their durations, > 0 logs only
                                        # statements running at least this number
                                        # of milliseconds


# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default          # terse, default, or verbose messages
#log_hostname = off
log_line_prefix = '%t '                 # special values:
                                        #   %a = application name
                                        #   %u = user name
                                        #   %d = database name
                                        #   %r = remote host and port
                                        #   %h = remote host
                                        #   %p = process ID
                                        #   %t = timestamp without milliseconds
                                        #   %m = timestamp with milliseconds
                                        #   %i = command tag
                                        #   %e = SQL state
                                        #   %c = session ID
                                        #   %l = session line number
                                        #   %s = session start timestamp
                                        #   %v = virtual transaction ID
                                        #   %x = transaction ID (0 if none)
                                        #   %q = stop here in non-session
                                        #        processes
                                        #   %% = '%'
                                        # e.g. '<%u%%%d> '
#log_lock_waits = off                   # log lock waits >= deadlock_timeout
log_statement = 'all'                   # none, ddl, mod, all
#log_temp_files = -1                    # log temporary files equal or larger
                                        # than the specified size in kilobytes;
                                        # -1 disables, 0 logs all temp files
log_timezone = 'localtime'

Monitor log file

You can monitor log using a command like

tail -f /var/lib/postgresql/9.3/main/pg_log/postgresql-2014-06-28_131500.log

More information

See documentation.

Upgrade PostgreSQL on Debian

According to PostgreSQL Versioning policy:

A major release is numbered by increasing either the first or second part of the version number, e.g. 9.1 to 9.2. Minor releases are numbered by increasing the third part of the version number, e.g. 9.2.3 to 9.2.4

Upgrading to a minor version is done through the upgrade process of Debian.

CAUTION: For major versions, the process is much more complex, as it involves schema changes. Detailed information is available here.

Christos Pontikis

Entrepreneur | Full-stack developer | Founder of MediSign Ltd. I have over 15 years of professional experience designing and developing web applications. I am also very experienced in managing (web) projects.