We want to run "pg_dump" on our RDS (which is Postgresql). But we know if we run "pg_dump" on our RDS, it will decrease the performance of RDS working on our production environment. We, thus, don't know how to run the "pg_dump" command on our RDS without decreasing its performance.
We appreciate any solution that helps us to solve our problem.
FYI, we think about a solution but we don't know it is correct or not. In our solution, we think we get a snapshot from our RDS and then try to transfer the snapshot instance to a test environment and enable it and try to run the "pg_dump" on it. Is it the right solution?
CodePudding user response：
Your snapshot solution will work. Or you could add a read replica instance and configure pg_dump to connect to the read replica.
CodePudding user response：
Your proposed solution to restore a secondary database from a Snapshot and run pg_dump there is one way to go.
Another is to create a read-replica and run pg_dump on there - the process is very similar with the difference, that the data on the read-replica will be kept up to date.
Option three would be to export your snapshot data to S3 and run whatever analytics you want on that data. It's not identical to pg_dump but depending on your use case it may work.
CodePudding user response：
in both case it MAY affect the performance of other clients connected to the database. but it will not stop your DB operation.
- when you are taking manual snapshot
- creating read replica, RDS takes a snapshot of the source instance and creates a read-only instance from the snapshot. Amazon RDS then uses the asynchronous replication method for the DB engine to update the read replica whenever there is a change to the primary DB instance.
for safer side you could scale-up your instance then go for snapshot, once done then scale-down back to previous state.
IMHO , first do it on lower environment then go for production.