PostgreSQL Metadata Dump

Artifact extract from the NeoCore PostgreSQL instance at 103.216.94.145:5443.

---

Connection Details

Property	Value
Host	103.216.94.145
Port	5443
Database	neocoredb
SSL Mode	prefer
Username	neocore
Schema pattern	{service-name}_db-linux

---

Schema Inventory

Run this query to list all NeoCore schemas:

SELECT schema_name,
       pg_size_pretty(SUM(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::BIGINT) AS schema_size
FROM information_schema.tables
WHERE schema_name LIKE '%_db%'
GROUP BY schema_name
ORDER BY schema_name;

Expected schemas:

Schema	Service	Tables
customer-service_db-linux	customer-service	13
deposit-service_db-linux	deposit-service	6
product-service_db-linux	product-service	10
transaction-service_db-linux	transaction-service	8
generalledger-service_db-linux	generalledger-service	9
clearing-service_db-linux	clearing-service	1
common_db	common-service (localhost:5432)	3
share-service_db-linux	share-service	2
system-setup-service_db-linux	system-setup-service	1
periodicactivity-service_db-linux	periodicactivity-service	5+
bankandbranch-service_db-linux	bankandbranch-service	—

---

Table Row Count Snapshot

SELECT schemaname,
       relname AS tablename,
       n_live_tup AS row_count,
       pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS size
FROM pg_stat_user_tables
WHERE schemaname LIKE '%_db%'
ORDER BY schemaname, n_live_tup DESC;

---

Index Inventory

SELECT
  schemaname,
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname LIKE '%_db%'
ORDER BY schemaname, tablename, indexname;

Key indexes enforced by Flyway migrations:

Schema	Table	Index	Type
customer-service_db-linux	customer_master	idx_cust_id	Unique
customer-service_db-linux	customer_contact	idx_mobile_number	Unique
customer-service_db-linux	customer_kyc	idx_id_number	Unique
deposit-service_db-linux	term_deposit	idx_deposit_number	Unique
deposit-service_db-linux	term_deposit	idx_cust_id	B-tree
transaction-service_db-linux	transaction_master	idx_transaction_ref	Unique
transaction-service_db-linux	transaction_master	idx_ref_account_date	B-tree composite
generalledger-service_db-linux	gl_account_head	idx_account_head_code	Unique
product-service_db-linux	deposit_scheme	idx_scheme_code	Unique

---

Flyway Migration History

SELECT
  version,
  description,
  type,
  script,
  installed_on,
  execution_time,
  success
FROM flyway_schema_history
WHERE success = true
ORDER BY installed_rank;

Flyway schema history table is created per service schema. Each service has its own flyway_schema_history table in its schema.

---

PostgreSQL Version & Extensions

-- Check version
SELECT version();

-- Check installed extensions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;

Expected extensions:

Extension	Version	Usage
uuid-ossp	1.1	UUID generation for audit IDs
pg_stat_statements	1.10	Query performance monitoring
pgcrypto	1.3	Password / secret hashing

---

Backup & Restore

Backup a schema

pg_dump \
  -h 103.216.94.145 \
  -p 5443 \
  -U neocore \
  -n "customer-service_db-linux" \
  -F c \
  -f backups/customer_service_db_$(date +%Y%m%d).dump \
  neocoredb

Backup all NeoCore schemas

$schemas = @(
  'customer-service_db-linux', 'deposit-service_db-linux', 'product-service_db-linux',
  'transaction-service_db-linux', 'generalledger-service_db-linux', 'share-service_db-linux',
  'system-setup-service_db-linux', 'clearing-service_db-linux',
  'periodicactivity-service_db-linux', 'bankandbranch-service_db-linux'
)

$date = Get-Date -Format "yyyyMMdd"
New-Item -ItemType Directory -Force backups

foreach ($schema in $schemas) {
  $safeName = $schema -replace '[-]', '_'
  pg_dump `
    -h 103.216.94.145 -p 5443 -U neocore `
    -n $schema -F c `
    -f "backups/${safeName}_${date}.dump" `
    neocoredb
  Write-Host "Backed up $schema"
}

Restore a schema

pg_restore \
  -h 103.216.94.145 \
  -p 5443 \
  -U neocore \
  -d neocoredb \
  -n "customer-service_db-linux" \
  backups/customer_service_db_20260629.dump

---

HikariCP Pool Monitoring

-- Active connections per schema/service (proxy via service config)
SELECT client_addr, datname, usename, state, query_start, query
FROM pg_stat_activity
WHERE datname = 'neocoredb'
  AND state != 'idle'
ORDER BY query_start;

HikariCP config reference (from application.yml):

spring:
  datasource:
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      connection-timeout: 30000   # 30s
      idle-timeout: 600000        # 10 min
      max-lifetime: 1800000       # 30 min