. 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

https://github.com/mihailShumilov/mysql2postgresql


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'