"Orphaned" files after initdb

Hi,

On 2013-04-01 20:43:36 +0200, Magnus Hagander wrote:

Maybe my thinking is just missing something obvious here. But looking
at a fresh initdb, I'm seeing a number of files in the data directory
for this database that don't have corresponding entries in pg_class. I
looked for entries based on relfilenode in pg_class - am I missing
some other way we put files in there?

I'm seeing the same on both head and 9.1 as an example, but different oids.

I feel I'm either missing something obvious, or we have a problem
here? And somehow the first one seems more likely..

The query I ran (yeah, i'm sure it can be written prettier, but this
was quick-n-dirty):

with t as (
select * from pg_ls_dir('<datadir>/base/1') l(f)
where f not in (
select relfilenode::text from pg_class
union all select relfilenode::text || '_vm' from pg_class
union all select relfilenode::text || '_fsm' from pg_class)
)
select f,
size,
modification
from t, pg_stat_file('<datadir>/base/1/' || f)
order by 1;

You're missing nailed tables which don't have a valid relfilenode (but
InvalidOid/0) in pg_class.relfilenode. You need to query the refilenode like
SELECT pg_relation_filenode(pg_class.oid) FROM pg_class.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Apr 1, 2013 at 9:09 PM, Andres Freund <andres@2ndquadrant.com> wrote:

Hi,

On 2013-04-01 20:43:36 +0200, Magnus Hagander wrote:

Maybe my thinking is just missing something obvious here. But looking
at a fresh initdb, I'm seeing a number of files in the data directory
for this database that don't have corresponding entries in pg_class. I
looked for entries based on relfilenode in pg_class - am I missing
some other way we put files in there?

I'm seeing the same on both head and 9.1 as an example, but different oids.

I feel I'm either missing something obvious, or we have a problem
here? And somehow the first one seems more likely..

The query I ran (yeah, i'm sure it can be written prettier, but this
was quick-n-dirty):

with t as (
select * from pg_ls_dir('<datadir>/base/1') l(f)
where f not in (
select relfilenode::text from pg_class
union all select relfilenode::text || '_vm' from pg_class
union all select relfilenode::text || '_fsm' from pg_class)
)
select f,
size,
modification
from t, pg_stat_file('<datadir>/base/1/' || f)
order by 1;

You're missing nailed tables which don't have a valid relfilenode (but
InvalidOid/0) in pg_class.relfilenode. You need to query the refilenode like
SELECT pg_relation_filenode(pg_class.oid) FROM pg_class.

Ha. Of course. I knew it was me getting it wrong.

You'd get extra happy if only you knew how many pretty senior pg folks
i pestered about that one on irc before posting here :D

Thanks!

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-04-01 21:24:06 +0200, Magnus Hagander wrote:

On Mon, Apr 1, 2013 at 9:09 PM, Andres Freund <andres@2ndquadrant.com> wrote:

Hi,

On 2013-04-01 20:43:36 +0200, Magnus Hagander wrote:

Maybe my thinking is just missing something obvious here. But looking
at a fresh initdb, I'm seeing a number of files in the data directory
for this database that don't have corresponding entries in pg_class. I
looked for entries based on relfilenode in pg_class - am I missing
some other way we put files in there?

I'm seeing the same on both head and 9.1 as an example, but different oids.

I feel I'm either missing something obvious, or we have a problem
here? And somehow the first one seems more likely..

The query I ran (yeah, i'm sure it can be written prettier, but this
was quick-n-dirty):

with t as (
select * from pg_ls_dir('<datadir>/base/1') l(f)
where f not in (
select relfilenode::text from pg_class
union all select relfilenode::text || '_vm' from pg_class
union all select relfilenode::text || '_fsm' from pg_class)
)
select f,
size,
modification
from t, pg_stat_file('<datadir>/base/1/' || f)
order by 1;

You're missing nailed tables which don't have a valid relfilenode (but
InvalidOid/0) in pg_class.relfilenode. You need to query the refilenode like
SELECT pg_relation_filenode(pg_class.oid) FROM pg_class.

Ha. Of course. I knew it was me getting it wrong.

You'd get extra happy if only you knew how many pretty senior pg folks
i pestered about that one on irc before posting here :D

Most of those probably didn't spend too much time on developing wal->logical
changes transformations ;)

For some real reason to send this email: Even if you add pg_relation_filenode()
to the above query, its still not correct. You also need to disambiguate by
tablespace. Just for the case youre using it for something more interesting
than a freshly initdbed cluster.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Apr 1, 2013 at 10:53 PM, Andres Freund <andres@2ndquadrant.com> wrote:

On 2013-04-01 21:24:06 +0200, Magnus Hagander wrote:

On Mon, Apr 1, 2013 at 9:09 PM, Andres Freund <andres@2ndquadrant.com> wrote:

Hi,

On 2013-04-01 20:43:36 +0200, Magnus Hagander wrote:

Maybe my thinking is just missing something obvious here. But looking
at a fresh initdb, I'm seeing a number of files in the data directory
for this database that don't have corresponding entries in pg_class. I
looked for entries based on relfilenode in pg_class - am I missing
some other way we put files in there?

I'm seeing the same on both head and 9.1 as an example, but different oids.

I feel I'm either missing something obvious, or we have a problem
here? And somehow the first one seems more likely..

The query I ran (yeah, i'm sure it can be written prettier, but this
was quick-n-dirty):

with t as (
select * from pg_ls_dir('<datadir>/base/1') l(f)
where f not in (
select relfilenode::text from pg_class
union all select relfilenode::text || '_vm' from pg_class
union all select relfilenode::text || '_fsm' from pg_class)
)
select f,
size,
modification
from t, pg_stat_file('<datadir>/base/1/' || f)
order by 1;

You're missing nailed tables which don't have a valid relfilenode (but
InvalidOid/0) in pg_class.relfilenode. You need to query the refilenode like
SELECT pg_relation_filenode(pg_class.oid) FROM pg_class.

Ha. Of course. I knew it was me getting it wrong.

You'd get extra happy if only you knew how many pretty senior pg folks
i pestered about that one on irc before posting here :D

Most of those probably didn't spend too much time on developing wal->logical
changes transformations ;)

For some real reason to send this email: Even if you add pg_relation_filenode()
to the above query, its still not correct. You also need to disambiguate by
tablespace. Just for the case youre using it for something more interesting
than a freshly initdbed cluster.

Right. I was, but with a single tablespace :)

You also need to add a IS NOT NULL to the definitions, or it's always
going to return zero rows (just in case somebody is picking up the
query)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers