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

[SCHLEMMER N. <Nor...@pd...>]

Hello

Well, I performed the following steps:

.) On the source system running Debian 12, I performed a database export from PostgreSQL and compressed the file.
.) On the source system, the tracker directory was also zipped.
.) Both zip files and the docker-compose.yml file were copied to the destination system running Debian 13 / Trixie and unzipped there.
.) The Roundup image has now been updated to version 2.5.0.
.) A PostgreSQL 17 container was started, and the export file was imported. Subsequently, another export was created, which was then imported into a PostgreSQL 18 container.
.) The PostgreSQL 17 container has now been deleted, and the PostgreSQL 18 container is being used as a service in the compose file.
.) Both services in the Docker Compose file, the PostgreSQL and the Roundup issue tracker, are now running, all previous data is present and appears to be intact.

I then compared the tracker directory with one from version 2.5.0 and noticed a few changes, apart from the message files, which I still want to look at in detail.

Br
Norbert


-----Ursprüngliche Nachricht-----
Von: John Rouillard <ro...@ie...> 
Gesendet: Dienstag, 04. November 2025 03:31
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*


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