Postgres Pro
Downloads
Home   >   mailing lists

Thread: "Orphaned" files after initdb

"Orphaned" files after initdb

From
Magnus Hagander
Date:
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;

Which gives me:       f        |  size  |      modification
-----------------+--------+------------------------11809           |  65536 | 2013-04-01 20:34:45+0211809_fsm       |
24576| 2013-04-01 20:34:45+0211809_vm        |   8192 | 2013-04-01 20:34:45+0211811           |  16384 | 2013-04-01
20:34:45+0211812          |  32768 | 2013-04-01 20:34:45+0211821           | 344064 | 2013-04-01 20:34:45+0211821_fsm
   |  24576 | 2013-04-01 20:34:45+0211821_vm        |   8192 | 2013-04-01 20:34:45+0211823           | 131072 |
2013-04-0120:34:45+0211824           |  90112 | 2013-04-01 20:34:45+0211825           | 507904 | 2013-04-01
20:34:45+0211825_fsm      |  24576 | 2013-04-01 20:34:45+0211825_vm        |   8192 | 2013-04-01 20:34:45+0211827
   |      0 | 2013-04-01 20:34:45+0211829           |   8192 | 2013-04-01 20:34:45+0211830           |  73728 |
2013-04-0120:34:45+0211831           | 196608 | 2013-04-01 20:34:45+0211832           |  65536 | 2013-04-01
20:34:45+0211832_fsm      |  24576 | 2013-04-01 20:34:45+0211832_vm        |   8192 | 2013-04-01 20:34:45+0211834
   |  16384 | 2013-04-01 20:34:45+0211835           |  32768 | 2013-04-01 20:34:45+02pg_filenode.map |    512 |
2013-04-0120:34:45+02PG_VERSION      |      4 | 2013-04-01 20:34:44+02
 
(24 rows)



The filenode map and pg_version obviously make sense, but where are
the others from?

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

Re: "Orphaned" files after initdb

From
Andres Freund
Date:
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

Re: "Orphaned" files after initdb

From
Magnus Hagander
Date:
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 HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/

Re: "Orphaned" files after initdb

From
Andres Freund
Date:
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

Re: "Orphaned" files after initdb

From
Magnus Hagander
Date:
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 HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/