Migrating from PostgreSQL?
Strategies for moving data from PostgreSQL to MariaDB, using the CONNECT storage engine with ODBC/JDBC or foreign data wrappers.
There are many different ways to migrate from PostgreSQL to MariaDB. This article will discuss some of those options.
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.
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 driver. PostgreSQL does have a freely available ODBC driver called . Therefore, if you install psqlODBC on the MariaDB Server, and then configure the system's ODBC framework (such as ), then the MariaDB server will be able to connect to the remote PostgreSQL server. At that point, you can create tables with the and table options set, so that you can access the PostgreSQL tables from MariaDB.
See 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:
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 driver. PostgreSQL does have a freely available . 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 and table options set, so that you can access the PostgreSQL tables from MariaDB.
See 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:
PostgreSQL's can also be used to migrate from PostgreSQL to MariaDB.
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:
PostgreSQL's allows you to copy the data from a PostgreSQL table to a text file. This data can then be loaded into MariaDB with .
MySQL Workbench has a that requires an driver. PostgreSQL does have a freely available ODBC driver called .
See for more information.
PostgreSQL's and use a language called . This language is quite different than the default SQL/PSM language used for MariaDB's . PL/pgSQL is more similar to PL/PSQL from Oracle, so you may find it beneficial to try migrate with set.
This page is licensed: CC BY-SA / Gnu FDL
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;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;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;Migrating from PostgreSQL?