Fly, Penguin!

I blog so I don't forget.

pg_dump and pg_restore

1 minute read #postgres #snippet

Since 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   -U user      -d db   > my_backup.sql

# restore database; maybe you can also use the admin user here (untested)
psql       -p   -U db_user   -d db   < my_backup.sql

Second 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   -U user      -d db   -Fc -b -f   my_dump_file.pg_dump

# and restore it. (add "-v" for more verbosity)
pg_restore -p   -U db_user   -d db   -1          my_dump_file.pg_dump