Backup and restore of PostgreSQL Bonita DB (primary key counter)

Q&A
1

Hi,

I tried backing up all databases used by Bonita BPM (both bonita database and business database) in PostgreSQL using pg_dumpall.

Then I restore the databases with psql, all data are restored correctly. But when I add any data that concerns bonitadb database, e.g. user or group or organization, it failed and I got the following error message from log file:

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "user__pkey"
 Detail: Key (tenantid, id)=(1, 1) already exists.
       at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
       at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
       at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
       at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
       at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
       at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
       at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
       at sun.reflect.GeneratedMethodAccessor67.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
       at java.lang.reflect.Method.invoke(Method.java:498)
       at org.postgresql.ds.PGPooledConnection$StatementHandler.invoke(PGPooledConnection.java:428)
       at com.sun.proxy.$Proxy3.executeUpdate(Unknown Source)
       at sun.reflect.GeneratedMethodAccessor67.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
       at java.lang.reflect.Method.invoke(Method.java:498)
       at bitronix.tm.resource.jdbc.BaseProxyHandlerClass.invoke(BaseProxyHandlerClass.java:64)
       at com.sun.proxy.$Proxy44.executeUpdate(Unknown Source)
       at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
       ... 86 more

After many tries to add a new user, I finally succeeded. Each time the error appears, the id field is incremented by 1 for that (tenantid, id) pair.

So it seems that pg_dumpall failed to backup the primary key counter for each bonitadb table, in this case the user_ table. The psql restore just resets all these counter values. I tried to investigate the user_ table and bonitadb but couldn't find any postgresql sequence that stores this key counter.

Is there anything I'm missing from postgresql database backup or are the bonitadb sequence values stored elsewhere than in the postgresql server?

BR,

Minh

2

I figured out that I need to stop the bonita server before taking snapshot of the database or restoring data to the database, that somehow avoids intefering with the sequencing mechanism from bonita engine.

https://documentation.bonitasoft.com/bonita/7.5/back-up-bonita-bpm-platform