Postgresql – Is it possible to migrate a postgresql database without downtime

Copy 50 to 100 databases to a completely new server with zero downtime? I don't think that's going to happen. When you're talking about a migration, planned downtime is an absolute must.

In this case, depending on the size of the databases, I'd take the scripting route with this. If you are proficient with PowerShell, you can loop through all the databases and take backups to store in accessible storage for the new server. Then on the new server, you can script out the restore of all the databases.

Don't forget about the server-level items (logins, jobs, etc.). You can script these as well, but with this particular requirement SSIS actually makes it very easy. There are tasks to do all of these server-level items.

If you're expecting no downtime, how are the clients going to redirect their requests to the new database server? There are a few external solutions for this, but something to consider when you promise "zero downtime".

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...