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
> -----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 Instance
>
> Boblitz 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
> > 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?
>
> 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