Setup PostgreSQL

This post is part of the series GIS

I’d like to show how to setup a PostgreSQL database cluster and its basic usage.

Setup a virtual Linux machine

First, setup a Linux VM. I’m using a Debian 8 here.

Basic Setup

Set your locale:

export LANGUAGE=en_US.UTF-8
export LANG=en_US.UTF-8
export LC_ALL=en_US.UTF-8
locale-gen en_US.UTF-8
dpkg-reconfigure locales


echo "Europe/Berlin" >/etc/timezone
dpkg-reconfigure --frontend noninteractive tzdata

Update Debian packages:

apt-get update

Install some Linux tools, like sudo and hovered for a fast pseudo-number generator within your VM:

apt-get -y install sudo haveged

Install PostgreSQL

apt-get -y install postgresql-9.4

Setup a PostgreSQL Database Cluster

A first cluster was setup by installing the package, you can view all clusters with pg_lsclusters:

root@artofcoding6:~# pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

It’s reachable on PostgreSQL’s standard port 5432. You can create your own cluster using pg_createcluster:

postgres@artofcoding6:~$ pg_createcluster 9.4 mycluster
Creating new cluster 9.4/mycluster ...
  config /etc/postgresql/9.4/mycluster
  data   /var/lib/postgresql/9.4/mycluster
  locale en_US.UTF-8
Flags of /var/lib/postgresql/9.4/mycluster set as -------------e-C
  port   5433

The new cluster got the next free port available 5433:

root@artofcoding6:~# pg_lsclusters 
Ver Cluster Port Status Owner    Data directory                    Log file
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main      /var/log/postgresql/postgresql-9.4-main.log
9.4 main    5433 online postgres /var/lib/postgresql/9.4/mycluster /var/log/postgresql/postgresql-9.4-mycluster.log

which is important when you want to connect to the new cluster, you have to supply the port to psql using the -P option:

root@artofcoding6:~# su - postgres
postgres@artofcoding6:~$ psql -P 5433

pg_createcluster has a variety of options, so you can control where the cluster’s data is stored, who is the owner or whether the database is started automatically or not.

Database Cluster Configuration

You can list or modify configuration values using pg_conftool:

postgres@artofcoding6:~$ pg_conftool 9.4 main show all
data_directory = '/var/lib/postgresql/9.4/main'
datestyle = 'iso, mdy'
default_text_search_config = pg_catalog.english
dynamic_shared_memory_type = posix
external_pid_file = '/var/run/postgresql/'
hba_file = '/etc/postgresql/9.4/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.4/main/pg_ident.conf'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
log_line_prefix = '%t [%p-%l] %q%u@%d '
log_timezone = localtime
max_connections = 100
port = 5432
shared_buffers = 128MB
ssl = true
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
stats_temp_directory = '/var/run/postgresql/9.4-main.pg_stat_tmp'
timezone = localtime
unix_socket_directories = '/var/run/postgresql'

Connecting to Administrative Database “postgres”

root@artofcoding6:~# su - postgres
postgres@artofcoding6:~$ psql
psql (9.4.4)
Type "help" for help.


Installing AdminPack

For more remote administration options (see adminpack Extended Features) through pgAdmin III or other tools, install the extension adminpack:

postgres@artofcoding6:~$ psql
postgres=# CREATE EXTENSION adminpack;

Your Own Database

Create your own database with createdb. The user issuing this command is automatically the database owner, -e shows SQL commands:

postgres@artofcoding6:~$ createdb -e mydatabase

You can easily connect to your new database by supplying psql with the its name:

postgres@artofcoding6:~$ psql mydatabase
psql (9.4.4)
Type "help" for help.


Dropping a database is also easy:

postgres@artofcoding6:~$ dropdb mydatabase

PostgreSQL Command Line Tools

postgres@artofcoding6:~$ pg_isready
/var/run/postgresql:5432 - accepting connections

pg_top shows informations about the system and PostgreSQL’s connections:

root@artofcoding6:~# apt-get -y install pgtop
postgres@artofcoding6:~$ pg_top

last pid:   469;  load avg:  0.00,  0.01,  0.05;       up 3+04:15:53                                                                         14:32:08
2 processes: 1 running, 1 sleeping
CPU states:  1.0% user,  0.0% nice,  0.5% system, 98.6% idle,  0.0% iowait
Memory: 1450M used, 4536M free, 57M buffers, 1286M cached
DB activity:   6 tps,  0 rollbs/s,   0 buffer r/s, 100 hit%,   1335 row r/s,    0 row w/s 
DB I/O:     0 reads/s,     0 KB/s,     0 writes/s,     0 KB/s  
DB disk: 9.3 GB total, 5.0 GB free (46% used)
Swap: 467M free

  470 postgres  20    0  229M   13M run     0:00  0.00%  0.00% postgres: postgres postgres [local] idle                                              
  405 postgres  20    0  223M   12M sleep   0:00  0.00%  0.00% postgres: postgres postgres ::1(51141) idle                                           


This entry was posted in Databases and tagged , . Bookmark the permalink.