From: | Andreas Joseph Krogh <andreas(at)visena(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Sv: Re: Sv: Re: Does PostgreSQL check database integrity at startup? |
Date: | 2017-12-31 00:27:41 |
Message-ID: | VisenaEmail.0.3ba989d7b40a2db4.160a9f3d3f2@tc7-visena |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
På søndag 31. desember 2017 kl. 00:49:31, skrev Stephen Frost <
sfrost(at)snowman(dot)net <mailto:sfrost(at)snowman(dot)net>>:
* Andreas Joseph Krogh (andreas(at)visena(dot)com) 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(at)[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 <http://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 <http://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(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2017-12-31 01:03:15 | Re: Sv: Re: Sv: Re: Does PostgreSQL check database integrity at startup? |
Previous Message | Ertan Küçükoğlu | 2017-12-31 00:01:39 | RE: Change column type macaddr to macaddr[] |