Chapter 9. Database Management
This chapter covers several of the topics associated with managing a PostgreSQL database system. These include starting and stopping the PostgreSQL backend, as well as the creation, removal, and maintenance of individual databases. There is also a section devoted to the topic of backing up and restoring data from a database.
Starting and Stopping PostgreSQL
In this section we cover two options provided with PostgreSQL that are used to start and stop PostgreSQL. The first is a general purpose application called pg_ctl, which should function identically on any machine, regardless of the system. This script is intended to be run by the system user (e.g., the user who owns the data directory) configured to execute the postmaster backend.
The second provided script is the SysV-style script, found in the contrib/start-scripts sub-directory within the PostgreSQL source path. The installation of the SysVscript is discussed in Chapter 2. By default this script is named linux, as it is intended for a Linux system's start-script directory, though in the installation instructions it is renamed to a script called postgresql in the system's service start-up directory (e.g., /etc/rc.d/init.d).
The main functional difference between pg_ctl and the SysV-style service script is that pg_ctl is intended to be used by the user who runs the postmaster backend (e.g., postgres), whereas the service script is intended to be run by the root user.
The service script is not strictly Linux specific, and should be compatible with most systems based on SysV start-up scripts. However, if you are not running Linux, you may prefer to stick with the pg_ctl script.
Using pg_ctl
The pg_ctl script is provided with PostgreSQL as a general control application. With it, you can start, stop, re-start 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 status [-D DATADIR]
The following options may be passed to pg_ctl:
- -w
Causes the pg_ctl application to wait until the operation has finished before returning to a command line. This option may be passed to either the start or restart action; by default, the application sends the command on to the postmaster and exits immediately for these actions.
- -W
Causes the pg_ctl application not to wait until the operation has finished before returning to a command line. This option may only be passed to the stop action; by default, the application sends the stop command on to the postmaster, and waits for the action to finish before exiting.
- -D DATADIR
Specifies the directory which contains the default database files. This is optional, because you may have this value already set in the PGDATA environment variable. If the PGDATA environment variable is not set, the -D flag is required.
- -s
Suppresses any output from the pg_ctl application, aside from system errors. By default, if this flag is not specified, information about the activity within the database (or specific information about startup or shutdown, depending on the action) will be displayed to the screen of the user who initiated the command.
- -l FILENAME
Specifies a file FILENAME to append database activity to. This option is only available with the start action.
- -m SHUTDOWN-MODE
Sets the mode with which to shut down the postmaster backend.
- smart
Makes the postmaster wait until all clients are disconnected before shutting down.
- fast
Shuts the postmaster down without waiting for clients to disconnect.
- immediate
Shuts the postmaster down more abruptly than fast mode, bypassing normal shutdown procedures. This mode causes the database to re-start in recovery mode the next time it starts, which verifies the integrity of the system.
This option is of course only available to the stop and restart actions.
- -o "OPTIONS"
Passes the options specified by OPTIONS (within double quotes) to be passed directly through to the postmaster (e.g., the -i flag to enable TCP/IP connections). See the reference entry on postmaster for a complete list of these flags.
 | Many of the run-time configuration options for the postmaster backend 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 format, and should not be modified unless you are sure you understand the purpose of the parameter. |
Starting PostgreSQL with pg_ctl
To start PostgreSQL's postmaster backend, the start argument must be passed to pg_ctl. Remember that pg_ctl must be run by the postgres user (or whatever user you have configured to own the PostgreSQL data path).
Example 9-1 starts the postmaster backend, using the data path of /usr/local/pgsql/data. The database system starts up successfully, reports the last time the database system was shut down, and provides various debugging statements before returning the postgres user to a shell prompt.
Example 9-1. Starting PostgreSQL with pg_ctl
[postgres@booktown ~]$ pg_ctl -D /usr/local/pgsql/data start postmaster successfully started DEBUG: database system was shut down at 2001-09-17 08:06:34 PDT DEBUG: CheckPoint record at (0, 1000524052) DEBUG: Redo record at (0, 1000524052); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 815832; NextOid: 3628113 DEBUG: database system is in production state [postgres@booktown ~]$
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.
 | Never use kill -9 (kill -KILL) on the postmaster process. This can result in lost or corrupted data. |
Example 9-2 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 9-2. Stopping PostgreSQL with pg_ctl
[postgres@booktown ~]$ pg_ctl -D /usr/local/pgsql/data stop -m fast Fast Shutdown request at Mon Sep 17 09:23:39 2001 DEBUG: shutting down waiting for postmaster to shut down..... DEBUG: database system is shut down done postmaster successfully shut down [postgres@booktown ~]$
| 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. |
Re-starting PostgreSQL with pg_ctl
You may pass the restart argument to pg_ctl as shorthand for sequential stop and start calls to pg_ctl. This argument may also specify the -m flag to indicate the preferred shutdown mode.
PostgreSQL stores the most recently used start-up options in a temporary file called postmaster.opts, within the PostgreSQL data path (PGDATA). This file is used when pg_ctl is invoked with the restart argument to ensure that your run-time options are preserved. Avoid placing your own configurations on the postmaster.opts file, as it will be overwritten when pg_ctl is executed with the start argument.
Example 9-3 re-starts the Book Town database server with the postgres user.
Example 9-3. Re-starting PostgreSQL with pg_ctl
[postgres@booktown ~]$ pg_ctl -D /usr/local/pgsql/data restart Smart Shutdown request at Mon Sep 17 08:33:51 2001 DEBUG: shutting down waiting for postmaster to shut down.....DEBUG: database system is shut down done postmaster successfully shut down postmaster successfully started [postgres@booktown ~]$ DEBUG: database system was shut down at 2001-09-17 08:33:53 PDT DEBUG: CheckPoint record at (0, 1000524116) DEBUG: Redo record at (0, 1000524116); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 815832; NextOid: 3628113 DEBUG: database system is in production state [postgres@booktown ~]$
Checking Status of PostgreSQL with pg_ctl
You may use the status argument to check the status of a running postmasterprocess. While not having any effect on the data itself, the data path must be known to pg_ctl. If the PGDATA environmental variable is not set, the -D flag must be passed to pg_ctl.
Example 9-4 checks the status of the Book Town PostgreSQL server.
Example 9-4. Checking Status with pg_ctl
[postgres@booktown ~]$ pg_ctl -D /usr/local/pgsql/data status pg_ctl: postmaster is running (pid: 11575) Command line was: /usr/local/pgsql/bin/postmaster '-D' '/usr/local/pgsql/data' '-i' '-s' [postgres@booktown ~]$
| A lot of typing can be saved by making sure the PGDATA variable is set. If you intend to always use the same data directory, you may set the PGDATA variable (e.g., in the /etc/profile file, as is recommended in Chapter 2) and never have to apply the -D flag. |
Using the SysV Script
The SysV-style script, if installed, operates similarly to the pg_ctl script. In fact, the SysV-style script operates as a management program that wraps around the pg_ctl command. The primary difference is that the SysV script is intended to be invoked by the root user, rather than the user who owns and runs PostgreSQL (e.g., postgres). The script itself handles the switching of the userids at the appropriate times.
Using the SysV script instead of manually invoking pg_ctlis advantageous in that it simplifies system startup and shutdown procedures. The postgresql script file in /etc/rc.d/init.d/ is a plain text file, and can be modified in any standard text editor. Within this file you may easily locate the startup and shutdown routines, and add or remove options to pg_ctl as you most commonly use them. The pg_ctl commands are simplified by using either the single administrative start or stop parameter with the postgresql.
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/rc.d/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 | 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 9-5 uses the service command to start PostgreSQL.
Example 9-5. Starting PostgreSQL with Service Command
[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/rc.d/init.d/postgresql { start | stop | restart | status }Example 9-6 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.
Example 9-6. Checking Status with postgresql Script
[root@booktown ~]# /etc/rc.d/init.d/postgresql status pg_ctl: postmaster is running (pid: 13238) Command line was: /usr/local/pgsql/bin/postmaster '-D' '/usr/local/pgsql/data' [root@booktown ~]#
As you can see from the output of Example 9-6, the SysV script is just a convenient wrapper to the pg_ctl command discussed in the previous section.