So I recently posted: A MySQL DBA looks at PostgreSQL
This post will explore the migration from MySQL to PostgreSQL. I will soon follow it up with a PostgreSQL migration back to MySQL. The longterm goal with these posts is to show how the data works within the different databases as well as how to solve similar issues in each database when it should arise.
For the migrations I will use the often used example: World database available on dev.mysql.com.
I will also acknowledge this, I am more experienced with MySQL than PostgreSQL. PostgreSQL DBAs might write and recommend different solutions to such situations. This is also a very simple example.
First to ensure this process is from start to finish:
So now let me get the PostgreSQL database set up and ready.
This simple perl script (mysql2pgsql.perl) helps the migration process from MySQL to PostgreSQL.
Gather the MySQL data and get it ready.
So let us see what we have.
Well the counts match and data is available. But now I want to see the MySQL version of a "SHOW CREATE TABLE"; Keep in mind that in MySQL CREATE DATABASE and CREATE SCHEMA are basically the same thing.
As you can see to see the table is the same as a mysqldump command
$ mysqldump -u root -p --no_data --database world --tables City
More work than a typical MySQLis used to having to do just to see the table structure.
But our data and schema is moved over into PostgreSQL from MySQL.
Another post soon to come... Moving it back.
This post will explore the migration from MySQL to PostgreSQL. I will soon follow it up with a PostgreSQL migration back to MySQL. The longterm goal with these posts is to show how the data works within the different databases as well as how to solve similar issues in each database when it should arise.
For the migrations I will use the often used example: World database available on dev.mysql.com.
I will also acknowledge this, I am more experienced with MySQL than PostgreSQL. PostgreSQL DBAs might write and recommend different solutions to such situations. This is also a very simple example.
First to ensure this process is from start to finish:
mysql> create database world;
Query OK, 1 row affected (0.00 sec
# mysql world < world_innodb.sql
mysql> show create table City;
CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB
mysql> select count(ID) from City\G
*************************** 1. row ***************************
count(ID): 4079
So now let me get the PostgreSQL database set up and ready.
# su postgres
$ psql
psql (9.3.2)
Type "help" for help.
postgres=# CREATE DATABASE world;
CREATE DATABASE
# GRANT ALL ON DATABASE world TO testuser;
GRANT
postgres=# \q
This simple perl script (mysql2pgsql.perl) helps the migration process from MySQL to PostgreSQL.
# su testuser
$ cd
$ pwd
/home/testuser
$ wget http://pgfoundry.org/frs/download.php/1535/mysql2pgsql.perl
Gather the MySQL data and get it ready.
mysqldump -u root -p world > mysql2postgresql.sql
$ ./mysql2pgsql.perl mysql2postgresql.sql mysql2postgresql.pgsql
table "city" will be dropped CASCADE
"city_id_seq"--
table "country" will be dropped CASCADE
table "countrylanguage" will be dropped CASCADE
$ psql world < mysql2postgresql.pgsql | more
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
CREATE TABLE
INSERT 0 1
..
INSERT 0 1
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to constraint countrylanguage_countrycode_fkey on table countrylanguage
drop cascades to constraint city_countrycode_fkey on table city
..
INSERT 0 1
INSERT 0 1
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
..
INSERT 0 1
CREATE INDEX
ALTER TABLE
So let us see what we have.
$ psql -d world
psql (9.3.2)
Type "help" for help.
world=> \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+----------
public | city | table | testuser
public | country | table | testuser
public | countrylanguage | table | testuser
(3 rows)
world=> select count(ID) from City;
count
-------
4079
(1 row)
world=> select * from City limit 10;
id | name | countrycode | district | population
----+-------------------------------------+-------------+----------------------+------------
1 | Kabul | AFG | Kabol | 1780000
2 | Qandahar | AFG | Qandahar | 237500
3 | Herat | AFG | Herat | 186800
4 | Mazar-e-Sharif | AFG | Balkh | 127800
5 | Amsterdam | NLD | Noord-Holland | 731200
6 | Rotterdam | NLD | Zuid-Holland | 593321
7 | Haag | NLD | Zuid-Holland | 440900
8 | Utrecht | NLD | Utrecht | 234323
9 | Eindhoven | NLD | Noord-Brabant | 201843
10 | Tilburg | NLD | Noord-Brabant | 193238
(10 rows)
world=> \dt+ City
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+--------+-------------
public | city | table | testuser | 432 kB |
(1 row)
Well the counts match and data is available. But now I want to see the MySQL version of a "SHOW CREATE TABLE"; Keep in mind that in MySQL CREATE DATABASE and CREATE SCHEMA are basically the same thing.
$ pg_dump -t city -s world
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: city; Type: TABLE; Schema: public; Owner: testuser; Tablespace:
--
CREATE TABLE city (
id integer DEFAULT nextval('city_id_seq'::regclass) NOT NULL,
name character(35) DEFAULT ''::bpchar NOT NULL,
countrycode character(3) DEFAULT ''::bpchar NOT NULL,
district character(20) DEFAULT ''::bpchar NOT NULL,
population integer DEFAULT 0 NOT NULL
);
ALTER TABLE public.city OWNER TO testuser;
--
-- Name: city_pkey; Type: CONSTRAINT; Schema: public; Owner: testuser; Tablespace:
--
ALTER TABLE ONLY city
ADD CONSTRAINT city_pkey PRIMARY KEY (id);
--
-- Name: city_countrycode_idx; Type: INDEX; Schema: public; Owner: testuser; Tablespace:
--
CREATE INDEX city_countrycode_idx ON city USING btree (countrycode);
--
-- PostgreSQL database dump complete
--
As you can see to see the table is the same as a mysqldump command
$ mysqldump -u root -p --no_data --database world --tables City
More work than a typical MySQLis used to having to do just to see the table structure.
But our data and schema is moved over into PostgreSQL from MySQL.
Another post soon to come... Moving it back.
