N
N
Nikita2016-01-20 16:37:02
PostgreSQL
Nikita, 2016-01-20 16:37:02

How to solve a problem when copying a table in PostgreSQL?

There is a DB, the backup copy is removed every day. But lately there have been errors when deploying a copy. Data duplication errors began to appear in the dump, although no duplicates were found on the original. After digging deeper, I found out that when pg_dump is launched, the COPY command is called. As a result, I got an interesting picture

# copy my_table (id, filter_state_id, analytic_pack, view_type, detail_type, top, 
       params_json, code, name) to '/tmp/copy.txt';
$ cat /tmp/copy.txt | grep 43727
43727   4425    AnyData     1       1       7                       AnyName
43727   4425    AnyData     1       1       7                       AnyName

# select * from my_table where id=43727;
  id   | filter_state_id | analytic_pack | view_type | detail_type | top | params_json | code |     name     
-------+-----------------+---------------+-----------+-------------+-----+-------------+------+--------------
 43727 |            4425 | AnyData   |         1 |           1 |   7 |             |      | AnyName
(1 row)

PostgreSQL version: 9.4
Actually the question is: how can this be, where can the COPY command take a duplicate record from.
I checked it on another table, where there is data versioning, it turned out that under one Id there are identical records but different versions.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
V
Vladimir, 2016-01-21
@rostel

COPY has an OIDS parameter,
try to dump/restore it with your hands

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question