When it comes to types of Database Management Systems (DBMS), FoxPro is one of such with pedigree. Initially developed by Fox Software, an improvement was made by Microsoft. Nevertheless, this DBMS is unique in the sense that it has a data size limit of 2 GB. In addition, it is not formulated in such a way that it can work with several hi-load database systems especially when data source has many connections. This is a disadvantage for companies that intend to run simultaneous connections at once. Hence, the reason for a migration to better DBMS. FoxPro, in early days, had a tool called ‘Visual FoxPro’ which Microsoft canceled.
The best option is to migrate from FoxPro to PostgreSQL because of the many benefits which are:
- Cross-platform – it is available on both Windows and Unix platforms
- Many databases feature such as transactions, sub-selects, user-defined types which are all sophisticated and complex
- High scalability and reliability
- Open-sourced platform
- Unlike other popular DBMS, the BOOLEAN type has similar syntax such as ‘t’ is TRUE and ‘f’ is FALSE
Interestingly, the migration process between both platforms is somewhat easy compared to the rest. The reason for the simplicity is because of the data storage of FoxPro 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 becomes complicated when there is a need to covert thousands of tables on the FoxPro database. However, this risk is mitigated by using special migration tools to automate the process.
Possible solutions
- Using Comma Separated Values (CSV) – The first step in this process is to export FoxPro tables into CSV format. This serves as a link through which the data moves into PostgreSQL. With the dbf2csv tool, DBF files are converted into CSV format. The second step which involves the transfer of data into PostgreSQL is done with pgloader tool. But this step requires some set of operations:
LOAD CSV
FROM ‘path/to/file.csv’ (x, y, a, b, c, d)
INTO postgresql:///pgloader?csv (a, b, d, c)
WITH truncate,
skipheader=1,
fieldsoptionallyenclosedby ‘”‘,
fieldsescapedby double-quote,
fieldsterminatedby’,’
SETclient_encodingto ‘latin1’,
work_memto ’12MB’,
standard_conforming_stringsto ‘on’
BEFORE LOADDO
$$ droptableifexists csv; $$,
$$ createtable csv (
abigint,
bbigint,
cchar(2),
dtext
);
$$;
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. Therefore, the database administrator should convert concerned data with special programs.
- 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.
- Intelligent Converters developed a special tool called DBF to PostgreSQL tool which helps to migrate database from FoxPro to PostgreSQL. The product does direct reading from the source files and writing to the target database, 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
