Prepare local PostgreSQL

Create and check the database used by a native development installation.

Use this page for the native installation or a Nix development shell. The container route already configures PostgreSQL; skip this page for containers.

1. Open a database administrator session

On Ubuntu/Debian with the PostgreSQL service running:

sudo -u postgres psql

On macOS with a fresh Homebrew PostgreSQL installation:

psql postgres

An existing installation may use a different administrator account. Use that account rather than resetting an existing database.

2. Create a local development role and database

At the psql prompt:

CREATE ROLE zotonic LOGIN;
\password zotonic
CREATE DATABASE zotonic OWNER zotonic ENCODING 'UTF8' TEMPLATE template0;
\q

For this disposable local example, enter zotonic at both password prompts. This matches Zotonic's development defaults. Use it only on your local development machine. If the role or database already exists, inspect its owner and settings instead of recreating it or changing its password blindly.

The garden site will use its own schema inside this database. The database owner can create that schema; the application role does not need superuser privileges.

3. Test the same connection Zotonic will use

psql -h localhost -U zotonic -d zotonic -W -c 'SELECT current_user, current_database();'
psql -h localhost -U zotonic -d postgres -W -c 'SELECT current_database();'

Enter the development password. Expect zotonic for both values in the first query and postgres in the second. addsite also connects to the postgres maintenance database to check that the site database exists. Do not continue to addsite until this succeeds.

If the server refuses the connection, check that PostgreSQL is running on localhost port 5432. If authentication fails, check the password and the first matching pg_hba.conf rule. From the administrator's psql session, SHOW hba_file; locates that file. A local password-based configuration can use:

host    zotonic,postgres    zotonic    127.0.0.1/32    scram-sha-256
host    zotonic,postgres    zotonic    ::1/128         scram-sha-256

Place specific rules before a broader conflicting rule, then reload PostgreSQL with SELECT pg_reload_conf(); in the administrator session. PostgreSQL's authentication documentation explains that only the first matching rule applies.

Optional: use a local Unix socket

If Zotonic and PostgreSQL run on the same host, you can use a Unix socket instead of TCP. In Zotonic's database configuration, set {dbhost, socket} for /run/postgresql, or {dbhost, "/var/run/postgresql"} for a different absolute socket directory. The configured dbport selects the socket filename .s.PGSQL.<port>; it is normally 5432. Use the actual directory reported by SHOW unix_socket_directories;.

For example, in the site's configuration term list:

{dbhost, socket},
{dbport, 5432}

Test the same endpoint and role:

psql -h /run/postgresql -p 5432 -U zotonic -d zotonic -W

Socket connections match local rules in pg_hba.conf. For password authentication, a specific rule is local zotonic,postgres zotonic scram-sha-256. Peer authentication instead checks the operating-system identity: test as the service user and configure a deliberate user mapping when the names differ. Socket mode does not silently fall back to TCP. The service must be able to access the socket directory; a container needs an explicitly shared socket path.

When creating a site, use bin/zotonic addsite -h socket garden (or the absolute directory after -h) together with any other required database options.

4. Continue the installation

Build and start Zotonic, then create garden. For a different database host, role, or password, configure those values before creating the site; see the addsite options. Container connections use host postgres, not localhost.

Check or change a site database connection

Access needed: Database and server configuration access.

Identify the site's effective database host, port, database name, role, and schema. Do not print the password into a shared report.
Test connectivity from the same host or container that runs Zotonic. A successful connection from your laptop may follow a different route.
Check authentication and the role's access to the intended database and schema.
Make a required connection change in acceptance first, at the owning configuration layer.
Restart or reload according to the deployment procedure and check startup logs.
Verify an existing page and a small write using the intended site account. Confirm that you did not connect to an empty or wrong environment's database.

A schema separates site tables within a database; it is not a substitute for environment isolation. Do not grant superuser rights to bypass a permissions error. For a fresh local development database, use the developer setup task instead of adapting production credentials.

Local socket connections

For PostgreSQL on the same host, set dbhost to socket (atom, string, or binary) to use /run/postgresql, or set it to an absolute socket directory. dbport selects .s.PGSQL.<port>, normally .s.PGSQL.5432. This mode does not fall back to TCP.

Check SHOW unix_socket_directories; in PostgreSQL and test with psql -h /run/postgresql -p 5432 -U ROLE -d DATABASE as the Zotonic service user. Socket connections use local rules in pg_hba.conf; TCP connections use host rules. Match the intended password or peer-authentication policy. Keep the socket directory writable only by trusted users. For containers, verify the mount and permissions inside the container.

Test the database connection

Run bin/zotonic connectdb to test a connection using the global Zotonic database configuration. It does not open PostgreSQL’s interactive client and has no site selector in this checkout.

The command prints connection options, including the password, before testing. Keep that output private. A site can override global database settings, so success does not prove that its own database and schema are available.

For a site-specific investigation, inspect its configuration and use a database client deliberately, or query application data through Zotonic models with the correct context. Prefer models for normal mutations so notifications and cache invalidation remain coordinated.

Type at least two characters