@kapa.ai what is the correct way to do a migration of a large postgres database that has tables incrementally populated by airbyte?

Migrating a Large Postgres Database with Airbyte Incremental Syncs The knowledge sources provide some relevant guidance, though they don't cover every scenario exhaustively. Here's the recommended approach based on what's documented: 1. Use Dual-Writing for Incremental Connections When migrating (e.g., upgrading destination versions or moving to a new schema), the safest approach for incrementally-synced tables is dual-writing — running two parallel connections temporarily. [Upgrading to Destinations V2] The steps are: 1. Create a new destination connection pointing to the new target schema/database. 2. Copy existing raw data from the old schema to the new one, so historical data doesn't need to be re-synced: ◦ Run the appropriate SQL in your data warehouse to copy raw tables from the original stream namespace to the new one. 1. Copy the connection state from the old connection: ◦ Go to the existing connection → Settings → Advanced → copy the state. 1. Paste the state into the new connection via Update State — this prevents re-syncing all historical data. 2. Enable the new connection to run in parallel. 3. Once verified, switch your downstream processes (dashboards, dbt models, etc.) to the new tables and pause the old connection. 2. If Upgrading the Postgres Destination to v3.0.0 (Direct Load) If you're upgrading the Postgres destination connector itself, be aware of these breaking changes: [Postgres Migration Guide] • Raw tables are no longer created by default — data goes directly to final tables. • The

_airbyte_loaded_at

column is replaced by

_airbyte_meta

and

_airbyte_generation_id

. • If you depend on both raw and final tables, you must create two separate Postgres destination connectors (one with "Raw tables only" enabled, one without) and dual-connect each source. • Update any downstream dbt models or SQL queries that reference old raw tables or

_airbyte_loaded_at

. 3. Performance Considerations for Large Databases Keep in mind: [Postgres Troubleshooting] • Postgres is not a data warehouse and is recommended only for datasets under ~10GB. For larger volumes, consider BigQuery, Snowflake, or Redshift. • Monitor memory and CPU during syncs, especially with typing and deduplication enabled. • Scaling IOPS (disk throughput) is as important as memory/compute when handling large data volumes. 4. Consider CDC for the Source For large Postgres source databases, using CDC (Change Data Capture) via logical replication is the most efficient method, as it only replicates actual changes rather than full table scans. [Postgres Replication] Summary: The core pattern is to use dual-writing with state copying to avoid re-syncing historical data, update any downstream dependencies, and be cautious about Postgres as a destination for very large datasets. image 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai does this apply to airbyte cloud?

The knowledge sources don't provide a fully explicit answer for every scenario on Airbyte Cloud, but here's what is stated: • The dual-writing / incremental state-copying steps are documented under a section explicitly marked [Airbyte Open Source Only] for some steps (e.g., updating the default destination version). However, the general dual-writing process of creating a new destination, copying state, and running parallel connections is not restricted to OSS only. [Upgrading to Destinations V2] • The Postgres destination v3.0.0 (Direct Load) migration guidance applies to both Cloud and OSS, as the connector is enabled on both platforms. [Postgres metadata] • On Airbyte Cloud, SSL/TLS is enforced by default, and you must allowlist Airbyte's IP addresses for your Postgres instance. [Postgres Destination] • The step to manually update the default destination version in the workspace is explicitly called out as OSS-only — on Cloud, version management is handled differently. [Quick Start to Upgrading] In short, most of the migration concepts (dual-writing, state copying, schema changes) apply to both Cloud and OSS, but some operational steps (like manually setting the default connector version) are OSS-specific. For Cloud-specific upgrade concerns, I'd recommend checking with Airbyte Cloud support directly, as the knowledge sources don't cover all Cloud-specific nuances. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.