Add collation to Postgres post-initdb

Note: this is for old Postgres installs that lack the pg_import_system_collations function.

We need to operate as user so that the new collation has the proper ownership. We connect to template1, because by default we can’t connect to template0.

$ sudo -u postgres psql -d template1

Now allow ourselves to connect to template0

template1=# UPDATE pg_database SET datallowconn = TRUE WHERE datname = 'template0';
template1=# \c template0

Create collation in the proper schema (pg_catalog). Quote collation name to preserve capitalization.

template0=# CREATE COLLATION pg_catalog."en_US" (LOCALE='en_US.utf8');

Now reset datallowconn to FALSE.

template0=# UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template0';

Now any database created from the template0 template will inherit the new collation.