I've run into this problem twice in the last few days.
postgresql is running, shutdown, reboot, restart, and tables are missing.
The shutdown is clean:
LOG: unexpected EOF on client connection
LOG: received smart shutdown request
LOG: shutting down
LOG: database system is shut down
LOG: database system was shut down at 2007-10-06 18:50:33 PDT
LOG: checkpoint record is at 2/3310F20C
LOG: redo record is at 2/3310F20C; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 228741; next OID: 16449
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system is ready
LOG: transaction ID wrap limit is 2147484146, limited by database
"postgres"
LOG: received smart shutdown request
LOG: shutting down
LOG: database system is shut down
The startup is clean:
FATAL: lock file "postmaster.pid" already exists
HINT: Is another postmaster (PID 99) running in data directory
"/home/postgres/
db"?
LOG: database system was shut down at 2007-10-06 18:50:44 PDT
LOG: checkpoint record is at 2/3310F250
LOG: redo record is at 2/3310F250; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 228741; next OID: 16449
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system is ready
LOG: transaction ID wrap limit is 2147484146, limited by database
"postgres"
LOG: received SIGHUP, reloading configuration files
but then I discover a tabel is gone:
ERROR: relation "ifl" does not exist
LOG: unexpected EOF on client connection
All the disk space is still used, so the data is there...
I try to do a dump, but now I get:
ERROR: out of memory
DETAIL: Failed on request of size 20.
Ok, this I can fix, put the correct "ulimit -d" in my postgresql
startup script and it goes away - for now.
Version: psql (PostgreSQL) 8.1.3
Questions.
1) At some point I expect the ulimit solution is going to stop
working. What do I need to do to stop postgresql from
returning this error? Is it a shread memory setting?
Can I change something in a .conf file?
Should I be thinking about changing the way the schemas
are designed to put relations in different schemas rather
than all being in the same one?
The database only has about 8 million entries (going by a
"wc -l" of the dump'd file.)
2) Why has it forgotten about my tables?
How can I encourage it to not forget about them?
The only solution I currently have to (2) is to wipe out the
database (rm -rf) and restore from an earlier dump. I am
slowly getting tired of doing this.
Will upgrading help?
Thanks,
Darren