RE: Unable to Connect to DB Instance (SOLVED)
Good Morning,
First and foremost - thank you for the info.
While we still cannot determine that cause of the missing files (I see no indication of them being deleted)
I was able to use a combination of touch and the "blunderbuss" to fully recreate the two databases. While it may not
always be useful - here is what I did
1) Run psql
2) Connect to db1
3) Run REINDEX SYSTEM db1 (I'm on 9.1 so options may vary)
4) Wait -> message some file not found -
5) in second session touch the file (pay attention to the directory)
6) goto 3)
This until no error, then same procedure for REINDEX DATABASE
There were files missing from base, global and pg_tblspc!
Rinse and repeat for the second DB. This obviously took quite a bit of time - but in the end, I got it to work.
Again, this worked for me and I had a backup of the postgres instance itself, so I could take the chance.
Regards,
John Boblitz
Show quoted text
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Mittwoch, 4. Juli 2018 21:37
To: Boblitz John <john.boblitz@bertschi.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Unable to Connect to DB InstanceBoblitz John <john.boblitz@bertschi.com> writes:
Thanks - I get "pg_db_role_setting" as a response.
Hm ... not its index? If the table itself is gone, it's surprising that you can get
through session startup.I have already attempted to reindex system but get:
I had in mind just reindexing the specific table you're having trouble with ...
but this:ERROR: could not open file "base/11919/11680": No such file or
directoryshows that there's another table that also has a problem, and there may be
more :-(. I don't know what the odds are that you can get out of this
completely. I would NOT recommend "reindex system" as a blunderbuss
solution. You do not know how much is corrupted and there's a significant
chance of making things worse by tromping over the whole database using
catalogs of uncertain reliability.Did you identify which table 11680 is?
In the case of pg_db_role_setting, a possible solution is to "touch" the
missing file so it exists; it'll be empty, which means that you'll have lost any
ALTER DATABASE/ROLE SET settings, but that's better than not being able to
dump at all. (You might then need to REINDEX pg_db_role_setting to get its
indexes in sync with it being empty.)Whether an equally drastic answer is tolerable for your other missing
table(s) depends on what they are...regards, tom lane