Disclaimer: Not a DBA, nor I am not a DB guy, so please excuse any ignorance in the below.
1. Background
We have a MS Access 2003 database that we are using to manage registration and workshop/accommodation allocation for a conference. The database isn't particularly complicated (around 20 tables or so), nor is the dataset large (largest table has around 13,000 records, most of the others have around 5000 or so records.)
The structure is a bit convoluted though (mostly for historical reasons), and most of the queries we use are quite join-heavy. Some of these seem to take longer than you'd expect them to, for such a small dataset.
The database design is a bit quirky - there's heavy use of varchars for many things, stacks of NULLs everywhere, and not really much use of validation/constraints.
2. MS Access to MySQL
Recently, this was ported over from a pure-Access database to a Access front-end over a MySQL backend on somebody's desktop, mostly to provide multi-user capabilities. I've been told automated tools were used for this, so I assume we weren't using too many MySQL-specific features.
3. MySQL to Postgres
I recently looked at moving this over to a PostgreSQL in a proper server. Postgres was chosen mainly for convenience since we already have a Postgres instance setup there (used for some Django projects).
I tried a MySQL to PostgreSQL conversion using Enterprise DB's Migration Studio, hit an issue with two of the tables complaining about CLOB's...
4. MS Access to Postgres
Anyhow, somebody else suggested it might be better to just go straight from the original MS Access database to PostgreSQL.
My first question is, what is the current recommended procedure for this?
I saw this page from 2001:
and the tool referenced there appears to lead to a 404 page.
I also saw the tools referenced there:
and most of them appear quite dated, from the MS Access 97 era.
Has anybody had any experiencing doing a similar port (Access 2007 to Postgres) recently, what tools did you use, were there any gotchas you hit etc? Or just any general advice at all here?
5. Postgres Specific Features
Once the port is done, I assume they'll probably be some work involved to clean it up, and leverage on some of Postgres's features. In particular, I'm hoping to at least get some decent data validations/constraints in.
The issue is we still need to maintain compatibility, where we can, with the Access frontend.
That and hopefully clean up some of the queries a bit, and try and figure out why some forms in Access are taking so long to load.
Any particularly good books here that you'd recommend? I saw some Postgres-specific books on Amazon, but none seem to have particularly good reviews (or were rather simplistic). Recommendations?
Cheers,
Victor
