2.4. Starting and Stopping PostgreSQL

2.4.1. Using pg_ctl

The pg_ctl script is provided with PostgreSQL as a general control application. With it, you can start, stop, restart, or check on the status of PostgreSQL.

Here is the syntax for pg_ctl, from the -  -help option:

	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]
	pg_ctl kill    SIGNALNAME PID

The following actions may be passed to pg_ctl:

The following options may be passed to pg_ctl:

Many of the run-time configuration options for postmaster can be found in the postgresql.conf file, which is stored in the PostgreSQL data path (e.g., /usr/local/pgsql/data). The options in this file are of a more technical nature, and should not be modified unless you are sure you understand their purpose.

[postgres@booktown ~]$ pg_ctl -D /usr/local/pgsql/data start
LOG:  database system was shut down at 2005-08-22 11:36:51 PDT
LOG:  checkpoint record is at 0/33FB28
LOG:  redo record is at 0/33FB28; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 565; next OID: 10791
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system is ready
LOG:  transaction ID wrap limit is 2147484144, limited by database "postgres"

[postgres@booktown ~]$

2.4.1.2. Stopping PostgreSQL with pg_ctl

The PostgreSQL postmaster backend can be stopped in the same fashion that it is started—by passing the stop argument to pg_ctl. The application pg_ctl checks for the running postmaster process, and, if the stop command was executed by the user who owns the running processes (e.g., postgres) the server is shut down.

There are three ways in which PostgreSQL can shut down the backend: smart, fast, and immediate. These arguments are passed to pg_ctl following the -m flag, to indicate the desired shutdown mode.

A smart shutdown (the default) causes PostgreSQL to wait for all clients to first cancel their connections before shutting down. A fast shutdown causes PostgreSQL to simply shut down through its normal routine, without checking client status. An immediate shutdown bypasses the normal shutdown procedure, and will require the system to go through a recovery mode when restarted.

Example 2-13 calls the pg_ctl script to stop the postmaster process in fast mode. The postmaster backend will not wait for any client connections to disconnect before shutting down.

Example 2-13. Stopping PostgreSQL with pg_ctl

[postgres@booktown ~]$ pg_ctl -D /usr/local/pgsql/data stop -m fast
waiting for postmaster to shut down....LOG:  received fast shutdown request
LOG:  shutting down
LOG:  database system is shut down
done
postmaster stopped

The smart shutdown is equivalent to a kill -TERM on the running postmaster process, while fast is equivalent to a kill -INT, and immediate is equivalent to a kill -QUIT.

[postgres@booktown ~]$ pg_ctl -D /usr/local/pgsql/data restart
waiting for postmaster to shut down....LOG:  received smart shutdown request
LOG:  shutting down
LOG:  database system is shut down
done
postmaster stopped
postmaster starting
[jd@jd pgsqldev]$ LOG:  database system was shut down at 2005-08-22 11:38:07 PDT
LOG:  checkpoint record is at 0/33FBB0
LOG:  redo record is at 0/33FBB0; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 565; next OID: 10791
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system is ready
LOG:  transaction ID wrap limit is 2147484144, limited by database "postgres"
[postgres@booktown ~]$

[postgres@booktown ~]$ pg_ctl -D /usr/local/pgsql/data status
pg_ctl: postmaster is running (PID: 26522)
/tmp/pgsqldev/bin/postgres
[postgres@booktown ~]$

2.4.2. Configuring the PostgreSQL SysV Script

The SysV script will allow the graceful control of the PostgreSQL database through the use of the SysV runlevel system. The SysV script can be used for starting, stopping, and status-checking of PostgreSQL. It is known to work with most Red Hat based versions of Linux, including Mandrake; however, it should work with other SysV systems (e.g., UnixWare, Solaris, etc.) with little modification. The script is named linux. To use it, you will first need to copy the linux script to your init.d directory. You may require root access to do this.

First, change to the directory where you unpacked the PostgreSQL source. In our case, the path to that directory is /usr/local/src/postgresql-8.0.1/. Then, issue a cp command to copy the script from contrib/start-scripts into the init.d directory. Example 2-16 demonstrates how to do this on a Red Hat Linux system.

$ cd /usr/local/src/postgresql-8.0.1/
$ su -c "cp contrib/start-scripts/linux /etc/init.d/postgresql"

Example 2-16 arbitrarily re-names the new copy to postgresql; you may call it whatever you prefer, though it is typically named either postgresql, or postgres.

You will need to make the script file executable after copying it. To do so, use the command shown in Example 2-17.

$ su -c "chmod a+x /etc/init.d/postgresql"

There are no additional requirements to use the SysV script with Red Hat/Fedora, if you do not intend on using it to start PostgreSQL automatically (i.e., if you wish to use the script manually). However, if you do wish for the script to startup PostgreSQL automatically when the machine boots up (or changes runlevels) you will want to check your Linux distributions documentation on the specific ways this task is handled. RedHat and Fedora both use a program called chkconfig and the SysV script is configured to work with the chkconfig application.

$ /etc/init.d/postgresql start
Starting PostgreSQL: ok
$ /etc/init.d/postgresql stop
Stopping PostgreSQL: ok

The SysV script logs redirects all PostgreSQL debugging output to $PGDATA/serverlog, by default. You can adjust this to put the log anywhere you like. It is common to have the log placed in /var/log/postgres.

The instructions for installation of the postgresql script are covered in Chapter 2. Depending on your machine's configuration, there may be more than one method of invoking the script once it has been properly installed. Remember that the actual name of the SysV script file in the /etc/init.d/ directory may be an arbitrary name, depending on how it was copied. The most common names given to this script are postgresql and postgres.

If your system supports the service command, you should be able to use it as a wrapper to the installed PostgreSQL script with the following syntax:

service postgresql { start | stop | restart | reload | status }

The service command accepts only the parameters described in the preceding syntax. No other options are accepted. You can modify the way any of these general modes runs by editing the script (e.g., /etc/rc.d/init.d/postgresql) manually. Example 2-19 uses the service command to start PostgreSQL.

[root@booktown ~]# service postgresql start
Starting PostgreSQL: ok
[root@booktown ~]#

Alternatively, if the service command does not exist on your system, the postgresql script can be manually invoked with its complete system path:

/etc/init.d/postgresql { start | stop | restart | reload | status }

Example 2-20 checks the status of PostgreSQL's backend process by directly calling the postgresql script in the complete path. This assumes that your system has its SysV start-up scripts installed in the /etc/rc.d/init.d/ directory.

[root@booktown ~]# /etc/rc.d/init.d/postgresql status
pg_ctl: postmaster is running (PID: 26522)
/tmp/pgsqldev/bin/postgres
[root@booktown ~]#

As you can see from the output of Example 2-20, the SysV script is just a convenient wrapper to the pg_ctl command discussed in the previous section.

2.4.3. Calling postmaster Directly

The postmaster program is the multi-user PostgreSQL database server backend itself. This is the process that your PostgreSQL clients actually connect to, where a connection to a postgres backend is negotiated.

This binary is typically not called manually, but is indirectly executed through either the pg_ctl or SysV script discussed earlier in this section. However, these scripts at some point call the postmaster binary directly, and it can be helpful in configuring your PostgreSQL system to know what the postmaster is, and what it does.

The postmaster can only access one database cluster at a time, though you may have several concurrent postmaster applications running on different TCP/IP ports with a different database cluster for each.

Although you should never have to execute the postmaster directly. It can be useful for certain diagnostic situations. Next is the syntax for the postmaster program:

	postmaster is the PostgreSQL server.
	
	Usage:
	postmaster [OPTION]...
	
	Options:
	-B NBUFFERS     number of shared buffers
	-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
	-o OPTIONS      pass "OPTIONS" to each server process
	-p PORT         port number to listen on
	-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.

The majority of the postmaster options can and should be set from the Section 3.1. If you need more information on the specific postmaster options please refer to the online documentation from the PostgreSQL project. However there is one option that can be useful if you find yourself needing to reindex system catalogs.

In the rare case that you find your system indexes corrupted you must shutdown your postgresql installation and start up the postmaster directly using the -P option.

         /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data -o -P
         

The corruption of system indexes is rare and is usually indicitive of other problems such as faulty hardware or a significantly long time between routine maintenance tasks.

The postmaster also accepts the following two debugging options, for interested developers: