How to migrate your Vidispine MySQL to PostgreSQL

By Isak - March 3, 2016

We have seen that the existing MySQL to PostgreSQL conversation tools you can find are not always 100% successful. To make sure that your Vidispine database is successfully converted read this post on how to migrate your existing Vidispine MySQL database to PostgreSQL.

There are several MySQL-to-PostgreSQL conversion tools out there. I did not get 100% success with any of the ones I tried. Instead, this is how I did it.

  1. Get a database dump from MySQL, without any CREATE.
    $ mysqldump -u {USER} -p -c --skip-create-options \
      --no-create-db --no-create-info --compatible=postgresql \
      --default-character-set=utf8 -K --skip-lock-tables \
      {DATABASE} -r {MYSQL.dump}
  2. Verify that the dump is actually UTF-8.
    $ isutf8 < {MYSQL.dump}
  3. If your database is not UTF-8 (we recommend that it is), you may export it using the native character encoding and then convert the dump file to UTF-8.
    $  mysqldump -u {USER} -p -c --skip-create-options \
      --no-create-db --no-create-info --compatible=postgresql \
      --default-character-set=latin1 -K --skip-lock-tables \
      {DATABASE} -r {MYSQL-latin.dump}
    $ iconv --from-code latin1 --to-code utf-8 < {MYSQL-latin.dump} > {MYSQL.dump}
    $ isutf8 < {MYSQL.dump}
  4. Install the same Vidispine version on a PostgreSQL instance. Stop Vidispine.
  5. Grab the schema from the PostgreSQL instance.
    $ pg_dump --section=pre-data -cs {POSTGRESQL-db} > {POSTGRESQL-pre.dump}
    $ pg_dump --section=post-data -cs {POSTGRESQL-db} > {POSTGRESQL-post.dump}
  6. Create the PostgreSQL database that is going to be used. (You can use the same as in 4 if you want.)
    $ echo "drop database {NEWDATABASE}; create database {NEWDATABASE};" | psql
  7. Collect all pieces and insert into PostgreSQL.
    $ ( ( \
        cat {POSTGRESQL-pre.dump} ; \
        echo "update pg_cast set castcontext='a' where casttarget = 'boolean'::regtype;" ; \
        echo 'set standard_conforming_strings to false;' ; \
        echo 'set escape_string_warning to false;' ; \
        grep -v 'LOCK TABLES' < {MYSQL.dump} | \
          sed -e 's/"c_entityType"/"c_entitytype"/g' |
          sed -e 's/"c_masterFileid"/"c_masterfileid"/g'; \
        cat {POSTGRESQL-post.dump} \
        echo "update pg_cast set castcontext='e' where casttarget = 'boolean'::regtype;" ; \
        ) | psql {NEWDATABASE} 2>&1 ) | tee /tmp/insert.log