Re: Start/stop postgresql with pg_ctl or service without root access on RHEL

Jean-Michel Scheiwiler <jm(dot)scheiwiler(at)gmail(dot)com> writes:
> We plan to use postgresql on RHEL 6.
> DBAs won't have root access but they will need to start/stop the databases.
> They'll be able to do so with pg_ctl as postgres.
> However databases should also start automatically when the server reboots
> and so we should use services (/etc/init.d/postgresql-9.x).

Don't do that.

> When postgres is started with service (as root) and stopped with pg_ctl as
> postgres, the pid file in $PGDATA is deleted but not the specific pid and
> lock files (respectively in /var/run and /var/lock/subsys) created by the
> init.d script.
> It leads to an inconsistent state where service postgresql-9.x status
> throws"postgresql-9.x
> dead but pid file exists".

That's one reason why you shouldn't do that. There are others; for
instance I don't think you end up with the same selinux context for
the daemon if you don't go through "service start".

> So what is the best practice and solution for this situation?

I'd go with the restricted sudo approach. Customized init scripts are
a pain --- either they get overwritten by package upgrades, or they
fail to track changes in the script, and either result is bad.

regards, tom lane