FoxPro can be define as a easy Database Management System (DBMS) created by Fox Software and then by Microsoft. Contrary to other advanced database management systems, FoxPro saves each table within a single file that applies certain restriction on data size (max is 2GB). Additionally FoxPro isn’t enhanced for hi-load database systems with lots of synchronized connections to the data source. In view of this concept, it is not a shock that a lots of companies give consideration to moving their databases to more appropriate and robust DBMS. The popularity grew to become a lot more pronounced after Microsoft terminated support of Visual FoxPro.

To deal with the restrictions mentioned above, anyone may give consideration to migration from FoxPro to PostgreSQL having in mind the vital benefits of PostgreSQL:

• cross platform: PostgreSQL can be obtained on every variation of Unix platform, and also on Windows
• sophisticated object-relational DBMS having virtually all conceivable database features like subselects, transactions, user-defined types and much more
• PostgreSQL is probably the most scalable and dependable database systems
• it is free and perhaps much better since it’s open-source
• similar syntax for BOOLEAN type: ‘t’ is TRUE and ‘f’ is FALSE in both FoxPro and PostgreSQL (unlike other popular DBMS)

The procedure of database migration from FoxPro to PostgreSQL is much more easy than migration between another DBMS. This is because FoxPro database is merely a data storage without stored procedures, triggers, views as well as other non-trivial database objects. So transferring data is the one thing needed in to migrate FoxPro database to PostgreSQL server. Even so, that process becomes complex and a tiresome routine when converting a large number of FoxPro tables. In such instances special tools can be adopted to automate the procedure of data migration.

Available Solutions
(1) Moving FoxPro data through CSV format. The best easy method of doing FoxPro to PostgreSQL migration is always to export DBF files (FoxPro tables) into Comma Separate Values format thereafter import it to PostgreSQL. DBF files can be transformed into CSV format using free tool dbf2csv available at SourceForge.net. The second aspect of the migration approach can be achieved via free pgloader tool located at http://pgloader.io. To load CSV data into PostgreSQL database with pgloader you will need to define some details about the operation. Below is an example:

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
);
$$;

Apart from this post-processing, additional steps may be necessary in case the original FoxPro data consists of some national (non-ANSI) symbols and isn’t saved in Unicode code page. When this happens the database migration professional must do the required conversion of charset using special script or program.

(2) FoxPro to PostgreSQL database migration with PgDBF. PgDBF is a program to transform FoxPro/XBase databases (DBF files) into the format appropriate for import into psql client. This process can deal with possible encoding issue described in the earlier paragraph, however it still calls for manual intermediate steps or custom scripting.

(3) You can get commercial software solutions that are capable of completing migration from FoxPro to PostgreSQL with just a few clicks of mouse button. Among them is the DBF to PostgreSQL converter via Intelligent Converters. This program offers all necessary features for efficient conversion:

• All formats of FoxPro data and MEMO files are supported
• All versions of PostgreSQL servers are supported
• High performance due to direct connection to source and destination databases (the software doesn’t use ODBC or any other middleware software)
• Support for large .dbf files (4GB+)
• Option to merge DBF data into an existing PostgreSQL database
• All FoxPro code pages are supported
• Stores conversion settings into profile
• Command line support
• Easy to use wizard-style interface