Postgresql CronJob setup

Question

So I setup a private instance of Postgresql. Everything is working fine except if Postgres goes down I can't get the cronjob to bring it back up.

My Postgres script:

PIDFILE="$HOME/pgsql/data/postmaster.pid"
if [ -e "${PIDFILE}" ] && (ps -u $(whoami) -opid= | grep -P "^\s*$(cat ${PIDFILE})$" &> /dev/null); 
 then
  echo "Postgres already running."
  exit 99
fi
echo "Starting up postgres."
$HOME/bin/pg_ctl start -D $HOME/pgsql/data -l $HOME/tmp/pgsql.log -o "-p {PORT}" >> $HOME/tmp/pg_ctl.log 2>&1 
echo $! > "${PIDFILE}"
chmod 644 "${PIDFILE}"

When I run the script I keep getting "Starting up postgres."

On pg_ctl.log I get:

pg_ctl: invalid data in PID file "{HOME}/pgsql/data/postmaster.pid"

On pgsql.log I get:

FATAL: bogus data in lock file "postmaster.pid": " "

Any help would be appreciated.

Accepted Answer

1

I think the problem is that you're overwriting the postmaster.pid that PostgreSQL creates automatically with an empty string.

Try removing the "echo $! > "${PIDFILE}"" statement from your script.

permanent link

answered 06 Sep '14, 00:54

seanf
12.2k●3●18●36
accept rate: 37%

edited 06 Sep '14, 00:54

So I manually created a postmaster.pid with the correct pid inside and it worked like it should. Now with your suggestion I removed that line from the script, however, no file was created. Is there a setting that I missed? I installed postgresql 9.2 with the make world option so I could have all the extensions.

(06 Sep '14, 01:03) jayhalleaux

This should be set via the "external_pid_file" value in your postgresql.conf file. I think it's commented out by default, but you can set it:

external_pid_file = '/home/username/pgsql/data/postmaster.pid'

(06 Sep '14, 01:42) ryans ♦♦

Answer 2

0

Add a command after 'starting postgres' which removes the previous PID file before starting.

permanent link

answered 05 Sep '14, 21:20

johns ♦♦
5.3k●2●12
accept rate: 23%

That is not the problem, the problem is that it does not recognize the PID file that was created on the initial startup. Once the postgres is up and running I want it to keep checking if is still running, otherwise create a new instance. If I delete the PID then I will be re-initializing the postgres server every time I run this script which will be every 5 minutes.

Also I noticed where the problem is initially:

echo $! > "${PIDFILE}"

does not place a PID number into the PID file, it just leaves a blank space

(06 Sep '14, 00:10) jayhalleaux

Postgresql CronJob setup

Follow this question

Related questions