Skip to main content

RDS Postgres Database Recovery

Keywords: PG, reboot, killall

Figure out if you need to completely roll back a database or create a new instance of your database. If you create a new instance, the current instance will still exist. If you rollback, it rolls back the currently active database.

Rolling back a database (Taken from https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html)​

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
  2. In the navigation pane, choose Databases.
  3. Choose the DB instance that you want to restore.
  4. For Actions, choose Restore to point in time.
  5. The Launch DB Instance window appears. pitr-launch-db-instance.png
  6. Choose Latest restorable time to restore to the latest possible time, or choose Custom to choose a time.
  7. If you chose Custom, enter the date and time to which you want to restore the instance. Note: Times are shown in Coordinated Universal Time (UTC).
  8. For DB instance identifier, enter the name of the target restored DB instance.
  9. Choose other options as needed, such as storage autoscaling.
  10. Choose Launch DB Instance.

Restoring From a snapshot:​

  1. Sign in: https://console.aws.amazon.com/rds/
  2. In the navigation pane, choose Snapshots snapshot.png 3.Choose the DB snapshot that you want to restore from.
  3. For Actions, choose Restore Snapshot. 2021-02-08_1536.png
  4. Engine is Postgres, choose new unique instance name (this is not the name of the database, but the name of the instance) 2021-02-08_1538.png
  5. Choose Specs for the database: 2021-02-08_1541.png
  6. For Prod, choose multi-deployment zz.png
  7. Look everything once over and if everything seems appropriate to you, click Restore Database 2021-02-08_1541.png

NOTE:​

  • A URI is built via the following syntax: postgres://<username>:<password>@<host>:<port>/<dbname>
  • Restoring from a snapshot will create a different host, everything else should be the same
  • Rolling back a database will have no differences in URI
  • You need to warm up the database cache if you plan to use the new instance restored from snapshot
  • Can run something like this to warm for the larger tables in shell
tables=('ab_logs' 'app_accesses' 'app_activations' 'app_form_responses' 'app_infos' 'apps' 'emails' 'general_copies' 'payment_notifications' 'pro_subscriptions' 'search_analytics' 'simple_copies' 'user_infos' 'users')
for table in "${tables[@]}"
  do
      echo "starting ${table}"
      psql -X <URI> -c "SELECT pg_prewarm('${table}', last_block:=1000);"
done