Eine vorhandene Serendipity Installation von MySQL auf Postgres migrieren

Penny hat ihr vorhandenes Blog von MySQL auf Postgres migriert und die Vorgehensweise dokumentiert. Das ist natürlich ein sehr spezieller Fall, lässt sich aber meiner Meinung nach, auch gut auf andere MySQL-to-Postgres Migrationen anwenden. Mit ihrer Erlaubnis poste ich hier mal ihre Vorgehensweise:

„1. dump the mysql database with some special magic options. I’m not
sure about the encoding part of all this, I assumed the mysql database
was unicode but may have been wrong.

mysqldump –skip-comments \

–complete-insert –default-character-set=UTF8 \

–no-create-info –skip-add-locks –skip-opt \

–compatible=postgresql \

$yourdbname > mysql-pg-compat.sql

2. create a temporary postgres database to work in

createdb -O $someuser -E UTF8 s9ytemp

3. point a test s9y installation at that new database you created and
run through the installation process to get it to install all your schema.

4. make sure at this point you also install all the plugins you want

5. the s9y installation process does put data into those tables, so get
pg_dump to create you just the schema part:

pg_dump -Fc -f pg-schema.sql s9ytemp

6. drop and recreate your temporary postgres database

dropdb s9ytemp
– repeat step 2.

7. restore the schema you just created into your new temp database

pg_restore -s -v -Fc -d s9ytemp pg-schema.sql

8. start munging some stuff in the mysql dump
8a. add transactions around it
echo „BEGIN;“ > mysql-pg-compat-tx.sql
cat mysql-pg-compat.sql >> mysql-pg-compat.sql
echo „COMMIT;“ >> mysql-pg-compat-tx.sql

8b. fix up weird table name sensitivity issue

cat mysql-pg-compat-tx.sql | perl -pe ’s/pluginPath/pluginpath/g‘ >

(you could do this in place with perl -pi -e ’s/pluginPath/pluginpath/g‘
mysql-pg-compat-tx.sql too.

9. Restore the data from the mysql dump into the postgres database
psql -f mysql-pg-compat-tx(-fixed).sql -d s9ytemp

Watch the output.. as soon as you see errors, ctrl-c it and check the
error & fix it. Repeat all necessary steps until this completes with no

This took me about 6 goes to get right, at which point I found a few
instances of 4, and 8b. You might find others 🙂 This is why 8a is
important 🙂

10. Fix up the sequences

Create a file called fixsequencesgen.sql containing:


‚SELECT setval(‚ ||

quote_literal(relname) ||

‚, (SELECT MAX(id) FROM ‚ ||

substring(relname from 1 for octet_length(relname) – 7) ||

‚), true);‘




relkind = ‚S‘ AND

relname like ‚%_id_seq‘ AND

relnamespace IN (






nspname NOT LIKE ‚pg_%‘ AND

nspname != ‚information_schema‘


and then do:

psql -d s9ytemp -f fixsequencesgen.sql -o fixsequencestemp.sql

cat fixsequencestemp.sql | grep SELECT > fixsequences.sql

(again, you could do this in place)

open up fixsequences.sql and change these lines:

– SELECT setval(’s9yauthors_authorid_seq‘, (SELECT MAX(id) FROM
s9yauthors_autho), true);
– SELECT setval(’s9ycategory_categoryid_seq‘, (SELECT MAX(id) FROM
s9ycategory_categor), true);
+ SELECT setval(’s9yauthors_authorid_seq‘, (SELECT MAX(authorid) FROM
s9yauthors), true);
+ SELECT setval(’s9ycategory_categoryid_seq‘, (SELECT MAX(categoryid)
FROM s9ycategory), true);

This is because the above script I stole from another project mysql ->
pg migration I did (moodle) which always uses ‚id‘ as the field name for
the primary key. It could probably be smarter, but changing the two
lines works fine too.

Apply that file to the database:

psql -d s9ytemp -f fixsequences.sql

11. Do a pgdump of your new s9y postgres database:

pg_dump -Fc s9ytemp > s9y.pgdump

12. Copy it to your production server, and do

createdb -E UTF8 $yours9ydbuser s9y
pg_restore -Fc -d s9y s9y.pgdump

13. Switch over your production s9y to use postgres (by editing

14. Done! Go and have something strong to drink!

Other miscellaneous things I found:

– – I couldn’t get the spamblocklog table to create itself .. I ended up
commenting out the if ($versioncomparison) { line above the CREATE TABLE
part in the plugin …

– – Running postgres 8.1 on the computer I was working on and then trying
to restore into a 7.4 production postgres was an unhappy experience that
I don’t recommend. I didn’t realise this until I got to step 12 🙁

In case that happens, try 11 and 12 like:

pg_dump -Fp s9ytemp (rather than Fc) and then you can just do psql -d
s9y < s9y.pgdump"


„Do you hate starting on a new project and having to try to figure out someone else’s idea of a database? Or are you in QA and the developers expect you to understand all the relationships in their schema? If so then this tool’s for you.

SchemaSpy is a Java-based tool that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships. The browsing through relationships can occur though HTML links and/or though the graphical representation of the relationships. It’s also designed to help resolve the obtuse errors that a database sometimes gives related to failures due to constraints.“



Materialized Views in PostgreSQL

„Materialized Views in PostgreSQL

A materialized view is a technique to „cache“ the results of a query when its result set is large or the query is very slow and that query is used very often. A materialized view is a set of query results stored in a table. Usually this means the results of a selection of a view. Queries involving aggregation are a good reason to have a materialized view. Usually a slow query is not a good reason to have a materialized view unless you have exhausted the best advice from pgsql-general and pgsql-performance. „

Materialized Views in PostgreSQL

Materialized Views in PostgreSQL

PostgreSQL Performance Tuning Checklist

„PostgreSQL 8.0 Performance Checklist
This is a set of rules of thumb for setting up your PostgreSQL 8.0 server. A lot of the below is based on anecdotal evidence or practical scaling tests; there’s a lot about database performance that we, and OSDL, are still working out. However, this should get you started. All information below is useful as of January 12, 2005 and will likely be updated later. Discussions of settings below supercede the recommendations I’ve made on General Bits.“

PostgreSQL Performance Tuning Checklist

PostgreSQL Performance Tuning Checklist

PostgreSQL 8.1 veröffentlicht

Heute wurde die endgültige Version von PostgreSQL 8.1 veröffentlicht. Download gibts hier. Änderungen sind unter anderem verbesserte Performance durch diverse Verbesserungen und die Möglichkeit eines Two-Phase-Commits. Ausserdem werden nun von PL/PgSQL IN und INOUT Parameter wie bei Oracle PL/SQL unterstützt.
Alle weiteren Änderungen sind in den Release Notes dokumentiert.