Importing Databases into CloudNativePG (CNPG) Clusters
In the previous post of our CNPG series, we explored the importance of secrets, roles, and database creation in a multi-database cluster setup. If you’ve been following along and applying the concepts, you should now have a working multi-node cluster with one or more empty databases ready to use.
In this post, we’ll look at practical ways to bring your databases to life by importing real data. There are several approaches, each with its own strengths.
Common Import Methods
You generally have two main categories of database import:
- Native CNPG methods (require access to the source cluster)
- Microservice-style approach
- Monolithic-style approach
- External access methods (using standard PostgreSQL tools)
All of these methods rely heavily on PostgreSQL’s native backup tools: pg_dump and pg_restore. The key differences lie in the workflow and requirements.
CNPG initdb.import Method
The official CloudNativePG documentation covers the initdb.import feature in detail here. The process is straightforward when conditions are ideal.
Key requirements include:
- Full network connectivity between source and target clusters
- Proper access credentials
- Compatible TLS certificates (critical in production environments)
- Matching or newer PostgreSQL version on the target
- Reliable online backup strategy (including snapshots) on the source
When everything aligns, this method can feel seamless. However, real-world migrations often face challenges:
- Source clusters frequently run older PostgreSQL versions (sometimes as old as 9.x) while targets use modern versions (16–18).
- Older versions may lack advanced features like proper Multi-Version Concurrency Control (MVCC) snapshots.
- TLS certificate mismatches (different domains, intermediate CAs, etc.) are common.
- The source cluster might no longer be online or accessible.
Careful planning and testing of workarounds are essential for successful migrations.
Traditional PostgreSQL Export/Import (Most Common Approach)
In many cases, standard pg_dump backups already exist. These produce plain SQL files (e.g., mydb_backup.sql) that can be easily transferred, reviewed, and restored.
Advantages:
- Works well for small to medium-sized databases
- Creates complete, consistent backups
- Easy to transfer over networks
- No complex TLS configuration needed between clusters
- Simple to automate with scripts
Disadvantages:
- Potential security considerations during transfer
- Compliance with local data protection regulations
- Large databases may require physical media shipment
- Usually requires a maintenance window
Typical Workflow
Here’s the standard process most teams follow:
- Announce a maintenance window.
- Put the source database in read-only mode to prevent data loss.
- Create a full backup:
pg_dump -U postgres -h localhost mydb > mydb_backup.sql
- Review and edit the backup file if needed (e.g., update database owners or references).
- Transfer the file to the target environment.
- Restore using one of these options:
Option A – Using pg_restore:
pg_restore -d mydb mydb_backup.sql
Option B – Using psql (often simplest for new empty databases):
psql -h newhost -U mydb -d mydb < mydb_backup.sql
You can also pipe the dump directly (if network conditions allow):
pg_dump -U source_user -h source_host source_db | psql -U target_user -d target_db
Recommended Flow Diagram
Real-World Tips
In one recent project, I successfully migrated 18 databases of varying sizes by handling them one at a time. This approach allowed careful validation after each import. For larger-scale migrations, a well-tested shell script with looping can work well — but always expect at least one database to need special handling (different extensions, custom settings, etc.). Thorough testing in a staging environment is highly recommended.
This method gives you a reliable, battle-tested way to move your databases into CloudNativePG. In future posts, we’ll dive deeper into post-import validation, performance tuning, and ongoing maintenance strategies.
Stay tuned!
Did you find this post helpful? You can support me.
Related posts
- Databases – Postgresql – Pilot
- Databases – Postgresql – Gitea and PGBouncer
- Databases – Postgresql – PGbouncer
- CNPG – Cloud Native Postgresql – Pilot
- CNPG – considerations for migrating databases
- CNPG – installation
- CNPG – Installation – Roles and Databases
Author Profile
Latest entries
blog27.06.2026CNPG – Installation – Database Import- blog27.06.2026CNPG – Installation – Roles and Databases
- blog25.06.2026CNPG – installation
- blog24.06.2026CNPG – considerations for migrating databases
