Pretty sure you are going to have to perform a good old-fashioned dump-and-restore here. Amazon doesn't seem to offer any other import options than loading in a dump:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html
and even if they did, AFAICT the only export options offered by Heroku are pg_dumps (i.e. not basebackups or the ability to set up an external hot standby node):
https://devcenter.heroku.com/articles/heroku-postgres-import-export
If you're lucky, you're using a Postgres version new enough to support both parallelized pg_dump (9.3+) and parallelized pg_restore (8.4+), which will make your job a bit quicker. And hopefully you can afford to take a maintenance window during which you lock out writes to your site while the switchover is underway...
This is a good question,
working in cloud environment is tricky sometimes.
You can use pg_dumpall -f dump.sql
command, that will dump your entire database to a SQL file format, In a way that you can reconstruct it from scratch pointing to other endpoint. Using psql -h endpoint-host.com.br -f dump.sql
for short.
But to do that, you will need some EC2 instance with some reasonable space in disk (to fit your database dump). Also, you will need to install yum install postgresql94.x86_64
to be able to run dump and restore commands.
See examples at PG Dumpall DOC.
Remember that to keep integrity of your data, it is recommended (some cases it will be mandatory) that you shutdown the systems that connect to the database during this maintenance window.
Also, if you need speed up things, consider using pg_dump
instead pg_dumpall
, by taking advantage of parallelism (-j njobs
) parameter,
when you determine the number of CPUs involved in the process, for example -j 8
will use until 8 CPUs. By default the behavior of pg_dumpall
or pg_dump
is use only 1. The only advantage by using pg_dump
instead pg_dumpall
is that you will need to run the command for each database that you have, and also dump the ROLES (groups and users) separated.
See examples at PG Dump DOC and PG Restore DOC.
Best Answer
It's not supported in either direction, with the most pressing problem being the platform and architecture dependence of that replication method.
In the case of migrating to the service, the problem is doubly so, because slurping random, physical base backups from the internet as would be required to set up replication as it is seen today is a somewhat ticklish sounding proposition.
It sucks, and I'd like to lift that restriction some day, if that and the other problems (some not) can be solved.
Please feel free to write into postgres@heroku.com if you want to discuss your problem in more detail, although I'm most unsure if we have the technology to make this a super-easy proposition right now. Still, the main way we can make it better is by studying it, if you'd be so kind to share.