7

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?

Marc Rasmussen
  • 19,771
  • 79
  • 203
  • 364
  • 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. – Nick Barnes Feb 13 '19 at 10:12

1 Answers1

11

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.

  • The command in the question is already using `--column-inserts`, sounds to me like there's something else going on here... – Nick Barnes Feb 13 '19 at 10:15
  • 1
    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`. –  Feb 13 '19 at 10:18
  • 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. –  Feb 13 '19 at 10:54
  • some version of greenplum seems can't dump data with `pg_dump --inserts --column-inserts` – yurenchen Apr 06 '23 at 17:47