Initial Setup

The following describes the initial setup of my workstation. Running psql gives a SQL shell in a development database called felix. The user felix has superuser privileges. Newly created databases get German collation with UTF-8 encoding by default.

Add User felix with superuser privileges

sudo -u postgres psql <<'EOF'
CREATE USER felix SUPERUSER
;
EOF

Change Collation

To get correct default collation, change the collation of the template1 database, before creating any other database.

psql postgres <<'EOF'
ALTER DATABASE template1 is_template=false
;
ALTER DATABASE template1 RENAME TO template1_bak
;
CREATE DATABASE template1
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'de_DE.UTF-8'
    LC_CTYPE = 'de_DE.UTF-8'
    TEMPLATE template0
;
ALTER DATABASE template1 is_template=true
;
EOF

https://stackoverflow.com/a/60396581/241240

Create Database felix

createdb felix
psql -c '\l'