Databases: PostgreSQL (pg*), MySQL (my*), Redis (r*), MongoDB.
| Name | Runs | What it does | |
|---|
pg | psql | psql | needs psql |
pgl | psql -h localhost | psql to localhost | needs psql |
pgls | psql -l | list databases | needs psql |
pgready | pg_isready | is the server accepting connections | needs psql |
pgsize | psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC" | database sizes | needs psql |
pgconns | psql -c "SELECT pid, usename, datname, state, left(query, 60) AS query FROM pg_stat_activity WHERE pid <> pg_backend_pid()" | active connections | needs psql |
function pgq {
[ "$#" -eq 2 ] || { echo "Usage: pgq <database> <sql>" >&2; return 2; }
psql -d "$1" -c "$2"
}
pgq DB SQL | | run one queryexample$ pgq shop 'SELECT count(*) FROM orders'
count
-------
1520
(1 row) | needs psql |
function pgdump {
[ "$#" -eq 1 ] || { echo "Usage: pgdump <database>" >&2; return 2; }
_f="$1-$(date +%Y%m%d-%H%M%S).dump"
pg_dump -Fc -f "$_f" "$1" && echo "$_f"
unset _f
}
pgdump DB | | dump to DB-YYYYmmdd-HHMMSS.dump (custom format)example$ pgdump shop
shop-20261002-103512.dump | needs psql |
function pgrestore {
[ "$#" -eq 2 ] || { echo "Usage: pgrestore <file.dump> <database>" >&2; return 2; }
pg_restore --no-owner -d "$2" "$1"
}
pgrestore FILE DB | | restore a .dump into DB (no drop, no owners)example$ pgrestore shop-20261002-103512.dump shop_copy
(no output on success) | needs psql |