CODEWITHSUNDEEP
sqlpostgresqlamazon-web-servicesamazon-rdspostgresql-9.6
Rando
Rando

Reputation: 55

How to make duplicate a postgres database on the same RDS instance faster?

thank you guys in advance.

I am having a 60GB Postgres RDS on aws, and there is databaseA inside this RDS instance, I want to make a duplicate of databaseA called databaseB in the same RDS server.

So basically what I tried is to run CREATE DATABASE databaseB WITH TEMPLATE databaseA OWNER postgres; This single query took 6 hours to complete, which is too slow. I see the max IOPS during the process is 120, not even close to the limit of aws general SSD's limit 10,000 IOPS. I have also tried tunning up work_mem, shared_buffers, effective_cache_size in parameter group, There is no improvements at all.

My last option is to just create two separate RDS instance, but It will be much easier if I can do this in one instance. I'd appreciate any suggestions.

(The instance class is db.m4.xlarge)

Upvotes: 1

Views: 922

Answers (1)

Deepak Singhal
Deepak Singhal

Reputation: 10866

As mentioned by Matt; you have two options:

  1. Increase your server size which will give you more IOPS.
  2. Switch to provisioned IOPS

As this is a temporary requirement I will go with 1 because u can upgrade to max. available server --> do database copy --> downgrade db server seamlessly and won't take much time. Switching SSD to provisioned IOPS will take lots of time because it needs to convert your data and hence more downtime. And later again when u will switch back from provisioned iops to SSD again it will take time.

Note that Both 1 & 2 are expensive ( if u really dont need them ) if used for long term; so u can't leave it as is.

Upvotes: 1

Related Questions