Although FoxPro is one of legacy DBMS, it is still used by many companies and organizations. The most important limitation of this database management system is data size limit of 2 GB and lack of support for hi-load environments with many concurrent connections. 

When considering upgrade of FoxPro database, the best option is to migrate data to PostgreSQL because of the many benefits which are:

1.   Cross-platform – it is available on both Windows and Unix platforms
2.   Many databases feature such as transactions, sub-selects, user-defined types which are all sophisticated and complex
3.   High scalability and reliability
4.   Open-sourced platform
5.   Unlike other popular DBMS, the BOOLEAN type has similar syntax such as ‘t’ is TRUE and ‘f’ is FALSE 

Migration between those DBMS is relatively easy because of FoxPro engine which lacks views, triggers, stored procedures, and other non-trivial database objects. All that is required is to migrate data to PostgreSQL server. The task may become even more complicated when there are thousands of FoxPro tables to migrate. However, possible risk of data loss or corruption due to human mistakes is eliminated by using special migration tools to automate the process.

Possible solutions

1.   Using Comma Separated Values (CSV) – The first step in this process is to export FoxPro tables into CSV format. It is an intermediate storage for FoxPro data before moving into PostgreSQL database. DBF files can be imported into CSV format using free tool dbf2csv. The second step is to import CSV data into PostgreSQL database using pgloader tool that requires some preprocessing implemented by the script below:

LOAD CSV  

      FROM ‘path/to/file.csv’ (x, y, a, b, c, d)  

      INTO postgresql:///pgloader?csv (a, b, d, c)  

      WITH truncate,  

           skip header = 1,  

           fields optionally enclosed by ‘”‘,  

           fields escaped by double-quote,  

           fields terminated by ‘,’  

       SET client_encoding to ‘latin1’,  

           work_mem to ’12MB’,  

           standard_conforming_strings to ‘on’  

    BEFORE LOAD DO  

     $$ drop table if exists csv; $$,  

     $$ create table csv (  

         a bigint,  

         b bigint,  

         c char(2),  

         d text  

        );  

   $$; 

However, the system may necessitate another step if there are non-ANSI symbols contained in the FoxPro data that are not even stored in the Unicode page. In this case database administrator or person responsible for data migration should convert concerned data using special software. 

1.   Database migration with PgDBF – To import data into psql client, the PgDBF converts the FoxPro databases into a suitable format. While this step is capable of handling encoding issues, there are manual custom scripting that may be required.
2.   Intelligent Converters developed a special tool called DBF to PostgreSQL tool which helps to migrate database from FoxPro to PostgreSQL. This converter reads data from DBF files and writes it to PostgreSQL database directly, without using middleware such as ODBC, which facilitates high performance of overall process. Other features of the database migration tool are:

•   Support for FoxPro data and MEMO files
•   Support for all PostgreSQL servers
•   Support for .dbf files which exceed 4GB
•   Merging of DBF data into existing PostgreSQL database
•   Support for all FoxPro code pages
•   Storage of conversion settings as profile
•   Command line support
•   Interface for wizard-style