How to migrate from MySQL to PostgresQL?

answered Dec 18 '10

Evgeny
6665 ● 31 ● 49 ● 95
http://askbot.org/

PostgresQL has excellent support of the full text search and transactions that help prevent data inconsistency issues when the software on the server fail. Askbot has also been tested with MySQL and works well but with a significant limitation - only MyISAM storage engine works with Askbot. MyISAM engine is the only MySQL storage engine supporting the full text indexing, but it does not understand transactions. So - PostgresQL is a much better choice of the database to run askbot forum.

To switch from MySQL to PostgresQL, create a new PostgresQL database then follow the steps shown below.

First open a shell window in the directory that contains the settings.py file - the site project directory.

Run a command to save the current forum data:

python manage.py dump_forum

You will be asked a question how to name the dump - and a file with that name and extension .json will be saved in the current working directory, suppose it's about-cats.json. The file extension will be added automatically.

Change the database settings in the settings.py file so that the site uses the new postgresql database:

DATABASE_ENGINE = 'postgresql_psycopg2' #also change the user name and password

Initialize the tables for the forum:

python manage.py syncdb
python manage.py migrate

Finally, load the data:

python manage.py load_forum about-cats.json

See how your forum works with the PostgresQL database:

python manage.py runserver `hostname -i`:8000