# Postgres DB

## Overview

MOSIP uses [Postgres](https://www.postgresql.org) DB for all relational data storage. The DB creation SQL scripts are located under `/db_scripts` the folder of the module repository. In [sandbox deployment](https://github.com/mosip/mosip-infra/tree/release-1.2.0/deployment/v3/external/postgres), Postgres is installed as a docker inside the cluster. However, in production deployment, typically, Postgres will be installed external to the cluster.

## ER diagrams

Entity relationships diagrams for all databases used in MOSIP are given below.

* [mosip\_audit](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-audit-er.png)
* [mosip\_authdevice](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-authdevice-er.png)
* [mosip\_credential](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-credential-er.png)
* [mosip\_hotlist](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-hotlist-er.png)
* [mosip\_ida](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-ida-er.png)
* [mosip\_idmap](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-idmap-er.png)
* [mosip\_idrepo](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-idrepo-er.png)
* [mosip\_kernel](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-kernel-er.png)
* [mosip\_keymgr](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-keymgr-er.png)
* [mosip\_master](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-master-er.png)
* [mosip\_pms](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-pms-er.png)
* [mosip\_prereg](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-prereg-er.png)
* [mosip\_regdevice](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-regdevice-er.png)
* [mosip\_regprc](https://github.com/mosip/documentation/blob/1.2.0/docs/_images/mosip-regprc-er.png)

## Configuration parameters

* Connection details
  * `{module_name`}`_database_url`
  * `{module_name`}`_database_username`
  * `{module_name`}`_database_password`
* Hibernate configurations
  * `javax.persistence.jdbc.driver`
  * `hibernate.dialect`
  * `hibernate.jdbc.lob.non_contextual_creation`
  * `hibernate.hbm2ddl.auto`
  * `hibernate.show_sql`
  * `hibernate.format_sql`
  * `hibernate.connection.charSet`
  * `hibernate.cache.use_second_level_cache`
  * `hibernate.cache.use_query_cache`
  * `hibernate.cache.use_structured_entries`
  * `hibernate.generate_statistics`
  * `logging.level.org.hibernate.SQL`
  * `logging.level.org.hibernate.type`

### Production DB configuration

These are some of the *reference settings* of a production database. It is expected that these are reviewed and finalized for a given deployment.

```
resources: 
  limits: {}
  #   cpu: 250m
  #   memory: 1Gi
  requests: 
    cpu: 8000m
    memory: 32000Mi

postgresqlExtendedConf:                 
   wal_level: logical
   max_wal_senders: 20
   max_replication_slots: 10
   shared_buffers: 16GB                         
   max_prepared_transactions: 1000
   huge_pages: try                              
   work_mem: 16MB                       # min 64kB
   maintenance_work_mem: 3GB            # min 1MB
   effective_cache_size: 32GB                   
   log_min_duration_statement: 1000
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.mosip.io/1.2.0/id-lifecycle-management/supporting-components/persistence/postgres-db.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.