FoxPro is a simple Database Management System (DBMS) developed by Fox Software and later by Microsoft. Unlike other advanced database management systems, FoxPro stores each table in a single file that applies certain limitation on data size (max is 2GB). Also FoxPro is not optimized for hi-load database systems with many simultaneous connections to the data source. In view of this fact it is not surprised that many organizations consider migrating their databases to more efficient and powerful DBMS. The trend became even more pronounced after Microsoft cancelled support of Visual FoxPro.
In order to resolve the limitations listed above, someone may consider migration from FoxPro to PostgreSQL having in mind essential advantages of PostgreSQL:
The process of database migration from FoxPro to PostgreSQL is more easy than migration between another DBMS. The reason is that FoxPro database is just a data storage without stored procedures, triggers, views and other non-trivial database objects. So moving data is the only thing required in order to migrate FoxPro database to PostgreSQL server. However, even that task becomes complicated and tedious routine when converting thousands of FoxPro tables. In such cases special tools may be used to automate the process of data migration.
Available Solutions
(1) Migrating FoxPro data through CSV format. The most straight forward way to do FoxPro to PostgreSQL migration is to export DBF files (FoxPro tables) into comma-separated values format and then import it to PostgreSQL. DBF files can be converted into CSV format using free tool dbf2csv available at SourceForge.net. The second part of the migration process can be done via free pgloader tool available at http://pgloader.io. To load CSV data into PostgreSQL database with pgloader you have to define some details of the operation. Here 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
);
$$;
Besides this post-processing, additional steps may be required if the original FoxPro data includes some national (non-ANSI) symbols and is not stored in Unicode code page. In such cases the database migration specialist should do the necessary conversion of charset using special script or program.
(2) FoxPro to PostgreSQL database migration with PgDBF. PgDBF is a program to convert FoxPro/XBase databases (DBF files) into the format suitable for import into psql client. This approach can handle possible encoding issue mentioned in the previous paragraph, but it still requires manual intermediate steps or custom scripting.
(3) There are commercial software products that can do complete migration from FoxPro to PostgreSQL with a few clicks of mouse button. One of them is FoxPro to PostgreSQL converter by Intelligent Converters. The product provides all necessary capabilities for efficient conversion: