[Pkg-puppet-devel] Bug#932135: Bug#932135: puppetdb can't create/upgrade its DB schema past version 65 with postgres-11 11.4-1

[Apollon Oikonomopoulos]

Control: tags -1 confirmed upstream fixed-upstream
Control: severity -1 important
Control: clone -1 -2
Control: reassign -2 postgresql-11
Control: tags -2 upstream
Control: found -2 11.4-1
Control: affects -2 puppetdb
Control: severity -2 serious
Control: forwarded -2 https://www.postgresql.org/message-id/15865-17940eacc8f8b081%40postgresql.org
Control: retitle -2 ALTER TABLE statements causing "relation already exists" errors when some indexes exist

Hi,

On 13:46 Mon 15 Jul     , Gabriel Filion wrote:
> Hi there!
> 
> I've hit a bug with a new installation of puppetdb on buster (e.g. I've
> re-created my puppetmaster vagrant box) where puppetdb would fail to start,
> erroring out on an SQL upgrade of the database schema during the first service
> start.
> 
> I'll include the error log lower down since it's pretty long.
> 
> I've found a bug report on pupperware (puppet packaged up in docker containers)
> that describes exactly the same problem, identifies a faulty postgresql 9.6.x
> version and seems to point to an upstream bug report that contains a fix.
> 
> https://github.com/puppetlabs/pupperware/issues/82
> 
> Since in buster we're using postgresql-11, we've had to identify which version
> had introduced the problem. I'm not sure about the exact minor version of
> postgres, but for certain when downgrading the debian package to postgres-11
> version 11.3-1, then puppetdb is able to start and complete its schema upgrade.
> So the bug must have been introduced somewhere between 11.3 and 11.4
> 
> The upstream bug report says that there might be a fix for puppetdb available:
> 
> https://tickets.puppetlabs.com/browse/PDB-4422
> 
> It might be interesting to test applying the fix from the most appropriate
> branch (I'm not sure whether 6.0 or 6.3 makes more sense) and then test a new
> install with postgresql-11 version 11.4-1 to see if it goes through the schema
> upgrade successfully.

Thanks for the report and detailed information! According to 
https://ci.debian.net/packages/p/puppetdb/testing/amd64/, PuppetDB broke 
with postgresql-common/202, however it's really postgresql-11 11.4 that 
broke things while fixing CVE-2019-10164[1].

I'm inclined to say "this is a PostgreSQL bug", but there's no clear 
resolution from the Postgres side yet. PuppetDB upstream has already 
fixed this and the patch is trivial, so I'll prepare an upload to 
unstable to make it work again. However, I won't be filing for a stable 
update yet; instead, let's clone this bug as a PostgreSQL one and see 
what the Postgres maintainers say.

Cheers,
Apollon

[1] https://www.postgresql.org/message-id/15865-17940eacc8f8b081%40postgresql.org