Hi all

I have customers who're becoming interested in using RDS PostgreSQL to replace self-managed AWS EC2 setups with all the attendant backups, replication, failover, etc. Feature-wise, RDS is acceptable for most of them as it includes the key extensions that most users require.

I can't move any of them over to RDS, though, because they're operations that can't afford multi-hour downtime. Right now, the only way to move data into or out of RDS PostgreSQL is with pg_dump and pg_restore. Even with parallel dump and restore a migration would take much too long.

Personally I'm also concerned about being able to get their data back out if they want to move off RDS Postgres later or use a hybrid setup later.

When looking around in RDS I can see that WAL archiving is clearly enabled. WAL seems to handed to an Amazon-internal script that presumably stashes it in S3. RDS Postgres uses streaming replication for multi-AZ. So the functionality is present internally and working.

What I need to be able to move people onto (and off) RDS Postgres is to expose that externally. For inward migration:

• Ability to "seed" an RDS Postgres instance with a data directory from outside RDS (created by pg_basebackup or by pg_start_backup + rsync + pg_stop_backup)
  
• Ability to set the new RDS postgres instance up with a recovery.conf that replays from a remote streaming replica and also restores WAL from a restore_command (an amazon-provided script, of course) that can read WAL from S3, perhaps as archived by WAL-E. Streaming replication only isn't good enough, WAL archiving fallback is really necessary.
  

For outward migration, which would be very reassuring to have:

• Ability to create replication{} entries in pg_hba.conf to allow remote streaming replication connections and pg_basebackup.
  
• Ability to set up the RDS Postgres instance to store WAL segments (and any backup label etc it creates) in an S3 bucket accessible to the user.
  

Possible caveats and concerns include:

• Mismatched compile time options or mismatched architecture. There's no way around this except for the user to migrate (using slony-I, Londiste, BDR when it's ready, etc) to a compatible DB instance then migrate into RDS.
  
• If RDS requires any special initdb-time options. Without knowing more about the innards of RDS PostgreSQL it's hard to say whether that could be a concern.
  
• Security concerns with "untrusted" procedural languages, adminpack, etc already installed in the DB before migration. Disabling these in your version of plperl.so, plpython.so, etc will be trivial, so attempting to use them post-migration will result in an ERROR.
  
• Security concerns with user defined C extensions. This is a non-issue, because C extensions can't be embedded inside the database, they have to be in the libdir, and the user can't modify the libdir in RDS instances.
  
• Security concerns with user GRANTs and REVOKEs, especially for superuser. You'd probably need code that walked pg_authid on startup to transform any superuser rights into grants to rds_superuser instead.
  
• Missing extensions. Just like with an existing PostgreSQL filesystem level dump and restore, this just results in ERRORs when attempting to use extension functionality. Users can drop the objects that use the extensions.
  

So while there are a few challenges, there's nothing insurmountable unless Amazon have patched their version of PostgreSQL so it's no longer on-disk compatible with community PostgreSQL.

If inward and outward migration by replication can be made possible, it'd be a real boost, so I'd love your thoughts on this.

See also this related Stack Overflow question: http://dba.stackexchange.com/q/61305/7788

(Please forgive the awful formatting, this forum markup is not familiar and not much fun. Don't suppose you guys will support Markdown at some point?)

Edited by: ringerc_2q on Jun 1, 2014 10:13 PM