Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 23, 2003

Between the Software and the Database - Page 2

By James Koopmann

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.

  postmaster [OPTION]...

  -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"
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.)
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
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
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();
 PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

template1=# SELECT current_database();
(1 row)

template1=# select current_user;
(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
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();
 PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

template1=# select current_user;
(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

PostgreSQL Archives

Latest Forum Threads
Postgresql Forum
Topic By Replies Updated
Creating a Table Where Date has to be Now OR Future Shadowayara 1 June 6th, 03:56 PM
Streaming Replication on a Single Database lbergeson 0 January 30th, 03:19 PM
Error - SQL state: 22P02 raj_db 1 December 20th, 08:30 AM
Searching in multi-dimensional array fields hdany 0 December 1st, 06:46 AM