Skip to main content

Data migration

If you need to migrate data from the document database into the Document capability of the PostgreSQL database, follow the steps below. pgdoc does not move the existing data in the document database automatically.

document database  --export-->  intermediate files (JSON / EJSON)  --import-->  PostgreSQL database

Pause writes during the migration to avoid inconsistencies between the two sides, and keep the original document database data as the basis for rollback. Decide whether to retire the old data only after verifying that your workloads run correctly on PostgreSQL.

Export data from the document database​

  • Export target collections from the console
  • Or iterate collections with the SDK / HTTP API and export documents as JSON files
  • The export keeps the document database JSON representation (EJSON), preserving type information such as ObjectId and dates

💡 Tip: Export collection by collection so that you can verify and troubleshoot in batches during the import stage.

Import into PostgreSQL​

Import the exported data into the PostgreSQL database. Choose a method based on data volume and operational requirements:

MethodUse case
DMC importA small number of collections, development and testing, manual operations
SQL / script importSchema and data initialization, with a controllable and auditable process
Batch writes from a business scriptData that needs cleaning or field conversion during import

For details, see Import data and DMC database management.

Keep each import batch to 1000 records or fewer. Split larger imports into batches so that a failed batch does not force you to start over.

⚠️ Note: Documents in the document database and tables in PostgreSQL are not one-to-one. Decide the mapping first — which collections become tables, and which fields become columns or jsonb fields — and then run the import.

The target schema for the import should be pgdoc_data. See Data storage location.

Verify with sampling after the import:

select count(*) from pgdoc_data.<your_collection>;
select * from pgdoc_data.<your_collection> order by created_at desc limit 10;

After the migration, enable pgdoc to have requests served by PostgreSQL.