I kept getting
Incorrect string value warnings when trying to import UTF-8 strings
into Mysql. It turns out that Mysql has pretty questionable utf-8 support as seen from this
Well enough was enough so I decided to migrate some datbases to Postgresql. Here are some notes/scripts that helped me.
A word of warning, I'm by no means a Postgresql expert. This was figured out through lots of googling and trial and error.
Firstly, download this helper script: https://github.com/lanyrd/mysql-postgresql-converter
Before doing anything else you probably want to change the default behaviour for new databases to not allow new users to be able to connect to them.
sudo -u postgres psql REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;
Now lets migrate the database.
#!/bin/bash DB=$1 USER=$2 PASS=$3 if [ -z "$PASS" ]; then echo "usage: $0 db_name user password" exit 1 fi echo -n "dumping... " mysqldump --compatible=postgresql --add-drop-table --order-by-primary --default-character-set=utf8 -r mysql.out -u $USER --password=$PASS $DB echo "dumped" ./db_converter.py mysql.out postgres.in sudo -u postgres createdb -O $USER $DB sudo -u postgres psql -d $DB -f postgres.in
You could probably pipe those commands together to avoid make intermidiate sql files but my databases where small enough (~200MB) so that wasn't a big issue for me.
After creating and populating the database the permissions were all terrible. I just
psql and then edited/copy/pasted the following:
sudo -u postgres psql
\connect <db_name>; -- connecting to database REVOKE CONNECT ON DATABASE <db_name> FROM PUBLIC; GRANT CONNECT ON DATABASE <db_name> TO <user>; -- permissions on database REVOKE ALL ON DATABASE <db_name> FROM PUBLIC; GRANT ALL ON DATABASE <db_name> TO <user>; -- permissions on schema REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT CREATE, USAGE ON SCHEMA public TO <user>; -- existing tables REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO <user>; -- existing sequences REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM <user>; GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO <user>; -- future tables ALTER DEFAULT PRIVILEGES FOR ROLE <user> IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <user>; -- future sequences ALTER DEFAULT PRIVILEGES FOR ROLE <user> IN SCHEMA public GRANT USAGE ON sequences TO <user>;