Move Postgres 13 database from Heroku to AWS RDS
Updated: Jul 8, 2022
As Heroku started to limit my infra possibilities due to lack of visibility inside the servers and configurability then I decided to move all my infra from Heroku to AWS. Also Heroku started to be extremely costly as my application grew. One of the bigger components was to move Heroku postgres addon database to AWS RDS PG 13.
There were minor version differences with Heroku having PG13.5 and AWS having latest supported version as 13.4 but as I discovered the dump is not affected by it I didn't dig much into this matter. As it turned out then everything worked fine as well in the long run.
Will describe the steps I took to move the database over.
Take a dump
Yeap, sometimes you need to. Specially when you are not having fancy enough PG addon in Heroku which takes care of it automatically. My addon subscription wasn't and therefore I had to dump the db manually as follows:
Open your Heroku application's dashboard
Click Resources tab
Click the Postgres addon
Click Durability tab
There you should see the MANUAL BACKUPS & DATA EXPORTS section in the bottom of page with the Create Manual Backup button- click it
Wait a bit and if it's really small db, it might take only few seconds
When the dumping finishes successfully it will show up in the table with the possibility to download it- do it.
Prepare the DB in AWS
I'm not gonna go into much detail here. Just sharing some general best practices I follow with DBs:
Make sure you are not using too big version difference in AWS compared to Heroku. As I said my 13.4 vs 13.5 wasn't a big deal but this is still risky. Preferably always use the same db version if possible.
Isolate the DB from outside environment (specially if you are setting up production database). For that I create a separate Security Group in AWS for DB which only allows incoming DB connections on port 5432 from the application server. This is the only security group I will assign to DB. I also prohibit the "access from outside environment" (separate checkbox in the db setup wizard).
Disable automatic minor version upgrades- might be convenient but this is guaranteed to cause problems in bigger production environments with more strict SLAs, specially if the application servers are more sensitive to DB changes.
Log as much as possible, I love the Performance Insights feature in AWS! Also if the cost is ok, then send the DB logs also to CloudWatch if possible- you can make some fancy dashboards there to monitor the state of DB and also define alarms for custom conditions to alert you if something should go off.
NB! Make sure you have also prepared the application-user with all the needed permissions and ownerships and created the application DB itself as well in AWS RDS instance.
Restore the dump to RDS
Check the man-page for more thorough instructions and configuration possibilities but:
pg_restore --verbose --clean --no-acl --role=db_role -h your.db.address -U user_to_connect -d database_name path/to/your-db-heroku-dump.sqlTo save time on reading the man page I will also describe the parameters here:
--verbose: Specifies verbose mode.
--clean: Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)
--no-acl: Prevent restoration of access privileges (grant/revoke commands).
--role: Specifies a role name to be used to perform the restore. This option causes pg_restore to issue a SET ROLE rolename command after connecting to the database. It is useful when the authenticated user (specified by -U) lacks privileges needed by pg_restore, but can switch to a role with the required rights. Some installations have a policy against logging in directly as a superuser, and use of this option allows restores to be performed without violating the policy.
-h: Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. The default is taken from the PGHOST environment variable, if set, else a Unix domain socket connection is attempted.
-U: User name to connect as.
-d: Connect to database dbname and restore directly into the database. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options.
You might see some errors like
pg_restore: error: could not execute query: ERROR: role "rjhfhybtspyhxt" does not existbut this is can be ignored since this role indeed doesn't exist and we don't want it to exist in the new DB as it seems to be one of the Heroku backend roles. Not sure why the --role parameter in the pg_restore command didn't help here.
Hope this helps you!
PS! If you liked the article you can buy me a coffee to support me going! :)



Comments