On Tue, 16 Oct 2018 at 18:58, Laurenz Albe <laurenz.a...@cybertec.at> wrote:
> Please don't top post. > > Joshua White wrote: > > On Mon, 15 Oct 2018 at 21:13, Laurenz Albe <laurenz.a...@cybertec.at> > wrote: > > > Joshua White wrote: > > > > I'm hoping someone can point me in the right direction. I've got a > PostgreSQL 10 server > > > > instance on CentOS 6, which I set up and manage. I have full admin > rights on this machine, > > > > so I can access logs, etc. > > > > > > > > Recently I attempted to create a new database in this cluster. The > command succeeds, > > > > but when I try to connect to the new database, I get a "could not > open file" error: > > > > > > > > psql: FATAL: could not open file "base/618720/2610": No such file > or directory > > > > > > > > It has been some time since I set up the database, so I don't know > how long ago this > > > > became an issue. I can't seem to find any other instances of this > problem online either. > > > > The logs are not helpful - even on the highest debug setting, I only > see the > > > > "connection authorized" then the fatal "could not open file" error. > > > > > > > > The data directory is on a separate disk array to the OS. Recently > checked it and > > > > there are no disk errors. > > > > > > > > Any thoughts or ideas would be much appreciated. > > > > > > Looks like the file backing the "pg_index" table is gone. > > > > > > Can you check if the file exists in the data directory or not? > > > > Thanks for the tip. I've checked and the on-disk file behind "pg_index" > still exists. > > > > I have existing databases in this cluster that I'd prefer not to drop > and recreate if possible. > > > > I've tried dropping and recreating the new database I want to use, but > each time get the same type of error. > > "pg_index" initially uses file 2610. > That may of yourse change if you rewrite the table. > > Try the following as OS user "postgres": > oid2name -d <your database> -f 2610 > Then you can see which table is associated to that file. > > Anyway, your database seems to be quite wrecked, and you'd probably need > an expert to save what can be saved. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > Sorry about the top-posting - default behaviour of my email client. ./oid2name -d postgres -f 2610 returns no tables. Four of my six custom databases within the cluster return "pg_index" as the table. The other two return nothing. I guess this is fast becoming a situation where I'd be better off to start over and restore the entire cluster from backups. Not ideal, but might have to be done. At least I can take the opportunity to replace CentOS 6 with 7 if I do that. Kind Regards, Joshua.
