# Hibernate Envers - default\_catalog not working

**URL:** <https://discourse.hibernate.org/t/hibernate-envers-default-catalog-not-working/2647>\
**Category:** Hibernate ORM\
**Created:** [April 24, 2019, 5:25pm UTC](https://discourse.hibernate.org/t/hibernate-envers-default-catalog-not-working/2647 "2019-04-24T17:25:47Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![adorogensky](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/adorogensky/32/566_2.png) [@adorogensky](https://discourse.hibernate.org/u/adorogensky)\
**Post date:** [April 24, 2019, 5:25pm UTC](https://discourse.hibernate.org/t/hibernate-envers-default-catalog-not-working/2647/1 "2019-04-24T17:25:47Z")

</div>

```auto
spring.jpa.properties.org.hibernate.envers.store_data_at_delete = true
spring.jpa.properties.org.hibernate.envers.default_schema = audit
spring.jpa.properties.org.hibernate.envers.default_catalog = audit
spring.jpa.properties.org.hibernate.envers.audit_table_suffix = _audit

```

is not working. Audit changes get stored in ‘audit’ schema of the application database. I want them to go to a standalone database ‘audit’. Anyone has any ideas if I miss any configuration? I’m using Postgres 9.6

---

<div class="post-metadata">

**Author:** ![gavenkoa](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/gavenkoa/32/537_2.png) [@gavenkoa](https://discourse.hibernate.org/u/gavenkoa)\
**Post date:** [April 28, 2019, 9:28pm UTC](https://discourse.hibernate.org/t/hibernate-envers-default-catalog-not-working/2647/2 "2019-04-28T21:28:21Z")

</div>

> standalone database ‘audit’.

That parameter probably is defined by JDBC connection property.

Depending on DB/JDBC `schema` / `catalog` isn’t same as DB instance.

---

<div class="post-metadata">

**Author:** ![lromano](https://avatars.discourse-cdn.com/v4/letter/l/f17d59/32.png) [@lromano](https://discourse.hibernate.org/u/lromano)\
**Post date:** [May 2, 2019, 8:24am UTC](https://discourse.hibernate.org/t/hibernate-envers-default-catalog-not-working/2647/3 "2019-05-02T08:24:54Z")

</div>

I use the schema value in the Annotation like this  
`
@Entity(name = “Audit”)
@Table(name = “audit”, schema = “audit”)
`  
This is a flexible solution, you can decide at table level which schema has to be used.  
Hope this helps

---

<div class="post-metadata">

**Author:** ![Naros](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/naros/32/19_2.png) [@Naros](https://discourse.hibernate.org/u/Naros)\
**Post date:** [May 2, 2019, 6:42pm UTC](https://discourse.hibernate.org/t/hibernate-envers-default-catalog-not-working/2647/4 "2019-05-02T18:42:07Z")

</div>

> [@adorogensky](#):
>
> Audit changes get stored in ‘audit’ schema of the application database. I want them to go to a standalone database ‘audit’. Anyone has any ideas if I miss any configuration? I’m using Postgres 9.6

That is not possible because Envers uses the same database connection that your application uses. This means the only options currently available are to write the audit data to a separate schema or catalog, depending on the database platform you’re on.

---

<div class="post-metadata">

**Author:** ![lromano](https://avatars.discourse-cdn.com/v4/letter/l/f17d59/32.png) [@lromano](https://discourse.hibernate.org/u/lromano)\
**Post date:** [May 3, 2019, 9:58am UTC](https://discourse.hibernate.org/t/hibernate-envers-default-catalog-not-working/2647/6 "2019-05-03T09:58:54Z")

</div>

What I suggest you is to install dblink extension on your main DB and configure the connection.  
`
CREATE EXTENSION IF NOT EXISTS dblink SCHEMA ;
CREATE SERVER 
FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (host ‘<your address, can be on the same db server>’,
dbname ‘audit’, port ‘5432’);`

CREATE USER MAPPING FOR   
SERVER   
OPTIONS (user ‘’, password ‘’);

Then when you have to insert data in the db unwrap your Hibernate session as explained below:  
`
Session sessionJDBC = session.unwrap(Session.class);`

```
			sessionJDBC.doWork(new Work() {
				
				@Override
				public void execute(Connection conn) throws SQLException {
					try {
						PreparedStatement stmt = conn.prepareStatement("SELECT " 
																        + <your schema> 
																		+ ".dblink_exec('" + 'auditserver for instance' + "','INSERT INTO etc etc "');')");
						stmt.execute();

					} catch (Exception e){
						logger.error(e);				
					}

```

}  
});

This use same session. Hope this help.

---

<div class="post-metadata">

**Author:** ![Naros](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/naros/32/19_2.png) [@Naros](https://discourse.hibernate.org/u/Naros)\
**Post date:** [May 3, 2019, 3:58pm UTC](https://discourse.hibernate.org/t/hibernate-envers-default-catalog-not-working/2647/7 "2019-05-03T15:58:07Z")

</div>

> [@lromano](#):
>
> Then when you have to insert data in the db unwrap your Hibernate session as explained below:

If I understand what you’re proposing, I’m not sure that will work. In this case, @adorogensky doesn’t have control over the insert statements they want to have re-routed, they happen automatically when Envers attempts to save a dynamic-map entity instance, Hibernate is what translates that map instance into an actual insert operation, not the user.

Remember, data capture is only half of what Envers provides users. There is the entire Query API that Envers exposes that allows users to fetch entity instances based on revision numbers, date ranges, and user defined criteria which is not trivial to understand and then implement in just plain SQL.

---

<div class="post-metadata">

**Author:** ![lromano](https://avatars.discourse-cdn.com/v4/letter/l/f17d59/32.png) [@lromano](https://discourse.hibernate.org/u/lromano)\
**Post date:** [May 4, 2019, 11:05am UTC](https://discourse.hibernate.org/t/hibernate-envers-default-catalog-not-working/2647/8 "2019-05-04T11:05:16Z")

</div>

I think the only way to automate this behaviour is to create a trigger function (always using dbLink to save data in the standalone database). In this case you are bypassing Hibernate as the statement will be triggered by the database event handler.

Second solution is to write a store procedure and mapping that procedure in Hibernate; up to know I did not find a solution to use a single Hibernate instance to manage different databases.

---

<div class="post-metadata">

**Author:** ![Naros](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/naros/32/19_2.png) [@Naros](https://discourse.hibernate.org/u/Naros)\
**Post date:** [May 6, 2019, 2:09pm UTC](https://discourse.hibernate.org/t/hibernate-envers-default-catalog-not-working/2647/9 "2019-05-06T14:09:43Z")

</div>

> [@lromano](#):
>
> I think the only way to automate this behaviour is to create a trigger function (always using dbLink to save data in the standalone database). In this case you are bypassing Hibernate as the statement will be triggered by the database event handler.

A trigger is most definitely not the _only_ way.

In my experience, triggers can be unpredictable and performance hindering tools. If you really want to stream changes from one database to another, I might suggest that you check out _Debezium_ for doing that task because it can do it asynchronously without impacting your run-time performance.

> [@lromano](#):
>
> I did not find a solution to use a single Hibernate instance to manage different databases.

That’s because that isn’t how Hibernate works.

Traditionally, if you need to operate on multiple databases, you’ll need to have a `SessionFactory` that is configured per database, acquire a `Session` from each and perform your operations. But in this scenario, you need to take special care with transaction management, making sure the distributed transaction is committed correctly to guarantee database consistency across those participating in the transaction.

I believe I mentioned earlier, some databases have a way to mirror a remote table as a real table. This means you delegate the transaction management aspect to the database and you simply configure and interact with a single `SessionFactory`. If your platform supports this, this is the best alternative.

The _Debezium_ solution avoids the distributed transaction concerns and it also handles replication in an asynchronous way, allowing the run-time overhead to be non-existent. It will mean the user would have to use a `SessionFactory` for their main database reads/writes and a seperate `SessionFactory` for reading from the audit datastore.