Re: Switching Database Engines

On 04/28/2011 12:19 PM, Carlos Mennens wrote:
> It seems that the 'mysql2postgres.pl' tool has instructions embedded
> into the file so I ran the command as instructed to take the output
> file and insert it into my PostgreSQL server and got the following
> error message:
>
> $ psql -p 5432 -h db1 -U wiki -f mediawiki_upgrade.pg
> Password for user wiki:
> BEGIN
> SET
> SET
> SET
> psql:mediawiki_upgrade.pg:25: ERROR: relation "category" does not exist
>

My guess is that you need to run the main MediaWiki installer for
PostgreSQL first, to create a blank install, in order for the category
table to exist. The export tool is aimed to get the data out, not the
schema to create all the tables. After you create a blank instance,
then you do the data export.

If you have additional problems, try running that like this instead:

$ psql -p 5432 -h db1 -U wiki -e -f mediawiki_upgrade.pg

Note the extra "-e" on the command line. That will show you the line it
is executing as the script runs, so you'll see the one that fails too.
Very handy for debugging what's gone wrong in this sort of situation.

I wouldn't fight with this too much though. Unless you have some really
customized stuff in your wiki, there really is nothing wrong with the
idea of dumping everything into XML, creating a blank PostgreSQL-backed
MediaWiki install, then restoring into that. That's what I always do in
order to get a plain text backup of my server, and to migrate a wiki
from one server to another. There are all kinds of issues you could
have left here before this works, trying to do a database-level
export/reload--encoding, foreign key problems, who knows what else. The
database-agnostic export/import into XML avoids all of those.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books