Thread: Migrating PostgreSQL database to MySQL/MS Access

How can I migrate a PostgreSQL database to MySQL or MS Access? Are there any
tools available?
--
View this message in context:
http://www.nabble.com/Migrating-PostgreSQL-database-to-MySQL-MS-Access-tf2095559.html#a5776442
Sent from the PostgreSQL - general forum at Nabble.com.

RPK <rohitprakash123@indiatimes.com> wrote:

>
> How can I migrate a PostgreSQL database to MySQL or MS Access?

You can't.  Neither MySQL nor Access support the advanced features in
PostgreSQL.  You'll probably have to rewrite the app.

--
Bill Moran

Microsoft: Where do you want to go today?
Linux: Where do you want to go tomorrow?
FreeBSD: Are you guys coming or what?

On 8/12/06, Bill Moran <wmoran@collaborativefusion.com> wrote:
> RPK <rohitprakash123@indiatimes.com> wrote:
>
> >
> > How can I migrate a PostgreSQL database to MySQL or MS Access?

in your case i would suggest ms dts or vb script in conjunction with
the odbc driver for both databases.

> You can't.  Neither MySQL nor Access support the advanced features in
> PostgreSQL.  You'll probably have to rewrite the app.

well, since he wrote his app in vb.net he is probably using postgresql
as a dumb data store, it might not be all that difficult. still, not a
very smart move.

merlin

> well, since he wrote his app in vb.net he is probably using postgresql
> as a dumb data store, it might not be all that difficult. still, not a
> very smart move.
>

so if you have functions on server side or triggers or rules... then
you need some rewrite on the app... otherwise just to adapt the sql as
necessary...

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
                                       Richard Cook

On 8/12/06, Jaime Casanova <systemguards@gmail.com> wrote:
> > well, since he wrote his app in vb.net he is probably using postgresql
> > as a dumb data store, it might not be all that difficult. still, not a
> > very smart move.
>
> so if you have functions on server side or triggers or rules... then
> you need some rewrite on the app... otherwise just to adapt the sql as
> necessary...

the .net 'way' of database application development is to keep all the
logic in the .net middleware.  please note that I am completely
opposed to this because it obfuscates good data management practices.
however based on the op's comments I am guessing he is doing things
the .net way.

merlin

Merlin,
are you serious and well informed about this? There is really again somebody throwing away decades of experience, a theoretically proven data integrity system just to reimplement the wheel in a shiny, unproven technology?

Do you know for what benefit that happens? I have seen similiar stupidity with EJB, having RI within that layer. Why are people doing this? Do they know something I miss, or did they just not vivist Databases 101 ?

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.

Harald Armin Massa wrote on 13.08.2006 10:46:
> Do you know for what benefit that happens? I have seen similiar stupidity
> with EJB, having RI within that layer. Why are people doing this? Do they
> know something I miss, or did they just not vivist Databases 101 ?

I think this happens because of the OO/UML hype. People tend to see the database
as a mere storage facility and putting the logic (including RI and other stuff)
in the middle tier. This should make the application more platform independent.

I'm a J2EE developer but with a strong database background and I always have to
fight to get the basic checks into the database. Too many people coming from
MySQL, Access and dBase ("but we can check that in the application logic!")

And MS is going the same way I think (haven't done any .net development though,
but seeing SQL Server's transact SQL I really don't want to use that for
implementing my business logic as well ;)

Regards
Thomas

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thomas Kellerer wrote:
> Harald Armin Massa wrote on 13.08.2006 10:46:
>> Do you know for what benefit that happens? I have seen similiar stupidity
>> with EJB, having RI within that layer. Why are people doing this? Do they
>> know something I miss, or did they just not vivist Databases 101 ?

This is RDBMS Conventional Wisdom.  Putting lots of logic into the
RDB has it's down-side though: depending on the RDBMS, you might
need an exclusive lock to modify a trigger or CHECK constraints,
drop a stored procedure, etc.  If the object is on a popular table,
the whole app system may have to come down.

Next there's maintenance.  A FK relationship may make on-line
archiving a parent-child table pair slower or more complicated than
if the FK relationship was managed by the app system.

Next, performance.  If the server is burdened, or some of the RDBMS'
operations are inefficient, then doing the work inside the
application may be faster.

Lastly, there's politics.  If the DBA/Operations team is in one
division, and the Development group is in another, the dev team
might want to keep much of the system knowledge to itself, and not
share with the DBAs.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFE3vVTS9HxQb37XmcRAl3gAJ9ZxtE5zrhkgsXBgZ8J7YqdaIxPEgCfZ9qy
PBjxVm39dtz9crJkLrFti68=
=nKDU
-----END PGP SIGNATURE-----

On 13/8/06 09:57, "Thomas Kellerer" <spam_eater@gmx.net> wrote:

> Harald Armin Massa wrote on 13.08.2006 10:46:
>> Do you know for what benefit that happens? I have seen similiar stupidity
>> with EJB, having RI within that layer. Why are people doing this? Do they
>> know something I miss, or did they just not vivist Databases 101 ?
>
> I think this happens because of the OO/UML hype. People tend to see the
> database
> as a mere storage facility and putting the logic (including RI and other
> stuff)
> in the middle tier. This should make the application more platform
> independent.

I see a similar issue in my line of work. Many of the programmers (even
those working for the larger software houses in the social housing sector)
have started out their careers as housing offices or maintenance surveyors
or whatever and have drifted into programming over the years. Their bosses
(in fact, in some cases they are the bosses) demand database neutrality so
they can run with whatever DBMS the client prefers and they simply take the
simple option and include all RI and other constraints in the client or
middle tier, partly because they don't know any better, but more these days
because they don't get the time or resources to do the job properly :-(

You can imagine the fun we sometimes have chasing down data errors!

Regards, Dave

On Aug 13, 2006, at 11:34 am, Dave Page wrote:
> I see a similar issue in my line of work. Many of the programmers
> (even
> those working for the larger software houses in the social housing
> sector)
> have started out their careers as housing offices or maintenance
> surveyors
> or whatever and have drifted into programming over the years. Their
> bosses
> (in fact, in some cases they are the bosses) demand database
> neutrality so
> they can run with whatever DBMS the client prefers and they simply
> take the
> simple option and include all RI and other constraints in the
> client or
> middle tier, partly because they don't know any better, but more
> these days
> because they don't get the time or resources to do the job
> properly :-(
>
> You can imagine the fun we sometimes have chasing down data errors!


Dave,

The attitude where I work is similar.  Part of the problem is our
biggest app is like a plate of spaghetti... we have business rules
enforced everywhere from the Flash front-end (using ActionScript) to
the MSSQL back-end (Transact-SQL).  It's anyone's guess what's left
in the C#.  So on one hand it's easy to see why the developers would
like it all moved in one location.

Personally, I'm a data integrity fanatic - we've been bitten by
violated rules too often (my favourite is two tables used to store
two types of login, one must have even primary keys the other must
have odd ones :)  we learnt after I naively did a bulk insert into
both tables that constraints to enforce bizarre things like this are
quite useful...).  However, I can also see the advantage of collating
the business logic.  We are trying to switch development to Ruby on
Rails which will help in that it enforces an MVC architecture
(whether the developers will actually use it correctly is another
issue), but then leaves you with the problem of having to duplicate
all the business rules (at least those that enforce purely integrity)
in the database, so they can be used for interactive GUI error
messages as well as integrity.

Out of this situation seems to come the following problems:

- developers don't care about database integrity because "the app
works without it"
- developers are therefore too lazy to even consider (learning the
skill of) writing database constraints
- because the code lacks any formal business rules, they are never
clearly and explicitly documented (so we know what they actually are!)

We also have the issue some of our customers are clueless business
types from the Church of Microsoft and don't understand that Postgres
is actually better AND cheaper than SQL Server.  If any demand we use
SQL Server, you can double the complexity of including integrity.

I am the least educated person in my office (no degree) and I'm the
ONLY one who has any understanding of relational theory.  I can't
understand how developers employed to created database-backed
applications think they can get anywhere with so little understanding
of what they are doing.  Well actually, having seen lecture slides
from one of the developer's uni courses, I have a vague idea.

Any suggestions on how to correct this attitude (and consequently the
problems above) would be very welcome :)  I have actually written a
10-page introduction to relational theory, based on the bad design
decisions in our apps.  I intend to sit everyone round, nail their
feet to the ground, and not let them leave until they have at least
acknowledged every point.  How well this is received - and how long
my job lasts afterwards :) - remains to be seen.

Ashley

Ashley Moran wrote on 13.08.2006 13:46:
> I can't understand how developers employed to created database-backed
> applications think they can get anywhere with so little understanding of what
> they are doing.

Well spoken!!

> I have actually written a 10-page introduction to relational theory, based on
> the bad design decisions in our apps.

I will most probably prepare a training on how to use a database efficiently
covering *very* basic things that I have seen a tremendous lack of
understanding: Why do I need an Index? What happens if I don't? When is an index
used? How do I know whether a query actually uses an index? ....

I do agree that this might be a problem of universty education which focuses too
much on theory and not on real-world problems.

Thomas

> On 8/12/2006 Merlin Moncure <mmoncure@gmail.com> wrote:
> the .net 'way' of database application development is to keep
> all the logic in the .net middleware.  please note that I am
> completely opposed to this because it obfuscates good data
> management practices. however based on the op's comments I am
> guessing he is doing things the .net way.
>
> merlin

Speaking as an architect on both Java-based systems and .NET-based
systems, I need to jump in and correct this before too many rumors get
started.  The .NET camp argues about this all the time in the same way
that the Java camp does: should the business logic be in the database
tier or the middle tier, with valid arguments on both sides depending on
the specific situation.  (I tend to lean heavily towards database tier
myself for most data-related tasks, although in some cases this is more
for the benefit of performance than for design.)  Plus the .NET camp has
the same problem with less-advanced developers embedding business and
database logic in the _client_ tier as does the Java camp (the .NET
folks embed their dynamic SQL in Winforms apps and ASP/ASP.NET code
while the Java folks embed theirs in Swing apps or JSP).

The argument seems to be raised again more often now in the .NET camp
since the release of SQL Server 2005, since SQL Server 2005 can run CLR
(.NET-based) procedures native inside the database.  Now, of course,
some of the people who were keeping their .NET code in the middle tier
are starting to move this code into the data tier (still running their
same .NET code, but now running inside the database process space).
Whether or not in fact this is the right layer for that specific
operation depends on the situation.

- Bill

On Aug 13, 2006, at 2:16 pm, Thomas Kellerer wrote:
> I do agree that this might be a problem of universty education
> which focuses too much on theory and not on real-world problems.

 From what I've seen, a bigger problem is universities that focus on
neither!!!


Ashley

pgsql-general-owner@postgresql.org wrote:

> How can I migrate a PostgreSQL database to MySQL or MS
> Access? Are there any tools available?

Hi,

Can we know maybe why you want to do this?

---------------
Philippe Lang
Attik System

for an excellent book on keeping things where they should be go buy:
"The ART of SQL" by Stephane Faroult.  Sample chapter online.
http://www.oreilly.com/catalog/artofsql/

It's an excellent read, and provides ample arguments for keeping
constraint management with the database and not in each of the app(s)
that use the data.  Best book on SQL I've read in a number of years.



Ashley Moran wrote:
> On Aug 13, 2006, at 2:16 pm, Thomas Kellerer wrote:
> > I do agree that this might be a problem of universty education
> > which focuses too much on theory and not on real-world problems.
>
>  From what I've seen, a bigger problem is universities that focus on
> neither!!!
>
>
> Ashley
>
>

On Sat, 2006-08-12 at 08:08 -0700, RPK wrote:
> How can I migrate a PostgreSQL database to MySQL or MS Access? Are there any
> tools available?

Although migrating away from PostgreSQL is not a popular idea on this
list, it can be done.

If you're using any PostgreSQL-specific features, plan how you want
those things to work in MySQL/MSAccess. For everything else, just export
the tables from PostgreSQL using PgAdminIII (www.pgadmin.org),
phpPgAdmin, or just COPY
( http://www.postgresql.org/docs/8.1/static/sql-copy.html ). Then import
them into MySQL/MSAccess. You may need to make some simple changes, like
changing the data type of a column. Also consider writing some quick
scripts using JDBC or ODBC to move data.

Consider whether that's really what you want to do. PostgreSQL can most
likely be run in your environment (it runs on windows, linux, and many
other operating systems). Also, it most likely has far more capability
to do what you want than either MSAccess or MySQL. You can connect to
PostgreSQL with ODBC or JDBC and almost all languages have a PostgreSQL
module in addition to ODBC/JDBC. It is probably worth your time to ask a
few questions on this list to determine how PostgreSQL can work better
for you.

Regards,
    Jeff Davis

> > > well, since he wrote his app in vb.net he is probably using
> > > postgresql as a dumb data store, it might not be all that
> difficult.
> > > still, not a very smart move.
> >
> > so if you have functions on server side or triggers or rules...
> then
> > you need some rewrite on the app... otherwise just to adapt the
> sql as
> > necessary...
>
> the .net 'way' of database application development is to keep all
> the logic in the .net middleware.  please note that I am completely
> opposed to this because it obfuscates good data management
> practices.
> however based on the op's comments I am guessing he is doing things
> the .net way.

Not really. It's one of the ways. Another way that's pushed pretty hard
with .Net is sticking your logic in stored procedures. The .Net tools
from MS integrate very well with situations where all your logic is in
stored procedures - both in SQL Server 7.0/2000 (which has only TSQL
stored procs) and 2005 (which has TSQL and also CLR/.Net language
independent stored procedures).

The tools let you do it either way. Unfortunately a lot of people don't
realize the gains to be had by choosing the right one.

//Magnus

On 8/18/06, Magnus Hagander <mha@sollentuna.net> wrote:
> > the .net 'way' of database application development is to keep all
> > the logic in the .net middleware.  please note that I am completely
> > opposed to this because it obfuscates good data management
> > practices.
> > however based on the op's comments I am guessing he is doing things
> > the .net way.
>
> Not really. It's one of the ways. Another way that's pushed pretty hard
> with .Net is sticking your logic in stored procedures. The .Net tools
> from MS integrate very well with situations where all your logic is in
> stored procedures - both in SQL Server 7.0/2000 (which has only TSQL
> stored procs) and 2005 (which has TSQL and also CLR/.Net language
> independent stored procedures).
>
> The tools let you do it either way. Unfortunately a lot of people don't
> realize the gains to be had by choosing the right one.

yes. in fact, iirc the ms team blew out the java pet shop performance
demo by making use of stored procedures on the database.  the .net
stack can be used to make excellent database applications if used
properly.  however, most if not all the .net developers I have worked
with professionaly (with exceptions from the asp.net world) are vb6
expatriates who do thick client designs.  visual studio very much
encourages this as does the entire ado.net stack which is my least
favorite part of .net...imo its over designed and a solution in search
of a problem.

2005 yukon is actually a nice database, second in my opinion to only
postgresql in overall capabilities and general design.  you can't deny
though that ms encourages development of logic in the middle tier or
'business layer', whatever that means.

merlin