0

Again, please be kind. I'm not a dba and I inherited some old postgress databases.

One of my very ancient debian servers running postgres 7.4.7 filled the data file system. One of my cohorts decided to attempt a reindex followed by a 'vacuum all'. All of this failed miserably and left me with even less space. Now when I run 'psql -h' as postgres I get

'psql: FATAL: could not open relation "pg_trigger": No such file or directory'.

I can use psql to access the database in question. If I 'psql database' I can run 'CREATE DATABASE temp;' but 'psql temp fails with the above error. I do have what appear to be valid pg_dumps. Oh and there's no postgres database like I see on other servers. My question is: Is there a way to solve the above error or failing that is there a way to completely reset the database(s)?

Keep in mind that I can't upgrade postgres (as much as I want to). Some thing I hope may help:

  • The data is in /var/lib/postgres/data/base.
  • When I run createdb or dropdb I get the FATAL error.
  • It'not my fault but it's now my mess.
Improve this question
3
  • One more thing. I did Google this six way from Sunday. The best I found called for removing files and running initdb. But I have a feeling that may not be the best choice. But what do I know? I'm just the linux admin. Commented Nov 4, 2016 at 19:13
  • maybe enterprisedb people have some tool that helps you to recover the db. Commented Nov 4, 2016 at 19:19
  • There was a free subscription time ago. Commented Nov 4, 2016 at 19:22

1 Answer 1

Reset to default
0

Oh and there's no postgres database like I see on other servers

That's bad.

It sounds like somebody (not you, presumably) tried to DROP TABLE pg_somethingorother;. Since the pg_ tables (also called the catalog) are where things like table names, trigger code, etc, are kept, that sort of table drop is going to cause many problems.

Is there a way to solve the above error or failing that is there a way to completely reset the database(s)?

You stated earlier that you had some valid pg_dump files. I recommend the following course of action:

  1. Test those dump files. Ideally, that means getting a machine with disk space, and the appropriate postgres version (7.4.7 is ancient, as you note, so this might be hard), issuing initdb, and then loading the dump file (if the file has plaintext sql in it, that's something like psql < dumpfile; if it's not text, you'll use pg_load).
  2. If the dump files work, go back to your original machine, delete that broken database directory (/var/lib/postgres/data -- the directory with base and the conf files in it).
  3. Use initdb to start from scratch, then load it up, like you did in step 1.
  4. Create a cron which issues appropriate vacuum statements to your database, so you're less likely to have this problem in the future.
  5. Create a regular backup process, so if you do have this problem (or others) in the future, you have a more confident way forward.

If you can't get a machine with an old enough postgres on it to test the dump files, then you can try loading the dump files into a newer postgres. This should work in most cases. It can run into some problems, though. If it works, proceed with steps 2 and on. If it kinda-works, but has some errors, and you're not sure what's up, it might still be worth proceeding with steps 2 and on.

If the dump files don't work, you might be hosed. But I'm going to assume they do, because the alternative is sad.

I'm sorry you're in this situation, and I wish you luck.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

"Since the postgres database is where things like table names, trigger code, etc, are kept, very little is going to work." No, that's not how PostgreSQL works. Search initdb for this text: "The postgres database is a default database meant for use by users, utilities and third party applications." See also stackoverflow.com/a/28261773/562459
You're absolutely right -- I was conflating the postgres database and the catalog. I've updated my answer to reflect that -- the remainder of my answer stands.
It is perfectly normal that there is no database postgres. That was introduced in PostgreSQL 8.1. Also, it is imperative that the database be upgraded to 7.4.30 at the very least. In reality, there cannot be a reason to be on 7.4 unless you don't mind losing your data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.