Fall semester 2011
Advanced Systems Lab
PostgreSQL setup Tutorial
1 PostgreSQL setup tutorial
Download the source code for version 9.1.1 of PostgreSQL and build it on the cluster,in your
home directory,under /postgresql/.Building PostgreSQL 9.1.1 (from now on we will refer to
it as PostgreSQL) from source code,follows the normal procedure for building any Linux/Unix
applications.Read the supplied"README"and"INSTALL"documents for understanding what
you need to do and change.
do not forget to set the build destination for PostgreSQL to /postgresql/,using the
prefix option for./configure.
if you get missing library exceptions for zlib and readline you should pass the required
ags to./configure in order to build PostgreSQL without them.
Once PostgreSQL is built in your home directory,you can go ahead and play with it.We recom-
mend the following toy steps:
a) create a new directory in your home,called /test
db/,where you shall instantiate your
rst database cluster (the concept of database cluster is PostgreSQL specic,read about it
and get familiarized with it).Remember that PostgreSQL databases clusters are actually
les that respect a very well dened structure.In order to create the initial empty database
cluster,you will need to understand the initdb tool that is part of the PostgreSQL build
(it should be in /postgresql/bin/).
At this point you should have created a new database cluster for PostgreSQL.Now you can
move on and look at the created directory and le structure that represents the PostgreSQL
database cluster.You should remark,read and understand at least two of the conguration
les that reside in each PostgreSQL database cluster directory:
le postgresql.conf congures the way in which the PostgreSQL database server
will start and what options it will have.This is the actual database server congu-
ration le for the database cluster you just instantiated.Pay close attention to set-
tings such as listen
hba.conf refers to the client authentication.It controls who can connect to the
database server.Read about this le so that when need be,you are prepared to change
You should be able to explain the role of these two conguration les and of the explicitly
b) start the PostgreSQL database server over the created database cluster (/test
ctl tool that is part of the PostgreSQL build (it should be in /postgresql/bin/).
If the server does not start correctly,investigate the problem and x it.
c) stop the PostgreSQL database server,using the pg
ctl tool.If the server does not stop
correctly,investigate the problem and x it.
d) create a new database (called asldb) in the PostgreSQL database cluster you have previously
db/),using the createdb tool that is part of the PostgreSQL build (it
should be in /postgresql/bin/).The PostgreSQL server needs to be started in order to
perform this operation.
e) connect,using the PostgreSQL interactive terminal tool psql,to a PostgreSQL server in-
stance,which runs over the database cluster you created (/test
db/).Connect to the test
database created in the previous step - asldb,as well as to the database called postgres,
which exists by default in all PostgreSQL database clusters.
When connected to the postgresql database,run a SELECT SQL-query over the entire table
database.The output should be similar to:
datname | datdba | encoding |...
template1 | 10 | 6 |...
template0 | 10 | 6 |...
postgres | 10 | 6 |...
asldb | 10 | 6 |...
Once you have the PostgreSQL binaries in your home folder,and a clean populated TPC-H
database you can re-use this on all cluster machines.For your experiments,though,make sure
that the database you are running is on your/local/username directory.