Sv: Re: Sv: Re: Does PostgreSQL check database integrity at startup?

[Andreas Joseph Krogh <andreas@xxxxxxxxxx>]

På søndag 31. desember 2017 kl. 00:49:31, skrev Stephen Frost <sfrost@xxxxxxxxxxx>:

* Andreas Joseph Krogh (andreas@xxxxxxxxxx) wrote:
>  SELECT
>    quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
>    s.setting || '/base/' || db.oid || '/' || c.relfilenode,
>    (pg_stat_file(s.setting || '/base/' || db.oid || '/' ||
> c.relfilenode)).size as size
>  FROM
>    pg_settings s
>    JOIN pg_database db on (s.name = 'data_directory')
>    JOIN pg_class c on (datname = current_database())
>    JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)
>  WHERE
>    relfilenode <> 0
>    AND nsp.nspname !~ '^pg_'
>    AND nsp.nspname <> 'information_schema';
>
>  Technically speaking, while these queries are correct for PG10, in prior
>  versions of PostgreSQL it's possible to have user schemas that begin
>  with 'pg_' and therefore the filtering in the WHERE clause would have to
>  be more specific.
>
>  Note that both of these need to be run as a superuser in older versions
>  of PG.  In PostgreSQL 10, a user could be GRANT'd 'pg_read_all_settings'
>  and be able to run the first query.  We don't currently support being
>  able to GRANT a non-superuser the ability to run pg_stat_file(), but
>  that will likely be coming in PG 11.
>
>  Thanks!
>  
> That doesn't seem to work with custom types:

Nothing in this query referred to types, so I'm not sure what custom
types would have to do with it..?

> andreak@[local]:5433 10.1 andreak=# SELECT                         
>   quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
>   s.setting || '/base/' || db.oid || '/' || c.relfilenode,
>   (pg_stat_file(s.setting || '/base/' || db.oid || '/' || c.relfilenode)).size
> as size
>  FROM  
>   pg_settings s  
>   JOIN pg_database db on (s.name = 'data_directory')  
>   JOIN pg_class c on (datname = current_database())  
>   JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)  
>  WHERE
>   relfilenode <> 0  
>   AND nsp.nspname !~ '^pg_'
>   AND nsp.nspname <> 'information_schema';
>  ERROR:  could not stat file
> "/home/andreak/programs/postgresql-10/data/base/22039391/22039392": No such
> file or directory
>   
> │ public.biginttuple2 │
> /home/andreak/programs/postgresql-10/data/base/22039391/22039392 │

Considering this is saaying 'no such file or directory', I'm guessing
that somehow your data directory isn't what is listed in pg_settings..?

Alternatively, perhaps that table was concurrently dropped?

Are you able to provide any specifics about your system?  Does the
database directory exist?  Does that path look reasonable?  I find it
kind of interesting that the OID of the database and the relfilenode are
so close together- exactly what did you do to test this query?

 

 

Here's a simple test-case:

 

createdb test
test=# create table foo(id serial primary key, name varchar not null);
test=# CREATE TYPE BigIntTuple2 AS (f1 bigint, f2 bigint);
 

SELECT
  quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
  s.setting || '/base/' || db.oid || '/' || c.relfilenode
FROM
  pg_settings s
  JOIN pg_database db on (s.name = 'data_directory')
  JOIN pg_class c on (datname = current_database())
  JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)
WHERE
  relfilenode <> 0
  AND nsp.nspname !~ '^pg_'
  AND nsp.nspname <> 'information_schema';
 

┌─────────────────────┬──────────────────────────────────────────────────────────────────┐
│      ?column?       │                             ?column?                             │
├─────────────────────┼──────────────────────────────────────────────────────────────────┤
│ public.foo_id_seq   │ /home/andreak/programs/postgresql-10/data/base/22058766/22058767 │
│ public.foo          │ /home/andreak/programs/postgresql-10/data/base/22058766/22058769 │
│ public.foo_pkey     │ /home/andreak/programs/postgresql-10/data/base/22058766/22058776 │
│ public.biginttuple2 │ /home/andreak/programs/postgresql-10/data/base/22058766/22058778 │
└─────────────────────┴──────────────────────────────────────────────────────────────────┘
 

 

SELECT
  quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
  s.setting || '/base/' || db.oid || '/' || c.relfilenode,
  (pg_stat_file(s.setting || '/base/' || db.oid || '/' || c.relfilenode)).size as size
FROM
  pg_settings s
  JOIN pg_database db on (s.name = 'data_directory')
  JOIN pg_class c on (datname = current_database())
  JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)
WHERE
  relfilenode <> 0
  AND nsp.nspname !~ '^pg_'
  AND nsp.nspname <> 'information_schema';
 

ERROR:  could not stat file "/home/andreak/programs/postgresql-10/data/base/22058766/22058778": No such file or directory
 

$ file /home/andreak/programs/postgresql-10/data/base/22058766/22058776
/home/andreak/programs/postgresql-10/data/base/22058766/22058776: lif file
 

 

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@xxxxxxxxxx

www.visena.com