# PostgreSQL

## Overview

There is an internal PostgreSQL cluster. It runs PostgreSQL 18 on [Beartic](/doc/beartic-qwYI2V5WBW) node.

## Usage

```bash
# Part I – Database export
# If you need to transfer data from an existing instance, use pg_dump.
# You may skip down to "Part II" if you set up a new database.

# (on existing DB node)
# Please note that it's extremely important to use "--no-owner" flag, as
# on a new instance role name would likely be different.
pg_dump --no-owner myawesomedb > myawesomedb.sql

# Part II – Creating a database

# Hop onto the database node
tsh ssh root@ru-lnsk-database

# Create a new user-database pair.
# Copy the resulting PostgreSQL URI connection string.
./manage-db create my_awesome_app

# If YOU HAVE a database backup – copy it on database instance,
# then apply to a clean database
./manage-db import-dump my_awesome_app dump.sql

# Part III – Connecting to the database

# First, you need to obtain a B4CKSP4CE Root CA, as it's used
# to sign PostgreSQL server certificate.
# More info at https://ca.bksp.in

# (on your app node)
curl -fSsl https://ca.bksp.in/root/bksp-root.crt > B4CKSP4CE_Root_CA.crt

# Now you can connect using either full connection string from the Part II,
# or manually extract the neccessary fields from it.

# Prisma ORM is known to treat DATABASE_URL connection string well.
# You may put the one you've got on Part II, and use as is in environment
# variable. Don't forget to mount B4CKSP4CE Root CA file into container, and
# adjust connection string's sslrootcert path if neccessary
```

## Connection details

| **PGHOST** | **PGPORT** | **PGSSLMODE** | **PGSSLROOTCERT** | **PGUSER** | **PGPASSWORD** |
|--------|--------|-----------|---------------|--------|------------|
| db.svc.bksp.in | 5432   | `verify-full` | [Root CA](https://ca.bksp.in/root/bksp-root.crt) | (App Name) | (Generated) |

## Monitoring

Dashboard is available [here](https://g.bksp.in/goto/bfgc321spryf4b?orgId=1).

## Backup & Restore


:::warning
**Do not restore the cluster.**  
Unless a fatal data loss involving corruption of the cluster itself is occurred.  
  
If you need to restore a particular database – restore backup **on a new temporary cluster**, then dump the database of interest, and apply this dump to the primary cluster.

:::

This cluster runs on [Peg](https://github.com/b4ck5p4c3/peg) – which is glue between standard PostgreSQL 18 and WAL-G. We’re mostly focused on WAL logs backup, which is done automatically on PostgreSQL discretion.

Backups are encrypted using PGP key:

```bash
pub   ed25519 2026-03-13 [SC] [expires: 2029-03-12]
      65F4330020D9D0C38FD44D0608F1D49104A14258
uid           [ultimate] B4CKSP4CE Backup Key <ops.backup@bksp.in>
sub   cv25519 2026-03-13 [E] [expires: 2029-03-12]
      7111DA83B7E963B789DEB5AF53CBBE5CAA00C703
      
-----BEGIN PGP PUBLIC KEY BLOCK-----

mDMEabONBhYJKwYBBAHaRw8BAQdAwgni35Q2fruhapJBQluz//XNOdwx3txAus5B
2gF8GHW0KUI0Q0tTUDRDRSBCYWNrdXAgS2V5IDxvcHMuYmFja3VwQGJrc3AuaW4+
iJkEExYKAEEWIQRl9DMAINnQw4/UTQYI8dSRBKFCWAUCabONBgIbAwUJBaOagAUL
CQgHAgIiAgYVCgkICwIEFgIDAQIeBwIXgAAKCRAI8dSRBKFCWPTbAP4ummTGG8Zw
GAWgRmaHBh2vpTZrpZZ76BIdU8Wtglv9jAEA3ThfvKWr9nbBexCK50YgD3XE+Jn2
K9UYkLWdVhKZuAG4OARps40GEgorBgEEAZdVAQUBAQdApwwWsXqEDnAlXjpQ2sF7
0ONvgiM1E2k+DyPw7HD4gjUDAQgHiH4EGBYKACYWIQRl9DMAINnQw4/UTQYI8dSR
BKFCWAUCabONBgIbDAUJBaOagAAKCRAI8dSRBKFCWNjbAP9sWaCM7ERq4NsJVHIA
5cpAclhL8Eut2rB6iUbLRqgK2wD7B12rlbzbKSRaZMwjk8jXXO7us+i9sZFn+2Y+
/H6OOQM=
=gvJu
-----END PGP PUBLIC KEY BLOCK-----
```

Secret key is stored on Primary and Secondary USB sticks in possession of [Custodians](https://wiki.bksp.in/doc/certification-authority-iqeCZxuH8z#h-custodians).

See [Peg’s README](https://github.com/b4ck5p4c3/peg?tab=readme-ov-file#recovery) for detailed instructions on both full and point-in-time recovery procedures.

---

**Documents**

- [BKSP 101](https://wiki.bksp.in/s/public/doc/bksp-101-HvrtlzB32F)
- [Проекты](https://wiki.bksp.in/s/public/doc/proekty-yjWZscgTvo)
- [Infrastructure](https://wiki.bksp.in/s/public/doc/infrastructure-VPKsJwVSVh)
- [Пространство](https://wiki.bksp.in/s/public/doc/prostranstvo-UF1eSbQDi5)
- [Equipment](https://wiki.bksp.in/s/public/doc/equipment-t5MYAVJ8l0)
- [Other](https://wiki.bksp.in/s/public/doc/other-uPW1YJQaRt)