postgres: will not recognise existing tables

From: Darren Reed <darrenr(at)fastmail(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: postgres: will not recognise existing tables
Date: 2007-10-07 02:53:52
Message-ID: 47084A40.3020807@fastmail.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Darren Reed 2007-10-07 04:13:02 Is my database now too big?
Previous Message Peter Koczan 2007-10-06 17:30:12 Re: Postgresql takes more time to update