I have some huge DBF files (about 35 GB) to import to Postgres. Whats the best and fast way to do it?
OBS: I have 300 GB of free disk space and 8 GB RAM.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
We faced a similar problem but our solution involved some coding. I don't know if that's an option for you.
We followed the usual phases:
We read the DBF metadata and created an analogous SQL 'create table' statement. The mapping is fairly straightforward except that we checked the number of digits for numeric types and coerced them to ints or longs (int8) as appropriate. Our data had a lot of foreign keys and it was better to treat them as int8 than numeric(15,0).
We read the DBF metadata again and created a (java) prepared statement that would insert a single line of data.
We "called" that prepared statement for each row in the DBF file. This makes sure we don't have problems with inadvertent SQL injection, e.g., names like O'Reilly. In reality we used batches of 400 rows (ps.addBatch() and ps.executeBatch() in java) and used an explicit begin/commit block of 20 insert statements. Both dramatically improve performance.
Finally we added any primary keys.
We could have gone through an intermediate file and used COPY but it was easier (and safer) for us to do it programmatically.
We also wrote our own DBF reader. It only took a day and we were able to work around some numeric overflow bugs that we found in third-party libraries. You can find enough information on Google that this isn't too hard.
One thing to be careful about is that "dbf" is vague. All dBase files have the same overall structure but there are tweaks from different publishers and versions. They're documented but you'll want to determine what version your file uses and then code to it.
If the database files that you have come from database servers that you control or otherwise have access to, then you will find it a lot easier to use some form of ETL tool to pull the data from those servers and pump it into your postgres instance(s), instead of trying to decode the data files directly.
Even a manual export from the databases in their natural home to some convenient format and import from there would be preferable to working with the binary data files directly.