Re: [Roundup-users] Docker Container 2.5.0 / UniqueViolation: duplicate key value violates unique constraint "_issue_pkey"

[John R. <ro...@ie...>]

How did you do it? What procedure worked?

Also one thing I thought of. If you are using the same tracker home,
you can export just the database with no files
using roundup-admin. Then import just the database data. That should
speed up the process. My guess is the 6 hours
load time was spent shuffling those 85000 messages.

The export/import method is really meant for changing between back end
databases (e.g. anydbm or sqlite to postgres)
and the export format is not guaranteed to work between roundup
versions. However I don't know of any breaking
issues between 2.2.0 and 2.5.0 releases.

Glad to hear you have it working.

-- rouilj

On Mon, Nov 3, 2025 at 2:03 AM SCHLEMMER Norbert
<Nor...@pd...> wrote:
>
> Hi John
>
> I successfully upgraded to PostgreSQL 18.
> We have approximately 12,000 issues with 85,000 messages.
>
> Br
> Norbert
>
> -----Ursprüngliche Nachricht-----
> Von: John Rouillard <ro...@ie...>
> Gesendet: Samstag, 01. November 2025 02:51
> An: SCHLEMMER Norbert <Nor...@pd...>
> Cc: rou...@li...
> Betreff: Re: [Roundup-users] Docker Container 2.5.0 / UniqueViolation: duplicate key value violates unique constraint "_issue_pkey"
>
> *EXTERNAL source*
>
>
> Hi Norbert:
>
> Sorry, I'm kind of tied up with other things.
>
> On Wed, Oct 29, 2025 at 5:00 AM SCHLEMMER Norbert <Nor...@pd...> wrote:
> > What is the recommended upgrade path from Docker "2.3.0 / PostgreSQL 17" to "2.5.0 / PostgreSQL 18"
> > and for migrating to a different host with Debian 13 / Trixie?
>
> I suggest:
>
>    dump/restore the database from postgresql 17 to 18 using pg_dump etc.
>
>    then copy over the tracker home directory and follow the steps in upgrading.txt
>    (https://www.roundup-tracker.org/docs/upgrading.html) from 2.3.0 to 2.4.0 and then 2.5.0.
>    The required steps need to be done. If you omit the rest, it should still allow Roundup to work, but there may be security or other issues.
>
> > Can the Docker image simply be changed from roundup:2.3.1a0 to roundup:2.5.0-1?
> > I did this and didn't find any problems during testing. Is this procedure permissible and recommended?
> > If so, I can then upgrade to PostgreSQL 18 in the next step using SQL dump/restore.
>
> That should work provided you do the changes in upgrading.txt.
>
> Ralf said on Mittwoch, 29. Oktober 2025 09:29
> > On Wed, Oct 29, 2025 at 07:31:32AM +0000, SCHLEMMER Norbert wrote:
> > > An export was created from the productive Roundup instance 2.3.0.
> > > After importing in version 2.5.0, new messages can be added to an
> > > existing issue but creating a new issue result in this violation;
> > > see logs attached (the import process takes approximately 6 hours...).
>
> How many issues do you have?
>
> > Note that using the roundup-export / roundup-import is not recommended
> > for version upgrade, that should happen in-place. This might be the reason of the error-message you're seeing.
>
> > > UniqueViolation: duplicate key value violates unique constraint
> > > "_issue_pkey" DETAIL: Key (id)=(3)
> >  >       already exists.    Python 3.13.5
> > > /usr/local/bin/python3.13
>
> > Hmm, this might be because a retired node (e.g. an issue) is first
> > created and then retired, it could produce a duplicate primary key if a non-retired node *with the same key* already exists.
> > But under normal circumstances duplicate primary keys should not occur (even including retired nodes).
>
> This is happening post import, correct Norbert?
>
> We fixed an import issue with duplicate keys back in the 2.1.0 release. it was caused when a user was created with the name "A". Then it was retired. Later a new user with the name "A" was created. When the export/import was done if the retired entry was imported after the non-retired entry the name "A"
> which is a pkey conflicted.
>
> However for issues, the title is not a primary key so I am not sure how you would get this conflict.
>
> It almost looks like the sequence used to number (id) the issues isn't set properly.
> Can you connect to the database and run:
>
>   select * from _issue_ids;
>
> you should see something like:
>
>  last_value | log_cnt | is_called
> ------------+---------+-----------
>             1 |         0 |          f
>
> where last_value is one more than the largest id number in the _issues table.
> Something like `select max(id) from _issue;` might do the trick to find the current max id number.
>
> If the last_value is wrong, check the rest of the sequences (use \ds to list sequences and select like above to see the value).
>
> I haven't seen an issue like that in my (limited) postgres testing of import/export.
>
> -- rouilj