Re: Unable to Connect to DB Instance

On 07/04/2018 12:36 PM, Tom Lane wrote:
> Boblitz John <john(dot)boblitz(at)bertschi(dot)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 directory
>
> shows 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?

There is also this from the OP:

2018-07-04 09:15:23 CEST 192.168.250.50(28561) g11BaseERROR: could not
open file "pg_tblspc/24579/PG_9.1_201105231/24580/11866": No such file
or directory

Which failed on a query that references pgAgent and Slony.

So are you using either or both of those programs?

>
> 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
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com