Dumping with pg_dump

pg_dump -U [your username] [your schema] > [your-dump-file-name]_"$(date '+%F').sql" -h localhost -p 5433

Restoring with psql

psql -U postgres -h localhost -p 5433 -d [your schema] < [your-dump-file].sql

Reload configuration without restarting

SELECT pg_reload_conf();

Restore from a bz2 archive

bunzip2 your_dump.bz2 and then pg_restore -d [db-to-restore] -e [name-of-extracted-archive] -h [host] -U [user]

Backup and restore from a pod in a Kubernetes cluster

kubectl exec -it [your pod name] -- pg_dumpall -c -U postgres > /home/user/dump_db.sql
​
cat your_dump.sql | kubectl exec -it [your pod name] -- psql -U postgresp

Change owner recursively

select 'ALTER TABLE ' || t.tablename || ' OWNER TO [new user];'
 from  pg_tables t
 where schemaname = 'public';

Change owner of sequences recursively

select 'ALTER SEQUENCE ' || sequence_name || ' OWNER TO [your user];'
 from  information_schema.sequences t
 where sequence_schema = '[name of the schema]';

Delete all tables from a schema

select 'drop table if exists ' || tablename || ' cascade;' 
  from pg_tables
 where schemaname = '[name of the schema]'; 

Useful queries

https://gist.github.com/anvk/475c22cbca1edc5ce94546c871460fdd