1PGDBF(1)                    General Commands Manual                   PGDBF(1)
2
3
4

NAME

6       pgdbf - convert XBase / FoxPro tables to PostgreSQL
7
8

SYNOPSIS

10       pgdbf [-cCdDeEhqQtTuU] [-m memofile] filename [indexcolumn ...]
11
12

DESCRIPTION

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
22   Features
23       PgDBF was designed with a few core principles:
24
25              Simplicity.   This  code  should be understandable by anyone who
26              wants to hack it.
27
28              Robustness.  Every syscall that might possibly fail  is  checked
29              for success.
30
31              Speed.   PgDBF  was  born to be the fastest conversion available
32              anywhere.
33
34              Completeness.  It has full support for FoxPro memo files.
35
36              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
40
41   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
51       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
55       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
60

OPTIONS

62       -c     Generate a CREATE TABLE statement to make a table  with  similar
63              datatypes and column names as the DBF file. Default.
64
65       -C     Suppress the CREATE TABLE statement.
66
67       -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
71       -D     Suppress the DROP TABLE statement.
72
73       -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
79       -E     Do  not use the IF EXISTS modifier to DROP TABLE for compatibil‐
80              ity with versions of PostgreSQL older than 8.2.
81
82       -h     Print a help message, then exit.
83
84       -m memofile
85              The name of the associated memo file (if necessary).
86
87       -n     Create NUMERIC fields with type NUMERIC. Default.
88
89       -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
93       -p     Show a progress bar during the conversion process.
94
95       -P     Do not show a progress bar. Default.
96
97       -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
102       -Q     Do  not  enclose  the  name  of  the  table  in quotation marks.
103              Default.
104
105       -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
111       -t     Wrap the entire script in a transaction. Default.
112
113       -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
120       -u     Issue a TRUNCATE TABLE statement to clear the contents of a  ta‐
121              ble before copying data into it.
122
123       -U     Suppress the TRUNCATE TABLE statement. Default.
124
125

OPTION NOTES

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
133

BUGS

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
140       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
147

AUTHOR

149       Kirk Strauser <kirk@strauser.com>
150
151
152
153Version 0.6.2                  September 30 2012                      PGDBF(1)
Impressum