Converting MySQL to PostgreSQL

Very Short Intro
''You may have read a bunch of short articles with the same name on the web, but they were just snippets of information you needed. It's time to put it all together.''

You have a project in MySQL and suddenly you find out that you need to switch to PostgreSQL. Suddenly you see that there are many flavours of SQL and that your seemingly basic constructions throw a lot of errors. You don't have time to really rewrite your code from scratch, it may come later...

Actually, there may be some good reasons to switch...


 * you can sell your product with total peace of mind (PostgreSQL is BSD licensed, MySQL is more complicated)
 * you can find articles "Converting from MySQL to PostgreSQL" on the web; you won't find any "Converting from PostgreSQL to MySQL"
 * PostgreSQL may not be just another lousy database if Skype, Cisco, Juniper, IMDb, Pandora decided to rely on it and Sun Microsystems made it database of choice (which is explicitly funny because Sun acquired MySQL).

With PostgreSQL you may still feel a little like a second-class citizen, but not really the ignored one. There are some major projects like Asterisk, Horde or DBMail that have recognized its qualities and although MySQL was their first choice database, they are showing effort to make things run here too.

Check that the Server is Running
Most likely you don't need this chapter, but very briefly: after you've installed your package with PostgreSQL on your Linux machine (be it from a package or following these notes), you need to do something like

su - su - postgres createdb test psql test =# create user username password ' password '; -- To change a password: =# alter role username password ' password '; =# create database databasename with encoding 'utf8'; =# grant all privileges on database databasename to username; =# \l =# \c databasename =# \q

vi /etc/postgresql/pg_hba.conf

host   all         all         0.0.0.0           0.0.0.0            md5

be SURE to cover this security issue with iptables!

/etc/init.d/postgresql reload or /usr/lib/postgresql/bin/pg_ctl reload

postmaster successfully signaled

psql -h server -d databasename -U username

databasename=>

Using pgloader
Have a look at http://pgloader.io and you can migrate your MySQL database over to PostgreSQL in a single command:

pgloader mysql://user@localhost/dbname postgresql:///dbname

This will handle type casting with a default casting rules set, and also schema discovery in MySQL and creation in PostgreSQL, including tables, columns, constraints (primary keys, foreign keys, NOT NULL), default values, and secondary indexes. The data are transformed on the fly to be accepted by PostgreSQL, which includes getting rid of zero-dates (there's no year zero in our calendar, neither month nor day zero, and while MySQL doesn't care about that PostgreSQL is quite strongly opinionated that if you use year zero then what you're dealing with is not a date).

For more advanced options or if you want to change the default settings pgloader MySQL support allows you to write a full command using its own language with different rules to describe how you want your migration done.

Common way with SQL dump
Dump your tables with

mysqldump -u username -p --compatible=postgresql databasename > outputfile.sql

but even then you will have to change escaped chars (replacing \t with ^I, \n with ^M, single quote (') with doubled single quote and double (escaped) backslash (\\) with a single backslash). This can't be trivially done with sed command, you may need to write a script for it (Ruby, Perl, etc). There is a MySQL to PostgreSQL python convert script (you need to use  when exporting your mysqldump to make it work). It is much better and proven solution to prepend your dump with the following lines

SET standard_conforming_strings = 'off'; SET backslash_quote = 'on';

These options will force PostgreSQL parser to accept non-ANSI-SQL-compatible escape sequences (Postgre will still issue HINTs on it; you can safely ignore them). Do not set these options globally: this may compromise security of the server!

You also have to manually modify the data types etc. as discussed later.

After you convert your tables, import them the same way you were used to in MySQL, that is

psql -h server -d databasename -U username -f data.sql

Export using CSV-files
When you have a large sql dump containing binary data, it will not be easy to modify the data structure, so there is another way to export your data to PostgreSQL. Mysql have an option to export each table from the database as a separate .sql file with table structure and .txt file with table's data in CSV-format:

mysqldump -u username -p --compatible=postgresql -T /path/to/export databasename

Notice that /path/to/export should be writeable by user who runs mysqld, in most case it mysqld. After that you should modify your table structure according PostgreSQL format: When table structure will be ready, you should load it as it was shown above. You should prepare data files: replace carriage return characters to "\r" and remove invalid characters for your data encoding. Here is an example bash script how you can do this and load all the data in your database: CHARSET="utf-8" #your current database charset DATADIR="/path/to/export" DBNAME="databasename" for file in $DATADIR/*.txt; do  TMP=${file%.*} TABLE=${TMP##*/} echo "preparing $TABLE" #replace carriage return sed 's/\r/\\r/g' $file > /tmp/$TABLE.export.tmp #cleanup non-printable and wrong sequences for current charset iconv -t $CHARSET -f $CHARSET -c < /tmp/$TABLE.export.tmp > /tmp/$TABLE.export.tmp.out echo "loading $TABLE" /usr/bin/psql $DBNAME -c "copy $TABLE from '/tmp/$TABLE.export.tmp.out'" #clean up  rm /tmp/$TABLE.export.tmp /tmp/$TABLE.export.tmp.out done
 * convert data types
 * create separate keys definitions
 * replace escape characters
 * 1) !/bin/bash

Perl
You will need to install an appropriate DBD package. In Debian/Ubuntu run.

Data Types
The ideas for this table were partially derived from automated dump converting script. Official documentation: List of available data types can be reached also by using psql's internal slash command.
 * PostgreSQL 8.4 Data Types
 * MySQL 5.1 Data Types

Language Constructs

 * MySQL 5.1 SQL Statement Syntax
 * PostgreSQL 8.1 SQL Commands
 * PostgreSQL 8.2 SQL Commands

Functions

 * MySQL 5.1 Functions and Operators
 * PostgreSQL SQL Functions and Operators
 * mysqlcompat, a reimplementation of most MySQL functions in PostgreSQL

Common Errors

 * ERROR: relation "something" does not exist - usually table doesn't exist as you probably didn't make it with the new datatypes or syntax. Also watch out for case folding issues; PostgreSQL = postgresql != "PostgreSQL".
 * prepared statement "dbdpg_X" does not exist -

Install
In versions prior to 9.0, you have to make it available explicitly for every database: your_unix$ su - postgres your_unix$ .../pgsql/bin/createlang plpgsql -h localhost -d databasename

(On BSD systems, the username is pgsql)

Running A Function
SELECT definedfunction;

Administration
To use the same backup technique as used with MySQL, in : /dumps/postgresql/*/*.dump.gz { daily rotate 20 dateext nocompress sharedscripts create postrotate for i in $(su - postgres -c "psql --list -t" | awk '{print $1}' | grep -vE '^$|^template[0-9]'); do                       if [ ! -e /dumps/postgresql/$i ]; then mkdir -m 700 /dumps/postgresql/$i; fi                       # compress even in custom format, because it can be compressed more su - postgres -c "pg_dump --format=custom $i" | gzip > /dumps/postgresql/$i/$i.dump.gz               done endscript } /dumps/postgresql/*/*.sql.gz { daily rotate 20 dateext nocompress sharedscripts create postrotate for i in $(su - postgres -c "psql --list -t" | awk '{print $1}' | grep -vE '^$|^template[0-9]'); do                       if [ ! -e /dumps/postgresql/$i ]; then mkdir -m 700 /dumps/postgresql/$i; fi                       su - postgres -c "pg_dump --format=plain $i" | gzip > /dumps/postgresql/$i/$i.sql.gz                done endscript } /dumps/postgresql/*/*.tar.gz { daily rotate 20 dateext nocompress sharedscripts create postrotate for i in $(su - postgres -c "psql --list -t" | awk '{print $1}' | grep -vE '^$|^template[0-9]'); do                       if [ ! -e /dumps/postgresql/$i ]; then mkdir -m 700 /dumps/postgresql/$i; fi                       su - postgres -c "pg_dump --format=tar $i" | gzip > /dumps/postgresql/$i/$i.tar.gz                done endscript }

Related wikibooks

 * MySQL
 * PostgreSQL