0

Migration to another server is not easy when we have a large database. We want to move our database in Linode to amazon RDS database, i tried to pg_dump and pg_restore and it succeeds but it consume a lot of time.

My Question is: is there a fast way like compression OR can i sync the database in linode with the database in Amazon RDS

so i just then need to change CNAME to refer to amazon and then it will continue work as we already sync the database together ?

Any suggestion would help !

Craig Ringer
  • 11,083
  • 9
  • 40
  • 61
mohamed-ibrahim
  • 131
  • 1
  • 7

1 Answers1

4

If you were migrating to another real host, not to RDS, you could use streaming replication to do what you wanted:

  • pg_basebackup the DB to the new host
  • Start the replica with a recovery.conf pointing to the current master
  • Let replication catch up
  • Reload the master with synchronous replication enabled for the replica
  • ... then shut the master down, restart the replica as a master, and go.

This won't work with RDS because RDS doesn't give you access to the underlying server. You can't use pg_basebackup or control your own streaming replication. So if you're using RDS, your only real option is a dump and reload.

In theory Amazon could allow users to set up RDS replicas of masters that are not in RDS, then promote them to masters. This would be a very good idea, as it'd let people migrate DBs into RDS much more easily. It doesn't have that capability at time of writing, though, and it'd potentially be complicated to do because it'd only work when the origin database was compiled with compatible options, the same major version, and on the same CPU architecture as the replica server. You can't replicate between x86 and x64, for example. It'd also prevent Amazon from freely modifying their PostgreSQL to fit their needs.

This works in reverse too. You can't migrate from RDS using streaming replication. So if you have a big, busy DB in RDS, you're going to have a very hard time getting it back out again without a big chunk of downtime.

Frankly, if you're already running your own DB, I fail to see the appeal in RDS. It's expensive and removes most of the control you have over your data. (Note that I work for another company in the PostgreSQL services space, though, so my opinion needs to be considered with that in mind).

You might be able to use a 3rd party replication solution like Londiste, Bucardo, or Slony-I to replicate your database to RDS. I haven't tried any of these tools with RDS, and suspect they won't work due to the need to install extensions, run ticker daemons, etc. You might be able to do it with an EC2 node, but it'd be complicated and tricky to get right.

Craig Ringer
  • 11,083
  • 9
  • 40
  • 61
  • can you help with correct formula for pg_dump and pg_restore and it give me errors when try to restore – mohamed-ibrahim Dec 04 '13 at 23:53
  • 1
    @mohamed-stark `pg_dump` not `pg_dumb`. And no, not without basic details on what you're actually doing. Post a question to dba.stackexchange.com with *detailed explanations* of every step you take, exact command lines, exact error messages, PostgreSQL version(s), etc. – Craig Ringer Dec 04 '13 at 23:54
  • 1
    I considered your opinion. RDS seems like just another form of vendor lock-in to me. No thanks! – Michael Hampton Dec 04 '13 at 23:58
  • AWS supports replication both ways for MySQL RDS, so it's likely just a matter of time before they support it with Postgres. – EEAA Dec 05 '13 at 02:53
  • @EEAA Pg's replication is block level and not compatible across DB versions/builds; It's quite different to MySQL's. I suspect RDS may add support for external replication once Pg gets [logical replication](), hopefully in 9.5. See http://wiki.postgresql.org/wiki/BDR_User_Guide – Craig Ringer Dec 05 '13 at 03:18