CODEWITHSUNDEEP
postgresql
smilence
smilence

Reputation: 379

What’s the difference between COPY and pg_dump —data-only

I’m planning to migrate some tables out of existing database so the plan is to apply the schema on new database and then COPY data for each table.

What’s the difference of that versus pg_dump the data and then pg_restore?

Would copy needs restablishing indexes etc?

Upvotes: 4

Views: 7411

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247290

pg_dump --data-only will produce a complete SQL script that can be run with psql. That script contains both the COPY statement and the data:

COPY laurenz.data_2020 (id, d, x) FROM stdin;
1499906 2020-11-07 13:26:00 x
1499907 2020-11-07 13:27:00 x
1499908 2020-11-07 13:28:00 x
\.

So it is all in one, and there is no danger that you restore a file to the wrong table, for example.

Other that convenience, there is no fundamental difference to running COPY directly.

Upvotes: 4

Adrian Klaver
Adrian Klaver

Reputation: 19684

If you use pg_dump --data-only it will output the data as COPY statements, unless you override with --inserts or --column-inserts. So there is no difference in that case. In either case if the tables in the new database where not created with indexes they would need to be added. You could solve that with either -s -t <some_table> to get just the table schema or -t <some_table> to get the table schema and data.

Upvotes: 2

Related Questions