initdb

Create a database at /usr/local/var/postgres

$ initdb [[-D|--pgdata]] /usr/local/var/postgres

initdb [options] --pgdata directory

--pgdata directory
    Specifies the directory where the database cluster should be stored. This is a required option. Use PGDATA environment variable as the default.

-A auth-method
    Specifies the default authentication method for local connections ('trust', 'reject', 'password', 'md5', 'scram-sha-256', 'gss', 'sspi', 'ident', or 'peer'). Default is 'scram-sha-256'.

-E encoding
    Selects the encoding for the template database (template1). Default is derived from the locale.

-U username
    Sets the database superuser's user name. Defaults to the name of the operating system user running initdb.

-W
    Prompts for a password for the database superuser. This is generally recommended for enhanced security.

-X directory
    The location where the write-ahead log should be stored.

-k
    Use hard links instead of symbolic links. Useful when data and WAL are stored on the same filesystem, but hard link creation is only supported between files in the same file system. Valid only in Unix.

-n
    No synchronization with OS cache; rely on OS for synchronization. Useful on flash drives to avoid frequent syncs which may lead to premature drive failure.

-D directory
    Same as --pgdata.

-N
    Allow initialization in an existing, non-empty data directory.

-o options
    Passes options directly to the postgres backend process when it is invoked by initdb. The backend options must be specified as a single quoted string.

--locale=locale
    Sets the default locale for the database cluster.

--lc-collate=locale
    Sets the locale for collation (sorting). If not specified, it defaults to the --locale setting.

--lc-ctype=locale
    Sets the locale for character classification (character type). If not specified, it defaults to the --locale setting.

--lc-messages=locale
    Sets the locale for messages. If not specified, it defaults to the --locale setting.

--lc-monetary=locale
    Sets the locale for monetary formatting. If not specified, it defaults to the --locale setting.

--lc-numeric=locale
    Sets the locale for numeric formatting. If not specified, it defaults to the --locale setting.

--lc-time=locale
    Sets the locale for time formatting. If not specified, it defaults to the --locale setting.

The initdb command creates a new PostgreSQL database cluster. A database cluster is a collection of databases managed by a single PostgreSQL server instance. initdb creates the directories where the database data will reside and generates the initial database catalog tables. The user who executes initdb becomes the database superuser (the role that will own all initial database objects) so it's crucial to execute this command as the user that the PostgreSQL server will run as (typically 'postgres').

The command performs several crucial setup tasks including: creating the file system structure, generating SSL certificates if requested, setting default locale and encoding, and initializing the database system tables. Improper execution or configuration of initdb can lead to a non-functional or insecure PostgreSQL installation. It's vital to understand and carefully configure the parameters related to locale, encoding, and data storage location before executing initdb.

Running initdb as root is discouraged due to security implications. Always run it as the postgres user or another dedicated database user.
Carefully choose the encoding. Changing it later is difficult. The data directory must be empty before running initdb unless -N is specified. Overwriting an existing database cluster can lead to data loss and corruption.

Pay close attention to locale settings. Incorrect locale settings can affect character sorting, date formatting, and other locale-specific behaviors. It's generally best to align the locale with the expected data characteristics of the database.
Using --locale option set automatically all lc_* options which are not explicitly set.

Always set a password for the database superuser to prevent unauthorized access. Use the -W option to be prompted for the password during initialization.

initdb respects the PGDATA environment variable which defines the database cluster directory. If PGDATA is set, the --pgdata option becomes optional.

initdb has been a core component of PostgreSQL since its early days. It's responsible for setting up the foundation of a PostgreSQL database cluster. Over time, the command has evolved to support new authentication methods, encoding options, and locale settings to accommodate the growing needs of database administrators and developers.

pg_ctl(1), postgres(1), createdb(1), dropdb(1)