Postgres Cheat Sheet updates

[coolaj86]

• How to create remote users
• ULIDS
• Random IDs
• Backup DB
• Export to CSV
• Import from CSV
• Migrate PG v10 to v12, v14, v16, etc

How to Create a Remote Group + Users

~/bin/pg-addgroup:

#!/bin/sh
set -e
set -u

g_group='my_remote_users'
g_port=5432

# https://www.postgresql.org/docs/current/sql-createrole.html
echo "Creating role (group) '${g_group}'..."
echo "CREATE ROLE \"$g_group\" NOLOGIN;" |
    psql "postgres://postgres:postgres@localhost:${g_port}/postgres" -f -

echo "Updating ~/.local/share/postgres/var/pg_hba.conf to allow '${g_group}' users to login and access their own db..."
# 'host' instead of 'hostssl' since the decryption may happen at the SNI router
if ! grep -q -F "${g_group}" ~/.local/share/postgres/var/pg_hba.conf; then
    echo "# Allow $g_group to connect remotely over the internet
host sameuser         +$g_group        0.0.0.0/0               scram-sha-256
host sameuser         +$g_group        ::0/0                   scram-sha-256" \
        >> ~/.local/share/postgres/var/pg_hba.conf
fi

echo "Restarting postgres"
sudo systemctl restart postgres

~/bin/pg-adduser:

#!/bin/sh
set -e
set -u

my_prefix="${1:-'db'}"
g_group='my_remote_users'
g_rnd="$(xxd -l8 -ps /dev/urandom)"
g_user="${my_prefix}_${g_rnd}"
g_pw_base58="$(xxd -c 0 -l 64 -p /dev/urandom | xxd -r -ps | base64 -w 0 | tr -d /+_=- | tr -d 0IOl | cut -c 1-22)"
g_port=5432

echo "Creating database '${g_user}' (for user of the same name) ..."
echo "CREATE DATABASE \"$g_user\";" |
    psql "postgres://postgres:postgres@localhost:${g_port}/postgres" -f -

echo "Creating user '${g_user}' with login permissions as a member of '${g_group}' ..."
echo "CREATE ROLE \"$g_user\" LOGIN INHERIT IN ROLE \"${g_group}\" ENCRYPTED PASSWORD '$g_pw_base58';" |
    psql "postgres://postgres:postgres@localhost:${g_port}/postgres" -f -

echo "Granting '${g_user}' access to its own (same-name) database ..."
echo "GRANT ALL PRIVILEGES ON DATABASE \"${g_user}\" to \"${g_user}\";" |
    psql "postgres://postgres:postgres@localhost:${g_port}/postgres" -f -

echo "Password '${g_pw_base58}'"
echo ""
echo "psql 'postgres://${g_user}:${g_pw_base58}@localhost:${g_port}/${g_user}'"
echo ""

How to add a remote user to ~/.pgpass

~/bin/pg-passwd:

#!/bin/sh
set -e
set -u

if ! test -e ~/.pgpass; then
    touch ~/.pgpass
fi
if ! grep -q -i '^\s*#.*port:d' ~/.pgpass; then
    echo '# hostname:port:database:username:password' >> ~/.pgpass.header.txt
    mv ~/.pgpass ~/.pgpass.noheader.bak
    cat ~/.pgpass.header.txt ~/.pgpass.noheader.bak > ~/.pgpass
    rm ~/.pgpass.header.txt ~/.pgpass.noheader.bak
fi
if ! grep -q '^\s*#\s*export\s\+PGPASSFILE=' ~/.pgpass; then
    echo "# export PGPASSFILE='${HOME}/.pgpass'" >> ~/.pgpass.header.txt
    mv ~/.pgpass ~/.pgpass.noheader.bak
    cat ~/.pgpass.header.txt ~/.pgpass.noheader.bak > ~/.pgpass
    rm ~/.pgpass.header.txt ~/.pgpass.noheader.bak
fi
chmod 0600 ~/.pgpass

g_url="${1:-PROMPT_URL}"

fn_version() { (
    echo "pg-addpass v0.9.0"
); }

fn_help() { (
    echo ""
    echo "USAGE"
    echo "    [space] pg-addpass [pg-url]"
    echo ""
    echo "EXAMPLES"
    echo "    Prompt for PG_URL string"
    echo "        pg-addpass"
    echo "    Prefix with space and give PG_URL string"
    echo "          pg-addpass 'postgress://user:pass@host:port/db?sslmode=verify-full'"
    echo "    Parse PG_URL from .env file"
    echo "        cat .env | grep PG_URL | cut -d'=' -f2- | pg-addpass"
    echo ""
    echo "NOTES"
    echo "    - query parameters will be *ignored* (ex: ?sslmode=)"
    echo "    - passwords with ':' or '@' will not be parsed correctly"
    echo "      (you may be able to enter them into ~/.pgpass manually)"
    echo ""
    echo "WARNING"
    echo "    remember to clear this command from your shell history if"
    echo "    you don't want the password to be saved there"
    echo "    (some shells omit commands if you prefix them with a space)"
    echo ""
); }

case $g_url in
    PROMPT_URL)
        echo 'Example: postgres://user:pass@host:port/db?params'
        printf 'PG_URL: '
        read -r g_url
        ;;
    help | --help)
        fn_version
        fn_help
        exit 0
        ;;
    -V | --version | version)
        fn_version
        exit 0
        ;;
    *)
        # do nothing
        printf ''
        ;;
esac

if ! (echo "${g_url}" | grep -q '?'); then
    g_url="${g_url}?"
fi
g_params="$(
    echo "${g_url}" | cut -d'@' -f2 | cut -d'?' -f2-
)"
g_host_port="$(
    echo "${g_url}" | cut -d'@' -f2 | cut -d'/' -f1
)"
g_host="$(
    echo "${g_host_port}" | cut -d':' -f1
)"
g_db="$(
    echo "${g_url}" | rev | cut -d'/' -f1 | rev | cut -d'?' -f1
)"
g_user_pass="$(
    echo "${g_url}" | cut -d'@' -f1 | sed 's;://;:;g' | cut -d':' -f2-
)"
g_user="$(echo "${g_user_pass}" | cut -d':' -f1)"
g_pass="$(echo "${g_user_pass}" | cut -d':' -f2-)"
g_mask="$(printf '%s' "${g_pass}" | tr '[:graph:]' '*')"

if grep -q -F "${g_host_port}:${g_db}:${g_user_pass}" ~/.pgpass; then
    echo "'${g_host_port}:${g_db}:${g_user}:${g_mask}' found in ~/.pgpass"
    exit 0
fi

if grep -q -F "${g_host_port}:${g_db}:${g_user}:" ~/.pgpass; then
    {
        echo "${g_host_port}:${g_db}:${g_user} already exists in ~/.pgpass"
        echo ""
        echo "    PLEASE UPDATE PASSWORD MANUALLY"
        echo ""
    } >&2
    exit 1
fi

echo ""
echo "Saved to ~/.pgpass:"
echo "${g_host_port}:${g_db}:${g_user_pass}" >> ~/.pgpass
echo "${g_host_port}:${g_db}:${g_user}:${g_mask}"
if ! grep -q -F "*:*:${g_db}:${g_user}:" ~/.pgpass; then
    echo "*:*:${g_db}:${g_user_pass}" >> ~/.pgpass
    echo "*:*:${g_db}:${g_user}:${g_mask}"
fi

echo ""
echo "The password will be matched by host (or localhost), dbname, and username"
echo "    psql --host ${g_host} --dbname ${g_db} --username ${g_user}"
echo "    psql 'postgres://${g_user}@${g_host}/${g_db}?${g_params}'"

How to Proxy PG through SSH

The PG server can lock down what the SSH user is allowed to do:

/home/pg-proxy/.ssh/authorized_keys:

# allow ssh connections to proxy ports, but no login shell
permitopen="127.0.0.1:5432",permitopen="localhost:5432",no-pty,no-X11-forwarding,command="/bin/printf \"\nUSAGE\n    ssh '<host>' \n        -o LocalForward='5432 127.0.0.1:5432' \n        -o RequestTTY=no \n        -o SessionType=none \n        -o StdinNull=yes \n\n\"" ssh-rsa AAAA.... app@pg-client

An app account can run the SSH Proxy at system startup:

pg-register-ssh-proxy:

my_pg_host='pg.example.net'
sudo env PATH="$PATH" serviceman add \
    --path="$PATH" \
    --name pg-ssh \
    --username "$(id -u -n)" \
    -- \
        ssh "${my_pg_host}" \
            -o LocalForward="'5432 127.0.0.1:5432'" \
            -o RequestTTY=no \
            -o SessionType=none \
            -o StdinNull=yes \
            -o StrictHostKeyChecking=accept-new

The ad-hoc alpine version:
(must use \" rather than ', must prefix with username app@, must be started from ash - not fish, not inside of screen)

~/bin/serviceman-add ssh-forward-pg -- \
    ssh "[email protected]" \
    -o LocalForward="\"5432 127.0.0.1:5432\"" \
    -o RequestTTY=no \
    -o SessionType=none \
    -o StdinNull=yes \
    -o ProxyCommand="\"/home/app/.local/bin/sclient --alpn ssh %h\"" \
    -o StrictHostKeyChecking=accept-new

Note: add -o ProxyCommand="'sclient --alpn ssh ${my_pg_host}'" for ssh tunnels.

How to route with SNI + ALPN

With sslmode=require|verify-full Postgres uses SSLRequest (similar idea to StartTLS) before the real TLS connection. It begins with 00 00 00 08 04 d2 16 2f and does NOT include SNI or ALPN information until the character S is sent. Then normal TLS resumes.

This means that the proxy must support StartTLS - or sclient or openssl s_client must be used to proxy the connection (which might as well use sslmode=disable at that point).

See traefik/traefik#7507

ULIDs

• https://github.com/geckoboard/pgulid/blob/master/pgulid.sql
• Would you accept a PR to generate a ULID in UUID format? geckoboard/pgulid#3 (comment)

How to create a table with a Random ID

Auto-incrementing IDs are a bad idea. If you ever grow your database beyond a single instance it WILL cause problems.

Postgres, of course, being a good database, has a built-in function for using random IDs.

The problem, however, is that fully random IDs result in slow writes because the write index is always cold. ULIDs (above) solve this.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS example_table (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	foo VARCHAR(64) NOT NULL,
	bar TEXT DEFAULT NULL
);

How to backup a database

Backup everything including permissions and such:

pg_dumpall | gzip > backup.postgres.gz

Backup a single database:

pg_dump my_database | gzip > my_database.postgres.gz

See also:

• https://www.postgresql.org/docs/9.1/backup-dump.html
• https://www.postgresql.org/docs/9.1/app-pgdump.html

How to restore a database

Restore everything from pg_dumpall.

gunzip -c backup.postgres postgres.gz | psql

Backup a single database:

gunzip -c my_database.postgres.gz | psql

See also:

• https://www.postgresql.org/docs/9.1/backup-dump.html
• https://www.postgresql.org/docs/9.1/app-pgrestore.html

How to export a CSV

COPY (<select>) TO STDOUT WITH DELIMITER ',' CSV HEADER \g './path/to/file.csv';

COPY (SELECT * FROM expenses) TO STDOUT WITH DELIMITER ',' CSV HEADER \g '/Users/me/Downloads/report.csv';

Fields with commas will be double quoted. Fields that have double quotes will have those double quotes doubled.

See also:

• The \copy section of https://www.postgresql.org/docs/9.5/app-psql.html
• COPY at https://www.postgresql.org/docs/9.2/sql-copy.html
• https://www.postgresqltutorial.com/export-postgresql-table-to-csv-file/

How to import a CSV

\copy "my_table" FROM '/Users/me/Downloads/report.csv' WITH DELIMITER ',' CSV HEADER;

Note: \copy is different from COPY.

How to use TLS SNI

• https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLSNI

How to migrate to a new version

mv $HOME/.local/share/postgres ~/.local/share/postgres-v10.12

PGUSER=postgres PGPASSWORD=postgres pg_upgrade \
    --old-bindir="$HOME/.local/opt/postgres-v10.12/bin/" \
    --old-datadir="$HOME/.local/share/postgres-v10.12/var/" \
    --new-datadir="$HOME/.local/share/postgres/var/"

[coolaj86]

PostgreSQL How To

curl -sS https://webinstall.dev/ | bash

webi serviceman
webi postgres

sudo env PATH="$PATH" \
    serviceman add --system --path "$PATH" --username $(whoami) --name postgres -- \
    postgres -D "$HOME/.local/share/postgres/var" -p 5432

/home/app/.local/share/postgres/var -p 5432

Access on Localhost:

psql 'postgres://postgres:postgres@localhost:5432/postgres'

vim ~/.local/share/postgres/var/postgresql.conf

listen_addresses = 'localhost,10.0.0.100'

vim ~/.local/share/postgres/var/pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            password
# IPv4 internal network connections:
host    all             all             10.0.0.1/16             password
host    all             all             192.168.0.0/24          password

sudo systemctl restart postgres

psql 'postgres://postgres:[email protected]:5432/postgres'

Backup Database

pg_dump my_dbname > my_filename.sql

pg_dump -Fc my_dbname > my_filename.pgdump

Restore Backup

From pg_dumpall:
https://www.postgresql.org/docs/current/app-pgrestore.html

pg_restore --username postgres --no-owner --role=postgres -d postgres -1 ~/Downloads/postgres-yyyy-mm-dd.dump

From pg_dump;

psql < ./postgres-yyyy-mm-dd.sql

[coolaj86]

mkdir -p ./Backups/

pg_dumpall \
  --host localhost \
  --database postgres \
  --username postgres \
  --file ./Backups/all."$(date +%Y-%m-%d)".sql

pg_dump \
  --host localhost \
  --username postgres \
  --no-owner \
  --quote-all-identifiers \
  --no-privileges \
  --schema-only \
  dbname \
  --file ./Backups/dbname.schema."$(date +%Y-%m-%d)".sql

pg_dump \
  --host localhost \
  --username postgres \
  --no-owner \
  --quote-all-identifiers \
  --no-privileges \
  --data-only \
  dbname \
  --file ./Backups/dbname.data."$(date +%Y-%m-%d)".sql

touch ~/.pgpass
chmod 0600 ~/.pgpass
vim ~/.pgpass

# hostname:port:database:username:password
localhost:5432:*:postgres:postgres

[coolaj86]

Backup Heroku Database

# heroku config:get -a <app-name> DATABASE_URL
heroku config:get -a foobar DATABASE_URL

postgres://<alpha-user>:<hex-pass>@<aws-ec2>:5432/<alphanum-dbname>

The Heroku Way

Backup

my_date="$(
    date -u '+%F_%H.%M.%S'
)"
my_app='foobarapp'
heroku pg:backups:download -a "${my_app}" -o "postgres-${my_app}-${my_date}.dump"

Delete Everything

☣️ Caution! ☢️ Deletes EVERYTHING

heroku pg:reset -a "${my_app}"

Restore

[coolaj86]

Ended up creating a backup/restore script pack:

• https://github.com/therootcompany/pg-xzbackup.sh