1

I need to import a PostgresSql db from an old server into a new Amazon RDS.

I created a db dump file, connected to my RDS via pgAdmin, and started to import pgAdmin console (\i dump_file.sql).

Everything seems to work, but its really really slow and seems to be stuck on the same row for couple of hours.. I opened the RDS monitor and I see the following:

enter image description here

So it looks like there are write operators (but why so few) and some CPU usage, but it looks like its using really low percent of its ability. Also I checked the disk usage in details and the disk shrinks so its really writing, eg its not stuck, just very slow.

What can I do to import my db faster? why is it so slow? why does it use so little CPU and write ops?

It feels like RDS doesn't use its full potential..

PS my DB is approx 10Gb in size.

3

The default postgresql configuration is not good for bulk import of data. The main thing you need to do before importing that much data is to increase max_wal_size to reduce the number of times postgresql will need to perform a checkpoint during the import. Also, if you are certain about the quality of the data, import it within a transaction. See https://www.postgresql.org/docs/9.5/static/populate.htm for other ideas (turn off replication, disable indexes, disable triggers, use pg_restore --jobs X to parallelize the import)

1
  • If I don't commit until the end, is that considered a transaction or is there more to it? Aug 1 '16 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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