Migrating to MariaDB from PostgreSQL
Migrating from PostgreSQL? Contact the MariaDB migration experts!
Contact UsThere are many different ways to migrate from PostgreSQL to MariaDB. This article will discuss some of those options.
Contents
MariaDB's CONNECT Storage Engine
MariaDB's CONNECT storage engine can be used to migrate from PostgreSQL to MariaDB. There are two primary ways that this can be done.
See Loading the CONNECT Storage Engine for information on how to install the CONNECT storage engine.
Tables with ODBC table_type
The CONNECT storage engine allows you to create tables that refer to tables on an external server, and it can fetch the data using a compatible ODBC driver. PostgreSQL does have a freely available ODBC driver called psqlODBC
. Therefore, if you install psqlODBC
on the MariaDB Server, and then configure the system's ODBC framework (such as unixODBC), then the MariaDB server will be able to connect to the remote PostgreSQL server. At that point, you can create tables with the ENGINE=CONNECT
and table_type=ODBC
table options set, so that you can access the PostgreSQL tables from MariaDB.
See CONNECT ODBC Table Type: Accessing Tables From Another DBMS for more information on how to do that.
Once the remote table is setup, you can migrate the data to local tables very simply. For example:
CREATE TABLE psql_tab ( id int, str varchar(50) ) ENGINE = CONNECT table_type=ODBC tabname='tab' connection='DSN=psql_server'; CREATE TABLE tab ( id int, str varchar(50) ) ENGINE = InnoDB; INSERT INTO tab SELECT * FROM psql_tab;
Tables with JDBC table_type
The CONNECT storage engine allows you to create tables that refer to tables on an external server, and it can fetch the data using a compatible JDBC driver. PostgreSQL does have a freely available JDBC driver. If you install this JDBC driver on the MariaDB server, then the MariaDB server will be able to connect to the remote PostgreSQL server via JDBC. At that point, you can create tables with the ENGINE=CONNECT
and table_type=JDBC
table options set, so that you can access the PostgreSQL tables from MariaDB.
See CONNECT JDBC Table Type: Accessing Tables from Another DBMS for more information on how to do that.
Once the remote table is setup, you can migrate the data to local tables very simply. For example:
CREATE TABLE psql_tab ( id int, str varchar(50) ) ENGINE = CONNECT table_type=JDBC tabname='tab' connection='jdbc:postgresql://psql_server/db1'; CREATE TABLE tab ( id int, str varchar(50) ) ENGINE = InnoDB; INSERT INTO tab SELECT * FROM psql_tab;
PostgreSQL's Foreign Data Wrappers
PostgreSQL's foreign data wrappers can also be used to migrate from PostgreSQL to MariaDB.
mysql_fdw
mysql_fdw allows you to create a table in PostgreSQL that actual refers to a remote MySQL or MariaDB server. Since MySQL and MariaDB are compatible at the protocol level, this should also support MariaDB.
The foreign data wrapper also supports writes, so you should be able to write to the remote MariaDB table to migrate your PostgreSQL data. For example:
CREATE TABLE tab ( id int, str text ); INSERT INTO tab VALUES (1, 'str1'); CREATE SERVER mariadb_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '10.1.1.101', port '3306'); CREATE USER MAPPING FOR postgres SERVER mariadb_server OPTIONS (username 'foo', password 'bar'); CREATE FOREIGN TABLE mariadb_tab ( id int, str text ) SERVER mariadb_server OPTIONS (dbname 'db1', table_name 'tab'); INSERT INTO mariadb_tab SELECT * FROM tab;