- Newest
- Most votes
- Most comments
How about converting OID LOBs to BYTEA:
- Create a new column of type BYTEA
- Write a script to read from pg_largeobject and populate the BYTEA column
- Migrate using DMS (which supports BYTEA in Full LOB mode) This avoids the OID indirection and makes the data DMS-compatible.
You can use Homogeneous data migrations for PostgreSQL to PostgreSQL migrations. For Homogeneous migration AWS DMS connects to the source data provider, reads the source data, dumps the files on the disk, and restores the data using native database tools (pg_dump/pg_restore/Publisher/Subscriber) to provide easy and performant like-to-like migrations.
AWS DMS homogeneous migration from PostgreSQL to Amazon Aurora PostgreSQL
Migrating data from PostgreSQL databases with homogeneous data migrations in AWS DMS
Using a PostgreSQL database as a source for homogeneous data migrations in AWS DMS
Interesting, I had somehow missed that. We have been testing using an EC2 instance in the source account using pg_dump to local filesystem and then pg_restore to the RDS endpoint in the target account. We also tried pgcopydb which bypasses the need to write to local FS but both methods were prohibitively slow. Is the homogenous data migration likely to be faster than running pg_dump on an EC2 instance?
It depends on several factors. AWS DMS (homogeneous) gives better control over the migration process—for example with databases >100 GB, ongoing replication needs, or when you need to handle data-type limitations. For smaller databases, a one-time migration, or when you want maximum simplicity, pg_dump can be sufficient and often quicker to set up.
Relevant content
asked 4 years ago
Thanks - I passed that on to our DBAs as it's a bit outside my area (infrastructure) and coincidentally they had already started looking at this approach. Hopefully we can get some tests done soon.