PostgreSQL DBA Part 8: Databases, Schemas and search_path

This is Part 8 of the PostgreSQL DBA series. In Part 7 we covered roles, users and privileges. Now we look at the containers those roles work in. The short answer to the most searched question first: in PostgreSQL a database is a fully separate container (one connection can only see one database), while a schema is a folder inside a database (one connection can use many schemas at once). Use separate databases for unrelated applications, and schemas to organise the parts of one application.

By the end of this part you will be able to create databases with the right owner and encoding, change per-database settings, build your own template database, organise tables into schemas, and control search_path for a session, a user or a whole database. Every command was run on Ubuntu with the PostgreSQL 18 cluster 18 main set up in Part 2.

What you will learn

• How a cluster, its databases and their schemas fit together, and when to choose a database or a schema
• How to create, configure, rename and drop databases with CREATE DATABASE, createdb, ALTER DATABASE and DROP DATABASE
• What template1 and template0 are, and how to build a template of your own
• How to create schemas, and why the public schema behaves differently since PostgreSQL 15
• How search_path decides which table an unqualified name points to, and how to set it permanently

Prerequisites

• Parts 1 to 7 of this series, especially Part 4 (psql meta-commands such as \l and \dn) and Part 7 (roles and GRANT)
• One Ubuntu server with PostgreSQL 18 from the PGDG repository and the cluster 18 main running (pg_lsclusters shows it online)
• Shell access as a user with sudo, so you can run sudo -u postgres psql
• The default Ubuntu pg_hba.conf (Part 6), which allows scram-sha-256 password logins over localhost

How it works

A PostgreSQL cluster is one running server: one data directory, one port, one set of processes. Think of it as an office building. Inside the building are several databases, which are like separate locked offices. Inside each office are schemas, which are like filing cabinets, and the tables are the files in those cabinets.

A few things belong to the whole building, not to any one office: roles (users and groups), the list of databases and tablespaces. That is why a role you create once can log in to any database it is allowed to. These shared objects live in the pg_global tablespace.

cluster "18 main"  (port 5432, /var/lib/postgresql/18/main)
|
|-- shared by all databases: roles, tablespaces, the list of databases
|     roles: postgres, app_owner, app_user
|
|-- template0      pristine copy, nobody may connect
|-- template1      copied by every CREATE DATABASE
|-- postgres       default database for admin tools
|
`-- appdb          (owner app_owner)
      |-- schema public
      |-- schema sales       -> tables orders, customers
      `-- schema reporting

A client connection names one database and can only read tables in it. Schemas are not separated this way: a user connected to appdb can join sales.orders with a table in reporting in one query, given the privileges.

New databases are not built from nothing. CREATE DATABASE copies an existing database, by default template1. Whatever you put in template1 appears in every new database. template0 is a clean copy that should never change, so you can always create a database with nothing extra in it.

Finally, when you write SELECT * FROM orders without a schema name, PostgreSQL looks through a list of schemas called the search path (search_path) and uses the first orders it finds.

One warning from the documentation: databases look isolated to users, but they share one write-ahead log (WAL), so a physical backup restores the whole cluster, not one database. Weigh that before putting many unrelated, critical applications in one cluster.

Creating and configuring databases

Step 1: List the databases and their template flags

On the database server, as the postgres OS user, open psql and list the databases. A new cluster always has three: postgres, template0 and template1.

sudo -u postgres psql -c '\l'
sudo -u postgres psql -c "SELECT datname, datistemplate, datallowconn, datconnlimit FROM pg_database ORDER BY oid;"

                                                 List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |         |         |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |         |         |        |           | postgres=CTc/postgres
(3 rows)

  datname  | datistemplate | datallowconn | datconnlimit
-----------+---------------+--------------+--------------
 template1 | t             | t            |           -1
 template0 | t             | f            |           -1
 postgres  | f             | t            |           -1
(3 rows)

The catalog pg_database has one row per database. datistemplate = t means the database is meant to be copied. datallowconn = f on template0 means nobody can connect to it, which protects it from changes. datconnlimit = -1 means no connection limit. Your Collate and Ctype columns show the locale chosen when the cluster was created, for example en_US.UTF-8 on many servers.

Check: try to connect to template0. The refusal is expected and shows the protection works.

sudo -u postgres psql -d template0 -c 'SELECT 1'

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "template0" is not currently accepting connections

Step 2: Create a database for an application

On the database server, as the postgres OS user, create an owner role and an application user (as in Part 7), then a database owned by the owner role. The owner of a database can drop it, change its settings and create schemas in it, so make the application's owner role the owner, not postgres.

-- sudo -u postgres psql
CREATE ROLE app_owner LOGIN PASSWORD 'Owner#2026';
CREATE ROLE app_user  LOGIN PASSWORD 'User#2026';
CREATE DATABASE appdb OWNER app_owner;
\l appdb

CREATE ROLE
CREATE ROLE
CREATE DATABASE
                                              List of databases
 Name  |   Owner   | Encoding | Locale Provider | Collate |  Ctype  | Locale | ICU Rules | Access privileges
-------+-----------+----------+-----------------+---------+---------+--------+-----------+-------------------
 appdb | app_owner | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |
(1 row)

The new database copied its encoding and locale from template1. An empty Access privileges column means the defaults: every role may connect (Part 7 showed how to revoke that).

You need superuser or the CREATEDB attribute to create a database. Undo: DROP DATABASE appdb; (see Step 15 for its warnings).

Step 3: Create a database from the shell with createdb

createdb is a small program that connects to the postgres database and runs CREATE DATABASE for you. It is handy in scripts. On the database server, as the postgres OS user:

sudo -u postgres createdb -e -O app_owner -T template0 -E UTF8 reportdb "Reporting copy for the BI team"

SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE reportdb OWNER app_owner ENCODING 'UTF8' TEMPLATE template0;
COMMENT ON DATABASE reportdb IS 'Reporting copy for the BI team';

With -e you see what createdb sent: it emptied its own search path (a safety habit), ran CREATE DATABASE and added the comment. Check it:

SELECT datname, shobj_description(oid, 'pg_database') AS description
  FROM pg_database
 WHERE datname = 'reportdb';

 datname  |          description
----------+--------------------------------
 reportdb | Reporting copy for the BI team
(1 row)

Undo: sudo -u postgres dropdb reportdb.

Step 4: Create a database with a different encoding (needs template0)

Sometimes an old application needs a database in another encoding, such as LATIN1. The encoding and locale of a new database must match its template, except when the template is template0. The reason: template1 might hold data or indexes that depend on its encoding; template0 holds none. On the server, in psql as postgres:

CREATE DATABASE legacydb ENCODING 'LATIN1' LOCALE 'C';
CREATE DATABASE legacydb TEMPLATE template0 ENCODING 'LATIN1' LOCALE 'C';

ERROR:  new encoding (LATIN1) is incompatible with the encoding of the template database (UTF8)
HINT:  Use the same encoding as in the template database, or use template0 as template.
CREATE DATABASE

The first command fails because template1 is UTF8. The second works. Verify:

SELECT datname, pg_encoding_to_char(encoding) AS encoding, datcollate
  FROM pg_database
 WHERE datname = 'legacydb';

 datname  | encoding | datcollate
----------+----------+------------
 legacydb | LATIN1   | C
(1 row)

Step 5: Set a connection limit and per-database defaults

ALTER DATABASE changes a database's attributes, and it can store default values for settings that apply to every new session in that database. A common DBA use is a statement timeout for an application database, so one runaway query cannot run for hours. On the server, in psql as postgres (or as the database owner):

ALTER DATABASE appdb CONNECTION LIMIT 50;
ALTER DATABASE appdb SET statement_timeout = '60s';
ALTER DATABASE appdb SET timezone = 'Asia/Kolkata';

SELECT datname, datconnlimit FROM pg_database WHERE datname = 'appdb';
\drds

ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
 datname | datconnlimit
---------+--------------
 appdb   |           50
(1 row)

            List of settings
 Role | Database |       Settings
------+----------+-----------------------
      | appdb    | statement_timeout=60s+
      |          | TimeZone=Asia/Kolkata
(1 row)

The setting is saved but not applied to sessions that are already connected. It takes effect in new connections. No reload or restart is needed. Check from fresh connections:

sudo -u postgres psql -d appdb -c "SHOW statement_timeout" -c "SHOW timezone"
sudo -u postgres psql -d postgres -c "SHOW statement_timeout" -c "SHOW timezone"

 statement_timeout
-------------------
 1min
(1 row)

   TimeZone
--------------
 Asia/Kolkata
(1 row)

 statement_timeout
-------------------
 0
(1 row)

 TimeZone
----------
 Etc/UTC
(1 row)

In appdb the defaults apply; the postgres database still uses the values from postgresql.conf. Undo one setting with ALTER DATABASE appdb RESET timezone;, all of them with ALTER DATABASE appdb RESET ALL;, and the limit with ALTER DATABASE appdb CONNECTION LIMIT -1;.

Template databases

Because every new database is a copy, you can prepare a template once. Adding objects to template1 puts them in every new database, including ones you restore dumps into, which causes conflicts. A separate, named template is cleaner.

Step 6: Build your own template database

On the server, in psql as postgres. We create a database, add an audit schema and table that every branch database should have, then mark it as a template and block connections so nobody changes it by accident.

CREATE DATABASE company_template;
\c company_template
CREATE SCHEMA audit;
CREATE TABLE audit.change_log (
  log_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name text NOT NULL,
  changed_by text NOT NULL DEFAULT current_user,
  changed_at timestamptz NOT NULL DEFAULT now()
);
\c postgres
ALTER DATABASE company_template IS_TEMPLATE true ALLOW_CONNECTIONS false;
SELECT datname, datistemplate, datallowconn FROM pg_database ORDER BY datname;

     datname      | datistemplate | datallowconn
------------------+---------------+--------------
 appdb            | f             | t
 company_template | t             | f
 legacydb         | f             | t
 postgres         | f             | t
 reportdb         | f             | t
 template0        | t             | f
 template1        | t             | t
(7 rows)

company_template now behaves like template0: copyable, closed to connections. To change it later, set ALLOW_CONNECTIONS true, make the change, then set it back.

Step 7: Create a database from the template

CREATE DATABASE branch_mumbai TEMPLATE company_template OWNER app_owner;
\c branch_mumbai
\dt audit.*

CREATE DATABASE
You are now connected to database "branch_mumbai" as user "postgres".
             List of tables
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 audit  | change_log | table | postgres
(1 row)

The audit table came with the copy. Notice its owner is still postgres, even though app_owner owns the database: objects keep the owner they had in the template. Database-level GRANTs and ALTER DATABASE ... SET values are not copied either; you set those again on each new database.

Schemas

Step 8: Look at the public schema in a new database

Every new database has a schema called public. On the server, as the postgres OS user:

sudo -u postgres psql -d appdb -c '\dn+'

                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(1 row)

Read the Access privileges like this: pg_database_owner (a built-in role that always means "whoever owns this database", here app_owner) has U (USAGE) and C (CREATE). Everyone else (=U) has only USAGE: they can use objects there if granted, but cannot create anything.

Step 9: Create schemas and tables as the database owner

Now log in as app_owner over localhost (psql asks Password for user app_owner:). Creating schemas as the owner role means the owner role owns them, which keeps later GRANTs simple.

psql -h localhost -U app_owner -d appdb

CREATE SCHEMA sales;
CREATE SCHEMA reporting;

CREATE TABLE sales.orders (
  order_id  int PRIMARY KEY,
  customer  text NOT NULL,
  amount    numeric(10,2) NOT NULL
);
INSERT INTO sales.orders VALUES
  (1, 'Asha Traders', 2500.00),
  (2, 'Ravi Stores', 1200.50);

-- an old test table with the same name, left in public
CREATE TABLE public.orders (
  order_id  int PRIMARY KEY,
  note      text
);
INSERT INTO public.orders VALUES (99, 'old test table in public');

\dn

CREATE SCHEMA
CREATE SCHEMA
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 1
        List of schemas
   Name    |       Owner
-----------+-------------------
 public    | pg_database_owner
 reporting | app_owner
 sales     | app_owner
(3 rows)

Two tables called orders now exist; names only need to be unique inside a schema. Names starting with pg_ are reserved for the system. To create a schema owned by another role, use CREATE SCHEMA name AUTHORIZATION role; (Step 13 uses this).

The search_path

Step 10: See which table an unqualified name finds

Still connected as app_owner to appdb:

SHOW search_path;
SELECT current_schema(), current_schemas(true);
SELECT * FROM orders;
SELECT * FROM sales.orders;

   search_path
-----------------
 "$user", public
(1 row)

 current_schema |   current_schemas
----------------+---------------------
 public         | {pg_catalog,public}
(1 row)

 order_id |           note
----------+--------------------------
       99 | old test table in public
(1 row)

 order_id |   customer   | amount
----------+--------------+---------
        1 | Asha Traders | 2500.00
        2 | Ravi Stores  | 1200.50
(2 rows)

The default search path is "$user", public. $user means "a schema with the same name as the current user". There is no schema called app_owner, so that entry is skipped silently. The first schema that exists, public, is the current schema: unqualified names are looked up there, and new tables are created there.

current_schemas(true) shows the path as really used, including pg_catalog (built-in tables and functions), which is always searched first unless you list it elsewhere. So SELECT * FROM orders found the old test table, not the real orders. Writing sales.orders (a qualified name) always finds the intended table.

Step 11: Change the search path for one session

SET search_path TO sales, public;
SELECT current_schema();
SELECT * FROM orders;
CREATE TABLE customers (customer_id int PRIMARY KEY, name text);
\dt *.customers

 current_schema
----------------
 sales
(1 row)

 order_id |   customer   | amount
----------+--------------+---------
        1 | Asha Traders | 2500.00
        2 | Ravi Stores  | 1200.50
(2 rows)

             List of tables
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+-----------
 sales  | customers | table | app_owner
(1 row)

Now orders means sales.orders, and the new table went into sales, the first schema in the list. SET lasts only until you disconnect (or RESET search_path). Applications need something permanent.

Step 12: Make the search path permanent

First give app_user access to the sales table (as app_owner, as in Part 7), and see what app_user gets with the default path:

GRANT USAGE ON SCHEMA sales TO app_user;
GRANT SELECT ON sales.orders TO app_user;

psql -h localhost -U app_user -d appdb -c "SHOW search_path" -c "SELECT * FROM orders;"

   search_path
-----------------
 "$user", public
(1 row)

ERROR:  permission denied for table orders

This error confuses many people: the GRANT worked, but the unqualified name found public.orders, which app_user has no rights on. The fix is the right search path. Set it for the role in this database only. On the server, in psql as postgres:

ALTER ROLE app_user IN DATABASE appdb SET search_path = sales, public;
\drds

                List of settings
   Role   | Database |         Settings
----------+----------+---------------------------
 app_user | appdb    | search_path=sales, public
          | appdb    | statement_timeout=60s    +
          |          | TimeZone=Asia/Kolkata
(2 rows)

Verify from new connections as app_user and as app_owner:

psql -h localhost -U app_user -d appdb -c "SHOW search_path" -c "SELECT order_id, customer FROM orders;"
psql -h localhost -U app_owner -d appdb -c "SHOW search_path"

  search_path
---------------
 sales, public
(1 row)

 order_id |   customer
----------+--------------
        1 | Asha Traders
        2 | Ravi Stores
(2 rows)

   search_path
-----------------
 "$user", public
(1 row)

Only app_user in appdb is affected. You can set the search path at several levels; the most specific one wins when the session starts:

The connection-level option is useful for tools and applications that let you edit the connection string but not run SQL first:

psql "host=localhost dbname=appdb user=app_user options=-csearch_path=public" -c "SHOW search_path"
PGOPTIONS="-c search_path=public" psql -h localhost -U app_user -d appdb -c "SHOW search_path"

 search_path
-------------
 public
(1 row)

 search_path
-------------
 public
(1 row)

Undo the role setting with ALTER ROLE app_user IN DATABASE appdb RESET search_path;. Role and database settings are applied only at login: SET ROLE app_user inside an existing session does not load them.

Step 13: Give each user a private schema with $user

The default path starts with $user for a reason: if a user has a schema with their own name, they use it automatically. This is the secure pattern the documentation recommends for databases with several untrusted users. As postgres, create the schema, then remove the Step 12 setting so app_user uses the default path again:

sudo -u postgres psql -d appdb -c "CREATE SCHEMA AUTHORIZATION app_user;"
sudo -u postgres psql -c "ALTER ROLE app_user IN DATABASE appdb RESET search_path;"

CREATE SCHEMA AUTHORIZATION app_user without a schema name creates a schema named after the role and owned by it. Now connect as app_user:

SHOW search_path;
SELECT current_schema(), current_schemas(false);
CREATE TABLE scratch (id int);
\dt *.scratch

   search_path
-----------------
 "$user", public
(1 row)

 current_schema |  current_schemas
----------------+-------------------
 app_user       | {app_user,public}
(1 row)

CREATE TABLE
            List of tables
  Schema  |  Name   | Type  |  Owner
----------+---------+-------+----------
 app_user | scratch | table | app_user
(1 row)

Renaming and dropping

Step 14: Rename a database

On the server, in psql as postgres, connected to a different database than the one you rename:

ALTER DATABASE branch_mumbai RENAME TO branch_pune;

ALTER DATABASE

Only the owner (who also needs CREATEDB) or a superuser can rename a database. Renaming the database you are connected to fails with ERROR: current database cannot be renamed. Update connection strings and pg_hba.conf lines that name it. Check with \l branch_pune.

Step 15: Drop a schema and a database

As app_owner in appdb, a schema with objects in it cannot be dropped without CASCADE:

DROP SCHEMA reporting;
DROP SCHEMA sales;
DROP SCHEMA sales CASCADE;

DROP SCHEMA
ERROR:  cannot drop schema sales because other objects depend on it
DETAIL:  table sales.orders depends on schema sales
table sales.customers depends on schema sales
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table sales.orders
drop cascades to table sales.customers
DROP SCHEMA

The empty reporting schema dropped at once. For sales, read the DETAIL list before you add CASCADE; it is exactly what you will lose.

Dropping a database fails while anyone is connected. In this test app_user had a session open in a scratch database testdb (created with sudo -u postgres createdb testdb). As postgres, connected to the postgres database:

DROP DATABASE testdb;
SELECT pid, usename, datname, state FROM pg_stat_activity WHERE datname = 'testdb';
DROP DATABASE testdb WITH (FORCE);

ERROR:  database "testdb" is being accessed by other users
DETAIL:  There is 1 other session using the database.
 pid  | usename  | datname | state
------+----------+---------+--------
 1760 | app_user | testdb  | active
(1 row)

DROP DATABASE

pg_stat_activity shows who is connected (Part 10 covers it in depth). WITH (FORCE) tries to terminate those sessions first; it fails if prepared transactions, active logical replication slots or subscriptions exist in the database. Verify with \l testdb, which should list 0 rows. From the shell, dropdb testdb does the same, and dropdb --force testdb adds FORCE.

Common mistakes and how to fix them

ERROR: permission denied for table orders (but you granted SELECT)

The unqualified name resolved to a different table, such as public.orders, through the search path. Run SELECT current_schemas(true); as that user, then either qualify the name (sales.orders) or set the path with ALTER ROLE ... IN DATABASE ... SET search_path (Step 12).

ERROR: permission denied for schema public

On PostgreSQL 15 and later only the database owner can create in public. Create a schema for the application owned by the role that creates tables, or give the user a private schema (CREATE SCHEMA AUTHORIZATION app_user;). Granting CREATE on public to everyone brings back the old, insecure behaviour.

ERROR: no schema has been selected to create in

Every schema in the search path is missing or not usable. Names in the path that do not exist, or where the user lacks USAGE, are silently ignored, so a typo gives an empty path:

SET search_path TO saels;
SELECT current_schemas(false);
CREATE TABLE t1 (id int);

 current_schemas
-----------------
 {}
(1 row)

ERROR:  no schema has been selected to create in

Fix the spelling, or grant USAGE on the schema. With an empty path, SELECT * FROM orders fails with ERROR: relation "orders" does not exist even though the table exists.

ERROR: source database "template1" is being accessed by other users

Someone (often a GUI tool) is connected to the template. Find them with SELECT pid, usename, application_name FROM pg_stat_activity WHERE datname = 'template1';, ask them to disconnect, and never point tools at template1.

ERROR: new encoding (LATIN1) is incompatible with the encoding of the template database (UTF8)

Add TEMPLATE template0 (or -T template0 for createdb), as the HINT says. The same applies to a different locale.

ERROR: CREATE DATABASE cannot run inside a transaction block

Your tool or script wrapped the command in BEGIN. Run CREATE and DROP DATABASE on their own with autocommit on, and do not use psql -1 for such scripts.

Practice exercise

1. Create a database hrdb from company_template, owned by app_owner, with at most 20 connections. 2. In it, create a schema hr owned by app_owner, and make hr, public the search path for everyone who connects to hrdb. Verify both as app_owner.

Quick reference

# list databases and template flags
sudo -u postgres psql -c '\l'
sudo -u postgres psql -c "SELECT datname, datistemplate, datallowconn, datconnlimit FROM pg_database;"

# create a database owned by the application owner role
sudo -u postgres psql -c "CREATE DATABASE appdb OWNER app_owner;"

# same from the shell, from template0 with an explicit encoding
sudo -u postgres createdb -e -O app_owner -T template0 -E UTF8 reportdb "Reporting copy"

# other encoding or locale: template0 is required
sudo -u postgres psql -c "CREATE DATABASE legacydb TEMPLATE template0 ENCODING 'LATIN1' LOCALE 'C';"

# connection limit and per-database defaults (new sessions only)
sudo -u postgres psql -c "ALTER DATABASE appdb CONNECTION LIMIT 50;"
sudo -u postgres psql -c "ALTER DATABASE appdb SET statement_timeout = '60s';"
sudo -u postgres psql -c '\drds'
sudo -u postgres psql -c "ALTER DATABASE appdb RESET ALL;"

# turn a database into a closed template, then clone it
sudo -u postgres psql -c "ALTER DATABASE company_template IS_TEMPLATE true ALLOW_CONNECTIONS false;"
sudo -u postgres psql -c "CREATE DATABASE branch_mumbai TEMPLATE company_template OWNER app_owner;"

# schemas
psql -h localhost -U app_owner -d appdb -c "CREATE SCHEMA sales;"
sudo -u postgres psql -d appdb -c "CREATE SCHEMA AUTHORIZATION app_user;"
sudo -u postgres psql -d appdb -c '\dn+'

# inspect and set the search path
psql -h localhost -U app_user -d appdb -c "SHOW search_path" -c "SELECT current_schemas(true);"
sudo -u postgres psql -c "ALTER ROLE app_user IN DATABASE appdb SET search_path = sales, public;"
sudo -u postgres psql -c "ALTER DATABASE appdb SET search_path = sales, public;"
PGOPTIONS="-c search_path=sales" psql -h localhost -U app_user -d appdb

# rename and drop (destructive: check names and backups first)
sudo -u postgres psql -c "ALTER DATABASE branch_mumbai RENAME TO branch_pune;"
psql -h localhost -U app_owner -d appdb -c "DROP SCHEMA reporting;"
sudo -u postgres psql -c "DROP DATABASE testdb WITH (FORCE);"

Summary and what's next

• A cluster holds databases; a database holds schemas; roles are shared by the whole cluster
• Use separate databases for unrelated applications and schemas for parts of one application
• Create each database with OWNER set to the application owner role, and keep CREATE DATABASE outside transactions
• Use TEMPLATE template0 for a different encoding or locale, and for restoring pg_dump files
• Keep template1 clean; put shared starting objects in a named template marked IS_TEMPLATE true and ALLOW_CONNECTIONS false
• On PostgreSQL 15 and later only the database owner can create in public; give applications their own schemas
• Set search_path with ALTER ROLE ... IN DATABASE ... SET, check it with current_schemas(true), and qualify names in scripts

In Part 9 we look at tablespaces and where PostgreSQL stores data on disk.