Project

General

Profile

Wiki »

Creating a PostgreSQL cluster -- fwd_create_pg_cluster.sh

fwd_create_pg_cluster.sh creates a PostgreSQL data cluster with upstream initdb (not pg_createcluster), so the on-disk layout matches the official postgres Docker image. It initializes the cluster, applies a baseline postgresql.conf / pg_hba.conf, and creates the FWD admin and standard roles. It is baked into the base PostgreSQL image (COPY fwd_create_pg_cluster.sh /usr/local/bin/, see Building and Using Docker Images) and is safe to run either inside an Ubuntu-based container or on an Ubuntu host.

What it does

  1. Determines the PostgreSQL major version (from $PG_VERSION, else the newest /usr/lib/postgresql/<ver>).
  2. Verifies initdb / pg_ctl / psql are present.
  3. Refuses to overwrite an existing cluster unless --force is given (then it stops and removes it).
  4. Runs initdb with the chosen locale, encoding, and postgres superuser (optionally with a password file).
  5. Edits postgresql.conf: auth encryption, logging, max_connections, port, listen_addresses, and any --config settings.
  6. Edits pg_hba.conf: swaps the default trust rules for the chosen auth method and appends a baseline host all all all <auth> line.
  7. Starts a temporary, socket-only server with a trust-only bootstrap HBA to create the roles and apply any --set (ALTER SYSTEM) settings, then stops it.
  8. Optionally starts the cluster for real (--start), by handing it to startup_pg.sh -- see Running a PostgreSQL cluster.

The cluster runs as the postgres system user throughout (via gosu when root, else sudo -u postgres).

Requirements

  • PostgreSQL server + client packages (initdb, pg_ctl, psql under /usr/lib/postgresql/<ver>/bin).
  • A postgres system user.
  • Run as root (the script uses gosu/sudo to drop to postgres), as the postgres user, or as a user with sudo rights.
  • $PG_VERSION set (it is, inside the image) or a detectable /usr/lib/postgresql/<ver> layout on the host.

Usage

Usage: /usr/local/bin/fwd_create_pg_cluster.sh [options]

Creates a PostgreSQL cluster with upstream initdb, compatible with the official
postgres Docker image. Safe to run on Ubuntu (host) or in an Ubuntu-based container.

Options:
  --pgdata DIR                 Data directory (default: /opt/db/fwdcluster)
  --pgport PORT                Connection port (default: 5432)
  --locale LOCALE              Cluster locale (default: en_US.UTF-8)
  --encoding ENC               Cluster encoding (default: UTF8)
  --auth METHOD                pg_hba auth method: scram-sha-256 | md5 | password | trust
                               (default: scram-sha-256). Sets both of the below.
  --auth-local METHOD          Auth method for unix-socket and loopback connections,
                               overriding --auth. The usual reason to differ is to
                               leave local connections trusted for initialization
                               while requiring a password from anywhere else.
  --auth-host METHOD           Auth method for the baseline "host all all all" rule,
                               overriding --auth.

  --max-connections VALUE      Maximum connections (default: 100)

  --config <postgres setting> <value>  Configure a postgres setting by editing
                               postgresql.conf in place (existing keys only).
  --set <postgres setting> <value>  Configure a postgres setting via
                               ALTER SYSTEM (postgresql.auto.conf; adds new keys,
                               server-validated). Both are repeatable.

  --postgres-password PASS     Initial password for postgres superuser
  --admin-user NAME            Admin role name (default: fwd_admin)
  --admin-pass PASS            Admin role password
  --user-user NAME             Standard role name (default: fwd_user)
  --user-pass PASS             Standard role password

  --temp-port PORT             Temporary port for bootstrap (default: 55432)
  --socket-dir DIR             Unix socket dir for bootstrap (default: /tmp)

  --force                      Remove the cluster if it already exists.
  --start                      Start the cluster after creating it.

Examples:
  ./base_ubuntu/repo/fwd_create_pg_cluster.sh --pgdata /data/pg17 --locale en_US.UTF-8 --encoding UTF8 \
     --pgport 5433 \
     --max-connections 200 \
     --auth scram-sha-256 \
     --postgres-password adminpw \
     --admin-user app_admin --admin-pass S3cret! \
     --user-user app_user --user-pass userpw

Notes:
- Uses initdb (NOT pg_createcluster), so the layout matches postgres Docker images.
- Ensures correct ownership and permissions on PGDATA.

Cluster and layout

Option Default Meaning
--pgdata DIR /opt/db/fwdcluster Data directory (the cluster name is the final path element).
--pgport PORT 5432 Port written to postgresql.conf.
--locale LOCALE $LANG or en_US.UTF-8 Cluster locale passed to initdb --locale.
--encoding ENC UTF8 Cluster encoding passed to initdb --encoding.
--max-connections VALUE 100 Sets max_connections.
--auth METHOD scram-sha-256 pg_hba auth method: scram-sha-256, md5, password, or trust. Sets both of the next two.
--auth-local METHOD --auth Method for the unix-socket and loopback rules -- a client already on the machine.
--auth-host METHOD --auth Method for the appended host all all all rule -- a client from anywhere else.
--config <setting> <value> -- Set a parameter by editing postgresql.conf in place. Repeatable. See "Setting parameters".
--set <setting> <value> -- Set a parameter via ALTER SYSTEM (postgresql.auto.conf). Repeatable. See "Setting parameters".

Roles and passwords

Option Default Meaning
--postgres-password PASS (unset) If given, sets a password on the postgres superuser. If omitted, no password is set.
--admin-user NAME fwd_admin Name of the admin role.
--admin-pass PASS admin Password for the admin role.
--user-user NAME fwd_user Name of the standard role.
--user-pass PASS user Password for the standard role.

Bootstrap and flags

Option Default Meaning
--temp-port PORT 55432 Port for the temporary bootstrap server (role creation / --set).
--socket-dir DIR /tmp Unix-socket directory for the temporary bootstrap server.
--force off If the cluster already exists, stop and remove it, then recreate.
--start off Start the cluster (via startup_pg.sh) after creating it.
-h, --help, -? -- Print usage and exit 0.

Roles created

Role Source options Privileges
standard --user-user / --user-pass LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE
admin --admin-user / --admin-pass LOGIN SUPERUSER CREATEDB CREATEROLE
postgres --postgres-password password set only if the option is supplied

Roles are created idempotently (skipped if the name already exists), so re-running against a fresh cluster is safe.

Setting parameters: --config vs --set

There are two ways to set a postgresql.conf parameter, and they behave differently. Both are repeatable -- pass the option once per setting; passing several at once is the normal case, since tuning is usually a group of interrelated settings.

Aspect --config --set
Mechanism rewrites the line in postgresql.conf (via sed) runs ALTER SYSTEM SET -> postgresql.auto.conf
Adds a key not already in the file no -- silently skipped yes
Validates the setting name no (a typo is written or skipped; errors only at server start) yes -- an unknown name fails immediately and aborts the script
Value quoting you must embed quotes for string values the script quotes the value for you
Precedence at runtime base (postgresql.conf) postgresql.auto.conf is read last, so --set overrides --config / defaults for the same key
When applied before first start during the temporary bootstrap server
Best for standard knobs you want visible/greppable in the main conf extension GUCs, anything not already in the file, or when you want server-side validation

--config limitations

  • Existing keys only. --config edits a line that already appears in postgresql.conf (commented or not). Virtually all standard settings ship commented, so they work -- but a misspelled name, or a setting not present in the file (e.g. an extension GUC), is silently skipped, not appended.
  • No validation. The value is written verbatim; a bad value is only caught when the server starts.
  • Quote string values inside the value. Because the value is a single token, a string-valued setting must carry its own quotes and be shell-quoted so it stays one argument:
    --config log_line_prefix "'%m [%p] %u@%d '"

    Bare numbers, sizes, enums, and booleans (8GB, on, replica, 1.1) need no quoting.

--set limitations

  • A few parameters cannot be set by ALTER SYSTEM. Notably data_directory, and parameters that are not permitted in a config file (those given only on the server command line). Do not use --set for config_file / hba_file / ident_file -- the script manages those.
  • postgresql.auto.conf wins. Because it is read after postgresql.conf, a --set value silently overrides a --config value (or a default, including --max-connections / --pgport) for the same key. Pick one mechanism per setting to avoid confusion.
  • Restart-class settings apply on next start. Settings that require a restart (e.g. shared_buffers, max_connections) are written now and take effect when the cluster next starts -- which is fine here, since the cluster is started fresh afterward.
  • Extension GUCs need the extension. A dotted custom setting (e.g. pg_stat_statements.max) can be written, but only takes effect once the owning extension/library is loaded (shared_preload_libraries).
  • Values are quoted as SQL strings. The script wraps every value in single quotes (doubling any embedded quotes), which PostgreSQL coerces for numeric/enum/boolean settings -- so pass values bare (--set shared_buffers 8GB), unlike --config.

Configuration applied

postgresql.conf
  • password_encryption = scram-sha-256 when --auth scram-sha-256.
  • Uncomments the logging block: log_destination, logging_collector, log_directory, log_filename, log_file_mode, log_rotation_age, log_rotation_size.
  • max_connections and port from the options.
  • listen_addresses = '*' (see "Security notes").
  • Any --config settings.
postgresql.auto.conf
  • Any --set settings (via ALTER SYSTEM).
pg_hba.conf
  • Replaces the default trust on the local / 127.0.0.1/32 / ::1/128 rules (and their replication equivalents) with --auth-local.
  • Appends a baseline host all all all <auth-host> line so remote clients can connect with the chosen method.

Both default to --auth, so passing only --auth behaves as it always has. Splitting them is how you keep a cluster usable by whatever initializes it while still requiring a password from off the machine:

--auth-local trust --auth-host scram-sha-256

which produces:

local   all   all                    trust
host    all   all   127.0.0.1/32     trust
host    all   all   ::1/128          trust
host    all   all   all              scram-sha-256

Note that password_encryption is set to scram-sha-256 when either half asks for it, since the stored password has to suit the strictest rule.

Examples

Basic cluster with SCRAM auth and a superuser password:

fwd_create_pg_cluster.sh \
  --pgdata /opt/db/fwdcluster \
  --auth scram-sha-256 \
  --postgres-password 'S3cret!' \
  --start

Custom roles, non-default port, recreate if present:

fwd_create_pg_cluster.sh \
  --pgport 5433 \
  --admin-user app_admin --admin-pass 'A9!' \
  --user-user app_user  --user-pass 'u5r' \
  --force

Tuning -- a group of settings via --set (validated, added if absent, quoted for you):

fwd_create_pg_cluster.sh \
  --max-connections 200 \
  --set shared_buffers 8GB \
  --set effective_cache_size 24GB \
  --set work_mem 64MB \
  --set maintenance_work_mem 2GB \
  --set max_wal_size 4GB \
  --set checkpoint_completion_target 0.9 \
  --set random_page_cost 1.1 \
  --start

The same knobs are available through --config if you prefer them written into the main postgresql.conf -- but remember --config only touches keys already present, does not validate, and needs string values pre-quoted.

Security notes

  • listen_addresses = '*' is set, so the server listens on all interfaces. Restrict pg_hba.conf (and the published port) before exposing the cluster.
  • Passwords passed on the command line are visible in the process list and shell history. Prefer setting them in a controlled environment, and change the defaults (admin / user) for anything beyond a throwaway cluster.
  • The role-creation and --set steps use trust auth on a temporary, local-socket-only server on --temp-port, with a bootstrap HBA that is removed when the step finishes; it is never reachable over the network.
  • The admin role is created SUPERUSER (plus CREATEDB CREATEROLE) -- see "Roles created". It is not merely a schema owner: it can do anything on the cluster, including reading and altering every database. The standard role is deliberately the opposite (NOSUPERUSER NOCREATEDB NOCREATEROLE) and is what an application should connect as.

Exit behavior

  • Exits 1 if the cluster already exists and --force was not given.
  • Exits 1 if $PG_VERSION cannot be determined or a required tool is missing.
  • Exits 1 if a --set names a setting the server rejects (validated during the bootstrap server).
  • With --start: reports the running cluster, or exits 1 if it was started but is not running.
  • Without --start: prints a "ready to run" summary with next-step commands -- how to start it with startup_pg.sh (the supported path), and how to run it under the official postgres:<ver> image.

© 2004-2026 Golden Code Development Corporation. ALL RIGHTS RESERVED.