Moving SQL from OS X to Linux; Postgre or MySQL?

blogmin

I am planning a database to be served by Red Hat or Free/Open BSD, but would like to code it on my OS X 10.2 iBook (no broadband@home).

Which database, Postgre SQL or MySQL (if any) will transfer smoothly from OS X to Linux/Unix?

Which Linux/Unix works best for that?

Thanx.

toma42

The particulars of the database are insignificant. Just use standard sql (no db specific calls) for all queries. Don't use views or stored procedures.

Use a database abstraction layer (I prefer pear) then you can dump your db from your mac to whatever other db you want.

blogmin

My calls will be fairly basic, but in PHP I still have to call a specific DB right? Im mean, how else could I open the connection?

I am not sure what the database abstraction layer part is, i can guess from the name though. Where can I find more info about that?

I guess I could save the open DB part in its own file or global vari...

toma42

http://pear.php.net has a good db abstraction layer

Sxooter

Either MySQL or Postgresql will run fine on MAC OSX, BSD, or Linux. Postgresql still has some issues on Windows that will hopefully be taken care of by the upcoming 7.4 release.

What you should really worry about is which is a better fit. If you need foreign keys, constraints, transactions, or stored procedures, then Postgresql is a good choice, if you want something a little simpler to maintain, without the extras postgresql has, MySQL is a good choice. And don't forget to look at Firebird / Interbase, it's quite good too.

blogmin

But are you sure I can take a complicated MySQL DB on OS X, and drag it over a nework to a linux MySQL server? I guess thats my real question...

Sxooter

Originally posted by blogmin
But are you sure I can take a complicated MySQL DB on OS X, and drag it over a nework to a linux MySQL server? I guess thats my real question...

Yes, I'm sure. The only issues to worry about are how you move your data. Use the dump command that comes with MySQL and you should be fine. note that it's NOT uncommon for the data representation ON THE DISK to be different in one OS versus another. So, copying the data directory might actually fail, but that's ok. The dump program is designed to allow for portability.

Same goes for ALL databases. Use their export utility to get your data out and you should be ok.

blogmin

ok, so I have to move it table by table then huh? I am planning on at least 20 tables of relational data in one DB... MySQL will even keep my relations too right?

[deleted]

" MySQL will even keep my relations too right?"

MySQL has no relations, unless you are using InnoDB, but your data will remain exactly the same during the move.

You can create a backup of your entire database at once using the mysqldump command, and you can restore that all at once just by feeding the backupfile to the commandline interpreter.

You may want to read the mysql manual for more information about that.

blogmin

thanks!

chriskl

What could burn you is endianness differences. I'm not sure about PPC and i386 if they're the same or not, but I doubt that either MySQL or PostgreSQL store and transfer data in an endian independent way.

Chris

[deleted]

How does that matter?

The data is transferred as a mysqldump, an ASCII file containining MySQL's interpretation of the data. Surely both mysql for max and for linux will interpret that file the same way.

csn

Originally posted by Sxooter
And don't forget to look at Firebird / Interbase, it's quite good too.

Did you get it working? 😉 How's it compare to PostgreSQL?

Sxooter

Originally posted by csn
Did you get it working? 😉 How's it compare to PostgreSQL?

I grabbed the RPMs and got them installed, but haven't played with it a whole bunch really.