Thread: automatic restart on reboot

I'm deploying Postgres on a production environment and wanted to know if
people could share what steps they've taken to have their DB's come back
up on system reboot (either on purpose or accidental).

Are there any things to check for?

What about postmaster.pid, /tmp/blah..., etc...  Any script examples would
be appreciated.

    Thx in Advance,
    Aurangzeb

On Sun, 12 Jan 2003, Aurangzeb M. Agha wrote:

> I'm deploying Postgres on a production environment and wanted to know if
> people could share what steps they've taken to have their DB's come back
> up on system reboot (either on purpose or accidental).
>
> Are there any things to check for?
>
> What about postmaster.pid, /tmp/blah..., etc...  Any script examples would
> be appreciated.

On FreeBSD, this is handled by the script /usr/local/etc/rc.d/010.pgsql.sh
and you can view the latest copy at:

http://www.freebsd.org/cgi/cvsweb.cgi/~checkout~/ports/databases/postgresql7/files/pgsql.sh.tmpl?rev=1.14

Files in /usr/local/etc/rc.d/ which are +x and have a .sh suffx are
automatically run at boot time.

> On Sun, 12 Jan 2003, Aurangzeb M. Agha wrote:
>> I'm deploying Postgres on a production environment and wanted to know if
>> people could share what steps they've taken to have their DB's come back
>> up on system reboot (either on purpose or accidental).
>>
>> Are there any things to check for?
>>
>> What about postmaster.pid, /tmp/blah..., etc...  Any script examples would
>> be appreciated.

Assuming that you've got things set up to start PG from an init script,
it should usually Just Work.

Once in a while, if your system crashed without shutting down Postgres,
the postmaster will refuse to start because it sees the
$PGDATA/postmaster.pid file and thinks there's already a postmaster
running.  (This can only happen if the PID assigned to the postmaster
before the system crash chances to match a different process that is
alive at the instant the new postmaster tries to start.)  You can defend
against that by forcibly removing $PGDATA/postmaster.pid during boot
before you try to start the postmaster.

HOWEVER: that's only a good idea if you do it in a script that is
guaranteed to run ONLY at system boot.  Don't put it into the start
script for Postgres itself, or you'll have broken the interlock against
starting two postmasters in the same $PGDATA directory.  Which is a Very
Important Safety Feature.  If you do that, the consequences will be far
far worse than just having the postmaster not start automatically after
a reboot :-(

There are other standard system daemons, such as sendmail, that also
need pidfiles removed during boot if you want to be certain that they'll
start reliably.  If you dig around in your boot scripts you will
probably find someplace that handles this.  That's where to add
$PGDATA/postmaster.pid.

            regards, tom lane

On Sun, 12 Jan 2003, Aurangzeb M. Agha wrote:

> I'm deploying Postgres on a production environment and wanted to know if
> people could share what steps they've taken to have their DB's come back
> up on system reboot (either on purpose or accidental).
>
> Are there any things to check for?
>
> What about postmaster.pid, /tmp/blah..., etc...  Any script examples would
> be appreciated.

I added the following lines to the /etc/rc.d/rc.local script:

rm -f /db/data/postmaster.pid
raidstart /dev/md0
mount /dev/md0 /mnt/db
su - postgres -c 'pg_ctl start | rotatelogs $PGDATA/pglog 86400 2>1&'

So, I make sure the old postmaster.pid is gone (since we're booting when
we run rc.local, it's safe to assume that any old postmaster.pid is left
over from a crash.)  Then I start the RAID array just in case it didn't
get started earlier, and mount it before running postgresql.  The outputs
of postgresql are then piped into apache's rotatelogs command to get me
daily rotated logs from postgresql.  Note that you need to have the path
to rotatelogs in postgres's path, or refer to it fully qualified to make
that work right.