How to produce a postgresql dump of multi-valued INSERTs instead of COPY and to batch the inserts into explicit transactions?

Question

I cannot find anywhere any info on how exactly should I bulk my inserts into batches of 100-inserts-per-transaction while producing a database dump via pg_dump utility. How exactly do I need to perform it? I failed to find any parameters in

> man pg_dump

to perform this. Even the most elaborate answer on StackOverflow on the topic by @CraigRinger does not describe the way the dump of extended imports may be produced. Could anyone please share their recipe here?

Polysepalous

You don't. Why don't you want to use COPY? – Cog

@RichardHuxton because all the COPYing happens in one huge transaction, and once there is one little failure, the whole table turns empty. On the other hand, every insert being kept transactional results in massive waste of time while loading the dump. I wonder why would Craig mention this option if it wouldn't be possible, maybe in other databases. But I'm failing to find out how it is to be done in Postgres – Swatch

This still isn't making any sense to me. How can the data be incorrect? You are dumping it. If you want to partially restore it then you want the control on the restoring end of the process, surely? – Cog

@RichardHuxton I cannot say for sure why some of the inserts of the dump are failing but they certainly do. I suspect this is due to constraints and/or composite keys, which include foreign keys along with a primary one. But again I didn’t investigate the exact reason for this. Nevertheless failing imports in a dump is a well-known problem, as far as I can see it in the Internet – Swatch

@RichardHuxton I suspect that when you have constraints and composite keys in your DB then the order of insertions may play crucial role – Swatch

No. A failing restore of a backed-up database is a very rare problem and almost always due to operator error. If you are trying to restore a raw database into a different database which already has data in it then of course you will have problems - you are using the wrong tools for the job. – Cog

Answer 1

--rows-per-insert was added to pg_dump for v12. Before that, there is no clean way to do this.

With plain text dumps, the transaction control will be decided based on how you replay the dump, not on how you take it. If you just stream the dump into psql with no options, each insert will naturally be its own transaction.

Ofeliaofella