3 ms·

Overall a nice post. It doesn't quite get into all the options for pg_dump as there's a number of other flags that can be useful to reduce downtime. It's also p

Overall a nice post. It doesn't quite get into all the options for pg_dump as there's a number of other flags that can be useful to reduce downtime. It's also probably worth taking a look at Amazon Migration Service as well (though I haven't personally used it or heard from others that have yet) - https://aws.amazon.com/dms/ https://aws.amazon.com/dms/. One tool they did miss for continuous archiving is WAL-E, which tends to be the one most used including by us at Citus Cloud and Heroku Postgres - https://github.com/wal-e/wal-e https://github.com/wal-e/wal-e

lexandstuff 10y ago
Unfortunately, DMS is quite limited for Postgres. We trialled it but found these two limitations deal breakers: * "AWS DMS doesn't support change processing on Amazon RDS for PostgreSQL. You can only do a full load." * "AWS DMS doesn't map some PostgreSQL data types, including the JSON data type. The JSON is converted to CLOB." http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.Limitations http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.... Hopefully it improves soon, since it's an otherwise awesome tool.
willlll 10y ago
Yeah, I would* do a `pg_dump | pg_restore` with these settings: https://github.com/heroku/heroku/blob/master/lib/heroku/helpers/pg_dump_restore.rb#L65-L73 https://github.com/heroku/heroku/blob/master/lib/heroku/help... *disclaimer: I wrote that code while at heroku
chrisatumd 10y ago
We've tested out DMS to migrate from a 3 node Oracle RAC to Oracle RDS, and I was incredibly impressed. They list a bunch of limitations in their documentation that you should look at first. It migrated our roughly 1 TB database in two hours and ten minutes and handled replication flawlessly - not a single record discrepancy between the two database systems over a week long test. We were not expecting it to work that well and doubted that it would work with RAC as the source.
seag64 10y ago
Yeah, came here to say that the migration service was built to solve this exact problem (among others).
eliribble 10y ago
Maybe Yani himself can chime in when it's daytime in his time zone, but if memory serves correctly he did look at WAL-E. It had some nice features in its favor. I'm not sure why it didn't make it into the blog post.

lexandstuff 10y ago

Unfortunately, DMS is quite limited for Postgres. We trialled it but found these two limitations deal breakers: * "AWS DMS doesn't support change processing on Amazon RDS for PostgreSQL. You can only do a full load." * "AWS DMS doesn't map some PostgreSQL data types, including the JSON data type. The JSON is converted to CLOB." http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.Limitations http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.... Hopefully it improves soon, since it's an otherwise awesome tool.

willlll 10y ago

Yeah, I would* do a `pg_dump | pg_restore` with these settings: https://github.com/heroku/heroku/blob/master/lib/heroku/helpers/pg_dump_restore.rb#L65-L73 https://github.com/heroku/heroku/blob/master/lib/heroku/help... *disclaimer: I wrote that code while at heroku

chrisatumd 10y ago

We've tested out DMS to migrate from a 3 node Oracle RAC to Oracle RDS, and I was incredibly impressed. They list a bunch of limitations in their documentation that you should look at first. It migrated our roughly 1 TB database in two hours and ten minutes and handled replication flawlessly - not a single record discrepancy between the two database systems over a week long test. We were not expecting it to work that well and doubted that it would work with RAC as the source.

seag64 10y ago

Yeah, came here to say that the migration service was built to solve this exact problem (among others).

eliribble 10y ago

Maybe Yani himself can chime in when it's daytime in his time zone, but if memory serves correctly he did look at WAL-E. It had some nice features in its favor. I'm not sure why it didn't make it into the blog post.