Forum OpenACS Q&A: PostgreSQL data disappeared

I already thought that my so far unfortunate Oracle installation process finally ended happily. Too early.

After installing ora8 database I tried according to Bruno's instructions to reboot the server to test if Oracle starts on boot. Well, the server didn't reboot. What makes everything a bit more tricky is that the server is in Finland and I'm in Switzerland.

So I asked some help and my colleagues back home discovered that the shutdown process hadn't gone through, but the umount of /usr had resulted in an illegal seek. They made a hard reboot and the server had booted fine when I came to school today. Or so I thought...

My suspicions woke up when trying to load front page of my site welcomed me to install OpenACS. A dotlrn installation in another port said it couldn't connect to database.

Trying to launch psql revealed the dark truth: two of the (test) databases had totally disappeared, and the only one left was empty:

suisse-dev=# \d
No relations found.
suisse-dev=# 

I'm totally knocked out. What on earth can have happened?

When they did the reboot, the postmaster.pid file was not removed. This means the postmaster process didn't startup as usual.

First check to verify that postmaster isn't running:

$ps axww|grep post

if you don't see a process for postmaster with your data directory, then you need to remove the postmaster.pid file and try starting it again.

What I don't get is that you were following Bruno's instructions on Oracle. Are you installing both databases?

The problem with the Oracle install is that the startup files in /etc/rc.d/init.d/oracle8i shut Oracle down after sshd goes down, and also the websites are usually still running. If the webserver is still running, Oracle will never shut down. The local keyboard will already not work, and you can no longer log in via ssh to cure the error. The only thing you can do is to power cycle the machine.

So, how do you control the webserver to ensure it goes down before Oracle? Also, what are the links in /etc/rc.d/rc6.d that contain oracle8i and sshd?

If you use daemontools (svc) to shut down the webservers, I have an init.d script that does that first on shutdown of the server. I'll post some better instructions later today.

I also found bugs in the init.d scripts of listener8i and postgresql: both fail to create a lock file like the Oracle8i script, which means that the processes are not correctly stopped when you shutdown or reboot.

These are my suggestions:

(Needless to say, make a full backup of all files if doing any file or db modifications.)

First, check the $PGHOME/data/base directory.  Do an 'ls' - perhaps the data is still there, just not accessible at this point in time.

If I recall correctly, each database has its own subdirectory under data/base .  The numbered directories are the OID of the database, I think.

One way (may take a while if you have a lot of data loaded) is to use grep(for text files) and the "od -a" or "strings" command (for binary files) to look through the various database files provided they still exist.

If you know a word or phrase that could only be in the database that is missing, grep through the various files there and see if you get a match.

Second, if the databases are present, but simply not recognized, the information may still be in the system catalog but messed up somehow.

Use pg_dump to dump out the information in text form and look at what there is.  Perhaps you can decipher a way to clean up the system catalog and get things working again.  No doubt the experts on the postgresql mailing lists will be able to assist.

The next question concerns your OS.  I assume you are running Linux - check your kernel version and OS for updates.  I know that I was bitten by a very bad bug in a now-obsolete distribution - the "bdflush" daemon that calls fsync() every 30 seconds was not running when the machine was booted, so changes I made would be recorded in the disk cache in RAM, but never actually written to the drive.

You may want to look through the "messages" log file and other logs in search of clues as to why this happened.  Usually the previous kernel messages from the next-to-last boot are recorded.

Finally, you should test your hardware, now that you know that it has failed.  The usual test is to compile a kernel, since it stresses the disk , CPU and memory subsystems.

Another alternative that has worked well for me is the Mersenne prime number detector, which heavily stresses CPU and memory subsystem - see mersenne.org and read up on the torture test setting.

Best of luck!

Yeah, your boot/reboot scripts are messed up just like mine were. Sshd goes down before Oracle, so if it hangs you can't login and fix it.

Also, I can't tell but if you used the stock postgresql scripts and the stock listerner8i script, they never actually are killed.

First to correct the order of kill/startup problem:

#!/bin/sh
# 
# chkconfig: 345 98 02
# description: Oracle8i starts the oracle database deamons ...

Note changes to the chkconfig line to kill on 02 (almost first) and start on 98 (almost last). Then as root:

# chkconfig --del oracle8i
# chkconfig --add oracle8i

Repeat the same steps for listener8i and postgresql. Also these scripts probably are broken. I'll show you my svc script so you can see what needs to be in them to work correctly.

#! /bin/sh

# chkconfig: 345 99 01
# description: svc start and kill all

# This is an example of a start/stop script for SysV-style init, such
# as is used on Linux systems.  You should edit some of the variables
# and maybe the 'echo' commands.
#
# add to starup via:
# chkconfig --add svc
#


# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# What to use to start up the postmaster
DAEMON="/usr/local/bin/svc"


# Only start if we can find svc.
test -f $DAEMON || exit 0

# Parse command line parameters.
case $1 in
   start)
         echo "Starting All SVC Daemons:"
         $DAEMON -u /service/*
         touch /var/lock/subsys/svc
         echo "ok"
         ;;
   stop)
         echo -n "Stopping All SVC Daemons: "
         svc -d /service/*
         rm -f /var/lock/subsys/svc
         echo "ok"
         ;;
   restart)
         echo -n "Restarting All SVC Daemons: "
         $0 stop
         $0 start
         echo "ok"
         ;;
   status)
         svstat /service/*
         ;;
   *)
         # Print help
         echo "Usage: $0 {start|stop|restart|status}" 1>&2
         exit 1
         ;;
esac

exit 0

Add the svc script (as root):

# chkconfig --add svc

Notice the touch and rm -f lines in start and stop parts of the script. These lines were missing in my copies of listener9i and postgresql.

Also note that my script is a little aggressive, starting all scripts in /service/, even ones with the down file in their run directory. There is a definite need for improvement here for it to work according to original design.