Create and Verify a PostgreSQL Database with createdb

createdb creates one PostgreSQL database in a single command, ready to confirm and use.

This guide covers confirming it exists with the right owner, then removing it cleanly if it was only a test. It uses createdb 16.15, installed with PostgreSQL 16.15 on Ubuntu. Allow about ten minutes if the server and login details are already available.

1. Check the installed command and your target

You need the PostgreSQL client utilities, a running server, a role that can create databases, and a database name that is not already used in the target cluster. Creating a database is a server operation, not a local directory operation, so sudo is normally irrelevant. Use the PostgreSQL role and connection details that own or administer the target cluster.

$ createdb --version
createdb (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
$ createdb --help | sed -n '1,35p'

The exact package suffix can differ, but the major and minor version should match the installed binary. If the server is a different PostgreSQL major version, check its documentation before relying on version-specific options.

2. Create a database with an explicit name

Choose a name that does not already exist. The command normally makes the connecting role the owner. This changes shared server state, so stop before running it if you are unsure which cluster the connection settings select.

$ createdb --host=DB_HOST --port=5432 --username=DB_ROLE APP_DATABASE

A successful run normally prints nothing and exits with status zero. Capture the status when this is part of a script:

$ printf 'createdb exit status: %s\n' "$?"
createdb exit status: 0

If the server asks for a password, createdb can prompt automatically. Use --password when you want the prompt before the initial connection attempt, or --no-password in a batch job where an unexpected prompt must fail. Do not put a password in the command line or a shell history.

3. Verify the database and owner

Check the result through a separate connection to a maintenance database. Replace the placeholders with the same host, port and role used for creation. The psql query reads the cluster catalogue and does not change the database.

$ psql --host=DB_HOST --port=5432 --username=DB_ROLE --dbname=postgres \\
    --command="SELECT datname, pg_get_userbyid(datdba) AS owner FROM pg_database WHERE datname = 'APP_DATABASE';"
   datname    |  owner
--------------+----------
 APP_DATABASE | DB_ROLE
(1 row)

The spacing varies. The useful check is one row with the requested name and the expected owner. If the postgres database is unavailable, connect to template1 instead, unless that is the database you are creating.

A role creating a database for somebody else needs the required privilege. Use --owner=APP_OWNER only when the connecting role is authorised to assign that owner:

$ createdb --host=DB_HOST --username=ADMIN_ROLE --owner=APP_OWNER APP_DATABASE

Do not infer success from the absence of output. Verify the owner with the query above. A failure such as permission denied, database already exists, or could not connect identifies a different problem and does not justify retrying with sudo.

4. Choose a template deliberately

PostgreSQL builds the new database from a template. The normal choice is template1, which can contain site-local objects. Use template0 when you need the pristine template contents supplied by the PostgreSQL installation and do not want additions from template1 copied into the new database.

$ createdb --host=DB_HOST --username=DB_ROLE --template=template0 APP_DATABASE

Do not use a populated template as a shortcut for application migrations without checking what will be copied. PostgreSQL also requires the selected template to be usable for the operation; active sessions on a template can prevent creation. If this command fails, inspect the server error and resolve the template or connection issue rather than deleting unrelated databases.

5. Set an owner, description or encoding only when needed

Keep the first creation command small. Add one option at a time so a later failure has an obvious cause. A description is stored as a database comment:

$ createdb --host=DB_HOST --username=DB_ROLE \\
    --owner=APP_OWNER \\
    APP_DATABASE "Application database for the reporting service"

For a database with a specified encoding, use the server-supported name:

$ createdb --host=DB_HOST --username=DB_ROLE \\
    --encoding=UTF8 APP_DATABASE

Locale and collation choices are harder to change later and must be compatible with the selected locale provider and template. Use --locale, --lc-collate, --lc-ctype, --locale-provider or the ICU options only after checking the exact values available on the target server. Do not guess a locale from a workstation and assume the server has it.

6. Make connection defaults visible in scripts

createdb also reads PGHOST, PGPORT and PGUSER as connection defaults. PGDATABASE supplies the database name if the command line does not. That makes an unqualified command such as createdb depend on the current role and environment, so prefer an explicit database name in automation.

$ env | grep -E '^(PGHOST|PGPORT|PGUSER|PGDATABASE)=' || true
$ createdb --no-password --host=DB_HOST --port=5432 \\
    --username=DB_ROLE APP_DATABASE

Use --no-password only when a configured authentication method, such as an appropriate .pgpass entry, can provide credentials without prompting. Protect that file according to PostgreSQL's requirements; never replace it with a password in a shared script.

7. Recover from a test database

There is no rollback flag for a successful createdb. If this was a disposable test, first confirm the name and cluster, then remove exactly that database with dropdb:

$ dropdb --host=DB_HOST --port=5432 --username=DB_ROLE --if-exists APP_DATABASE
$ psql --host=DB_HOST --port=5432 --username=DB_ROLE --dbname=postgres \\
    --command="SELECT 1 FROM pg_database WHERE datname = 'APP_DATABASE';"
 ?column?
----------
(0 rows)

Warning: dropping a database removes its objects and data. It is destructive and is not a recovery method for a production mistake. Take the approved backup or restore path first, and never copy a production database name into a cleanup command without checking the target connection.

Done means