Backup amazon RDS Postgresql database backup, Essential command for technical admin team.

  1. Change your database RDS instance security group to allow your machine to access it.
    • Add your ip to the security group to acces the instance via Postgres.
  2. Make a copy of the database using pg_dump
    • $ pg_dump -h <public dns> -U <my username> -f <name of dump file .sql> <name of my database>
    • you will be asked for postgressql password.
    • a dump file(.sql) will be created
  3. Restore that dump file to your local database.
    • but you might need to drop the database and create it first
    • $ psql -U <postgresql username> -d <database name> -f <dump file that you want to restore>
    • the database is restored
  4. pg_restore -h <host> -U <username> -c -d <database name> <filename to be restored>

Moreover,

Upload database 9.x.x postgresql to 11.x.x server.

pg_dump –no-owner -C db-snippetbucket-erp-com | psql -h ip-address-or-host -U user-name db-snippetbucket-erp-com

Without ownership, Upload database 9.x.x postgresql to 11.x.x server.

pg_dump –no-owner -C db-snippetbucket-erp-com | psql -h ip-address-or-host -U user-name db-snippetbucket-erp-com

For expert support, please contact us.