Multiple PuppetDB instances against a single PostgreSQL database

[irregulator]

Describe the Bug

Hello and thanks for developing PuppetDB!

Based on this sentence:

Run multiple instances of PuppetDB on multiple servers, and use a reverse proxy or load balancer to distribute traffic between them.

we are testing a setup with two (or more) PuppetDB instances using the same PostgreSQL database.

So let's assume the following:

• pgsql001 hosts the PostgreSQL database named puppetdb
• puppet001 runs both puppetserver and puppetdb and points to pgsql001 database
• puppet002 runs both puppetserver and puppetdb and points to pgsql001 database

Both PuppetDB instances are employing the very same credentials against a single database. puppet001 and puppet002 are used by a different set of puppet-agents, for example hosts in two different datacenters.

puppetdb logs feature no error but postgresql logs do:

2024-02-06 14:01:33.459 UTC [812825] puppetdb@puppetdb ERROR:  could not serialize access due to concurrent update
2024-02-06 14:01:33.459 UTC [812825] puppetdb@puppetdb STATEMENT:  insert into certnames (certname) values ($1)  on conflict (certname) do update set deactivated=null, expired=null  where (certnames.deactivated < $2 or certnames.expired < $3)

This seems like the two PuppetDB instances are competing to update the same row.

So our questions are:

• is it supported to run multiple PuppetDB instances against the same PostgreSQL database?
• if yes, how can we avoid these database errors?

Expected Behavior

We would expect to be run two different PuppetDB instances without database errors.

Steps to Reproduce

Steps to reproduce the behavior:

1. Setup two PuppetDB instances
2. Point them to the same PostgreSQL database
3. Let puppet-agents run

Environment

• PuppetDB version 8.3.0-1jammy
• Puppetserver version 8.4.0-1jammy
• PostgreSQL version 14.10-1.pgdg22.04+1
• Platform Ubuntu 22.04.3

Additional Context

I will gladly provide more information or context on this topic if requested.

[irregulator]

@austb Hi! Maybe you have insight regarding this issue? Or perhaps you can ping someone who has. Thanks in advance.

[austb]

@irregulator Running multiple PuppetDB servers connecting to a single Postgres is a supported use case, and it is done in many Puppet Enterprise installations.

In current versions of PuppetDB, errors like that are expected, and will even be generated when you have a single PuppetDB because it can submit multiple commands at the same time. The reason you see those errors in Postgres and do not see any errors in PuppetDB is because we catch that database error and automatically retry. You will only see those errors in PuppetDB if the retry attempts fail as well.

The specific SQL causing the error you are seeing was also causing a performance impact when we did internal scale testing simulation 100,000 agents, so a recent commit should reduce the prevalence of that error in the logs. But, it would still be considered normal to see ERROR: could not serialize access due to concurrent update in the Postgres logs due to the concurrent submission of data. If you do not see the error in the PuppetDB logs, it means the retry was successful and your data was correctly stored in the database.

[irregulator]

@austb thanks for the elaborate answer.

Just to be clear, we are running the non-enterprise version of PuppetDB, provided by https://apt.puppet.com/ . Can we safely assume that the "multiple PuppetDB against a single Postgres" holds true in that case as well?

Thanks and let's close the issue afterwards.

[austb]

Yes, non-enterprise PuppetDB has the same multi-PuppetDB -> single Postgres capabilities as the enterprise version.