0

We have a quite large (at least to us) database that has over 20.000 tables, which is running in an AWS EC2 Instance, but due to several reasons, we'd like to move it into an AWS RDS instance. We've tried a few different approaches for migrating into RDS but as per the data volume involved (2TB) and RDS' restrictions (users and permissions) and compatibility issues, we haven't been able to accomplish it.

Given the above facts, I was wondering if PostgreSQL actually supports something like mapping a remote schema into a database, if that would be possible we could try to tinker individual per schema migrations and, not the whole database at once, which would actually make the process less painful.

I've read about the IMPORT FOREIGN SCHEMA feature which seems to be supported from version 9.5 and, that seems to do the trick, but is there something like that for 9.4.9?

Improve this question
4
  • If its a new RDS instance, why not make it 9.6.1? Commented Apr 24, 2017 at 15:02
  • @pozs on the RDS side that certainly is an option, but don't I need a 9.5+ on the other side for the IMPORT FOREIGN SCHEMA to work? I would be a little worried about the approach of using the RDS instance as the "in use" database as that would assume that initially, ALL our schemas would be remote and I'll have to "localize" them gradually, instead of the opposite of using the EC2 instance and gradually making schemas remote. Commented Apr 24, 2017 at 15:15
  • @gvasques For IMPORT FOREIGN SCHEMA to work, the postgres_fdw needs to support it, which is on the "host" side. Also see Cross-Version Compatibility. Commented Apr 24, 2017 at 15:23
  • @pozs Thanks for the extra tip, will definitely research on that approach. Thanks a lot! Commented Apr 24, 2017 at 15:25

1 Answer 1

Reset to default
1

You might want to look at the AWS Database Migration tool, and the associated Schema Migration tool.

This can move data from an existing database into RDS, and convert - or at least report on what would need to be changed - the schema and associated objects.

You can run this in AWS, point it at your existing EC2-based database as the source, and use a new RDS instance as the destination.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Worth mentioning here is that DMS appears to support a continuous migration process such that when you're "done," the target db can remains in sync with the source so you're not just sitting on a frozen snapshot of the db, but rather a replica that remains in sync until you're ready to abandon the source db by moving your application to the target... if I'm reading it right.
@Michael-sqlbot: That is correct. There are usually some restrictions or configuration changes needed to support the continuous migration (i.e. for mysql, you have to use row-based replication) but your target can stay in sync with the source if you can comply with the restrictions.
Already tried the DMS tool, but doesn't support al structures present in our database as it found over 400 problems in the migration process :(
@gvasquez: did you run the schema migration tool? It's really intended for heterogeneous migrations. If the source and destination are the same, use the native tools to dump the schema (with no data) from the source, and use that to create it in the destination.
@chris I tried both the schema tool and pg_dump way. But seems like RDS has tighter rules for certain schema components that won't migrate from our db to an RDS instance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.