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.

Ronen Ness
  • 169
  • 8

1 Answers1

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)

DerfK
  • 19,313
  • 2
  • 35
  • 51