convert postgres dump insert to batch transaction?

Question

I am searching for a tool/script to convert a postgresql dump file of gigabytes size to a batch transaction commit file.

it should insert BEGIN and COMMIT of transactions in the dump file like so:

BEGIN; CREATE...; INSERT...; INSERT...; INSERT...; ... COMMIT; BEGIN; CREATE...; INSERT...; INSERT...; INSERT...; ....

Yes. Anything would be better than insert statements. The problem is that the data consists of gigabytes of insert statements and the delivering partner is not reachable to send another dump format.

Do you know a tool to convert/accelerate the import?

kgrittn · Accepted Answer · 2012-08-07 16:21:42Z

1

You should consider using the COPY command, the \copy meta-command of psql, or the pg_bulkload utility. Any of these should be significantly faster than the Ruby script you show, and have been well-tested for correct handling of line breaks and other odd characters within the data.

answered Aug 7, 2012 at 16:21

kgrittn

19.8k4 gold badges41 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Over a year ago

Yes anything would be better. The problem was that the data consists of gigabytes of insert statements and the delivering partner is not reachable to send another dump format. So i tried to find another solution.

kgrittn Over a year ago

Ah, it's unfortunate that you couldn't get the data in some more tabular format. Just be cautious about data that might fool your parsing. I think your bottleneck is going to be the parsing of the INSERT statements to pick out values, so besides grouping multiple statements into a single transaction (as you have done), I don't think there's much you can do to speed it up.

Alex · Accepted Answer · 2012-08-07 15:47:38Z

Because i couldn't find a tool i created my own script to do the job:

#!/usr/bin/ruby i=0 File.open(ARGV[1],"a"){|t| File.open(ARGV[0]){|f| a=[] while(l=f.gets(";")) if(a.length==80) i+=1 puts "i: #{i}" if(i%1000==0) t.write("BEGIN;\n#{a.join(" ")}\nCOMMIT;\n") a=[] else a<<l end end t.write("BEGIN;\n#{a.join(" ")}\nCOMMIT;\n") } }

Usage: ruby batchtransact.rb input.sql output.sql

Collectives™ on Stack Overflow

convert postgres dump insert to batch transaction?

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related