I am migrating from 2800GB postgreSQL(version 13.12) rds instance (out of 2800GB only 450GB is used) to a new 550GB postgreSQL rds instance, what value should i add to pg_transport.num_workers, max_worker_processes, pg_transport.work_mem
The rds instance type for source account is db.t4g.xlarge and destination account is db.t3.micro I was following the link and was assigning values for db parameters accordingly https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.TransportableDB.html#PostgreSQL.TransportableDB.Parameters
i tried below values: configuration 1:
pg_transport.num_workers: 10 max_worker_processes: 33 pg_transport.work_mem: not changed
configuration 2:
pg_transport.num_workers: 6 max_worker_processes: 27 pg_transport.work_mem: 128MB
for both the configuration the transportation is taking long time, around 3% per hour. Is it normal to take this amount of time?
currently the logs are showing like below logs after running the transport.import_from_server() funtion
From my experiments, yes, it is normal for it to take that long.
I suppose it is mostly due to how powerful the instances you are using are. Bigger instances have much higher improvements.
Still, DMS might be the best way to do this procedure for the lowest pain as @SerhiiH proposed.
Also keep in mind T instances are burstable, and get capped when they exhaust the CPU and/or IO burst credits available to them. When that happened in my tests, transfer rate went from 130 to 9 MB/s.
For people wanting to fixate on this and do the procedure manually, I did run a couple of tests:
db.t4g.medium to db.t4g.medium, gp3 storage, ~ 350 GB database:
| 1st run | 2nd run | 3rd and 6th run | 4th run | 5th run | |
|---|---|---|---|---|---|
| pg_transport.num_workers | 2 | 4 | 8 | 8 | 12 |
| max_worker_processes | 15 | 21 | 33 | 33 | 45 |
| pg_transport.work_mem | 131072 (128 MB) | 131072 (128 MB) | 131072 (128 MB) | 262144 (256 MB) | 131072 (128 MB) |
| Transfer rate in MB/s (min, max, avg) | 19, 58, 31 | 19, 95, 66 | 50, 255, 138 | 4, 255, 101 | 25, 165, 85 |
| ETA after 10m | 3h13m | 1h36m | 52m | 1h | 1h11m |
| Source, CPU usage (avg) | 10% | 15% | 40% | 39% | 37% |
| Source, additional RAM usage (avg) | N/A (did not check) | N/A (did not check) | 1.5 GB | N/A (did not check) | N/A (did not check) |
| Target, CPU usage (avg) | 12% | 18% | 34% | 28% | 25% |
| Target, additional RAM usage | N/A (did not check) | N/A (did not check) | 1.5 GB | N/A (did not check) | N/A (did not check) |
db.m6i.xlarge to db.m6i.xlarge, gp3 storage, ~ 390 GB database:
| 1st run | 2nd to 5th run | |
|---|---|---|
| pg_transport.num_workers | 8 | 16 |
| max_worker_processes | 33 | 57 |
| pg_transport.work_mem | 131072 (128 MB) | 131072 (128 MB) |
| Transfer rate in MB/s (min, max, avg) | 97, 155, 135 | 248, 545, 490 |
| ETA after 10m | 46m | 14m |
| Time taken | 48m | 14m |
| Source, CPU usage (avg) | 12% | 42% |
| Source, additional RAM usage | 940 MB | 1.5 GB |
| Target, CPU usage (avg) | 17% | 65% |
| Target, additional RAM usage | 1.3 GB | 3.3 GB |
