Migration from MS Access to PostgreSQL
Despite of the fact that database migration is tedious process demanding a lot of resources, many companies and organizations decide to move away from Microsoft Access due to essential weaknesses of this DBMS such as:
- Poor security system
- Low performance for databases exceeding 1 GB
- Limitations on remote connection
- Slowdown when is used by concurrent users
Those limitations may not affect small databases that are run by single user, but it cannot work for large corporate scale data warehouses. The main goal of the migration is to get higher performance, multiple user access, cross-platforming and a better security system with less total cost of ownership compared to MS Access.
One of the most popular destination of migrating from Microsoft Access is PostgreSQL, advanced open-source platform with capabilities exceeding many commercial database management systems. The main benefits of PostgreSQL towards MS Access are:
- Native client-server architecture
- No limitations of data volume and number of concurrent users
- Support for several models of indexing
- Strong transactional model
- Powerful options of backup/restore
- Database logic is implemented via stored procedures, functions and triggers complied with ANSI SQL standard
The common approach to database migration is known as extract-transform-load (ETL) method. To migrate database from MS Access to PostgreSQL, the following steps are required according to ETL approach:
- Export and convert the MS Access table and constraint definitions to PostgreSQL format then load to the destination database
- Export the source data into comma separate files (one file per table), make necessary conversions and load it into the destination database
- Extract queries from Microsoft Access database as “CREATE VIEW” statements; convert them according to the PostgreSQL syntax and then import into the target database.
Some of the steps listed above can be automated. For example, MS Access data can be exported to PostgreSQL database via Microsoft Access itself combined with PostgreSQL ODBC driver as follows:
- Setup ODBC connection for PostgreSQL
- Open MS Access database
- Highlight the table to be exported
- Select “File” > “Export” menu item
- In the appeared dialog box “Export Object Type” select “ODBC Database”
- Select the ODBC data source created on the 1st step
Obviously, this method still requires some manual efforts that could be a bottleneck for migration of large and complicated MS Access databases. Fortunately, there are dedicated software products that can automate the entire process of converting MS Access database to PostgreSQL. One of these products is MS Access to PostgreSQL migration tool created by Intelligent Converters. They are providing high quality solutions and tools for migration and synchronization within popular databases including PostgreSQL, MySQL, MS Access, SQL Server, Oracle, SQLite, FoxPro and IBM DB2.
The converter is a user-friendly application that has the capability of handling complex and very large MS Access database migration up to 10000 records per second on a modern hardware. It supports Unicode, carefully handles indexes, constraints and relationships between tables, migrates MS Access queries to PostgreSQL views.
Furthermore, the application can help you export data from MS Access to PostgreSQL script when there is no direct connection to the target server. This application has the capability of merging data into an existing PostgreSQL database and synchronize it into the target database with MS Access data. In conclusion, it supports command line which enables the automation, scheduling and scripting of MS Access to PostgreSQL database migration process.