From: Tom Lane <tgl@sss.pgh.pa.us>
To: flik <flikreview@gmail.com>
cc: pgsql-novice@lists.postgresql.org
Subject: Re: Fixing Integrity check errors
In-reply-to: <9bde7204-1b4c-8702-4593-5afd0607c5d2@gmail.com>
References: <9bde7204-1b4c-8702-4593-5afd0607c5d2@gmail.com>
Comments: In-reply-to flik <flikreview@gmail.com>
	message dated "Mon, 14 Mar 2022 21:14:55 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <225369.1647293582.1@sss.pgh.pa.us>
Date: Mon, 14 Mar 2022 17:33:02 -0400
Message-ID: <225370.1647293582@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/225370.1647293582%40sss.pgh.pa.us>
Precedence: bulk

flik <flikreview@gmail.com> writes:
> What would be the best way to solve integrity errors (unique 
> constraints) caused by a badly done dump/restore? My best guess as 
> ignorant ass, it's simply to dump the current data (only), start a new 
> project, apply the migrations and loading back data.
> Given that the schema is exactly the same, would this work? If so, how 
> can I do it with postgres?
> I already tried to pg_dump --data-only but I keep getting weird errors.

--data-only is really the source of your problem, probably.
If you do a normal (full) pg_dump and restore into an empty
database, then pg_dump should see to loading your data and
installing constraints in the correct order.  With --data-only
it can't make as many guarantees.

Usually, however, that only comes up with foreign key constraints.
If you're seeing unique-constraint failures then there might be
something else going on, such as
* you forgot to make sure the tables are empty before adding data
* or there's something corrupt about the indexes

			regards, tom lane