Skip to content

Get Started

This little guide will lead you for a simple migration project.

You may adapt to one of your existing databases. In this case, try PostgreSQL Migrator with a simple one.

Note

PostgreSQL Migrator never leaks data to the internet.

Requirements

Ensure you met the following prerequisites:

  • PostgreSQL Migrator.
  • a MySQL database with sakila database loaded.
  • a recent PostgreSQL instance ready to receive a light database.
  • a Linux system with psql.

Inspecting database requires some privileges.

Adapt connection strings to MySQL and PostgreSQL to match your installation.

1. Initialize

First, initialize the project directory where PostgreSQL Migrator will create all the files required for the migration. Use the initcommand with source DSN.

DSN

The pg_migrate init --source option indicates the database connection string, i.e. its Database URL (dburl).

Source format are:

  • for Oracle: oracle://[user[:password]@]localhost[/service_name]?CONNECT TIMEOUT=5&TIMEOUT=60
  • for Oracle with no service name, use /?SID=sid (explanation)
  • for MySQL: mysql://[user[:password]@]localhost[/db_name]

Likewise, target DSN format is postgresql://[user][:password]@localhost/[db_name].

For a MySQL database, init looks like this:

$ pg_migrate init --source "mysql://sakila:N0tSecret@localhost/sakila" --target=postgresql://postgres@localhost sakila-migration
16:11:42 INFO   Opening database connection pool. key=source driver=mysql dsn=my:sakila@localhost/sakila
16:11:42 INFO   Connecting to database.          key=source count=1
16:11:42 INFO   Opening database connection pool. key=target driver=pgx dsn=pg:postgres@localhost
16:11:42 INFO   Connecting to database.          key=target count=1
16:11:42 INFO   Initialized migration project.   source="MySQL Community Server - GPL" version=8.4.6 path=sakila-migration
16:11:42 INFO   Databases connections closed.    source=1 target=1
$

Leaking secrets

Passing secret as command line argument leaks in shell history and /proc. Prefer using PGMSOURCE environment variable for real secret. See pg_migrate init --help.

Move in the project directory before continuing this guide.

$ cd sakila-migration/

2. Inspect

pg_migrate inspect connects to source database and queries system views to build database objects inventory. After source inspection, PostgreSQL Migrator audits objects, scoring them and annotating migration compatibility issues. inspect command converts the source model for PostgreSQL.

$ pg_migrate inspect
16:12:29 INFO   Inspecting source database.      driver=mysql
16:12:29 INFO   Opening database connection pool. key=source driver=mysql dsn=my:sakila@localhost/sakila
16:12:29 INFO   Connecting to database.          key=source count=1
16:12:29 INFO   Inspecting source database.      driver=mysql
16:12:29 INFO   Connecting to database.          key=source count=2
16:12:30 INFO   Inspected metadada.              software="MySQL Community Server - GPL"
16:12:29 INFO   Found schema.                    name=sakila
16:12:30 INFO   Found tables.                    count=23
...
16:12:30 INFO   Auditing catalog.                driver=mysql
16:12:30 INFO   Catalog audited.
16:12:30 INFO   Converted catalog for PostgreSQL.
16:12:30 INFO   Auditing catalog.                driver=mysql
16:12:30 WARN   Target catalog has pending annotations. count=31
16:12:30 INFO   Databases connections closed.    source=2 target=0
16:12:30 INFO   Execute pg_migrate ui to browse project.
16:12:30 INFO   Execute pg_migrate dump to migrate for PostgreSQL.
$

PostgreSQL Migrator is resilient to inspection failure such as missing privileges or incompatibility. You may continue if pg_migrate tells you so.

3. Migrate

PostgreSQL Migrator prevents you for dumping unhandled annotations. An annotation is a special comment about an inspected or converted item, that requires attention from you. You may consult “Annotations” tab in the Web interface.

For now, you should ignore unhandled annotations by using the --force flag or by adding a permanent option to the configuration file. See Dump.Force for more details.

Migrate schema and data using pg_migrate dump --force.

$ pg_migrate dump --force
16:20:46 INFO   Executing queries in target PostgreSQL.
16:20:46 INFO   Opening database connection pool. key=source driver=mysql dsn=my:sakila@localhost/sakila
16:20:46 INFO   Connecting to database.          key=source count=1
16:20:46 INFO   Collected tables statistics.     count=23 missing=0
16:20:46 INFO   Opening database connection pool. key=target driver=pgx dsn=pg:postgres@localhost
16:20:46 INFO   Connecting to database.          key=target count=1
...
16:20:46 INFO   Create foreign key.              name=sakila.payment fk=payment_staff_id_fkey sn=20068
16:20:46 INFO   Create foreign key.              name=sakila.rental fk=rental_staff_id_fkey sn=20071
16:20:46 INFO   Copy completed.                  tables=16 data=2.6MB elapsed=660ms throughput=3.9MB/s jobs=4 mem=25.6MB
16:20:46 INFO   Databases connections closed.    source=5 target=4
$

pg_migrate dump is like pg_dump for foreign RDBMS.

4. Verify

Check effective migration using psql:

$ psql postgresql://postgres@localhost
psql (18.0, server 18.1)
Type "help" for help.

postgres=# select * from sakila.actor limit 1;
 actor_id | first_name | last_name |     last_update
----------+------------+-----------+---------------------
        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33
(1 row)

postgres=#

That’s it, you migrated your first database to PostgreSQL. Congratulation!

Going further

PostgreSQL Migrator includes:

  • A web UI to browse source and target databases. See Browser Demo.
  • A configuration file allowing you to fine tune conversion and dump. See References documentation for more details.