pg_dump and pg_restore
Posted on April 7, 2023 (Last modified on July 2, 2024) • 1 min read • 124 wordsSince I can never remember … . Using pg_dump and pg_restore, tested with AWS RDS.
First option, pg_dump can create SQL dumps, which can be read from psql.
# save database; "user" can be admin user (tested) or db_user (untested)
pg_dump -p my-db.host -U user -d db > my_backup.sql
# restore database; maybe you can also use the admin user here (untested)
psql -p my-db.host -U db_user -d db < my_backup.sqlSecond option, let’s now create a pg_dump “custom” dump file, and restore that with pg_restore.
# save the db ... (add "-v" for more verbosity)
pg_dump -p my-db.host -U user -d db -Fc -b -f my_dump_file.pg_dump
# and restore it. (add "-v" for more verbosity)
pg_restore -p my-db.host -U db_user -d db -1 my_dump_file.pg_dump