Postgres cheat-sheet

# run terminal if the current system user is one of DB Server users
~$ psql
psql (9.4.18)
postgres=#

# Access AWS RDS instance with username postgres
~$ psql -U postgres -h db-name.d3jif89zade.us-west-1.rds.amazonaws.com
psql (9.4.18)
postgres=#

# list all databases
postgres=# \l

# list all DBs with handy details like DB size, description and much more
postgres=# \l+

# Connect to database named "products"
postgres=# \c products

# running help for one of the commands
postgres=# \h CREATE

# list all users records
postgres=# SELECT * from pg_users;

# change or set password for user postgres
postgres=# \password postgres

# list all views from all schemas
postgres=# \dv *.*

# when running local
~$ pg_dump db-name > db-name-backup.sql

# remote DB backup
~$ pg_dump --dbname=postgresql://postgres:password@pg-db.ck34567865iod.eu-south-central-3.rds.amazonaws.com:5432/db-name > db-name-backup.sql

# Backup RDS DB in a security group and accessible only from Kubernetes cluster 🤯
# Create a container with name "container-name", run it while attaching bash and executing backup command 'pg_dump ...'
# All while streaming '>' output to the backup file in your machine
~$ kubectl run container-name --rm=true -it --restart=Never --image=postgres:alpine -- /bin/bash -c 'pg_dump --dbname=postgresql://postgres:password@pg-db.ck34567865iod.eu-south-central-3.rds.amazonaws.com:5432/db-name' > db-name-backup.sql