Open coolaj86 opened 3 years ago
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:postgres@10.0.0.100:5432/postgres'
pg_dump my_dbname > my_filename.sql
pg_dump -Fc my_dbname > my_filename.pgdump
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
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
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>
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"
☣️ Caution! ☢️ Deletes EVERYTHING
heroku pg:reset -a "${my_app}"
Ended up creating a backup/restore script pack:
How to Create a Remote Group + Users
~/bin/pg-addgroup
:~/bin/pg-adduser
:How to add a remote user to
~/.pgpass
~/bin/pg-passwd
: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
:An app account can run the SSH Proxy at system startup:
pg-register-ssh-proxy
:The ad-hoc alpine version: \ (must use
\"
rather than'
, must prefix with usernameapp@
, must be started fromash
- notfish
, not inside ofscreen
)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 usesSSLRequest
(similar idea to StartTLS) before the real TLS connection. It begins with00 00 00 08 04 d2 16 2f
and does NOT include SNI or ALPN information until the characterS
is sent. Then normal TLS resumes.This means that the proxy must support StartTLS - or
sclient
oropenssl s_client
must be used to proxy the connection (which might as well usesslmode=disable
at that point).See https://github.com/traefik/traefik/issues/7507
ULIDs
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.
How to backup a database
Backup everything including permissions and such:
Backup a single database:
See also:
How to restore a database
Restore everything from
pg_dumpall
.Backup a single database:
See also:
How to export a CSV
Fields with commas will be double quoted. Fields that have double quotes will have those double quotes doubled.
See also:
\copy
section of https://www.postgresql.org/docs/9.5/app-psql.htmlHow to import a CSV
Note:
\copy
is different fromCOPY
.How to use TLS SNI
How to migrate to a new version