| COMP9315 23T1 |
Prac Exercise 01 Setting up your PostgreSQL Server |
DBMS Implementation |
You ought to get it done by the middle of Week 2.
Notation:
In the examples below, we have used the $ sign to represent
the prompt from the Unix/Linux shell. The actual prompt may
look quite different on your computer (e.g. it may contain the computer's
hostname, or your username, or the current directory name).
In the example interactions, all of the things that the computer
displays are in this font.
The commands that you are supposed to type are in
this bold font.
Comments in the examples are introduced by '...' and are written in
this grey font;
comments do not appear on the computer screen, they simply aim to explain what
is happening.
Whenever we use the word edit, this means that you should
use your favourite text editor (e.g. vi, emacs, gedit,
etc.)
Finally, some commands use YOU as a placeholder for your CSE username (which is usually your zid). This is the same as the $USER environment variable in Linux.
PostgreSQL has three major components:
*.o files) (approx 150MB)
pg_ctl and psql) (approx 20MB)
You will not be able to fit the above components under your CSE home directory (insufficient disk quota)
The practical work for the assignments can be carried out on a special CSE server called vxdb*. You run your own PostgreSQL server on this machine and are effectively the database administrator of this server. This machine has been configured to run large numbers** of PostgreSQL servers.
* vxdb is identical to d.cse which you might remember from COMP3311.
** Note: "large numbers" is around 300. If you leave your work to the last minute, and find 400 other students all trying to run PostgreSQL on vxdb, performance will be sub-optimal. Of course, you can avoid this potential bottleneck by installing and running PostgreSQL on your home machine.
You must put your PostgreSQL source code, installed
executables and data under the /localstorage/$USER directory on vxdb.
You must run the server process on vxdb; do not
run your PostgreSQL server process on any other CSE machines; if you do, your PostgreSQL server will most likely be terminated automatically not long after it starts.
If you're doing all of this work on a laptop or desktop at home, then you can configure things however you like. You will still need folders for the same three components (source code, executables, and data), but you can place them wherever you like. PostgreSQL doesn't require any special privileges to run (at least on Unix-based systems like Linux and Mac OS X), so you do not need to create a special privileged PostgreSQL user; you can run the server as yourself.
vxdb before you submit assignments, since that's where we'll be running our tests to award your marks.
/localstorage/$USER directory on vxdb already
(e.g. from a previous database course).
If so, you can skip this section; but you might want to clean out any pgsql directory before you continue.
You can log into vxdb from a command-line (shell) window on any CSE machine (including vlab) via the command
ssh nw-syd-vxdb.cse.unsw.edu.au
If you're doing this exercise from home, you can use any ssh client, but you'll need to refer to nw-syd-vxdb.cse as d.cse:
ssh YourZID@d.cse.unsw.edu.au
You can check whether you're actually logged in to vxdb
by using the command:
hostname nw-syd-vxdb
Your home directory at CSE is directly accessible from
vxdb.
The first time you log in to vxdb, it automatically
creates a directory under /localstorage to hold your databases:
ls -al /localstorage/$USER
This directory is initially empty, but we're about to put the files for a PostgreSQL server into it.
Reminder:
If you are working from CSE make sure you are ssh'd on into vxdb.
The times below are approximate; they could double or triple depending
on your environment.
Non-experts should go straight to the detailed instructions below.
cd /localstorage/$USER tar xfj /web/cs9315/23T1/postgresql/postgresql-15.1.tar.bz2 ... creates and populates a directory called postgresql-15.1 ... cd postgresql-15.1 ./configure --prefix=/localstorage/$USER/pgsql ... produces lots of output ... make ... produces lots of output; takes approx 4-5 minutes ... make install ... produces lots of output ... cp /web/cs9315/23T1/postgresql/env /localstorage/$USER/env source /localstorage/$USER/env which initdb /localstorage/YOU/pgsql/bin/initdb initdb ... produces some output ... ls $PGDATA ... gives a listing of newly-created PostgreSQL data directory ... ... including PG_VERSION, base, global ..., postgresql.conf ... edit $PGDATA/postgresql.conf ... set listen_addresses = '' ... ... set max_connections = 10 ... ... set max_wal_senders = 4 ... ... set unix_socket_directories = 'name of PGDATA directory' ... ... if any of the above lines begins with '#', remove the '#' which pg_ctl /localstorage/YOU/pgsql/bin/pg_ctl pg_ctl start -l $PGDATA/log waiting for server to start.... done server started psql -l List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges -----------+-------+-----------+-----------+-------+------------------- postgres | YOU | LATIN1 | C | C | template0 | YOU | LATIN1 | C | C | =c/YOU : YOU=CTc/YOU template1 | YOU | LATIN1 | C | C | =c/YOU : YOU=CTc/YOU (3 rows) pg_ctl stop waiting for server to shut down.... done server stopped
The first step is to make sure that the directory /localstorage/$USER
exists. You can check this via the command:
ls -l /localstorage/$USER
If the above command says something like "No such file or directory", then you should create it using the instructions above.
Once you have a directory on the /localstorage filesystem, the next step
is to place a copy of the PostgreSQL source code under this directory.
The following commands will do this:
cd /localstorage/$USER tar xfj /web/cs9315/23T1/postgresql/postgresql-15.1.tar.bz2
This creates a subdirectory called postgresql-15.1 under your
/localstorage/$USER directory and unpacks all of the source code there.
This produces no output and will take a few moments to complete.
If you want to watch as tar unpacks the files, use
xvfj instead of xfj as the first argument to
tar.
Once you've unpacked the source code, you should change into the
newly created postgresql-15.1 directory and configure
the system so that it uses the directory /localstorage/$USER/pgsql
to hold the executables for your PostgreSQL server.
(Note that /localstorage/$USER/pgsql does not exist yet;
it will be created in the make install step).
The following commands will do the source code configuration:
cd /localstorage/$USER/postgresql-15.1 ./configure --prefix=/localstorage/$USER/pgsql
The configure command will print lots of messages about
checking for various libraries/modules/etc.
This process will take a minute, and should produce no errors.
After configuring the source code,
the next step is to build all of the programs.
Stay in the postgresql-15.1 directory
and then run the command:
make
This compiles all of the PostgreSQL source code, and takes around 4-5 minutes (depending on the load on vxdb). It will produce lots of output, but should compile everything OK. If anything goes wrong, the make process will stop partway through with an obvious error message.
Once the PostgreSQL programs are compiled, you need to install them. The following command does this:
make install
This creates the directory /localstorage/$USER/pgsql,
and copies all of the executables
(such as pg_ctl and psql) under that directory.
It will take a minute to do this,
and will produce quite a bit of output while it's doing it.
Ultimately, it should end with the message:
PostgreSQL installation complete.
You're not finished yet, however, since PostgreSQL has no directory
in which to store all of its data. You will install the data directories under /localstorage/$USER/pgsql
Before doing anything with the database, however,
you need to ensure that your Unix environment is set up correctly.
We have written a small script called env that will do this.
In this set up stage, you should copy this script to your /localstorage
directory:
cp /web/cs9315/23T1/postgresql/env /localstorage/$USER/env
The env script contains the following:
export PGDATA=/localstorage/$USER/pgsql/data export PGHOST=$PGDATA export LD_LIBRARY_PATH=/localstorage/$USER/pgsql/lib export PATH=/localstorage/$USER/pgsql/bin:$PATH alias p0="pg_ctl stop" alias p1="pg_ctl -l $PGDATA/log start"
This script sets up a number of environment variables. The critical ones are:
PGDATAPGHOST
Note that in the discussion below, we will use the string YOUR_PGDATA
to refer to that value that you assigned to PGDATA in your env
file and which has been set by source'ing the env file in your shell.
Once you have a copy of the env script and have set the values
appropriately,
you need to invoke it in every shell window where you plan to interact
with the database.
You can do this by explicitly running the following command in each
window:
source /localstorage/$USER/env
If that gets tedious, you might consider adding the above command
to your shell's startup script (e.g., ~/.bash_profile).
Once you've set up the environment, check that it's ok via the following commands:
echo $PGDATA
YOUR_PGDATA ... i.e. whatever value you set it to ...
which initdb
/localstorage/YOU/pgsql/bin/initdb
which pg_ctl
/localstorage/YOU/pgsql/bin/pg_ctl
If the system gives you different path names to the above,
then your environment is not yet set up properly.
Are you sure that you source'd your env file?
If all of the above went as expected, you are now ready to create the data directories and run the server. You can do this via the command:
initdb
... some output eventually finishing with something like ...
Success. You can now start the database server using:
pg_ctl -D YOUR_PGDATA -l logfile start
If you look at your data directory now, you should see something like:
ls $PGDATA PG_VERSION pg_hba.conf pg_replslot pg_subtrans postgresql.auto.conf base pg_ident.conf pg_serial pg_tblspc postgresql.conf global pg_logical pg_snapshots pg_twophase postmaster.opts pg_commit_ts pg_multixact pg_stat pg_wal pg_dynshmem pg_notify pg_stat_tmp pg_xact
You shouldn't start the server straight away, however, since there's one
more bit of configuration needed.
You need to edit the postgresql.conf file in the $PGDATA
directory and change the values of the following:
listen_addresses parameter to '':
this means that only Unix-domain sockets
can be used to connect to the server
(saving you fighting over TCP ports);
max_connections from 100 to 10:
this reduces the resources tied up by the server
to support those connections potentially occurring; and
unix_socket_directories parameter to the full path of your $PGDATA directory; make sure to input the literal path instead of using $USER (e.g. /localstorage/z5555555/pgsql/data):
this specifies where PostgreSQL keeps its connection sockets,
and should be the same as your $PGDATA
so psql and other clients can connect; and
max_wal_senders to e.g. 4 (or
any value less than whatever value you use for max_connections)
Once you're done, the "connections and authentications"
part of your modified postgresql.conf file
should look like (with the changes highlighted in red):
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) #port = 5432 # (change requires restart) max_connections = 10 # (change requires restart) #superuser_reserved_connections = 3 # (change requires restart) unix_socket_directories = 'YOUR_PGDATA' # comma-separated list of directories # (change requires restart) #unix_socket_group = '' # (change requires restart) #unix_socket_permissions = 0777 # begin with 0 to use octal notation # (change requires restart) .... max_wal_senders = 4
Note that it doesn't matter
that the file says port = 5432:
this value will be overridden
by whatever you set your
PGPORT environment variable to.
Note also that the 5432 also doesn't matter
because the # at the start of the line
means that it's a comment.
In the case of the lines that you are supposed to change,
make sure that you remove the #
from the start of those lines.
Everything is now ready to start your PostgreSQL server, which you can do via the command:
pg_ctl start -l $PGDATA/log
Note that PostgreSQL says "server starting", whereas it should probably say "attempting to start server".
A quick way to check whether the server is working is to run the command:
psql -l
SET
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privi
leges
-----------+-------+----------+---------+-------------+-------------
------
postgres | YOU | UTF8 | C | en_AU.UTF-8 |
template0 | YOU | UTF8 | C | en_AU.UTF-8 | =c/YOU
+
| | | | | YOU=CTc/YOU
template1 | YOU | UTF8 | C | en_AU.UTF-8 | =c/YOU
+
| | | | | YOU=CTc/YOU
(3 rows)
It is possible that the server may not start
correctly. If the server does not appear to have started,
you can check why by looking at the tail of the server log:
tail -20 $PGDATA/log
... information about what happened at server start-time ...
Note that you'll get error messages about not being able to run the statistics collector, and a warning that autovacuum was not started. These are not an issue at this stage.
If the server is running correctly,
the psql -l will give you a list of databases like the above.
If the server is not running, you'll get a message something like:
psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "YOUR_PGDATA/.s.PGSQL.5432"?
If this happens, you should check the log file to find out what went wrong. (Other things to check in case of problems are described below).
Assuming that the server is running ok, you can now use it to create and manipulate databases (see the example below). Once you've finished your session using PostgreSQL, you need to stop the server.
pg_ctl stop waiting for server to shut down.... done
If you still have a process that's using the database (e.g. a psql
process in another window), then the server won't be able to shut down.
You'll need to quit all of the processes that are accessing the database
before the above command will work.
You must shut down your server at the end of each session with PostgreSQL if you're working on the CSE workstations. Failure to do this means that the next student who uses that workstation may need to adjust their configuration (after first working out what the problem is) in order to start their server.
Once your server is up-and-running, you ought to load up the small sample database (on beers) and try a few queries on its data. This is especially important if you haven't used PostgreSQL before; you need to get used to its interactive interface.
You can set up the beer database as follows:
createdb beer psql beer -f /web/cs9315/23T1/pracs/p01/beer.dump ... around 20 lines include SET, CREATE TABLE, ALTER TABLE... psql beer SET psql (15.1) Type "help" for help. select count(*) from beers; count ------- 24 (1 row) \d ... gives a list of tables in the database ... ... explore/manipulate the database ... \q
For exploring the database with psql, there are a
collection of \d commands. You can find out more about
these via psql's \? command or by reading
the PostgreSQL manual chapter on psql.
To help with your explorations of the database, here is an
diagram of the schema.
Table/relation names are in bold; each box represents one
attribute; primary keys are underlined.
Note that all primary keys are symbolic (not numeric) in
this database.
You can look at the SQL schema from within psql.
It is very difficult to diagnose problems with software over email,
unless you give sufficient details about the problem.
An email that's as vague as My PostgreSQL server isn't
working. What should I do?
, is basically useless.
Any email about problems with software should contain details of
One way to achieve this is to copy-and-paste the last few commands and responses into your email.
But even with all of that information, there's a whole host of other environment information that's needed to be able to seriously work out why your server isn't running, that you can't put in an email. That's why it's better to come to a consultation, where we can work through the problem on a workstation (which is usually very quick).
When you use pg_ctl -l $PGDATA/log start to try to start your PostgreSQL server,
you observe something like:
pg_ctl -l $PGDATA/log start Using PostgreSQL with data directory YOUR_PGDATA waiting for server to start.................................................pg_ctl: could not start server Examine the log output. Check whether the server started ok via the command 'psql -l'. If it's not working, check /localstorage/$USER/pgsql/data/log for details.
Take the advice given to you by the command and look at the end of the log file to see if there are any clues there. You can do this via the command:
tail -20 $PGDATA/log
Sometimes you may need to look at more than the last 20 lines of the log file to find the relevant error message. Most of the error messages are self-explanatory, and you should learn what to do if any of them occurs. Some examples:
FATAL: lock file "postmaster.pid" already exists HINT: Is another postmaster (PID 31265) running in data directory "YOUR_PGDATA"? # You may already have another PostgreSQL server running # Or, the previous server may have quit without cleaning up the postmaster.pid file # Note that the server process may be running on another machine if you run your # server on the local machine rather than vxdb # If the server is running on another machine, log in there and run "pg_ctl stop" LOG: could not bind IPv4 socket: Address already in use HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for "localhost" FATAL: could not create any TCP/IP sockets # Another user is running a PostgreSQL server on this machine # Change the PGPORT value in /localstorage/$USER/env # and then reset your environment and try starting the server again FATAL: could not open relation mapping file "global/pg_filenode.map": No such file or directory FATAL: could not open relation mapping file "global/pg_filenode.map": No such file or directory FATAL: could not open relation mapping file "global/pg_filenode.map": No such file or directory FATAL: could not open relation mapping file "global/pg_filenode.map": No such file or directory # This means that there is another PostgreSQL server of yours still running # You'll need to find it # Note that the process could be running on any CSE machine where you ever # ran a PostgreSQL server, so you may need to check on multiple machines # Once you've found it, stop the server using the Unix kill command # and then reset your environment and try starting the server again
Sometimes the pg_ctl command will give a message that the server
has failed to start but you'll get no error messages at the end of the log
file, which will look something like:
LOG: database system was shut down at 2011-08-03 11:38:26 EST LOG: database system is ready to accept connections
One cause of this is having different directories for PGHOST in
the /localstorage/$USER/env file and for unix_socket_directory
in the YOUR_PGDATA/postgresql.conf file. It is critical that
these two both refer to the same directory. You can check this by running
the command:
psql -l psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/localstorage/$USER/pgsql/.s.PGSQL.5432"?
You should then check the YOUR_PGDATA/postgresql.conf file to
see whether unix_socket_directories has been set to
/localstorage/$USER/pgsql.
Note that the directory name may not be exactly the same as this; the critical
thing is that the directory be the same in both places.
When you use pg_ctl stop to try to shut down your PostgreSQL server,
you observe something like:
pg_ctl stop Using PostgreSQL with data directory YOUR_PGDATA waiting for server to shut down........................
and no done ever appears.
This is typically because you have an psql
session running in some other window (the PostgreSQL server won't shut
down until all clients have disconnected from the server).
The way to fix this is to find the psql session and end it.
If you can find the window where it's running, simply use \q
to quit from psql.
If you can't find the window, or it's running from a different machine
(e.g. you're in the lab and find that you left a psql running
at home), then use ps to find the process id of the
psql session and stop it using the Linux kill
command.
Occasionally, you'll find that your PostgreSQL server was not shut down cleanly the last time you used it and you cannot re-start it next time you try to use it. The symptoms are:
Using PostgreSQL with data directory YOUR_PGDATA pg_ctl: another server might be running; trying to start server anyway pg_ctl: could not start server Examine the log output. Check whether the server started ok via the command 'psql -l'. If it's not working, check /localstorage/$USER/pgsql/log for details.
If you actually go and check the log file, you'll probably find, right at the end, something like:
tail -2 /localstorage/$USER/pgsql/log FATAL: lock file "postmaster.pid" already exists HINT: Is another postmaster (PID NNNN) running in data directory "YOUR_PGDATA"?
where NNNN is a process number.
There are two possible causes for this: the server is already running
or the server did not terminate properly after the last time you used it.
You can check whether the server is currently running by the command
psql -l. If that gives you a list of your databases, then
you simply forgot to shut the server down last time you used it and it's
ready for you to use again. If psql -l tells you that
there's no server running, then you'll need to do some cleaning up
before you can restart the server ...
When the PostgreSQL server is run, it keeps a record of the Unix process that it's running as in a file called:
YOUR_PGDATA/postmaster.pid
Normally when your PostgreSQL server process terminates (e.g. via
pg_ctl stop), this file will be removed. If your PostgreSQL
server stops, and this file persists, then pgs becomes
confused and thinks that there is still a PostgreSQL server running
even though there isn't.
rm YOUR_PGDATA/postmaster.pid
You should also clean up the socket files used by the PostgreSQL server. You can do this via the command:
rm YOUR_PGDATA/.s.PGSQL.*
Once you've cleaned all of this up, then the pg_ctl
command ought to allow you to start your PostgreSQL server ok.