Обсуждение: Problem creating a database

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?

It's hard to determine what happened, but something has been
eating your data.  As it is, your best option would be to
drop the database and recreate it from a backup.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Please don't top post.

Joshua White wrote:
> On Mon, 15 Oct 2018 at 21:13, Laurenz Albe <laurenz.albe@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

On 15/10/2018 10:46, Joshua White wrote:
> Hi all,
>
> 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.
>
> Kind Regards,
> Joshua

Hi Joshua,
  I have a distant recollection of a similar problem on CentOS, solved 
by raising the mount point further up the fs hierarchy. i.e. I think I 
ended up having to have the disk partition mounted on /var/lib before it 
would work properly, after initially trying it on /var/lib/pgsql/data. 
If you're using 10 your path will probably be different, but I think it 
boiled down to the ownership of the underlying mount point.

Cheers,
Colin.

Joshua White wrote:
> > > > > > psql: FATAL:  could not open file "base/618720/2610": No such file or directory
> > > 
> > > "pg_index" initially uses file 2610.
> > > 
> > > Anyway, your database seems to be quite wrecked, and you'd probably need
> > > an expert to save what can be saved.
> > 
> So you're right about it being seriously corrupted somehow. All my custom databases seem to work fine.
> I have data checksums and amcheck enabled and haven't found any issues using the query below.
> 
> SELECT bt_index_check(index => c.oid), c.relname, c.relpages
> FROM pg_index i
> JOIN pg_opclass op ON i.indclass[0] = op.oid
> JOIN pg_am am ON op.opcmethod = am.oid
> JOIN pg_class c ON i.indexrelid = c.oid
> JOIN pg_namespace n ON c.relnamespace = n.oid
> WHERE am.amname = 'btree'
> -- Don't check temp tables, which may be from another session:
> AND c.relpersistence != 't'
> AND i.indisready AND i.indisvalid
> ORDER BY c.relpages DESC;
> 
> However, when I went to connect to template1, it failed:
> 
> # \connect template1
> FATAL:  could not open file "base/1/2610": No such file or directory
> Previous connection kept
> 
> If template1 is corrupt, that would explain why I am unable to create new databases.

Right.  I think that dumping what you can and importing it in a new cluster
is the way to go.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

>>Does it mean that GIN is a very good choice for low cardinality columns.  
>Not necessary. There is other index which also don’t keep column value in an every leaf. Hash, for instance. 

Well I asked about GIN's usefulness for low cardinality.  Good to know that Hash can also be considered.
BTW until recently wasn't Hash unsafe for production since it was not WAL logged.