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
stackoverflow.com question.
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.
migrate.sh
#!/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
ran 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>;