Re: "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