1PGDBF(1) General Commands Manual PGDBF(1)
2
6 pgdbf - convert XBase / FoxPro tables to PostgreSQL
7
10 pgdbf [-cCdDeEhqQtTuU] [-m memofile] filename [indexcolumn ...]
11
14 PgDBF is a program for converting XBase databases - particularly FoxPro
15 tables with memo files - into a format that PostgreSQL can directly
16 import. It's a compact C project with no dependencies other than stan‐
17 dard Unix libraries. While the project is relatively tiny and simple,
18 it's also heavily optimized via profiling - routine benchmark were many
19 times faster than with other Open Source programs. In fact, even on
20 slower systems, conversions are typically limited by hard drive speed.
21 Features
23 PgDBF was designed with a few core principles:
24 Simplicity. This code should be understandable by anyone who
26 wants to hack it.
27 Robustness. Every syscall that might possibly fail is checked
29 for success.
30 Speed. PgDBF was born to be the fastest conversion available
32 anywhere.
33 Completeness. It has full support for FoxPro memo files.
35 Portability. PgDBF runs on 32- and 64-bit systems, and both
37 little-endian (eg x86) and big-endian (eg PowerPC) architec‐
38 tures.
39 Performance
42 PgDBF's speed is generally limited by how fast it can read your hard
43 drives. A striped RAID of quick disks can keep PgDBF pretty well fed
44 on a single-processor system. One problem area is with memo files,
45 which may become very internally fragmented as memo fields are created,
46 deleted, and updated. For best results, consider placing the DBF and
47 FPT files on a RAM drive so that there's no seek penalty as there is
48 with spinning hard drives, or using a filesystem such as ZFS that
49 caches aggressively.
50 One particularly fragmented 160MB table with memo fields used to take
52 over three minutes on a FreeBSD UFS2 filesystem. Moving the files to a
53 RAM disk dropped the conversion time to around 1.2 seconds.
54 A certain test table used during development comprises a 280MB DBF file
56 and a 660MB memo file. PgDBF converts this to a 1.3 million row Post‐
57 greSQL table in about 11 seconds, or at a rate of almost 120,000 rows
58 per second.
59
62 -c Generate a CREATE TABLE statement to make a table with similar
63 datatypes and column names as the DBF file. Default.
64 -C Suppress the CREATE TABLE statement.
66 -d Generate a DROP TABLE statement before the CREATE TABLE state‐
68 ment. This is useful for replacing the contents of a table that
69 already exists in PostgreSQL. Default.
70 -D Suppress the DROP TABLE statement.
72 -e Change the DROP TABLE statement to DROP TABLE IF EXISTS so that
74 newer versions of PostgreSQL (8.2+) will only attempt to drop
75 the table if it's already defined. PostgreSQL will return an
76 error when attempting to drop a table that does not exist unless
77 IF EXISTS is used. Default.
78 -E Do not use the IF EXISTS modifier to DROP TABLE for compatibil‐
80 ity with versions of PostgreSQL older than 8.2.
81 -h Print a help message, then exit.
83 -m memofile
85 The name of the associated memo file (if necessary).
86 -n Create NUMERIC fields with type NUMERIC. Default.
88 -N Create NUMERIC fields with type TEXT. Use this if rows contain
90 invalid number data in NUMERIC fields (which are essentially
91 CHARACTER fields behind the scenes).
92 -p Show a progress bar during the conversion process.
94 -P Do not show a progress bar. Default.
96 -q Enclose the name of the table in quotation marks in statements
98 like "CREATE TABLE", "DROP TABLE", and so on. This is useful in
99 cases where the table name is a PostgreSQL reserved word, and
100 almost certainly harmless in all other cases.
101 -Q Do not enclose the name of the table in quotation marks.
103 Default.
104 -s encoding
106 Set the encoding used in the input file. When given, output will
107 be converted from that encoding to UTF-8. Supported encodings
108 depend on your version of the iconv library. Available only if
109 your copy of PgDBF was compiled with iconv support.
110 -t Wrap the entire script in a transaction. Default.
112 -T Remove the wrapper transaction. This is generally not a good
114 idea as it can cause the table to appear completely empty to
115 other clients during the data copying phase. If the entire
116 process occurs inside a transaction, the update is atomic and
117 other clients will have full access to all data in the table at
118 all times.
119 -u Issue a TRUNCATE TABLE statement to clear the contents of a ta‐
121 ble before copying data into it.
122 -U Suppress the TRUNCATE TABLE statement. Default.
124
127 The -c and -d arguments are incompatible with -u as it's pointless to
128 truncate a newly-created table. Specifying -c or -d will disable the
129 TRUNCATE TABLE statement as though -U was given. Similarly, using the
130 -u argument will disable the CREATE TABLE and DROP TABLE statements as
131 if -C and -D were given.
132
135 When multiple incompatible interpretations of a type are available,
136 such as the B type which can mean binary object in dBASE V or double-
137 precision float in FoxPro, PgDBF currently uses the FoxPro interpreta‐
138 tion.
139 Most XBase datatypes are supported, but some are not (yet). As of this
141 writing, PgDBF can handle boolean, currency, date, double-precision
142 float, float, general (although only outputs empty strings; it's
143 unclear how to resolve OLE objects at this time), integer, memo,
144 numeric, timestamp, and varchar fields. If you need other datatypes,
145 send a small sample database for testing.
146
149 Kirk Strauser <kirk@strauser.com>
150Version 0.6.2 September 30 2012 PGDBF(1)