Paul at 15:06, 4 March 2018

2018-03-04T15:06:26Z

← Older revision		Revision as of 15:06, 4 March 2018
Line 2:		Line 2:

	#!/usr/bin/perl		#!/usr/bin/perl

	use Modern::Perl;		use Modern::Perl;
	use DBIx::Class::Schema::Loader qw/ make_schema_at /;		use DBIx::Class::Schema::Loader qw/ make_schema_at /;

Paul: Created page with "Simple script for creating `Result` classes from an existing MySQL database, then producing a PostgreSQL schema file from those classes: #!/usr/bin/perl use Mod..."

2018-03-04T15:06:17Z

Created page with "Simple script for creating <code>Result</code> classes from an existing MySQL database, then producing a PostgreSQL schema file from those classes: #!/usr/bin/perl use Mod..."

New page

Simple script for creating <code>Result</code> classes from an existing MySQL database, then producing a PostgreSQL schema file from those classes:

#!/usr/bin/perl

use Modern::Perl;
use DBIx::Class::Schema::Loader qw/ make_schema_at /;

my $dsn = 'dbi:mysql:dbname=database';
my $user = '';
my $pass = '';

make_schema_at(
'MyDB::Schema',
{ debug => 1, dump_directory => './lib' },
[ $dsn, $user, $pass
],
);

my $schema = MyDB::Schema->connect($dsn, $user, $pass);
$schema->create_ddl_dir(['PostgreSQL'], '0.1', './', undef, { add_drop_table => 0 });

== Pre-migration checks ==

Two ways of testing to see whether data needs to be cleaned up before migrating:

# Quick scan of tables for obvious 'bad' values (e.g. DATE columns with '0000-00-00') using WHERE clauses - i.e. a blacklist.
# Exhaustive checks of every row - i.e. a whitelist.

Probably also a good idea to check foreign key relationships which have not explicitly been defined by the use of <code>REFERENCES</code>.

Reasons for pre-migration checks include:

* MySQL is not as strict as PostgreSQL about validating data (or at least, not with the default MySQL configuration).
* MySQL will usually enforce referential integrity if foreign keys are explicitly defined, but not all developers add the relevant <code>REFERENCES</code> constraints to their schemas.
* Easier to identify and fix bad data whilst still using MySQL, and having a clean data set should make migration smoother.

== Useful links ==

* [http://tapoueh.org/blog/2013/08/08-MySQL-to-PostgreSQL.html Migrating from MySQL to PostgreSQL] - Blog article about tool for migration.
* [https://github.com/philipsoutham/py-mysql2pgsql py-mysql2pgsql] - Python module for converting MySQL to PostgreSQL. Did not work for me, but that may be due to my lack of Python knowledge and not having all the dependencies.
* [http://wiki.postgresql.org/wiki/How_to_make_a_proper_migration_from_MySQL_to_PostgreSQL How to make a proper migration from MySQL to PostgreSQL] - From 2009, so generic advice is useful but other parts may be out of date.
* [http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL Converting from other Databases to PostgreSQL] - Starting point for jumping off to other sites.
* [http://stackoverflow.com/questions/4756825/mysql-to-postgresql-migration MySql to PostgreSql migration]
* [http://stackoverflow.com/questions/772111/switching-from-mysql-to-postgresql-tips-tricks-and-gotchas Switching from MySQL to PostgreSQL - tips, tricks and gotchas?] - Good list of things to watch out for.
* [http://stackoverflow.com/questions/724867/how-different-is-postgresql-to-mysql How different is PostgreSQL to MySQL?]
* [http://stackoverflow.com/questions/2831009/migrate-from-mysql-to-postgresql-on-linux-kubuntu/2831517 Migrate from MySQL to PostgreSQL on Linux (Kubuntu)]
* [http://www.xach.com/aolserver/mysql-to-postgresql.html Migrating from MySQL to PostgreSQL]
* [http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL Converting MySQL to PostgreSQL]

[[Category:MySQL]]

MySQL to PostgreSQL - Revision history

Paul at 15:06, 4 March 2018

Paul: Created page with "Simple script for creating Result classes from an existing MySQL database, then producing a PostgreSQL schema file from those classes: #!/usr/bin/perl use Mod..."

Paul: Created page with "Simple script for creating `Result` classes from an existing MySQL database, then producing a PostgreSQL schema file from those classes: #!/usr/bin/perl use Mod..."