Skip to content

Instantly share code, notes, and snippets.

@januszm

januszm/pg_dump_restore.sh

Last active March 13, 2023 18:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save januszm/23652572131f86b61bff06704059dc51 to your computer and use it in GitHub Desktop.
Save januszm/23652572131f86b61bff06704059dc51 to your computer and use it in GitHub Desktop.
Download ZIP
Postgresql dump and load compressed
Raw
pg_dump_restore.sh
sudo amazon-linux-extras install postgresql12
pg_dump -Fc -O -h yyy-production.xxx.region.rds.amazonaws.com -p 5432 -U yyy yyy_production -f yyy.dump
# TRUNCATE TABLE tables ... RESTART IDENTITY
# rails db:migrate
pg_restore -Fc -O --disable-triggers --data-only -h yyy-staging.xxx.region.rds.amazonaws.com -d yyy_staging -U yyy yyy.dump
# both commands accept the --table option to specify which table to dump/restore
# select setval('TABLE_NAME_id_seq', (select max(id) from TABLE_NAME)); may be required after RESTART IDENTITY, +1
# In case of version mismatch between the server and pg_dump, add pgdgXX repo and install XX client
[pgdg14]
name=PostgreSQL 14 for RHEL/CentOS7 - x86_64
gpgkey=https://download.postgresql.org/pub/repos/yum/RPM-GPG-KEY-PGDG-14
baseurl=https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-7-x86_64/
enabled=1
gpgcheck=1
# yum makecache
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment