Thread: Re: Does PostgreSQL check database integrity at startup?
Attachment
Attachment
Attachment
Attachment
Attachment
Attachment
Attachment
Attachment
>There should be a "catalog" that point where tables are stored in physical files...
Here is the query that gives you that information.SELECT c.oid,
n.nspname as schema,
c.relname as table,
(SELECT oid FROM pg_database WHERE datname = current_database() ) as db_dir,
c.relfilenode as filename
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind = 'r'
ORDER BY 2, relname;
On Fri, Dec 29, 2017 at 8:13 PM, Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* Edson Carlos Ericksson Richter (richter@simkorp.com.br) wrote:
> There should be a "catalog" that point where tables are stored in physical
> files (I think, at least, because at some point PostgreSQL need to know from
> where to read the data).
Yes, it's pg_class. Specifically, the relfilenode.
> Based on information from this catalog, would I have a tool (perhaps, a C
> function) that check that data is really there?
You could write such a function, but it wouldn't be able to be general
purpose as a zero-byte file is, in fact, a valid file. You could just
as easily do a 'select 1 from table limit 1;' and make sure that you get
back a successful, single-row, result, if you wish to verify that certain
tables in your database always have at least 1 row.
Thanks!
Stephen
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Attachment
On Fri, Dec 29, 2017 at 9:07 PM, Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* Melvin Davidson (melvin6925@gmail.com) wrote:
> >There should be a "catalog" that point where tables are stored in physical
> files...
>
> Here is the query that gives you that information.
>
> SELECT c.oid,
> n.nspname as schema,
> c.relname as table,
> (SELECT oid FROM pg_database WHERE datname =
> current_database() ) as db_dir,
> c.relfilenode as filename
> FROM pg_class c
> JOIN pg_namespace n ON (n.oid = c.relnamespace)
> WHERE relname NOT LIKE 'pg_%'
> AND relname NOT LIKE 'information%'
> AND relname NOT LIKE 'sql_%'
> AND relkind = 'r'
> ORDER BY 2, relname;
This isn't a terribly good query- it's entirely valid to have 'pg_blah'
and 'informationblah' tables in user schemas. If you'd like to filter
out the catalogs/internal schemas, filter based on schema name instead.
Also, this doesn't actually provide what Edson is asking for. Edson's
asking for a query that uses pg_ls_dir() or some custom function which
will run 'stat' on each file and return the size, according to the OS.
Thanks!
Stephen
Edson's original request was for a query that shows the FILENAMEs for the table.
As for "qood" query, that is entirely an opinion. The query WILL show all files associated
with ALL tables. You are free to edit and reconstruct as you choose.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Attachment
>the query is outright wrong.
Really? I submit a query to help and all you can do is criticize?If you don't like the query, write a better one yourself, but
kindly do not try to degrade the efforts of others.On Fri, Dec 29, 2017 at 9:22 PM, Stephen Frost <sfrost@snowman.net> wrote:
Melvin,
* Melvin Davidson (melvin6925@gmail.com) wrote:
> On Fri, Dec 29, 2017 at 9:07 PM, Stephen Frost <sfrost@snowman.net> wrote:> *Edson's original request was for a query that shows the FILENAMEs for the> > * Melvin Davidson (melvin6925@gmail.com) wrote:
> > > >There should be a "catalog" that point where tables are stored in
> > physical
> > > files...
> > >
> > > Here is the query that gives you that information.
> > >
> > > SELECT c.oid,
> > > n.nspname as schema,
> > > c.relname as table,
> > > (SELECT oid FROM pg_database WHERE datname =
> > > current_database() ) as db_dir,
> > > c.relfilenode as filename
> > > FROM pg_class c
> > > JOIN pg_namespace n ON (n.oid = c.relnamespace)
> > > WHERE relname NOT LIKE 'pg_%'
> > > AND relname NOT LIKE 'information%'
> > > AND relname NOT LIKE 'sql_%'
> > > AND relkind = 'r'
> > > ORDER BY 2, relname;
> >
> > This isn't a terribly good query- it's entirely valid to have 'pg_blah'
> > and 'informationblah' tables in user schemas. If you'd like to filter
> > out the catalogs/internal schemas, filter based on schema name instead.
> >
> > Also, this doesn't actually provide what Edson is asking for. Edson's
> > asking for a query that uses pg_ls_dir() or some custom function which
> > will run 'stat' on each file and return the size, according to the OS.
>
> table.*
As quoted previously, he request included:
---
> Based on information from this catalog, would I have a tool (perhaps, a C
> function) that check that data is really there?
---
Which is asking about having a function to 'stat' the files and check
their length ('data is really there').
> *As for "qood" query, that is entirely an opinion. The query WILL show all
> files associated*
> *with ALL tables. You are free to edit and reconstruct as you choose. *
No, it won't, it'll filter out tables which exist in user schemas that
happen to start with one of the strings that the query includes ('pg_',
'information', and 'sql_').
I encourage you to test it- create a table in the public schema called
'pg_whatever' and see if your query picks it up or not. This isn't a
minor complaint about style, the query is outright wrong.
Thanks!
Stephen
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Attachment
Attachment
On Sat, Dec 30, 2017 at 10:27 AM, Stephen Frost <sfrost@snowman.net> wrote:
Greetings Alban,
* Alban Hertroys (haramrae@gmail.com) wrote:
> In fact, I don't see it mentioned explicitly anywhere, but are we actually looking at a problem?
From the discussion in the google barman group, it's clear that the file
shouldn't be zero bytes in this specific case.
> I'm not convinced by the argument that a CRC check of a 0 byte file on a standby would not detect corruption. At the least, the CRC would be different or we would be left with a CRC on the master that we can't match any CRC's on the slave to if the file is larger on the master.
As discussed, files on the primary can be different at a byte level from
those on replicas and still be perfectly valid and correct, for a
variety of reasons from hint bit differences to differences due to the
replica not being at exactly the same point as the primary.
> If CRC's can be relied on to detect corruption (which they were designed to do), then that answers Edson's question.
The checksums included in PG are page-level and therefore there simply
isn't one to look at if the file is zero bytes.
Thanks!
Stephen
Steve Atkins and Stephen Frost,
You both remind me of gawkers at a disaster, pointing out "mistakes" that
first responders are making, but doing nothing yourselves to help.
My query works as designed and has done so for two years. It shows the
filenames for schemas and tables in the database. If you don't like it,
TOO BAD! Shame on you! Use your brains and write your own query.
Nuff said, I shall reply no more.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Attachment
På lørdag 30. desember 2017 kl. 23:06:52, skrev Stephen Frost <sfrost@snowman.net>:
Greetings,
* Melvin Davidson (melvin6925@gmail.com) wrote:
> My query works as designed and has done so
> for two years. It shows the filenames for schemas and tables in the
> database.
I'm glad to hear that it works in your specific use-case.
Unfortunately, it doesn't work in the general case and therefore isn't a
good example. A proper query to return the filename for each user table
in the current database is:
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';
Note that, as discussed earlier in this thread, this doesn't actually
answer what Edson was asking for. Here's the query that would answer
his original request:
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:
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
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 │
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
Attachment
På søndag 31. desember 2017 kl. 00:49:31, skrev Stephen Frost <sfrost@snowman.net>:
* Andreas Joseph Krogh (andreas@visena.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@[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);
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';
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 │
└─────────────────────┴──────────────────────────────────────────────────────────────────┘
│ ?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';
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
/home/andreak/programs/postgresql-10/data/base/22058766/22058776: lif file
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963