db_migrator
Tools to migrate other databases to PostgreSQL
Repository
cybertec-postgresql/db_migrator
https://github.com/cybertec-postgresql/db_migrator
Source
db_migrator-RELEASE_1_0_0.tar.gz
db_migrator-RELEASE_1_0_0.tar.gz
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
db_migrator | 1.0.0 | ETL | BSD 3-Clause | SQL |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 9540 | db_migrator | No | No | No | Yes | Yes | No | - |
| Related | mysql_fdw oracle_fdw tds_fdw orafce pg_bulkload jdbc_fdw db2_fdw pgtt |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PIGSTY | 1.0.0 | 1817161514 | db_migrator | - |
| RPM | PIGSTY | 1.0.0 | 1817161514 | db_migrator_$v | - |
| DEB | PIGSTY | 1.0.0 | 1817161514 | postgresql-$v-db-migrator | - |
Build
You can build the RPM / DEB packages for db_migrator using pig build:
pig build pkg db_migrator # build RPM / DEB packages
Install
You can install db_migrator directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:
pig repo add pgsql -u # Add repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install db_migrator; # Install for current active PG version
pig ext install -y db_migrator -v 18 # PG 18
pig ext install -y db_migrator -v 17 # PG 17
pig ext install -y db_migrator -v 16 # PG 16
pig ext install -y db_migrator -v 15 # PG 15
pig ext install -y db_migrator -v 14 # PG 14
dnf install -y db_migrator_18 # PG 18
dnf install -y db_migrator_17 # PG 17
dnf install -y db_migrator_16 # PG 16
dnf install -y db_migrator_15 # PG 15
dnf install -y db_migrator_14 # PG 14
apt install -y postgresql-18-db-migrator # PG 18
apt install -y postgresql-17-db-migrator # PG 17
apt install -y postgresql-16-db-migrator # PG 16
apt install -y postgresql-15-db-migrator # PG 15
apt install -y postgresql-14-db-migrator # PG 14
Create Extension:
CREATE EXTENSION db_migrator;
Usage
A framework for migrating databases from other data sources to PostgreSQL using foreign data wrappers and source-specific plugins.
Enabling
CREATE EXTENSION db_migrator;
Available Plugins
- ora_migrator - Oracle migration
- mysql_migrator - MySQL/MariaDB migration
- mssql_migrator - Microsoft SQL Server migration
Complete Migration Example (Oracle)
-- Setup (as superuser)
CREATE EXTENSION oracle_fdw;
CREATE SERVER oracle FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//dbserver.mydomain.com/ORADB');
GRANT USAGE ON FOREIGN SERVER oracle TO migrator;
CREATE USER MAPPING FOR migrator SERVER oracle
OPTIONS (user 'orauser', password 'orapwd');
-- Migrate (as migrator user)
CREATE EXTENSION ora_migrator;
SELECT db_migrate(
plugin => 'ora_migrator',
server => 'oracle',
only_schemas => '{TESTSCHEMA1,TESTSCHEMA2}'
);
Step-by-Step Migration
For more control, execute migration in stages:
-- 1. Create staging schemas and snapshot metadata
SELECT db_migrate_prepare(
plugin => 'ora_migrator',
server => 'oracle',
only_schemas => '{SCHEMA1}'
);
-- 2. Review and modify staging data
-- Edit pgsql_stage tables to customize type mappings, rename objects, etc.
UPDATE pgsql_stage.tables SET migrate = TRUE WHERE ...;
-- 3. Create schemas and migrate data
SELECT db_migrate_mkforeign(plugin => 'ora_migrator', server => 'oracle');
SELECT db_migrate_tables(plugin => 'ora_migrator');
-- 4. Create constraints and indexes
SELECT db_migrate_constraints(plugin => 'ora_migrator');
SELECT db_migrate_indexes(plugin => 'ora_migrator');
-- 5. Cleanup
SELECT db_migrate_finish();
Key Features
- Migrates tables, sequences, indexes, constraints, views, functions
- Data type mapping from source to PostgreSQL types (customizable)
- Continues on errors, reporting which objects failed
- Uses FDW staging schema for metadata inspection before migration
- Schema and object name translation via plugin functions
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.