postgres dump with inserts but without copy actions

Question

I am trying to dump my database.

For reasons I can't go into detail here, I am unable to use the "Copy" function. This means that the insert statements have to be "pure" insert. However, I am not sure how to reflect this in my dump.

Currently, this is my dump command:

 pg_dump -U myUser --column-inserts --data-only  -h localhost my_db> backup

Can anyone tell me how i might get the insert statements without the Copy function?

Fungoid

That's what --column-inserts is for, and it seems to work just fine for me... Double-check that you ran the right command, and are looking at the right backup. – Penicillium

Answer 1

The documentation of pg_dumps tells us this:

--inserts

Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents. Note that the restore might fail altogether if you have rearranged column order. The --column-inserts option is safe against column order changes, though even slower.

Vostok

The command in the question is already using --column-inserts, sounds to me like there's something else going on here... – Penicillium

No, --column-inserts is not the same as --inserts. It only instructs pg_dump to use explicit column names if an INSERT is part of the dump. It does not instruct the usage of INSERT over COPY. – Vostok

Strange. The OP already accepted this answer so it looks like it helped. But you are right that both options help. I've tested this, too. – Vostok

some version of greenplum seems can't dump data with pg_dump --inserts --column-inserts – Phelgen