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 initialises 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).

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

fwd_create_pg_cluster.sh [options]

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.
--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.
  • Appends a baseline host all all all <auth> line so remote clients can connect with the chosen method.

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.

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 (including how to run it under the official postgres:<ver> image).

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