Search code examples
postgresqlpgadmin

How is the Postgres 'system database' attribute set or unset?


One of several databases in a Postgres 10.15 cluster has been set to be a system database. The mechanism that caused this is unknown, though it may have happened at the same time that the database was converted to a template by updating the pg_database table. The pg_database table does not have a setting to control whether or not a database is a system database, however, and I cannot find any documentation describing how to set (or unset, which is what I really want to do) the system database flag.


Solution

  • The "system" attribute in pgAdmin is set for objects with an OID <= 16383. So a "system database" is one of the three initial database objects (initially named postgres, template0 and template1).

    You can't set or alter an OID. The closest you can do is to copy the "system" database, by using it as the template in CREATE DATABASE tmpdb TEMPLATE systemdb; and then renaming the old and new database. CREATE DATABASE assigns a new OID > 16383.