Start the Instance
The last few lines of the execution of the initdb script in Listing 1 gives you the startup commands for using the
instance. The particular command is postmaster and the first line of Listing 1 gives you the simplest method of starting the
instance. Basically, it just starts the instance against a particular data
directory. Listing 4 gives you the options to this
command. I would suggest with any command you initiate, always look at the
options. They are there for a reason and sometimes have vital information
particular to your environment that could alleviate future problems. For instance,
one of the checks you might do is verify that the postmaster version is the
same as the version of the interactive terminal program (psql). You may not
think this is a great problem, but when you get into an upgrade, it will rear
its' ugly head. In addition, I notice one option of -D where I can specify the
database directory. This tells me I can have multiple postmasters running on
the same machine and each of them accessing their own database cluster on one
machine. This is where looking at the help for the postmaster command pays off.
While it might not be fully obvious for the newcomer to PostgreSQL, but if you
were to try to start two instances without options to the postmaster command,
you would get an error such as the one in Listing 6.
Now you can plainly see that if you were to supply a port option to the
postmaster command either through the direct postmaster command or through the pg_ctl
script (discussed in a bit) you would successfully get a started instance as I
did in Listing 7. Make sure you research these
options and plan for proper use of them for your environment. The second line
for starting the postmaster in Listing 1 uses a PostgreSQL
supplied control script that accepts options to start the instance. This script
will always start postmaster in the background and has options to stop,
restart, HUP, and report on the status of the postmaster. Listing 5 shows the options to this script.
Listing 4
Options for the postmaster
$ postmaster --help
postmaster is the PostgreSQL server.
Usage:
postmaster [OPTION]...
Options:
-B NBUFFERS number of shared buffers (default 64)
-c NAME=VALUE set run-time parameter
-d 1-5 debugging level
-D DATADIR database directory
-F turn fsync off
-h HOSTNAME host name or IP address to listen on
-i enable TCP/IP connections
-k DIRECTORY Unix-domain socket location
-N MAX-CONNECT maximum number of allowed connections (default 32)
-o OPTIONS pass 'OPTIONS' to each backend server
-p PORT port number to listen on (default 5432)
-S silent mode (start in background without logging output)
--help show this help, then exit
--version output version information, then exit
Developer options:
-n do not reinitialize shared memory after abnormal exit
-s send SIGSTOP to all backend servers if one dies
Please read the documentation for the complete list of run-time
configuration settings and how to set them on the command line or in
the configuration file.
Report bugs to <pgsql-bugs@postgresql.org>.
Listing 5
Options for the pg_ctl script
$ /usr/local/pgsql/bin/pg_ctl --help
pg_ctl is a utility to start, stop, restart, reload configuration files,
or report the status of a PostgreSQL server.
$silence_echo echo "done"
Usage: fi
pg_ctl start [-w] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]
pg_ctl stop [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
pg_ctl restart [-w] [-D DATADIR] [-s] [-m SHUTDOWN-MODE] [-o "OPTIONS"]
pg_ctl reload [-D DATADIR] [-s]
pg_ctl status [-D DATADIR]o "postmaster successfully shut down"
fi
Common options:
-D DATADIR location of the database storage area
-s only print errors, no informational messages
-w wait until operation completes
-W do not wait until operation completes
--help show this help, then exityway" 1>&2
--version output version information, then exit
(The default is to wait for shutdown, but not for start or restart.)
fi
If the -D option is omitted, the environment variable PGDATA is used.
fi # stop, restart, reload
Options for start or restart:
-l FILENAME write (or append) server log to FILENAME. The
use of this option is highly recommended.
-o OPTIONS command line options to pass to the postmaster
(PostgreSQL server executable)g. Trying to start postmaster anyway." 1>&2
-p PATH-TO-POSTMASTER normally not necessary
fi
Options for stop or restart:
-m SHUTDOWN-MODE may be 'smart', 'fast', or 'immediate'
if [ -z "$POSTOPTS" ];then
Shutdown modes are:= "start" ];then
smart quit after all clients have disconnectedostmaster.opts.default
fast quit directly, with proper shutdown
immediate quit without complete shutdown; will lead to recovery on restart
fi
Report bugs to <pgsql-bugs@postgresql.org>.
Listing 6
Error when trying to start a second instance without
additional parameters
[postgres@koopgate postgres]$ postmaster -D /u04/pgsql/data
Lock file "/tmp/.s.PGSQL.5432.lock" already exists.
Is another postmaster (pid 1335) using "/tmp/.s.PGSQL.5432"?
postmaster: cannot create UNIX stream port
Listing 7
Starting an instance on a different port number
[postgres@koopgate postgres]$ postmaster -D /u04/pgsql/data -p 5433
LOG: database system was shut down at 2003-09-16 07:50:05 MDT
LOG: checkpoint record is at 0/80193C
LOG: redo record is at 0/80193C; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 509; next oid: 16976
LOG: database system is ready
Accessing the Database Cluster
Remember when creating the database cluster through the
initdb command, a template database named template1 is created. In order to
verify that we do indeed have an instance up and running we can connect to the
template1 database. Listing 8 shows the quick
steps on how to connect as well as a few SQL statements to verify that we are
alive and well.
Listing 8
Connecting to a database and verifying a running instance
[postgres@koopgate data]$ psql template1
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
template1=# SELECT version();
version
-------------------------------------------------------------
PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
template1=# SELECT current_database();
current_database
------------------
template1
(1 row)
template1=# select current_user;
current_user
--------------
postgres
(1 row)
template1=# \q
Earlier in the article, I created a cluster database and
specified a new superuser and password. Listing 9
shows the sequence of commands to connect to that instance. What you now need
to know is that even though I supplied a password option to this database
cluster, Postgres defaults one of its' configuration files to not use
passwords. This is of slight concern to me but you can make the change quite
easily. What you need to do is edit the client authentication configuration
file called pg_hba.conf in your data directory for the database cluster
created, in my example '/u03/pgsql/data'. There is documentation in the file as
to the different methods of authentication. I changed my method to md5. If you
do not change the method, anyone can connect to the database cluster with
superuser privileges.
Listing 9
Connecting to database with new superuser but NO password
[postgres@koopgate data]$ psql -d template1 -U mysuperuser
Password:
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
template1=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
template1=# select current_user;
current_user
--------------
mysuperuser
(1 row)
template1=# \q
Honestly, while I am thrilled about the ease of setup and
getting an instance up and running, I am not as quite thrilled to see some of
holes in the documentation for items that would be of great concern to DBAs. I had
to search the net for some questions I had on file movement and had to display
options through the help commands. In addition, it took me about an hour to
figure out the starting of a separate instance on a second port. But then again
I should heed my own advice, look at the help screen to these commands and
search the documents a bit more thoroughly.
»
See All Articles by Columnist James Koopmann
» 
IE 7
Firefox 2.0
Comment and Contribute
