This is part question, part short, sad tale.
Working on my database, I had a view that would lock up the
machine (eats all available memory, soon goes belly-up.) Turned out
to be a recursive view: view A asked a question of view B that
asked view A. [is it possible for pgsql to detect this? I worry about
my users doing this.] [and, yes, I should use kernel-level controls to
make sure that the postmaster process can't use all available
resources; but hey, it's a development machine. ]
Anyway, as I was tracking down this problem, I couldn't restart
PostgreSQL if the machine had crashed and I had a /tmp/.PGSQL.*
file in the temp directory; it assumed that the socket was in use.
So, I began restarting pgsql w/a line like
rm -f /tmp/.PGSQL.* && postmaster -i >log 2>log &
Which works great. Except that I *kept* using this for two weeks
after the view problem (damn that bash up-arrow laziness!), and
yesterday, used it to restart PostgreSQL except (oops!) it was
already running.
Results: no database at all. All classes (tables/views/etc) returned
0 records (meaning that no tables showed up in psql's \d, since
pg_class returned nothing.)
I don't know enough about why -- the /tmp files appear to have a
length of 0, but pgsql seems to care a great deal about them.
[ I did have a very fresh pg_dumpall file--thank you, anacron--so I
lost about 30 minutes worth of work, but it would have been
everything if I never backed up. ]
My advice:
1) Use pg_dumpall.
2) Don't delete those /tmp files until you're *sure* you're out of Pg
Anyone know what *happened* and *why*? Was there anything I
could have done?
Thanks!
[ I do read these lists, but always appreciate a cc on responses so I
don't accidentally miss them. TIA. ]
--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)
