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

**URL:** <https://community.ofelia.com/t/backup-and-restore-of-postgresql-bonita-db-primary-key-counter/10289>\
**Category:** Q&A\
**Created:** [19 July 2020 23:05 UTC](https://community.ofelia.com/t/backup-and-restore-of-postgresql-bonita-db-primary-key-counter/10289 "2020-07-19T23:05:42Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![hmk143](https://avatars.discourse-cdn.com/v4/letter/h/ac8455/32.png) [@hmk143](https://community.ofelia.com/u/hmk143)\
**Post date:** [19 July 2020 23:05 UTC](https://community.ofelia.com/t/backup-and-restore-of-postgresql-bonita-db-primary-key-counter/10289/1 "2020-07-19T23:05:42Z")

</div>

Hi,

I tried backing up all databases used by Bonita BPM (both bonita&nbsp;database&nbsp;and business database) in PostgreSQL using [pg\_dumpall](https://www.postgresql.org/docs/9.2/app-pg-dumpall.html).

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&nbsp;sequence values stored elsewhere than in the postgresql&nbsp;server?

BR,

Minh

---

<div class="post-metadata">

**Author:** ![hmk143](https://avatars.discourse-cdn.com/v4/letter/h/ac8455/32.png) [@hmk143](https://community.ofelia.com/u/hmk143)\
**Post date:** [20 July 2020 21:58 UTC](https://community.ofelia.com/t/backup-and-restore-of-postgresql-bonita-db-primary-key-counter/10289/2 "2020-07-20T21:58:59Z")

</div>

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](https://documentation.bonitasoft.com/bonita/7.5/back-up-bonita-bpm-platform)