pgsql to mysql migration

Anon

Hello,
We've made the decision to migrate from postgres to mysql due to the many misbehaviors that we had from postgres.

Might anyone know a safe way to do that concidering that the database contains live data from a website?

I've been searching for migration utilities all over freashmeat, google, sourceforge but with no luck....

can anyone help?

Anon

Funny, as I am moving from mysql to pgsql.

Which version of pgsql are you running and what were the misbehaviours?

Anon

Postgres version 7.2.1

The problem is that whenever i have queries based on the same link resource to postgres i get INVALID RESOURCE ID errors as if the connection is droped from postgres...

for example, i execute a query selecting all my users from the db,
works fine

next i try to execute a query to fetch the users photos from another table (i know it can be done in row, just for the example's case)
In the second query i execute i get these errors.

And they keep appearing without any normal patern or warning.

My queries are sql perfect, and all problems where solved when i switched my db class to create constant connections instead of the normal ones.

This resulted in very bad performance from the server as persistent connections stay open and incrementaly hang the db...

I heard some time ago that this was a php bug, and followed every patch/upgrade of php but the problem still remains leaving me with a bad performance for db calls.

Anon

Sounds like a php error rather than postgres. Sounds like you are using pg_connect rather than pg_pconnect. I have not encountered this problem using pear. And I use pconnects for better performance.

Anon

i use pconnect, pg_connect creates the problems i described, but i don't see any performace boosting with pconnect and i have 30+ pgsql open sockets for pg at my box just hanging...

Anon

I sounds like the postgresql backends are dying. This sometimes happens when you've got something like bad memory or a some other problem (corrupted binaries etc...)

What you are saying is that if you do this:

connect
run query 1
run query 2

that when you run query 2 you get an error saying that you lost the connection to the database? If so, then try either reinstalling postgresql from scratch, or investigate your hardware (memory is the common failure) for problems.

If you want pconnects to work, you need to configure postgresql to support MORE concurrent connections than you configure apache to have children. I.e. if you set postgresql for 64 max connections, set apache to support only 50 children and you'll be ok.

But don't switch over this. This doesn't sound like a typical postgresql problem at all. We use it extensively in php (without persistant connections) and it works just fine.