This older article was written before the addition of the PHP Migration feature along with improved functionality in the FmPro Migrator user interface. The process is much easier today!

How can FmPro Migrator help you?

title: FileMaker to PostgreSQL Migration with FmPro Migrator on Windows

4/27/2004

Synopsis:

In this article I provide practical migration information using FmPro Migrator on Windows to automate FileMaker to PostgreSQL database migrations. I include real-world tips based upon database migrations I have completed with FmPro Migrator. The prospective reader of this article is someone who is familiar with creating and using FileMaker databases, but who may not be as familiar performing the same tasks with a PostgreSQL database.

Overview

What is FmPro Migrator? FmPro Migrator is a graphical application available for MacOS X and Windows which generates database migration scripts to migrate FileMaker databases to a variety of destination database servers. This article focuses on using FmPro Migrator for Windows to migrate a FileMaker database to just one of those migration targets, which is PostgreSQL. FmPro Migrator for Windows makes ODBC queries to the FileMaker database in order to obtain metadata information about the database. From this metadata info, migration scripts and report files are then produced which migrate the database structure and data to PostgreSQL [See Figure 1].

Utilizing an automated process to create migration scripts is often essential for a successful migration project. A typical FileMaker database may contain dozens or even thousands of fields. Field names often include illegal SQL characters, spaces and PostgreSQL reserved words. Hours or even days of work can be saved by automating just the development of the table creation SQL code.

[Figure 1 - ODBC Metadata Query Process]

Why Migrate?

FileMaker is a terrific database for desktop or workgroup sized database solutions. FileMaker is unmatched in usability thus allowing even novices to quickly create useful database applications for themselves and other users. FileMaker solutions are often developed by individuals and departments who are working independently from a corporate IT department due to cost or reduced IT staffing conditions. Seen in this perspective, FileMaker can be a useful prototyping tool for testing user interface design and functionality required within a database application. If the application functionality or user base requirements continue to fit within FileMaker's capabilities, then the application can continue to be used within FileMaker. But if these needs change, and the application needs to be scaled up significantly, then the design work originally done within FileMaker can speed the development of an application running within PostgreSQL. Thus, FileMaker can be considered to be a RAD (Rapid Application Development) environment for larger database servers like PostgreSQL.

The typical reasons for customers wishing to migrate from FileMaker include FileMaker file size limitations and performance issues. FileMaker 6 has a maximum file size limitation of 2 GB. A FileMaker 6 database file which exceeds 2 GB in size generally gets corrupted almost immediately. Since FileMaker 7 now allows database files to contain up to 1,000,000 tables with the ability to grow to a size of up to 8TB, the need to migrate a database for file size reasons has been reduced.

Depending upon the type of tasks being performed with a FileMaker database, it may not be possible to even get close to the typically recommended maximum limit of 250 users per database. Tasks such as searching the database, long running report scripts or large numbers of records being calculated within summary fields can use up available CPU resources rather quickly. PostgreSQL databases aren't typically used to perform tasks such as unstored calculations nor do they implement features like the FileMaker summary field. These types of calculations can require increasing amounts of CPU resources by FileMaker as the number of records increase. PostgreSQL databases can also scale up much higher in terms of the hardware they can run on too. The PostgreSQL database can be installed on high end UNIX servers containing dozens of processors and many GB of RAM, compared to the typical MacOS X or Windows desktop computer typically used to host FileMaker databases.

PostgreSQL offers important database features which are simply not available with FileMaker. PostgreSQL offers the ability to create hot standby and multi-master replicated databases for high availability. As with most server-based database servers, PostgreSQL provides the ability to control every aspect of the database with either SQL commands or stored procedures. Even commercial databases such as Oracle can't match the rich array of programming languages which are available for the creation of these stored procedures in PostgreSQL. Furthermore both native and ODBC connectivity is available to PostgreSQL databases from each operating system platform for which it is compiled. FileMaker currently offers ODBC connectivity only from a Windows computer, thus limiting connectivity options. It is not unusual to see application crashes associated with making large ODBC requests to a FileMaker database. In fact for this very reason it is necessary to be careful when configuring ODBC connectivity parameters when copying data from FileMaker to PostgreSQL.

PostgreSQL also offers a richer feature set compared to other open source databases. Features such as stored procedures, views, triggers, and sequences are commonly available with commercial databases such as Oracle, and are also available with PostgreSQL.

Technical Challenges with Migration

Some of the technical challenges with manual migrations of FileMaker databases include changing database and column names, dealing with large numbers of FileMaker fields, migrating text fields greater than 254 characters, and migrating images from FileMaker. The migration process implemented by FmPro Migrator involves the use of Perl programs to migrate the actual data via an ODBC connection between the FileMaker and Oracle databases.

• FileMaker Database Name Issues - A FileMaker database file name can contain characters (i.e. spaces and special characters) which can't be used within a SQL statement. The first task is to manually change the database name to remove any special characters from the filename. FileMaker will give a warning message if related files can't be found, but this won't affect the migration process because the contents of each file gets transferred individually to the PostgreSQL database.

• FileMaker Field Name Issues - FileMaker field names may also contain characters which would be illegal for an PostgreSQL database column name. Fortunately FmPro Migrator handles this conversion process automatically when it generates migration scripts. But you do have to watch out for situations in which duplicate column names may be created. FmPro Migrator removes special characters from field names and changes spaces into underscore characters. PostgreSQL column names also get truncated to the PostgreSQL mandated limit of 31 characters. When migrating a FileMaker database containing hundreds of fields, it is generally more efficient to create the first pass of migration scripts and then try to create a table with the resulting table creation code. If there are duplicate column names, the database will provide an error message so that changes can be made within the original FileMaker database. The scripts can then be regenerated by FmPro Migrator to reflect these changes within FileMaker.

• Too Many FileMaker Fields - Within FileMaker versions prior to FileMaker 7 there is no built-in mechanism to keep track of variables while running a script or calculation. Therefore most FileMaker databases often contain dozens of global fields used to store this type of information. FileMaker summary and calculation fields are also handled differently within a database like PostgreSQL. Calculation features during record insertion are usually handled via triggers which fire during the insert or update of a record within PostgreSQL. Calculations are handled either via PL/pgSQL stored procedures or in-line PL/pgSQL code within the trigger itself. The problem with having so many fields is that you can exceed the maximum number of fields which can be created within an PostgreSQL table (250 - 1600 depending upon column types). It is generally possible to remove all global fields, unstored calculation fields and summary fields from the FileMaker database prior to starting the migration process. Removing or at least disabling unstored FileMaker calculation fields and summary fields is also advisable in order to prevent FileMaker from dropping the ODBC connection while waiting for these calculations to be completed. FileMaker will usually issue a "fetch forward" error when this occurs.

• Migrating FileMaker Large Text Fields - FileMaker can store as many as 64,000 characters within each text field. However the default PostgreSQL VARCHAR column stores up to 4096 characters. I usually designate fields containing more than 254 characters as Large Text fields. Fields which are identified as Large Text fields are migrated by FmPro Migrator as PostgreSQL TEXT columns which can store up to 1 GB of data per record. The scripts generated by FmPro Migrator utilize a Perl DBI/DBD:ODBC connection between the FileMaker and PostgreSQL databases in order to make it easier to migrate arbitrarily large amounts of data.

• Migrating FileMaker Repeating Fields - There is no data type within an PostgreSQL database which is exactly equivalent to the Repeating Fields feature within FileMaker, though PostgreSQL arrays offer very similar functionality. Repeating fields within FileMaker will only be completely transferred into PostgreSQL if the fields are text fields. FileMaker numeric fields which contain repeating values should be converted to text fields within FileMaker in order to transfer each of the repeating values. Otherwise, PostgreSQL will only recognize the first value of a numeric field. Each repeating field entry is separated by an ASCII (29) character which is used by FileMaker Pro to separate values within repeating fields. For full support of this functionality, redesign of the FileMaker Pro database structure should be considered. This type of feature should be implemented via separate rows of data within an PostgreSQL database with the child records having a common foreign key value pointing back to the primary key of the parent record.

• Migrating FileMaker Container Fields - FileMaker Pro uses Container fields for storing pictures, sound and QuickTime video. Data located within FileMaker Pro Container fields is transferred into PostgreSQL "lo" type columns. A user defined type called "lo" (also known as a domain with PostgreSQL 7.3+) is created in PostgreSQL prior to creating the database table. This object type is used by the scripts created by FmPro Migrator because it is fully supported by the PostgreSQL ODBC driver. There are some limitations which have been discovered while retrieving Container field data from FileMaker Pro. I have only been able to determine the storage format for FileMaker JPEG images as the internal format is not well documented. Therefore only JPEG Container field data is supported by the migration scripts. All container fields include the text "HTTP/1.0" prefixed to the data. This header information is removed from the data, with the remaining JPEG data being transferred into the PostgreSQL database table. Other types of container field data are also prefixed with the "HTTP/1.0" file header information which is followed by a JPEG icon representing the type of data contained within the field. So if sound information is stored within a Container field, you will end up getting a JPEG image of a sound icon copied into the PostgreSQL database. FileMaker appears to be unique in handling information storage in this manner. All other databases I have worked with simply store the exact binary information which you entered into the field. This feature of FileMaker appears to be related to the tight integration of the user interface with the database engine technology. There appears to be a desire by the FileMaker developers to always show some type of image representing the type of info within the field, even if the type of data does not lend itself to a visual display.

• Migrating FileMaker Calculations - FileMaker field calculations and calculated summary values need to be re-engineered either as PL/pgSQL stored procedures, triggers or application specific code by the application which will be providing the graphical interface to the PostgreSQL database. There are too many differences between how FileMaker handles these tasks to make it practical to automate the migration of calculation formulas.

• Migrating FileMaker Scripts - The functionality incorporated within FileMaker scripts will also typically be incorporated within stored procedures within a PostgreSQL database. These procedures might be called by a remotely triggered script which could be scheduled via a CRON job on a UNIX/Linux/MacOS X server.

Using ODBC with FileMaker

FileMaker 5, 5.5 and 6.0 include an ODBC driver which is only usable by 3rd party applications on Windows. FileMaker Pro 7 for Windows includes an ODBC driver which only allows a connection to the database from the localhost IP address - from the computer running the database.

FmPro Migrator works around these limitations by using Perl DBD:ODBC programs running on Windows which is able to access both the PostgreSQL and FileMaker databases for the data migration. The PostgreSQL and FileMaker databases can be running on different platforms, but PostgreSQL and FileMaker ODBC drivers both need to be installed on Windows. For an PostgreSQL database, this is accomplished by installing the PostgreSQL psqlodbc driver on Windows. For FileMaker 5.0 - 6.0, the ODBC driver software is installed along with the FileMaker database software. . An ODBC DSN is then created for each database connection. The naming of the ODBC DSNs is specified within the scripts generated by FmPro Migrator.

ActiveState Perl needs to be installed on Windows along with the Perl DBI and DBD::ODBC modules. The DBI/DBD::ODBC modules can be downloaded from www.cpan.org or with the following ActiveState PPM commands:

ppm
PPM> install DBI
PPM> install DBD-ODBC
PPM> quit

The FileMaker Example Database

The example database used for this article is a database named Example.fp5 database. This database includes a wide selection of fields so that we can see how the various data types get transferred to PostgreSQL. The following table shows the FileMaker data types and the compatible column types used within the PostgreSQL database.

[Table 1 - FileMaker and PostgreSQL Data Types]

FileMaker Date/Time fields can store Date or Time information and FileMaker 7 even includes a new Timestamp field which can be used for storing more precise Time data. However FileMaker does not provide any external way to query the database to determine which type of data is actually stored within the field. Therefore FmPro Migrator attempts to determine the type of stored data within the field based upon the name of the field. If a Date/Time field includes the text "time" within its name, it is migrated to a PostgreSQL Time column. This behavior can be overridden by simply changing the field name within FileMaker so that FmPro Migrator doesn't see the text "time" within the name of the field.

FileMaker Parameters

[Figure 2 - FileMaker Parameters]

The first step in the migration process is to launch FileMaker, open the Example.fp5 database file and add a few records of sample data to the database. Make sure that the Local and Remote Data Access Companion plug ins are enabled and that file sharing is enabled for the Example.fp5 FileMaker database file. The FileMaker database filename will become the PostgreSQL database table name.

Launch FmPro Migrator for Windows, click on the FileMaker folder tab then select FileMaker 6 as the source database and PostgreSQL as the destination database. The ODBC DSN for the source database will be used by FmPro Migrator to obtain metadata from the FileMaker database. Either keep the default "example_fmp_dsn" ODBC DSN name listed in FmPro Migrator or change it to match an existing FileMaker ODBC DSN on your computer.

Since I frequently perform migration projects for customers, I change the contents of the Customer field to reflect their name. The name within this field is used to create the Copyright information within each generated script, thus keeping my customer's legal department happy. Click the browse button to select an output directory, since there will be over a dozen files created for the migration process.

Create the FileMaker ODBC DSN

Open the Windows Data Sources (ODBC) Control Panel to create a new FileMaker 6 System DSN to match the ODBC DSN name entered into FmPro Migrator for the source database. The Data Sources (ODBC) control panel is located within the Administrative Tools folder of the Windows Control Panel.
Note: With Windows XP, it may be necessary to select the Classic View for the Control Panel in order to see the Administrative Tools folder.

[Figure 3- Data Sources (ODBC) Control Panel]

This ODBC DSN should be created as a System DSN, so click on the System DSN tab, then click the Add button.
Select the FileMaker Pro driver, then click the Finish button.
Note: If FileMaker 7 is being used as the source database then the DataDirect 32-BIT SequeLink 5.4 driver would need to be selected.

[Figure 4 - Create New FileMaker Datasource]

Enter the name of the ODBC DSN in the first field. This name should exactly match the name entered into the Source Database ODBC DSN field within FmPro Migrator. It is not necessary to enter the Remote Connection information unless the actual FileMaker database file is being accessed from another computer. Click the Advanced tab of the FileMakerPro ODBC Driver Setup panel.

[Figure 5 - Enter FileMaker ODBC DSN Name]

On the Advanced tab, change the Max Text Length from 255 to 65000. FileMaker 6 databases can store up to 64K of text within a text field, so this change in the ODBC Driver Setup panel insures that data is not truncated as it is read from the FileMaker database.

The Fetch Chunk Size determines how many records will be retrieved from the FileMaker database at a time. When reading records from the FileMaker database you will generally notice that 100 records are retrieved, then there will be a pause while the next 100 records are read from the database. This process will continue until all of the records have been read. The default Fetch Chunk Size is 100, and this value should generally not be increased. Increasing the ODBC Fetch Chunk Size can cause FileMaker to crash while serving database files. In fact this parameter may need to be reduced to as few as 10 records if there are more than 500 fields within the FileMaker database file.
Click the Ok button.

[Figure 6 - FileMaker ODBC DSN Advanced Parameters]

Now that the FileMaker ODBC DSN has been created, FmPro Migrator can query the FileMaker database and obtain the metadata required to create migration scripts to PostgreSQL. Click the Refresh button in FmPro Migrator. The example.fp5 database files opened in FileMaker 6 should show up in the Open Databases field.

[Figure 7 - FileMaker Open Databases]

PostgreSQL Database Parameters

PostgreSQL database parameters are entered on the FmPro Migrator "Other" tab. These parameters include database name, username, password and TCP/IP port number information which will be used within the generated migration scripts.

[Figure 8- PostgreSQL Database Parameters]

Generating Migration Scripts

Press the Migrate button in FmPro Migrator to generate migration scripts and database documentation files. Scripts which start with the name of the original FileMaker database file(s) will then be created within the output directory as shown below.

[Figure 9 - Generated PostgreSQL Migration Files]

If these scripts are not created, select the Status Window menu item from the FmPro Migrator File menu. The Status Window will display info about any problems which occurred during the script generation process.

[Figure 10 - FmPro Migrator Status Window]

There are 8 files created for the migration process, but we will only need to use the 5 files which are described in Table 2.

[Table 2 - Migration File Descriptions]

Checking Field Sizes

Open the Windows Command Prompt by selecting Start > Programs > Accessories > Command Prompt.
Use the cd command to navigate into the folder of generated scripts.

[Figure 11 - Opening Windows Command Prompt]

Prior to creating the PostgreSQL database table, it is necessary to determine the maximum amount of data stored within each field of the FileMaker database. Run the example_fmpro_max_fieldsize1.pl program from the Windows command prompt to create the fieldsize report file.

perl example_fmpro_max_fieldsize1.pl

After this program finishes, it will produce the example_fmpro_max_fieldsize_report.txt file. Examine the contents of the example_fmpro_max_fieldsize_report.txt file to determine whether there are more than 254 characters of information stored within any of the text fields of the FileMaker database file. For this article, the FileMaker field named “location” is used as an example of a text field which contains more than 254 characters. Examining the example_fmpro_max_fieldsize_report.txt file shows that FmPro Migrator will rename this FileMaker field as “location_” when creating the PostgreSQL database table. To designate this field as a Large Text field, enter “location_” within the FmPro Migrator Large Text Fields field.

[Figure 12 - Defining Large Text Fields]

Creating the PostgreSQL Table

Open the example_create_table1.sql file in a text editor. Using the fieldsize report you can determine the optimum size to use for each column of the new PostgreSQL table. It is generally a good idea to reduce text column widths from the default value of VARCHAR(254) to be closer to the actual amount of data contained within the FileMaker database.

This particular FileMaker example database does not contain a primary key. This is OK if all we want to do is perform a quick migration to get the data into PostgreSQL and then copy the data to another table later. But if we want the migration process to produce a table which is immediately ready for production use, we should add a primary key field to the table. FmPro Migrator automatically determines that you have a primary key within the FileMaker database by looking for the first numeric field which has the attributes of "Not Empty" and "Unique". Within the table creation SQL file, a primary key column will be created as BIGSERIAL NOT NULL, in order to provide an incrementing sequence upon record insertion. The table creation SQL code may be manually modified as needed prior to creating the PostgreSQL database table. The best strategy to follow is to make any column name changes within the original FileMaker database, then regenerate the scripts with FmPro Migrator. In most cases there is no reason to try to manually track the dependencies between the generated output files, so just let FmPro Migrator recreate these files for you.

The example_create_table1.sql file makes use of a user defined type called “lo” for storing FileMaker container field data as large binary objects within the PostgreSQL database table. The SQL statements used to create this user defined type (or domain in PostgreSQL 7.3+) is commented out by default. Un-comment the appropriate code for your particular version of the PostgreSQL database.

Transfer the example_create_table1.sql file to the PostgreSQL database server.
For this example, the PostgreSQL database is running on a Linux server. The file has been transferred to the PostgreSQL server via an FTP connection and an SSH session has been opened to the Linux server.
Execute the command listed at the top of this file to create the PostgreSQL database table.

psql -d test -U user1 -p 5432 -f example_create_table1.sql

[Figure 13 - Create the PostgreSQL Table]

The "CREATE TABLE" result from psql shows that the table was successfully created. You can further verify the table creation process by issuing a describe command for the new database table.

[Figure 14 - Verifying PostgreSQL Table Creation]

Migrating the Data

Now that the PostgreSQL table has been created, the Perl DBI program which transfers the data from FileMaker to PostgreSQL can be run. This part of the migration process requires the use of the psqlodbc driver in order to write data into the new PostgreSQL database table. Download and install the psqlodbc driver from the gborg website.
The psqlodbc ODBC driver is used to transfer data from FileMaker to PostgreSQL because it provides native support for the user defined “lo” column type used for transferring FileMaker container field data to PostgreSQL large object columns.

Create a new ODBC System DSN to match the Destination Database ODBC DSN name defined by FmPro Migrator within the example_fmpro_to_postgresql_xfer_odbc1.pl file. For this example database file, the ODBC DSN name is example_pg_dsn.
Click the System DSN tab, then click the Add button.
Select the PostgreSQL driver, then click the Finish button.

[Figure 15 - Creating PostgreSQL ODBC DSN]

Enter the data source name and server info on the first pane of the PostgreSQL ODBC driver panel. Then click on the Datasource button.

[Figure 16 - PostgreSQL ODBC DSN Configuration - Pane 1]

Increase the size for the Max LongVarChar parameter from 8190 to 65000.
Click the OK button, then the Save button.

[Figure 17 - PostgreSQL ODBC DSN Configuration - Pane 2]

Run the example_fmpro_to_postgresql_xfer_odbc1.pl script from the Windows command prompt window to copy the data from the FileMaker database to the new PostgreSQL table.
As the script runs, each processed record number will be displayed on the screen.
You can verify the copied data by using SQL commands in psql or by using a graphical utility.

Tips for Migrating Very Large FileMaker Databases

When migrating very large FileMaker database files (over 500 Mb) there are a few modifications I make to the migration process in order to reduce the overall processing time. The typical scenario I have encountered is a situation in which the client wants to migrate the FileMaker data into a large corporate database server. The migration task is part of a larger re-engineering effort to properly normalize the FileMaker data and create a corporate-wide user interface for the data. Migration of the data represents only the first step in the process. The second part of the engineering effort involves using SQL commands to cleanse the data and copy the data to the new normalized database tables. This task can be performed more efficiently in two steps because the second step will involve working with data which will be entirely contained within the PostgreSQL database.

I apply the following steps in this type of situation:
• Avoid Making Two Passes Through the Data - This means that I don't run the fieldsize report for very large database files. This also means that I don't have the information required to properly size the columns in the destination database.
• Create Each Column as Large as Possible - For each text column of the destination table I use the largest possible text compatible column size, which means using a TEXT data type in place of text, numeric and date fields. FileMaker Container fields get transferred to PostgreSQL "lo" object type columns so these columns don't require any changes.
It is easy to use a text editor to manually change the table creation SQL code by performing a global search and replace within the file. This step resolves the problem of data potentially getting truncated due to the PostgreSQL column size not being large enough. This also means that data type errors within the FileMaker database will not prevent the data from being written into the PostgreSQL database. You generally don't want to be part of the way through migrating a 500 Mb file and then encounter a data type mismatch error. It will be quicker to find and fix these types of errors during a data cleansing operation at a later point in time.
• Manually Change DBI data types - Once the new table has been created, I then manually modify the Perl DBI transfer program to reflect these changes. When dealing with large numbers of columns it is much quicker to manually change the bind variable data types with a text editor rather than manually enter each column name into FmPro Migrator as a Large Text Field. The changes I make include:
1) Changing all SQL_VARCHAR bind variable data types to SQL_LONGVARCHAR
2) Changing all SQL_DATE bind variable data types to SQL_LONGVARCHAR
3) Changing all SQL_DOUBLE bind variable data types to SQL_LONGVARCHAR
The SQL_LONGVARBINARY columns remain unchanged because these columns get transferred to "lo" object type columns - which store up to 1 GB of data per record.

Tips for Migration FileMaker Stand Alone Files

The FileMaker Developer version of FileMaker enables developers to package one or more FileMaker database files as a stand alone package which does not require the FileMaker database software. The FileMaker Developer Tool creates a folder for the solution which contains the FileMaker runtime executable and each of the individual FileMaker database files. On Windows, each of the FileMaker database files will be given a developer assigned extension which by default consists of the characters USR. So FileMaker databases which were originally named filename.fp5 will now be named filename.usr.

To work with stand alone solutions files on Windows, just change the filename extension from ".usr" to ".fp5" and use the FileMaker application to open the file. On MacOS X, the database filename extension will typically remain as ".fp5". All you have to do is drag and drop the filename onto the running FileMaker application icon in the dock. If you just double-click on the file instead of opening it with the FileMaker application then the FileMaker runtime application will launch the file - and this is not what you want to have happen. You will want the database to be opened within FileMaker so that you can enable the Local and Remote Data Access Companions and make changes to the fields within the Define Fields dialog. Once you have the database file open in FileMaker you can use FmPro Migrator to generate migration scripts in the same manner as any other FileMaker database file.

Conclusion

The automation which has resulted from the use of FmPro Migrator has made it possible for me and my customers take on projects which would have been too expensive or time consuming to be practical without an automated tool. FmPro Migrator has also made it possible for the FileMaker database to be used as a RAD (Rapid Application Development) tool for enterprise class databases like PostgreSQL.