Converting mysql to postgres

Hi,

Anyone got any cool tricks for converting a Rails site (in this case,
a typo installation) from a mysql database to a postgresql database?

I’ve got it almost working – I’m doing a SQL dump from mysql and
loading it into postgres. However, mysql does booleans as a tinyint
with 0 = false and 1 = true. When I try to import that into a
postgres database that expects booleans to be actual booleans, it
fails.

Any ideas?

Thanks,
Joe

Hi Joe,

IMHO you could utilize migrations for this. With a database.yaml which
sets
adapter to mysql do something like rake db_schema_dump (if I remember
that
correctly - it creates a schema.rb from your database) than change your
environment to the postgres-adapter and do something like rake
db_schema_import (don’t know if I remember that correctly either).

Cheers,
Jan

Joe Van D. wrote:

Hi,

Anyone got any cool tricks for converting a Rails site (in this case,
a typo installation) from a mysql database to a postgresql database?

I’ve got it almost working – I’m doing a SQL dump from mysql and
loading it into postgres. However, mysql does booleans as a tinyint
with 0 = false and 1 = true. When I try to import that into a
postgres database that expects booleans to be actual booleans, it
fails.

Any ideas?

Thanks,
Joe

I’ve had to deal with that going from Postgres to Oracle, which doesn’t
have the boolean. Why not just have your Postgres data model use
smallint and keep the same convention of zero and one? If you’re using
AR, it’s smart enough to do the right thing in any case (if your column
is named xxx, then the AR method xxx? will autoconvert numeric
zero/nonzero to logical false/true).

On 8/5/06, Francis C. [email protected] wrote:

fails.
is named xxx, then the AR method xxx? will autoconvert numeric
zero/nonzero to logical false/true).


Posted via http://www.ruby-forum.com/.


Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

Postgres has a native boolean column, so it makes sense to use it,
doesn’t it? It’s a bit more upfront work when dumping the data, but I
think it’s worth it so that the database is “done right.”

Joe, there are a couple things you can do. The first is to do a regex
replacement in your dump file. You could also just write a custom
dump script (using AR models) and output the SQL that you need, using
true/false instead of 1/0.

You might also be able to use migrations. Create the schema, but make
that column an integer column. Then load all your data in. Then do a
new migration that looks something like:
rename_column :my_table, :bool_field, :old_bool
add_column :my_table, :bool_field, :boolean
MyTable.update_all “bool_field=true”, “old_bool=1”
MyTable.update_all “bool_field=false”, “old_bool=0”

Assuming that all your data loads fine, I think that would be the
easiest way.

Pat

Pat M. wrote:

Postgres has a native boolean column, so it makes sense to use it,
doesn’t it? It’s a bit more upfront work when dumping the data, but I
think it’s worth it so that the database is “done right.”

No argument with your suggestions to Joe, but how do you determine that
the database is “done right”? I’m not a purist about sticking to
standard SQL, because every engine has its own quirks and its own lore,
and being sensitive to these can hugely improve performance. But I doubt
you’ll get a benefit from Postgres’ boolean, which is merely a
notational convenience. So why not stick to the standard in this case?

On 8/5/06, Francis C. [email protected] wrote:

So why not stick to the standard in this case?

That’s precisely why you should use a boolean. The standard defines
it, Postgres implements it.

Pat

Francis C. wrote:

the database is “done right”? I’m not a purist about sticking to
standard SQL, because every engine has its own quirks and its own lore,
and being sensitive to these can hugely improve performance. But I doubt
you’ll get a benefit from Postgres’ boolean, which is merely a
notational convenience. So why not stick to the standard in this case?

Hello Francis,
Not to ‘split hairs’, but if your worried about sticking to ‘the
standard’, then the standard actually does have a ‘boolean’ defined in
it. SQL99 defines it, and your going to laugh at this but, Oracle is
then ‘behind the times’ here in not defining a boolean type as an
allowed database column type, but it -does- have a boolean datatype that
you can use in pl/sql.

Perhaps the easiest way to go from postgreSQL->Oracle is to create

the datatype using user defined datatypes, but, thats beyond my Oracle
know how and starting to get away from the posters original question :wink:

Regards
Stef

Pat M. wrote:

On 8/5/06, Francis C. [email protected] wrote:

So why not stick to the standard in this case?

That’s precisely why you should use a boolean. The standard defines
it, Postgres implements it.

Pat

I may be wrong about this but BOOLEAN is optional in ANSI SQL, and only
Postgres implements it in a compliant way. This is a really tiny point
in the context of what the OP was asking, but I am curious whether you
think one should code to standards because they are standard, or because
they are widely-adopted? Oracle is far more interested in adding
up-stack features to their product line, and they probably won’t be
adding BOOLEAN any time soon.

It often happens that you have to migrate databases, and I’ve found that
migrating away from Postgres is generally painful, because it’s full of
great features that aren’t widely available (Postgres to Oracle is
especially painful, and especially common). The lack of BOOLEAN is easy
to work around, but other often-used Postgres features are not.

I love Postgres and I use it whenever possible. But if you’re going to
use Postgres features, you need to know what’s not available elsewhere,
isolate the dependencies in your code, and document them carefully (and
grep-ably). And of course you can use AR to wrap most of this up, but
only if performance doesn’t matter in your application.

On Aug 5, 2006, at 1:21 AM, Joe Van D. wrote:

Anyone got any cool tricks for converting a Rails site (in this case,
a typo installation) from a mysql database to a postgresql database?

I’ve got it almost working – I’m doing a SQL dump from mysql and
loading it into postgres. However, mysql does booleans as a tinyint
with 0 = false and 1 = true. When I try to import that into a
postgres database that expects booleans to be actual booleans, it
fails.

Any ideas?

How about

  • dump production db to fixtures
  • switch database.yml to postgresql
  • rake migrate
  • load fixtures to production

with the help of the ar_fixtures plugin
http://topfunky.net/svn/plugins/ar_fixtures
http://nubyonrails.com/articles/2005/12/27/dump-or-slurp-yaml-
reference-data

jeremy

On 8/5/06, Pat M. [email protected] wrote:

postgres database that expects booleans to be actual booleans, it
AR, it’s smart enough to do the right thing in any case (if your column

that column an integer column. Then load all your data in. Then do a
new migration that looks something like:
rename_column :my_table, :bool_field, :old_bool
add_column :my_table, :bool_field, :boolean
MyTable.update_all “bool_field=true”, “old_bool=1”
MyTable.update_all “bool_field=false”, “old_bool=0”

Assuming that all your data loads fine, I think that would be the easiest way.

Thanks – I ended up doing exactly that last night. Everything works
fine!

Joe

On Aug 5, 2006, at 12:13 PM, Jeremy K. wrote:

Any ideas?
reference-data

jeremy

This is pretty much what I did for a few of our development projects
where the client came to us with a Rails/MySQL application.

I turned this into rake tasks that also used RailsFS (I needed to
have an excuse to use this…)

http://www.robbyonrails.com/articles/2005/10/29/migrating-from-mysql-
to-postgresql-in-60-seconds-with-rails

:slight_smile:

Robby


Robby R.
Founder & Executive Director

PLANET ARGON, LLC
Ruby on Rails Development, Consulting & Hosting


www.robbyonrails.com

+1 503 445 2457
+1 877 55 ARGON [toll free]
+1 815 642 4068 [fax]

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs