0

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.

  • You need a tool to convert that DBF to some kind of text file (aka "CSV"), then you can load it using copy or pg_loader – a_horse_with_no_name Nov 4 '15 at 18:42
  • Yeahh, but pg_loader is quite confusing to install and use it on windows. – Ewerton Nov 4 '15 at 18:57
  • @Ewerton and how about the other suggestion (COPY or \copy)? – dezso Nov 4 '15 at 23:47
  • i cant find an example in the internet on how to import DBF using COPY – Ewerton Nov 5 '15 at 0:35
  • I don't know the details of DBF format. Can you add some example data to your question for a couple of rows? Plus the definition of the desired target table. Best for a simple table with only a few columns. And mention what the white space is (if any): space or tab character ... – Erwin Brandstetter Nov 5 '15 at 12:04
1

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:

  1. 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).

  2. We read the DBF metadata again and created a (java) prepared statement that would insert a single line of data.

  3. 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.

  4. 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.

  • This was my approach to. I coded a C# program that reads the PDF and generate the .sql inserts files with 5 million insert in each file, then i called these scripts by pgsql command line tool, one-by-one, it was a painful operation, so i am asking it to check if there is another more "automatic" way. – Ewerton Nov 5 '15 at 20:38
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.