Skip to main content

Migrating Known from MySQL to PostgreSQL

1 min read

Below are the steps I've taken to finally migrate my self hosted Known site from MySQL to PostgreSQL.

Currently the website install routine only supports MySQL so there is no need to go to either the /warmup or /begin folders on your site. You need to build that database manually.

createuser withknown

createdb -T template1 -O withknown withknown

psql -f /schema/postgres/postgres.sql withknown

To migrate the data from MySQL to PostgreSQL I use the mysql2postgresql tool from Mihail Shumilov

mysqldump --xml -u root withknown > withknown.xml

php convertor.php -i withknown.xml -o withknown.sql

Edit the file withknown.sql and remove all the DROP, CREATE, ALTER lines so you are left with only the INSERT lines, which hold the actual site data. (perhaps there is a option in mysqldump to do this directly)

To get rid of the over escaping from MySQL run the below sed line.

cat withknown.sql | sed -e 's/\\\\/\\/g' > withknown-final.sql

As last phase import the data in PostgreSQL

sql -U withknown -f withknown-final.sql withknown

Make sure you update the config.ini and change the connection info

database = 'Postgres'

dbname = 'withknown'

dbpass = 'XXX'

dbuser = 'withknown'

dbhost = 'localhost'