Open-source Database Converters for PostgreSQL

There are multiple types of database migration: between different DBMS, between local server and the cloud, between different versions of the same system. Choice of appropriate tool depends on kind of migration and top requirements for the task. This article explores a few popular open-source database converters for PostgreSQL suitable for different scenarios of database migration.

pgdump

pg_dump is a simple back up tool for single PostgreSQL database. It does not block other users reading or writing the database and so can backup live databases that is being used concurrently. Use pg_dumpall to backup an entire cluster or global objects shared between multiple databases in cluster.

pg_dump can do output in form of script or archive file formats. Script format is plain-text containing SQL statements to rebuild the database to the state of backup point. Script files can be used to restore the database on other machines, architectures, or even on other DBMS (after some pre-processing). Archive format must be used with PostgreSQL pg_restore tool only.

pgBackRest

pgBackRest is an open-source 3rd party backup/restore tool that handles physical backups with number of enhancements compared to the classic pg_dump tool:

• Local or Remote Operation - backup, restore, and archive locally or remotely via SSH with minimal configuration. Remote access to database server is never required
• Parallel Backup & Restore - solves the compression bottleneck with parallel processing, achieve 1TB/hr performance on multi-cores systems
• Backup Integrity Check - checksums are calculated for every file and validated during restore. After copying files is finished, it waits until all segments that make the backup consistent are stored in the repository
• Backup Resume - an aborted backup can be resumed from the breakpoint
• Delta Restore - any files not present in the backup are first removed and then checksums are taken for the remaining files, this approach can essentially decrease overall restore time
• Encryption - the repository can be encrypted for security purpose

Ora2Pg

Ora2Pg is a free tool for Oracle to PostgreSQL database migration written in Perl. It connects to Oracle database and extracts meta-objects and/or data, then it generates relevant SQL scripts that can be imported into PostgreSQL database to generate all original entries. Key features:

• Tables, views, sequences, indexes and constraints are migrated with all related properties and attributes
• Range/list partitions and subpartitions are supported
• Oracle views are migrated into PostgreSQL tables
• User-defined types are supported
• Materialized views are supported
• Oracle locator and spatial geometries are migrated into PostGis
• Synonyms are migrated as views

See Oracle to Postgres migration with ora2pg article for further information.

pg_chameleon

pg_chameleon is MySQL to PostgreSQL database replication tool written in Python 3. It uses MySQL replication protocol to replicate changes into the target PostgreSQL database. Key features:

• Reads multiple MySQL schemas and restore them into PostgreSQL database
• Full support for DDL statements such as CREATE, DROP, ALTER and others
• Option to refresh single tables or single schemas
• Replica is detached from MySQL for migration
• Option to override data types
• Soft initialization: the replica process will put the database in a consistent status gradually
• Option to schedule init_replica process

Have questions? Contact us