Advanced Systems Lab PostgreSQL setup Tutorial

arizonahoopleData Management

Nov 28, 2012 (4 years and 7 months ago)

219 views

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.
Remarks:
 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 specic,read about it
and get familiarized with it).Remember that PostgreSQL databases clusters are actually
les that respect a very well dened 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 conguration
les that reside in each PostgreSQL database cluster directory:
 le postgresql.conf congures the way in which the PostgreSQL database server
will start and what options it will have.This is the actual database server congu-
ration le for the database cluster you just instantiated.Pay close attention to set-
tings such as listen
address,port,max
connections,shared
buffers,work
mem
and unix
socket
directory.
 le pg
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
it.
You should be able to explain the role of these two conguration les and of the explicitly
mentioned parameters!
b) start the PostgreSQL database server over the created database cluster (/test
db/),using
the pg
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
created (/test
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
called pg
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.
2