1

I have an AWS RDS instance with multiple PostgreSQL databases in there including an application database and a monitoring database. The problem is that the monitoring database is consuming lots of resources and causes performance problems in the whole instance, so I'd like to have the application database and the monitoring database in different AWS RDS instances.

I have been investigating the issue and I've found that people are interested in having all their databases running on the same instance, while what I'm looking for is the other way around, so I'm wondering if I'm missing something.

The solutions that I've found are basically as follows:

  • Use an AWS RDS snapshot

    This should be easy to do, but snapshots are at the instance level and I don't want to copy all databases, but just a single one.

  • Use the AWS Database Migration Service

    The database migration service looks like a good approach because it uses a replication instance to make sure that all data written to the database while the migration task is running is captured. Unfortunately, I don't see how to use it for a single database.

  • Create a new instances and use pg_dump/pg_restore manually.

    This approach seems the easiest one, but I'd need to make sure that either the application or the monitoring service are stopped while this is running.

So far, it seems to me that the right solution is the third one for its simplicity. Do you see any advantage/disadvantage that I might have overlooked?

Improve this question
2
  • 1
    You can safely replicate the whole instance using DMS, then remove the unnecessary DBs on both resulting clusters.
    – dezso
    Jan 9, 2018 at 14:17
  • @dezso Thanks for your comment. I tried using a snapshot and then removing the unnecessary DBs as in your comment and that worked out well. The problem is that now the allocated storage is too big, but that's another problem.
    – jcollado
    Jan 10, 2018 at 8:52

1 Answer 1

Reset to default
2

You can safely replicate the whole instance using DMS, then remove the unnecessary DBs on both resulting clusters. After this, you will most possibly have excessive storage on the new instance - I think (but never tried) you can just do another such migration to a fitting (smaller) instance type.

Improve this answer
1
  • Thanks for your response. I tried DMS multiple times, but to no avail. The problem is that DMS doesn't really migrate the schema (default values and indexes are missing, for example). If I create the schema myself, then DMS fails to insert data in the right order. If I try to modify the schema after the migration, then indexes are created, but getting default values for each table in a SQL file is not something I know how to get from pg_dump. In the end, I created my own instance in EC2 and run pg_dump <source> | psql <destination> and worked perfectly. Sometimes good old tools are better.
    – jcollado
    Jan 14, 2018 at 21:02

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.