Backup & Restore Postgres

Backup PostgreSQL

full dump in SQL (including roles, schema)

pg_dump -h myapp.xxxxxxxx.eu-west-1.rds.amazonaws.com -U myappuser >> /tmp/postgres_dump.sql

Dump in SQL inserts data only (useful for rails `db:create & rails db:migrate` and then loading the data

pg_dump  --data-only --inserts -h myapp.xxxxxxxx.eu-west-1.rds.amazonaws.com -U myappuser  >> /tmp/myapp_dump.sql

linux

sudo -u postgres psql  myapp_prod < /tmp/myapp_dump.sql

OSx

 psql myapp_development < /tmp/my_app_prod_full_2024-03-13_07-54.sql