PostgreSQL should be already installed on your Laboratory PCs. The
server itself lives in the
/usr/bin directory and is
postgres. But normally we run it through a wrapper
postmaster, which also lives in
the NTFS notation, this may be something like
There is also a SQL client there, called
The way to run PostgreSQL is as follows: first you have to start the server, and then you talk to it using the client. The server and the client may run on the same machine, or they may run on different machines. Both configurations are supported. The same, of course, applies to Linux and therefore to the AVIDD cluster.
But before you can run the PostgreSQL server, you have to configure the IPC daemon, this is the only part that must be performed by the system administrator. All else you can do yourself, simply as a normal Cygwin (or Windows) user.
If you administer your own PC, then the procedure for configuring
and running the IPC daemon is as follows. First you have to
become the administrator in order to install the service. Because
the service is a Cygwin program, it must be configured using
cygrunsrv utility. This utility lives in
Issue the command:
# cygrunsrv --install ipc-daemon --path /usr/bin/ipc-daemon2 \ --desc "IPC daemon for PostgreSQL"Then start the IPC daemon with
# cygrunsrv --start ipc-daemonYou should be able to see the daemon if you run
# ps -efAlternatively, you can go to
-> My Computer -> Control Panel -> Administrative Tools -> Servicesand start the service the Windows way, i.e., by right-clicking on it and then selecting
Start- this, at least, is how it works on my PC under Windows 2000.
One way or another, you should have the daemon running. Now once the daemon runs, you can switch back to your normal user account.
You have to decide where you want to keep all your PostgreSQL data. If the data base is going to be for your personal use, you should select a directory in your Cygwin home. If the data base is going to be used by your research group and served from a server, you may as well dedicate the whole drive or at least a partition to it.
In this case I am simply going to create the data base in a directory in my Cygwin home:
gustav@WOODLANDS:../gustav 19:39:57 !539 $ pwd /home/gustav gustav@WOODLANDS:../gustav 19:39:58 !540 $ mkdirhier pgsql/data gustav@WOODLANDS:../gustav 19:40:03 !541 $At this stage the directory has to be initialized for work with a PostgreSQL server. The utility that does the initialization is called
initdband it will initialize the directory not just for one data base, but for the whole data base cluster, i.e., a collection of data bases, that are going to have their data stored in this area:
gustav@WOODLANDS:../gustav 19:47:39 !551 $ initdb -D `pwd`/pgsql/data The files belonging to this database system will be owned by user "gustav". This user must also own the server process. The database cluster will be initialized with locale C. Fixing permissions on existing directory /home/gustav/pgsql/data... ok creating directory /home/gustav/pgsql/data/base... ok creating directory /home/gustav/pgsql/data/global... ok creating directory /home/gustav/pgsql/data/pg_xlog... ok creating directory /home/gustav/pgsql/data/pg_clog... ok creating template1 database in /home/gustav/pgsql/data/base/1... ok creating configuration files... ok initializing pg_shadow... ok enabling unlimited row size for system tables... ok initializing pg_depend... ok creating system views... ok loading pg_description... ok creating conversions... ok setting privileges on built-in objects... ok vacuuming database template1... ok copying template1 to template0... ok Success. You can now start the database server using: /usr/bin/postmaster -D /home/gustav/pgsql/data or /usr/bin/pg_ctl -D /home/gustav/pgsql/data -l logfile start gustav@WOODLANDS:../gustav 19:48:44 !552 $Let us have a look at what some of the files created by
gustav@WOODLANDS:../gustav 19:50:28 !561 $ cd pgsql/data gustav@WOODLANDS:../data 19:50:31 !562 $ ls -FC PG_VERSION global/ pg_hba.conf pg_xlog/ base/ pg_clog/ pg_ident.conf postgresql.conf gustav@WOODLANDS:../data 19:50:33 !563 $There are three configuration files here and then some directories in which PostgreSQL is going to store data and logs.
The file that
defines from which hosts you can connect to the data base server
and as who,
header of the file explains in detail
how to construct the entries. An example entry may look as follows:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD host biblio adams 18.104.22.168 255.255.255.0 md5which specifies that a user
adamsis allowed to connect to the data base
bibliofrom a host of address
22.214.171.124, and that the user must be authenticated using the
md5method. By default all users from the local machine are allowed connections to PostgreSQL servers serving all data bases in the cluster. But also by default PostgreSQL will ignore this file altogether and allow data base access to the data base owner only. To change this you will have to run
-ioption. But before you do this, you should edit this file very carefully indeed.
used for the user identification
based on the
ident protocol. This protocol is very popular,
but not very secure. In order to use this protocol you would have
ident in the
METHOD field in the
there is the
postgresql.conf file, which is
the PostgreSQL configuration file. It has a lot of parameter definitions,
including the port number the server is going to listen on, whether
SSL should be used for security, what is the maximum number of connections
allowed, what is the maximum number of tables per data base, what language
should be used in the data base messages (e.g., Lithuanian), and so on.
There is usually
little need to change much on this file, unless your data base is
going to be really big and accessed very frequently.
Now you can start the
postmaster directing it to the
newly created area. The
postmaster will refuse to run
if the directories it's been pointed to have not been prepared
initdb. The program does not return and cannot be
talked to from the keyboard, although it logs all it does on standard
output. So the right way to run it is to start it in the background
and have logging redirected to a file. Here is how I do it:
gustav@WOODLANDS:../gustav 20:10:47 !586 $ postmaster \ -D `pwd`/pgsql/data > pgsql/data/Nov-16-2003.log 2>&1 &  2300 gustav@WOODLANDS:../gustav 20:10:32 !587 $ jobs + Running postmaster -D `pwd`/pgsql/data >pgsql/logs/Nov-16-2003.log 2>&1 & gustav@WOODLANDS:../gustav 20:12:06 !588 $Well, it runs! We can go to the
logsdirectory and see the logging:
gustav@WOODLANDS:../gustav 20:12:06 !589 $ cd pgsql/logs gustav@WOODLANDS:../logs 20:12:42 !590 $ ls Nov-16-2003.log gustav@WOODLANDS:../logs 20:12:42 !591 $ cat Nov-16-2003.log LOG: database system was shut down at 2003-11-16 19:48:43 USEST LOG: checkpoint record is at 0/83B238 LOG: redo record is at 0/83B238; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 480; next oid: 16976 LOG: database system is ready gustav@WOODLANDS:../logs 20:12:44 !592 $and once we're there we can look at the log continuously with
tail -f. If you are going to do this, you will need another window, in which to run a client application that talks to the server.
But before we are going to connect to the server let us create an
empty data base first. This is done
with the command
createdb. The command is going to connect
to the first
postmaster it finds on the default port.
If you run more than
one server, you will have to provide
createdb with more information.
But the default will do just fine for us. So let us create a data base
gustav@WOODLANDS:../gustav 20:25:27 !595 $ createdb neighbors CREATE DATABASE gustav@WOODLANDS:../gustav 20:25:42 !596 $Well, this worked.
Now we can connect to the data base and do various things with it.
If you want to completely delete the whole data base, issue the command (with caution!):
gustav@WOODLANDS:../gustav 20:37:10 !615 $ dropdb neighbors DROP DATABASE gustav@WOODLANDS:../gustav 20:37:18 !616 $
To stop the
postmaster you can simply type
control-C, if the postmaster runs in the foreground, or you can send
it a kill, if it runs in the background:
gustav@WOODLANDS:../gustav 20:37:18 !616 $ jobs + Running postmaster -D `pwd`/pgsql/data >pgsql/logs/Nov-16-2003.log 2>&1 & gustav@WOODLANDS:../gustav 20:38:20 !617 $ kill %1 gustav@WOODLANDS:../gustav 20:39:29 !618 $ jobs + Done postmaster -D `pwd`/pgsql/data >pgsql/logs/Nov-16-2003.log 2>&1 gustav@WOODLANDS:../gustav 20:39:37 !619 $
There are many methods to connect to a PostgreSQL data base
postmaster must be running, of course). First and
foremost PostgreSQL provides a C-language interface. There is a direct
interface and an embedded SQL interface. There is also a Tcl interface,
a Java interface, and a Python interface. And
last but not
least, a SQL (it is often pronounced sea-quell, hence ``a'' rather
than ``an'' in front of it) interface and a SQL
How can you find more about PostgreSQL?
is provided with it. You should find it in the
(the number may be different) on your Cygwin or Linux system. There is
html subdirectory there and if you point your browser
index.html file in it, you'll get to view the manuals.
There should be at least six of them:
In the next section I am going to show you how to talk to
PostgreSQL using the SQL client.