How to automate copying an RDS table to another RDS database

0

My main postgres database has a couple of materialized views that I refresh daily. I need to copy the output of these views over to another postgres database as tables that will help with calculating reports. Does the RDS system have a tool that I can use to automate this process? So far, I have looked at Data Pipelines and AWS Glue, but these services do not seem to accomplish the workflow I am attempting to create.

Data Pipelines is a legacy tool in maintenance mode. It seems to be designed to extract data from an RDS instance so it can be dumped into flat files in an S3 bucket. The goal is to run analysis against flattened data rather than move data between RDS instances.

AWS Glue is the new tool that replaces Data Pipelines. Again, it wants to flatten data and move it to an S3 bucket. I have no desire to use S3 buckets as an interim storage for my workflow. I simply want to copy a table in database A over to database B.

Does AWS have an RDS tool that will accommodate copying a table from database A over to database B?

2 Answers
1

Hi Hanmari.

I would recommend you checking AWS Database Migration Service (DMS). It can accomplish exactly what you are looking for.

DMS Architecture

Essentially you would connect both source and destination databases using endpoints and create a migration task in a DMS instance. You can even establish to maintain the destination database automatically up to date. There is also the option to use DMS Serverless which eliminates the need for you to manage another instance.

I hope this helps.

profile pictureAWS
EXPERT
answered 8 months ago
  • Hi there. If this answer helped, please consider accepting it. Thank you!

  • Hi Jose, I am looking into the data migration service angle. I was under the impression that data migration services were intended for a single-use export of a large dataset. My use case is very small and needs to reoccur regularly. So far, I have not seen any tools to implement a recurring copy/replace process. I imagine I would need to save the migration as a process that I could execute from the AWS CLI and then use a cron to trigger the migration on a schedule. The DMS Serverless option sounds promising if I can figure out the over-engineered interface.

0

You can create a read replica of your RDS instance for the reporting server.

AWS
MODERATOR
philaws
answered 8 months ago
  • I already have my main database replicated and am trying to avoid having to read from its replicas. The queries I am running on my logging database take a long time to run and the replicated servers time out the connection. I have tried increasing the timeout delay to ninety seconds, but this has not worked and I do not wish to push this restraint further. Foreign Data Wrapper connections have similarly failed to yield a stable, long-lasting connection for my number-crunching reports.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions