@ wrote... (1 year, 10 months ago)

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>;
Category: tech, Tags: database
Comments: 0
Click here to add a comment