Infrastructure at your Service

Daniel Westermann

What are those *_init files for in PostgreSQL?

Quite some time ago I’ve written about relation files in PostgreSQL and when they get created. In that post we talked about the files containing the actual data, the free space map (fsm) and the visibility map (vm). But this is not all, there is another kind of file you might spot if you take a look into the directory of a database.

The directory of the postgres database of a fresh initialized PostgreSQL cluster looks like this:

[email protected]:/u02/pgdata/PGDEV/base/13540/ [pgdev] oid2name 
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  13540       postgres  pg_default
  13539      template0  pg_default
      1      template1  pg_default
[email protected]:/u02/pgdata/PGDEV/base/13540/ [pgdev] ls
112       1259       13366      13377  2337      2603      2607_fsm  2611      2617      2651  2662  2675  2688  2704      2833      2840      3085  3381      3455      3541      3600      3603_vm   3766  4151  4164  5002  6175
113       1259_fsm   13367      1417   2579      2603_fsm  2607_vm   2612      2617_fsm  2652  2663  2678  2689  2753      2834      2840_fsm  3118  3394      3456      3541_fsm  3600_fsm  3604      3767  4152  4165  548   6176
1247      1259_vm    13368      1418   2600      2603_vm   2608      2612_fsm  2617_vm   2653  2664  2679  2690  2753_fsm  2835      2840_vm   3119  3394_fsm  3456_fsm  3541_vm   3600_vm   3605      3997  4153  4166  549   826
1247_fsm  13358      13368_fsm  16387  2600_fsm  2604      2608_fsm  2612_vm   2618      2654  2665  2680  2691  2753_vm   2836      2841      3164  3394_vm   3456_vm   3542      3601      3606      4143  4154  4167  6102  827
1247_vm   13358_fsm  13368_vm   174    2600_vm   2605      2608_vm   2613      2618_fsm  2655  2666  2681  2692  2754      2836_fsm  2995      3256  3395      3466      3574      3601_fsm  3607      4144  4155  4168  6104  828
1249      13358_vm   13371      175    2601      2605_fsm  2609      2615      2618_vm   2656  2667  2682  2693  2755      2836_vm   2996      3257  3429      3467      3575      3601_vm   3608      4145  4156  4169  6106  pg_filenode.map
1249_fsm  13361      13372      2187   2601_fsm  2605_vm   2609_fsm  2615_fsm  2619      2657  2668  2683  2696  2756      2837      3079      3258  3430      3468      3576      3602      3609      4146  4157  4170  6110  pg_internal.init
1249_vm   13362      13373      2224   2601_vm   2606      2609_vm   2615_vm   2619_fsm  2658  2669  2684  2699  2757      2838      3079_fsm  3350  3431      3501      3596      3602_fsm  3712      4147  4158  4171  6111  PG_VERSION
1255      13363      13373_fsm  2228   2602      2606_fsm  2610      2616      2619_vm   2659  2670  2685  2701  2830      2838_fsm  3079_vm   3351  3433      3502      3597      3602_vm   3764      4148  4159  4172  6112
1255_fsm  13363_fsm  13373_vm   2328   2602_fsm  2606_vm   2610_fsm  2616_fsm  2620      2660  2673  2686  2702  2831      2838_vm   3080      3379  3439      3503      3598      3603      3764_fsm  4149  4160  4173  6113
1255_vm   13363_vm   13376      2336   2602_vm   2607      2610_vm   2616_vm   2650      2661  2674  2687  2703  2832      2839      3081      3380  3440      3534      3599      3603_fsm  3764_vm   4150  4163  4174  6117

You can see the files containing actual data (the numbers with any suffix), free space and visibility maps and three other files will not look at in this post (pg_filenode.map,pg_internal.init,PG_VERSION).

Lets assume the cluster is running for the time, the application got deployed and users are already working on it. For whatever reason you check the directory of the postgres database once more and now it looks like this:

[email protected]:/u02/pgdata/PGDEV/base/13540/ [pgdev] ls
112       1259       13366      13377       2224      2601_vm   2606      2609_vm   2615_vm   2619_fsm  2658  2669  2684  2699      2757      2838      3079_fsm  3350      3431      3501      3596      3602_fsm  3712      4147  4158  4171  6111              PG_VERSION
113       1259_fsm   13367      1417        2228      2602      2606_fsm  2610      2616      2619_vm   2659  2670  2685  2701      2830      2838_fsm  3079_vm   3351      3433      3502      3597      3602_vm   3764      4148  4159  4172  6112
1247      1259_vm    13368      1418        2328      2602_fsm  2606_vm   2610_fsm  2616_fsm  2620      2660  2673  2686  2702      2831      2838_vm   3080      3379      3439      3503      3598      3603      3764_fsm  4149  4160  4173  6113
1247_fsm  13358      13368_fsm  16387       2336      2602_vm   2607      2610_vm   2616_vm   2650      2661  2674  2687  2703      2832      2839      3081      3380      3440      3534      3599      3603_fsm  3764_vm   4150  4163  4174  6117
1247_vm   13358_fsm  13368_vm   16388       2337      2603      2607_fsm  2611      2617      2651      2662  2675  2688  2704      2833      2840      3085      3381      3455      3541      3600      3603_vm   3766      4151  4164  5002  6175
1249      13358_vm   13371      16388_init  2579      2603_fsm  2607_vm   2612      2617_fsm  2652      2663  2678  2689  2753      2834      2840_fsm  3118      3394      3456      3541_fsm  3600_fsm  3604      3767      4152  4165  548   6176
1249_fsm  13361      13372      16391       2600      2603_vm   2608      2612_fsm  2617_vm   2653      2664  2679  2690  2753_fsm  2835      2840_vm   3119      3394_fsm  3456_fsm  3541_vm   3600_vm   3605      3997      4153  4166  549   826
1249_vm   13362      13373      16391_init  2600_fsm  2604      2608_fsm  2612_vm   2618      2654      2665  2680  2691  2753_vm   2836      2841      3164      3394_vm   3456_vm   3542      3601      3606      4143      4154  4167  6102  827
1255      13363      13373_fsm  174         2600_vm   2605      2608_vm   2613      2618_fsm  2655      2666  2681  2692  2754      2836_fsm  2995      3256      3395      3466      3574      3601_fsm  3607      4144      4155  4168  6104  828
1255_fsm  13363_fsm  13373_vm   175         2601      2605_fsm  2609      2615      2618_vm   2656      2667  2682  2693  2755      2836_vm   2996      3257      3429      3467      3575      3601_vm   3608      4145      4156  4169  6106  pg_filenode.map
1255_vm   13363_vm   13376      2187        2601_fsm  2605_vm   2609_fsm  2615_fsm  2619      2657      2668  2683  2696  2756      2837      3079      3258      3430      3468      3576      3602      3609      4146      4157  4170  6110  pg_internal.init

In addition to the files we already know, there are two “_init” files. What are they used for? To answer that lets check the names of those relations using oid2name:

[email protected]:/u02/pgdata/PGDEV/base/13540/ [pgdev] oid2name -f 16388
From database "postgres":
  Filenode  Table Name
----------------------
     16388          t1
[email protected]:/u02/pgdata/PGDEV/base/13540/ [pgdev] oid2name -f 16391
From database "postgres":
  Filenode  Table Name
----------------------
     16391          t2

Asking pg_class what type of relation those are, tells as both are ordinary tables:

postgres=# select relkind from pg_class where relname in ('t1','t2');
 relkind 
---------
 r
 r
(2 rows)

But ordinary tables do not have any "*_init" files, do they? They have, as long as they are created as "unlogged" tables:

postgres=# \d t1
            Unlogged table "public.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 

postgres=# \d t2
            Unlogged table "public.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 

Each unlogged table automatically gets a so-called initialization fork:

postgres=# create unlogged table t3 ( a int );
CREATE TABLE
postgres=# select pg_relation_filepath('t3');
 pg_relation_filepath 
----------------------
 base/13540/16394
(1 row)

postgres=# \! ls -l $PGDATA/base/13540/16394*
-rw------- 1 postgres postgres 0 Aug 24 13:17 /u02/pgdata/PGDEV/base/13540/16394
-rw------- 1 postgres postgres 0 Aug 24 13:17 /u02/pgdata/PGDEV/base/13540/16394_init

What are those forks used for? What you need to know with unlogged tables is, that they are re-initialized when PostgreSQL crashes. First lets add some data to the unlogged table:

postgres=# insert into t3 select * from generate_series(1,1000);
INSERT 0 1000
postgres=# select count(*) from t3;
 count 
-------
  1000
(1 row)

Now lets crash PostgreSQL:

[email protected]:/home/postgres/ [pgdev] ps -ef | grep "postgres \-D"
postgres   35443       1  0 13:22 ?        00:00:00 /u01/app/postgres/product/DEV/db_0/bin/postgres -D /u02/pgdata/PGDEV
[email protected]:/home/postgres/ [pgdev] kill -9 35443

Starting up and checking the content of the tables confirms that it is empty:

[email protected]:/home/postgres/ [pgdev] pg_ctl start
[email protected]:/home/postgres/ [pgdev] psql -c "select count(*) from t3" postgres
 count 
-------
     0
(1 row)

What happened is, that PostgreSQL replaced the data file of that unlogged table with it’s initialization fork.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure