A PostgreSQL instance (or cluster) can contain many databases, three of them (template0, template1 and postgres) are there by default. Over the last years we trained many people on PostgreSQL Essentials and there have been mainly two points that needed more clarification when it comes to catalogs and the postgres default database:

  1. Does the postgres default database define the catalog and somehow is the master database?
  2. What exactly is in the global catalog?

In this post we’ll look into both points and I hope to make it more clear what the shared/global catalog contains, and that the postgres default database is not a master database and it does not define the postgres catalog.

For the first point (is the default postgres database a master database and does it define the catalog?) the answer can quite easily be given. The default postgres database is there for only one reason: Because most client utilities assume it is there, and by default connect into that database. But this does not mean, that the default postgres is any special, you can go well ahead and drop it:

postgres=# l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=# c template1
You are now connected to database "template1" as user "postgres".
template1=# drop database postgres;
DROP DATABASE
template1=# l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(2 rows)

template1=#

We even have customers which do that by default. The default postgres database is nothing special and initially it is exactly the same as template1. You can easily re-create, it if you want:

template1=# create database postgres;
CREATE DATABASE
template1=# l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

This answers the first question: The default postgres database is not a master database and it does not define the PostgreSQL catalog. Again, check here if you want to have more details about the three default databases.

The second question can be answered easily as well: What exactly is in the global/shared catalog? Most of the PostgreSQL catalog tables are per database, such as pg_tables:

postgres=# d pg_tables
              View "pg_catalog.pg_tables"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 schemaname  | name    |           |          | 
 tablename   | name    |           |          | 
 tableowner  | name    |           |          | 
 tablespace  | name    |           |          | 
 hasindexes  | boolean |           |          | 
 hasrules    | boolean |           |          | 
 hastriggers | boolean |           |          | 
 rowsecurity | boolean |           |          |

All these catalog tables and views are in a system schema called “pg_catalog”. This schema is not listed by default when you use the “dn” shortcut in psql:

postgres=# dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

You need to add “S” for system, to list the system schemas:

postgres=# dnS
        List of schemas
        Name        |  Owner   
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 public             | postgres
(4 rows)

Some catalog tables/views are global to the cluster/instance and are not per database. The obvious ones are users/roles and tablespaces. None of them are per database as users/roles can have access to various databases and various databases can store relations in the same tablespace. The question now is: How can I know if a catalog table/view is global or per database? Even global catalog tables/views are listed in the local catalog schema:

postgres=# d pg_catalog.pg_roles
                         View "pg_catalog.pg_roles"
     Column     |           Type           | Collation | Nullable | Default 
----------------+--------------------------+-----------+----------+---------
 rolname        | name                     |           |          | 
 rolsuper       | boolean                  |           |          | 
 rolinherit     | boolean                  |           |          | 
 rolcreaterole  | boolean                  |           |          | 
 rolcreatedb    | boolean                  |           |          | 
 rolcanlogin    | boolean                  |           |          | 
 rolreplication | boolean                  |           |          | 
 rolconnlimit   | integer                  |           |          | 
 rolpassword    | text                     |           |          | 
 rolvaliduntil  | timestamp with time zone |           |          | 
 rolbypassrls   | boolean                  |           |          | 
 rolconfig      | text[]                   | C         |          | 
 oid            | oid                      |           |          |

By only looking in the catalog schema we can not answer that question. What we can do, however, is to look at the data directory ($PGDATA). The databases are in “base” and the global/shared catalog is in “global”:

postgres@centos8pg:/home/postgres/ [pgdev] cd $PGDATA
postgres@centos8pg:/u02/pgdata/DEV/ [pgdev] ls -l | egrep "base|global"
drwx------. 6 postgres postgres    58 Nov 21 09:50 base
drwx------. 2 postgres postgres  4096 Nov 21 09:48 global

When we look into the “global” directory we’ll see a number of OIDs (object identifiers), this is how PostgreSQL internally is referencing the relations:

postgres@centos8pg:/u02/pgdata/DEV/ [pgdev] ls -l global/
total 564
-rw-------. 1 postgres postgres  8192 Nov 21 03:52 1213
-rw-------. 1 postgres postgres 24576 Nov 20 22:52 1213_fsm
-rw-------. 1 postgres postgres  8192 Nov 21 03:53 1213_vm
-rw-------. 1 postgres postgres  8192 Nov 20 22:52 1214
-rw-------. 1 postgres postgres 24576 Nov 20 22:52 1214_fsm
-rw-------. 1 postgres postgres  8192 Nov 20 22:52 1214_vm
-rw-------. 1 postgres postgres 16384 Nov 20 22:52 1232
-rw-------. 1 postgres postgres 16384 Nov 20 22:52 1233
-rw-------. 1 postgres postgres  8192 Nov 20 22:57 1260
-rw-------. 1 postgres postgres 24576 Nov 20 22:52 1260_fsm
-rw-------. 1 postgres postgres  8192 Nov 20 22:52 1260_vm
...

Each of these OIDs is one relation of the global/shared catalog. As we are not interested in the visibility maps and free space maps let’s exclude them, and only list the unique OIDs:

postgres@centos8pg:/u02/pgdata/DEV/ [pgdev] ls -l global/ | awk -F " " '{print $9}' | egrep "^[0-9]" | egrep -v "fsm|vm"
1213
1214
1232
1233
1260
1261
1262
2396
2397
2671
2672
2676
2677
2694
2695
2697
2698
2846
2847
2964
2965
2966
2967
3592
3593
4060
4061
4175
4176
4177
4178
4181
4182
4183
4184
4185
4186
6000
6001
6002
6100
6114
6115

These are the relations in the global/shared catalog. For translating these OIDs into human readable names there is oid2name. Without any additional parameters oid2name will give you the name of the databases listed in the “base” directory:

postgres@centos8pg:/u02/pgdata/DEV/ [pgdev] oid2name 
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  24616       postgres  pg_default
  12905      template0  pg_default
      1      template1  pg_default

We can also pass the OIDs of the shared/global catalog to oid2name and the result will answer the second question: What, exactly, is in the global/shared catalog?

postgres@centos8pg:/u02/pgdata/DEV/ [pgdev] for i in `ls -l global/ | awk -F " " '{print $9}' | egrep "^[0-9]" | egrep -v "fsm|vm"`; do oid2name -x -S -q -o $i; done | grep -v "index"
      1213  pg_tablespace  1213  pg_catalog   pg_global
      1214  pg_shdepend  1214  pg_catalog   pg_global
      1260   pg_authid  1260  pg_catalog   pg_global
      1261  pg_auth_members  1261  pg_catalog   pg_global
      1262  pg_database  1262  pg_catalog   pg_global
      2396  pg_shdescription  2396  pg_catalog   pg_global
      2846  pg_toast_2396  2846  pg_toast   pg_global
      2964  pg_db_role_setting  2964  pg_catalog   pg_global
      2966  pg_toast_2964  2966  pg_toast   pg_global
      3592  pg_shseclabel  3592  pg_catalog   pg_global
      4060  pg_toast_3592  4060  pg_toast   pg_global
      4175  pg_toast_1260  4175  pg_toast   pg_global
      4177  pg_toast_1262  4177  pg_toast   pg_global
      4181  pg_toast_6000  4181  pg_toast   pg_global
      4183  pg_toast_6100  4183  pg_toast   pg_global
      4185  pg_toast_1213  4185  pg_toast   pg_global
      6000  pg_replication_origin  6000  pg_catalog   pg_global
      6100  pg_subscription  6100  pg_catalog   pg_global

Here is the answer (excluding the indexes). If we exclude the toast tables as well, you’ll notice that not many catalog tables/views are in the global/shared catalog:

postgres@centos8pg:/u02/pgdata/DEV/ [pgdev] for i in `ls -l global/ | awk -F " " '{print $9}' | egrep "^[0-9]" | egrep -v "fsm|vm"`; do oid2name -x -S -q -o $i; done | egrep -v "index|toast"
      1213  pg_tablespace  1213  pg_catalog   pg_global
      1214  pg_shdepend  1214  pg_catalog   pg_global
      1260   pg_authid  1260  pg_catalog   pg_global
      1261  pg_auth_members  1261  pg_catalog   pg_global
      1262  pg_database  1262  pg_catalog   pg_global
      2396  pg_shdescription  2396  pg_catalog   pg_global
      2964  pg_db_role_setting  2964  pg_catalog   pg_global
      3592  pg_shseclabel  3592  pg_catalog   pg_global
      6000  pg_replication_origin  6000  pg_catalog   pg_global
      6100  pg_subscription  6100  pg_catalog   pg_global

That’s it, hope it helps.

Post Views: 2,730
Thumbnail [60x60]
by
Daniel Westermann