Arranging for PostgreSQL Startup and Shutdown

In most environments, you will probably want to arrange for your PostgreSQL server to start when you boot your operating system. You'll also want to arrange for your PostgreSQL server to terminate gracefully when you power off your system. In this section, I'll show you how to make these arrangements for Windows and Red Hat Linuxthe details will vary if you are using a different operating system.

First, let's see how to start and stop a PostgreSQL server on-demand.

Using pg_ctl

The easiest way to start a PostgreSQL server (that is, a postmaster) is to use the pg_ctl command. pg_ctl is a shell script that makes it easy to start, stop, restart, reconfigure, and query the status of a PostgreSQL server.

To start a server, use pg_ctl start:

$ pg_ctl start -l /tmp/pg.log -o -i

pg_ctl start fires up a postmaster. You can use several options with the pg_ctl start command, as shown in Table 21.8.

The -D data-directory option tells the postmaster where to find your database cluster. If you don't include this option, the postmaster will interrogate the $PGDATA environment variable to find your cluster. If I am starting a postmaster from a shell script, I usually define PGDATA and then use it when I invoke pg_ctl:

... export PGDATA=/usr/local/pgdata pg_ctl -D $PGDATA ...

Arranging things this way makes it a bit more obvious that PGDATA is defined and that the postmaster will use that variable to find the cluster.

The -l logfile-name option determines where the postmaster will send error and informational messages. If you include this option, the postmaster's stdout and stderr will be appended to the named file. If you don't, the postmaster will write to the controlling terminal. That can be handy if you're trying to debug a server-related problem, but it's generally a bad idea. The problem with sending server output to the controlling terminal is that the controlling terminal will disappear if you log outany server output written after you log out is lost.

You use the -o postmaster-options to specify options that will be passed along to the new postmaster. Any option supported by the postmaster can be specified after the -o flag. Enclose the postmaster-options in single or double quotes if it contains any whitespace. For example:

$ pg_ctl start -o "-i -d 5"

You will rarely, if ever, need to use the -p postmaster-path option. The -p option tells pg_ctl where to find the postmaster. In the normal case, pg_ctl can find the postmaster executable by looking in the directory that contains pg_ctl. If pg_ctl doesn't find the postmaster in its own directory, it will search in the bindir directory. The bindir directory is determined at the time your copy of PostgreSQL is built from source (that is, the -bindir configuration option). You will need to use only pg_ctl's -p option if you move the postmaster away from its normal location (don't do that).

The -s option is used to tell pg_ctl to be silent. Without the -s flag, pg_ctl will cheerfully display progress messages as it goes about its work. With the -s flag, pg_ctl will tell you only about problems.

Finally, use the -w flag if you want the pg_ctl program to wait for the postmaster to complete its startup work before returning. If pg_ctl has to wait for more than 60 seconds, it will assume that something has gone wrong and will report an error. At that point, the postmaster may or may not be running: Use pg_ctl status to find out. I recommend including the -w flag whenever you invoke pg_ctl from a script; otherwise, your script will happily continue immediately after the pg_ctl command completes (but before the server has booted). If you want to see what kind of problems you may run into when you don't wait for a complete boot, try this:

$ pg_ctl -s stop $ pg_ctl start -l /tmp/pg.log ; psql -d movies postmaster successfully started psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

See what happened? The pg_ctl command returned immediately after spawning the postmaster, but the psql command started running before the postmaster was ready to accept client connections. If you were to try that inside of a shell script, the PostgreSQL client (psql in this case) would fail. This kind of problem (apparently random client failures) can be hard to track down and usually results in a dope slap.

Shutdown Modes

You also can use pg_ctl to shut down (or restart) the postmaster. The postmaster honors three different shutdown signals:

• Smart shutdown When the postmaster receives a terminate signal (SIGTERM), it performs a smart shutdown. In smart shutdown mode, the server prevents new client connections, allows current connections to continue, and terminates only after all clients have disconnected.
• Fast shutdown If the postmaster receives an interrupt signal (SIGINT), it performs a fast shutdown. In fast shutdown mode, the server tells each server process to abort the current transaction and exit.
• Immediate shutdown The third shutdown mode is called immediate, but it might be better termed crash. When you shut down the postmaster in this mode, each server process immediately terminates without cleaning up itself. An immediate shutdown is similar to a power failure and requires a WAL (write-ahead-log) recovery the next time you start your database.

To shut down the postmaster using pg_ctl, use the command

$ pg_ctl stop [smart|fast|immediate]

If you want to restart the postmaster using pg_ctl, use the command

$ pg_ctl restart [smart|fast|immediate]

Now that you know how to start up and shut down a PostgreSQL server on demand, let's see how to make a server start when your computer boots.

Configuring PostgreSQL Startup on Unix/Linux Hosts

Configuring PostgreSQL to automatically start when your Unix/Linux system boots is not difficult, but it is system-specific. Systems derived from BSD Unix will usually store startup scripts in the /etc/rc.local directory. Systems derived from System V Unix (including Red Hat Linux) will store startup scripts in the /etc/rc.d directory. The PostgreSQL Administrator's Guide contains a number of suggestions for configuring automatic PostgreSQL startup for various Unix/Linux systems. In this section, I'll describe the process for Red Hat Linux systems.

First, let's see the easy way to configure startup and shutdown on a typical Red Hat Linux system. There are only three steps required if you want to do things the easy way:

• Log in as the superuser (root)

   

• Copy the file start-scripts/linux from PostgreSQL's contrib directory to /etc/rc.d/init.d/postgresql

   

• Execute the command /sbin/chkconfig add postgresql

   

That's it. The chkconfig command arranges for PostgreSQL to start when your system boots to multiuser mode and also arranges for PostgreSQL to shut down gracefully when you shut down your host system.

Now, let's look at the more complex way to arrange for startup and shutdown. Why might you want to do things the hard way? You may find that the functionality provided by the startup script (and chkconfig) don't fit quite right in your environment. You may have customized run levels (described next), or you may want to change the point in time that PostgreSQL starts (or stops) relative to other services. Reading the next section will also give you a good understanding of what chkconfig is doing on your behalf if you decide to use it.

When a Linux system boots, it boots to a specific runlevel. Each runlevel provides a set of services (such as network, X Windows, and PostgreSQL) . Most Linux distributions define seven runlevels:

• Runlevel 0 Halt
• Runlevel 1 Single-user (maintenance mode)
• Runlevel 2 Not normally used
• Runlevel 3 Multi-user, networking enabled
• Runlevel 4 Not normally used
• Runlevel 5 Multi-user, networking enabled, X Window login
• Runlevel 6 shutdown

In the usual case, your system is running at runlevel 3 or runlevel 5. You can add PostgreSQL to the set of services provided at a particular runlevel by adding a startup script and a shutdown script to the runlevel's directory.

Startup scripts are stored in the /etc/rc.d directory tree. /etc/rc.d contains one subdirectory for each runlevel. Here is a listing of the /etc/rc.d directory for our Red Hat 7.1 system:

$ ls /etc/rc.d init.d rc0.d rc2.d rc4.d rc6.d rc.sysinit rc rc1.d rc3.d rc5.d rc.local

The numbers in the directory names correspond to different runlevels. So, the services provided at runlevel 3, for example, are defined in the /etc/rc.d/rc3.d directory. Here is a peek at the rc3.d directory:

$ ls /etc/rc.d/rc3.d K03rhnsd S05kudzu S14nfslock S55sshd S85gpm K20nfs S06reconfig S17keytable S56rawdevices S90crond K20rwhod S08ipchains S20random S56xinetd S90xfs K35smb S08iptables S25netfs S60lpd S95anacron K45arpwatch S10network S26apmd S80isdn S99linuxconf K65identd S12syslog S28autofs S80pppoe S99local K74nscd S13portmap S40atd S80sendmail

Inside a runlevel subdirectory, you will see start scripts and kill scripts. The start scripts begin with the letter S and are executed whenever the runlevel begins. The kill scripts begin with the letter K and are executed each time the runlevel ends. A start script is (appropriately enough) used to start a service. A kill script is used to stop a service.

The numbers following the K or S determine the order in which the scripts will execute. For example, S05kudzu starts with a lower number so it will execute before S06reconfig.

I'll assume that you want to run PostgreSQL at runlevels 3 and 5 (the most commonly used runlevels). The start and kill scripts are usually quite complex. Fortunately, PostgreSQL's contrib directory contains a sample startup script that you can use: contrib/start-scripts/linux. To install this script, copy it to the /etc/rc.d/init.d directory and fix the ownership and permissions (you'll need superuser privileges to do this):

# cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql # chown root /etc/rc.d/init.d/postgresql # chmod 0755 /etc/rc.d/init.d/postgresql

Notice that you are copying the startup file to /etc/rc.d/init.d rather than /etc/ rc.d/rc3.d, as you might expect. Start and kill scripts are usually combined into a single shell script that can handle startup requests as well as shutdown requests. Because a single script might be needed in more than one runlevel, it is stored in /etc/rc.d/ init.d and symbolically linked from the required runlevel directories. You want PostgreSQL to be available in runlevels 3 and 5, so create symbolic links in those directories:

# ln -s /etc/rc.d/init.d/postgresql /etc/rc.d/rc3.d/S75postgresql # ln -s /etc/rc.d/init.d/postgresql /etc/rc.d/rc3.d/K75postgresql # ln -s /etc/rc.d/init.d/postgresql /etc/rc.d/rc5.d/S75postgresql # ln -s /etc/rc.d/init.d/postgresql /etc/rc.d/rc5.d/K75postgresql

The numbers that you chose (S75 and K75) are positioned about three quarters through the range (00-99). You will want to adjust the script numbers so that PostgreSQL starts after any prerequisite services and ends after any services that depend upon it. Whenever we reach runlevel 3 (or runlevel 5), the init process will execute all start scripts numbered less than 75, then your postgresql script, and then scripts numbered higher than 75.

You also want to ensure that PostgreSQL shuts down gracefully when you reboot or halt your server. The contributed script can handle that for you as well; you just need to create symbolic links from the halt (rc0.d) and reboot (rc6.d) directories:

# ln -s /etc/rc.d/init.d/postgresql /etc/rc.d/rc0.d/K75postgresql # ln -s /etc/rc.d/init.d/postgresql /etc/rc.d/rc6.d/K75postgresql

As before, you will want to review the other scripts in your rc0.d and rc6.d directories to ensure that PostgreSQL is shut down in the proper order relative to other services.