# Operate the GizDB server

:::status{of="gizdb-server"}
:::

This guide installs the GizDB server and operates it on one Linux machine, from a clean machine
to production. The server speaks the PostgreSQL wire protocol. [Use GizDB](../guides/data.md) tells
how to use the database. This page tells how to run it.

Every command block on this page is a tested step. A scenario test reads this page
and runs the blocks in order (refer to [How this page is tested](#how-this-page-is-tested)).

## Before you start

You need:

- Linux on x86-64, with systemd. The steps use a systemd user service. They do not need root.
- `curl`, to install the server. You do not need the Gizlang toolchain.
- The PostgreSQL client programs `psql` and `pg_isready` (this guide used version 16).
- For TLS: `openssl`.

The steps use these shell variables. Set them first, in the shell where you do the steps:

```sh ops settings
export GIZDB_HOME="$HOME/gizdb"   # the binary, the data, the archive and the backups
export GIZDB_UNIT=gizdb           # the name of the systemd unit
export PGHOST=127.0.0.1 PGPORT=5432 PGUSER=postgres PGDATABASE=postgres
export PGPASSWORD='change-me'     # the superuser password: choose your own
```

`psql`, `pg_isready` and the other PostgreSQL clients read `PGHOST`, `PGPORT`, `PGUSER`,
`PGDATABASE` and `PGPASSWORD`.

## Install the server

The server is the program `gizdb`. Every Gizlang release ships it prebuilt and signed. A
database host needs only the server, not the compiler: the installer's `--only gizdb` installs
`gizdb` alone, the same version and signature as the toolchain, with no compiler or LLVM.

Install it into `$GIZDB_HOME`:

```sh ops
curl -fsSL https://gizstack.com/install | GIZDB_INSTALL="$GIZDB_HOME" sh -s -- --only gizdb --no-modify-path > /dev/null
ls "$GIZDB_HOME/bin"
```

```text output
gizdb
```

- The installer checks the SHA-256 of the installer it downloads, the release signature
  (Ed25519) and the SHA-256 of every file before it activates the version. It needs no root.
- The license notices of the third-party code in the binary are in
  `$GIZDB_HOME/versions/*/share/licenses` and `$GIZDB_HOME/versions/*/licenses`.
- The Gizlang toolchain has the same `gizdb` next to `gizlang`, for development on your own
  computer. You do not need the toolchain on a database host.
- To install the server on a different machine, run the same command there.

At run time the shipped binary needs only the C library (OpenSSL, SQLite and Brotli are linked in):

```sh ops release
ldd "$GIZDB_HOME/bin/gizdb" | awk '{print $1}' | grep -v -e vdso -e ld-linux | LC_ALL=C sort
```

```text output
libc.so.6
libm.so.6
```

Managed databases on GizStack (`giz db create`, `connect` and `backup`) are coming. Until
then, you run the server as this page tells.

## Start the server as a service

The server keeps its data in one folder (`--dir`). It writes each commit to its log before it
replies. Thus a stop of the process at any time loses no confirmed commit.

1. Make the folders, and the environment file of the service. The file holds the superuser
   password (`GIZDB_PASSWORD`) and the server options (`GIZDB_OPTIONS`). Only you can read it.

   ```sh ops
   mkdir -p "$GIZDB_HOME/data" "$GIZDB_HOME/archive" "$GIZDB_HOME/backups"
   umask 077
   cat > "$GIZDB_HOME/gizdb.env" <<EOF
   GIZDB_PASSWORD=$PGPASSWORD
   GIZDB_OPTIONS=--port $PGPORT --archive $GIZDB_HOME/archive --max-connections 20 --statement-timeout 30s
   EOF
   ```

2. Write the unit file, and start the service:

   ```sh ops
   mkdir -p ~/.config/systemd/user
   cat > ~/.config/systemd/user/$GIZDB_UNIT.service <<EOF
   [Unit]
   Description=GizDB server (PostgreSQL protocol)

   [Service]
   EnvironmentFile=$GIZDB_HOME/gizdb.env
   ExecStart=$GIZDB_HOME/bin/gizdb --dir $GIZDB_HOME/data \$GIZDB_OPTIONS \$GIZDB_TLS
   Restart=on-failure

   [Install]
   WantedBy=default.target
   EOF
   systemctl --user daemon-reload
   systemctl --user enable --now -q "$GIZDB_UNIT"
   timeout 60 sh -c 'until pg_isready -q; do sleep 1; done'
   psql -At -c "SELECT version()"
   ```

   ```text output
   PostgreSQL 16.0 (GizDB)
   ```

- `$GIZDB_OPTIONS` and `$GIZDB_TLS` come from the environment file. systemd splits them into
  separate options. `$GIZDB_TLS` is empty until you [enable TLS](#enable-tls).
- The server listens on 127.0.0.1 by default. To listen on all addresses, add `--bind 0.0.0.0`
  to `GIZDB_OPTIONS`. Before you do this, enable TLS.
- A user service stops when you log out. To keep it running, run `loginctl enable-linger` one
  time.

### Server options

`gizdb --help` lists every option.

| Option | Meaning |
| --- | --- |
| `--dir DIR` | The database folder. The default is `data`. |
| `--port N` | The TCP port. The default is 5432. |
| `--bind ADDRESS` | The listen address. The default is 127.0.0.1. |
| `--password SECRET` | The superuser password. `GIZDB_PASSWORD` is the same, and keeps the password out of the process list. Without a password, the server serves only this host: on another `--bind` address it does not start, unless `--trust-all yes` (every client is accepted; the server prints a warning). |
| `--archive DIR` | Copy the log to DIR, for [restore to a point in time](#back-up-and-restore-to-a-point-in-time). |
| `--archive-every SECONDS` | How often the server copies the log to the archive. The default is 60. |
| `--tls-cert FILE`, `--tls-key FILE` | The certificate chain and the key, in PEM. Refer to [Enable TLS](#enable-tls). |
| `--max-connections N` | The maximum number of client sessions at one time. Refer to [Limits](#limits). |
| `--statement-timeout DURATION` | The default `statement_timeout` of each session, for example `30s`. |

## Users, passwords and TLS

### Superuser and roles

- With a server password, a client logs in with SCRAM-SHA-256.
- A user name that is not a role logs in as the superuser, with the server password.
- A role logs in with its own password.

Make a table, and a role `app` that can read and add rows only:

```sh ops
psql -q -c "CREATE TABLE notes (id int PRIMARY KEY, title text NOT NULL)"
psql -q -c "INSERT INTO notes VALUES (1, 'Buy milk'), (2, 'Write guide')"
psql -q -c "CREATE ROLE app LOGIN PASSWORD 'app-secret'"
psql -q -c "GRANT SELECT, INSERT ON notes TO app"
export APP_PASSWORD='app-secret'
```

Log in as `app`:

```sh ops
PGUSER=app PGPASSWORD="$APP_PASSWORD" psql -At -c "SELECT current_user, count(*) FROM notes GROUP BY 1"
PGUSER=app PGPASSWORD="$APP_PASSWORD" psql -At -c "DELETE FROM notes" 2>&1 || true
PGUSER=app PGPASSWORD=wrong psql -At -c "SELECT 1" 2>&1 | grep -o 'password authentication failed for user "app"'
```

```text output
app|2
ERROR:  permission denied for table notes
password authentication failed for user "app"
```

To change the password of a role, use `ALTER ROLE`:

```sh ops
psql -q -c "ALTER ROLE app PASSWORD 'app-secret-2'"
PGUSER=app PGPASSWORD='app-secret-2' psql -At -c "SELECT 'new password works'"
export APP_PASSWORD='app-secret-2'
```

```text output
new password works
```

To change the superuser password, change `GIZDB_PASSWORD` in the environment file, and restart
the service.

To let a role see only some rows, use row level security. A policy filters each read of the role.
The superuser and the table owner are not filtered. A read through a parent table uses the
policies of the parent. Foreign key checks see all rows.

```sh ops
psql -q -c "CREATE TABLE tasks (id int PRIMARY KEY, owner text NOT NULL)"
psql -q -c "INSERT INTO tasks VALUES (1, 'app'), (2, 'ana')"
psql -q -c "GRANT SELECT ON tasks TO app"
psql -q -c "ALTER TABLE tasks ENABLE ROW LEVEL SECURITY"
psql -q -c "CREATE POLICY own ON tasks USING (owner = current_user)"
PGUSER=app PGPASSWORD="$APP_PASSWORD" psql -At -c "SELECT id, owner FROM tasks"
psql -At -c "SELECT count(*) FROM tasks"
```

```text output
1|app
2
```

### Enable TLS

The server reads the certificate chain and the key in PEM. This example makes a self-signed
certificate for `localhost`. For a server that other machines use, get a certificate from your
certificate authority for the name that clients connect to.

```sh ops requires=openssl
openssl req -x509 -newkey ec -pkeyopt ec_paramgen_curve:P-256 -nodes -days 365 \
  -subj /CN=localhost -addext subjectAltName=DNS:localhost,IP:127.0.0.1 \
  -keyout "$GIZDB_HOME/server.key" -out "$GIZDB_HOME/server.pem" 2>/dev/null
chmod 600 "$GIZDB_HOME/server.key"
echo "GIZDB_TLS=--tls-cert $GIZDB_HOME/server.pem --tls-key $GIZDB_HOME/server.key" >> "$GIZDB_HOME/gizdb.env"
systemctl --user restart "$GIZDB_UNIT"
timeout 60 sh -c 'until pg_isready -q; do sleep 1; done'
psql "host=localhost sslmode=verify-full sslrootcert=$GIZDB_HOME/server.pem" -c '\conninfo' | grep -o 'SSL connection (protocol: TLSv1.3'
```

```text output
SSL connection (protocol: TLSv1.3
```

- `sslmode=verify-full` makes psql check the certificate and the host name.
- The server also accepts connections without TLS. `--tls-require yes` refuses them, but
  [`gizlang:postgres` has no TLS yet](#gizlang). Thus this guide does not use it.

To replace the certificate (for example, before it expires), replace the two files. New sessions
use the new files. You do not restart the server.

```sh ops requires=openssl
openssl req -x509 -newkey ec -pkeyopt ec_paramgen_curve:P-256 -nodes -days 365 \
  -subj /CN=localhost -addext subjectAltName=DNS:localhost,IP:127.0.0.1 \
  -keyout "$GIZDB_HOME/new.key" -out "$GIZDB_HOME/new.pem" 2>/dev/null
mv "$GIZDB_HOME/new.key" "$GIZDB_HOME/server.key"
mv "$GIZDB_HOME/new.pem" "$GIZDB_HOME/server.pem"
psql "host=localhost sslmode=verify-full sslrootcert=$GIZDB_HOME/server.pem" -At -c "SELECT 'new certificate works'"
```

```text output
new certificate works
```

## Connect from clients

Each example connects as `app` and reads the note with id 1, with a parameter.

### psql

```sh ops
PGUSER=app PGPASSWORD="$APP_PASSWORD" psql -At -c "SELECT title FROM notes WHERE id = 1"
```

```text output
Buy milk
```

### Node.js (node-postgres)

Install the driver in your project with `npm install pg`. `connect.mjs`:

```js ops file=connect.mjs
import pg from "pg";
const client = new pg.Client({ host: "127.0.0.1", port: Number(process.env.PGPORT),
  user: "app", password: process.env.APP_PASSWORD, database: "postgres" });
await client.connect();
const result = await client.query("SELECT title FROM notes WHERE id = $1", [1]);
console.log(result.rows[0].title);
await client.end();
```

```sh ops requires=node-postgres
node connect.mjs
```

```text output
Buy milk
```

### Python (psycopg 3)

Install the driver with `pip install "psycopg[binary]"`. `connect.py`:

```python ops file=connect.py
import os, psycopg
with psycopg.connect(host="127.0.0.1", port=os.environ["PGPORT"], user="app",
                     password=os.environ["APP_PASSWORD"], dbname="postgres") as conn:
    print(conn.execute("SELECT title FROM notes WHERE id = %s", (1,)).fetchone()[0])
```

```sh ops requires=psycopg
python3 connect.py
```

```text output
Buy milk
```

### Java (JDBC)

Put the PostgreSQL JDBC driver (`postgresql-42.x.jar`) in `CLASSPATH`. `Connect.java`:

```java ops file=Connect.java
import java.sql.*;

public class Connect {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://127.0.0.1:" + System.getenv("PGPORT") + "/postgres";
        try (Connection c = DriverManager.getConnection(url, "app", System.getenv("APP_PASSWORD"));
             PreparedStatement s = c.prepareStatement("SELECT title FROM notes WHERE id = ?")) {
            s.setInt(1, 1);
            try (ResultSet r = s.executeQuery()) { r.next(); System.out.println(r.getString(1)); }
        }
    }
}
```

```sh ops requires=jdbc
java Connect.java
```

```text output
Buy milk
```

### Gizlang

`gizlang:postgres` is the PostgreSQL client of the Gizlang SDK. It does not support TLS yet. Use
it on the same machine, or on a private network. `notes.giz`:

```gizlang ops file=notes.giz
import {System} from "gizlang:system";
import {Option} from "gizlang:core";
import {parseI64, IntegerParse} from "gizlang:text";
import * as Pg from "gizlang:postgres";

function main(sys: System): void {
    let port = 5432;
    match (parseI64(sys.environment("PGPORT") ?? "5432")) {
        IntegerParse.ok(n) => { port = n; }
        IntegerParse.invalid(at) => {}
        IntegerParse.overflow(at) => {}
    }
    const password = sys.environment("APP_PASSWORD") ?? "";
    const c = Pg.connect(sys, { host: "127.0.0.1", port: port, user: "app", password: password, database: "postgres" })!;
    const result = c.query("SELECT title FROM notes WHERE id = $1", [Option.some("1")])!;
    print(result.rows[0][0] ?? "NULL");
    c.close();
}
```

```sh ops
giz notes.giz
```

```text output
Buy milk
```

## Back up and restore to a point in time

A restore to a point in time needs two things:

- A base backup. `BACKUP TO 'folder'` makes one while the server runs. The folder must exist
  and be empty. Only a superuser can run it.
- The log after the backup. The server copies it to the `--archive` folder every
  `--archive-every` seconds. The newest part is still in the database folder.

1. Make a base backup:

   ```sh ops
   mkdir "$GIZDB_HOME/backups/base-1"
   psql -q -c "BACKUP TO '$GIZDB_HOME/backups/base-1'"
   ls "$GIZDB_HOME/backups/base-1" | grep -x BACKUP_LABEL
   ```

   ```text output
   BACKUP_LABEL
   ```

   The file `BACKUP_LABEL` shows that the backup is complete.

2. Work continues. Then a mistake deletes every note:

   ```sh ops
   PGUSER=app PGPASSWORD="$APP_PASSWORD" psql -q -c "INSERT INTO notes VALUES (3, 'Call Ana')"
   sleep 1
   export GOOD_TIME="$(date -u '+%Y-%m-%d %H:%M:%S+00')"
   sleep 1
   psql -q -c "DELETE FROM notes"
   ```

3. Stop the server. Restore the backup and the log up to the time before the mistake, into a new
   folder. Give the archive and the database folder to `--logs`.

   ```sh ops
   systemctl --user stop "$GIZDB_UNIT"
   mkdir "$GIZDB_HOME/restored"
   "$GIZDB_HOME/bin/gizdb" restore --base "$GIZDB_HOME/backups/base-1" \
     --logs "$GIZDB_HOME/archive,$GIZDB_HOME/data" --to "$GIZDB_HOME/restored" --until "$GOOD_TIME"
   ```

   Without `--until`, the restore replays all of the log.

4. Keep the damaged folder and the old archive. Start the server on the restored folder, with a
   new archive:

   ```sh ops
   mv "$GIZDB_HOME/data" "$GIZDB_HOME/data.before-restore"
   mv "$GIZDB_HOME/restored" "$GIZDB_HOME/data"
   mv "$GIZDB_HOME/archive" "$GIZDB_HOME/archive.before-restore"
   mkdir "$GIZDB_HOME/archive"
   systemctl --user start "$GIZDB_UNIT"
   timeout 60 sh -c 'until pg_isready -q; do sleep 1; done'
   psql -At -c "SELECT id, title FROM notes ORDER BY id"
   ```

   ```text output
   1|Buy milk
   2|Write guide
   3|Call Ana
   ```

5. Make a new base backup now. A restore of the new archive starts from it.

   ```sh ops
   mkdir "$GIZDB_HOME/backups/base-2"
   psql -q -c "BACKUP TO '$GIZDB_HOME/backups/base-2'"
   ```

Roles and grants are in the database. Thus the restore also restores them.

## Upgrade the server

- An upgrade installs the new version with the same command. The database folder stays.
- GizDB does not yet promise that a new version opens the database folder of an older version.
  Thus make a base backup before each upgrade. To go back, install the old version
  (`--version VERSION`), and restore that backup, as in
  [Back up and restore](#back-up-and-restore-to-a-point-in-time).
- The installer replaces `$GIZDB_HOME/bin/gizdb` in one step. The running server keeps the old
  binary until the restart.

```sh ops
mkdir "$GIZDB_HOME/backups/before-upgrade"
psql -q -c "BACKUP TO '$GIZDB_HOME/backups/before-upgrade'"
curl -fsSL https://gizstack.com/install | GIZDB_INSTALL="$GIZDB_HOME" sh -s -- --only gizdb --no-modify-path > /dev/null
systemctl --user restart "$GIZDB_UNIT"
timeout 60 sh -c 'until pg_isready -q; do sleep 1; done'
psql -At -c "SELECT count(*) FROM notes"
```

```text output
3
```

## Monitor the server

These checks work today:

```sh ops
systemctl --user is-active "$GIZDB_UNIT"
pg_isready -q && echo accepting connections
psql -At -c "SELECT pg_is_in_recovery()"
psql -At -c "SHOW statement_timeout"
```

```text output
active
accepting connections
f
30s
```

| Check | What it tells |
| --- | --- |
| `systemctl --user is-active` | The process runs. systemd restarts it after a failure (`Restart=on-failure`). |
| `pg_isready` | The server accepts connections. |
| `SELECT pg_is_in_recovery()` | `t` on a standby, `f` on a primary. |
| `journalctl --user -u $GIZDB_UNIT` | What the server printed. The server prints only errors. |
| `du -s $GIZDB_HOME/data $GIZDB_HOME/archive` | The disk space of the database and of the archive. |

The server has no metrics endpoint, and no `pg_stat_activity` or other statistics views yet.

## Limits

| Limit | Value today | What happens at the limit |
| --- | --- | --- |
| Client sessions | `--max-connections` (20 in this guide) | The next client gets `FATAL: sorry, too many clients already`. |
| Statement time | `--statement-timeout` (30 s in this guide) is the default `statement_timeout` of each session. `SHOW statement_timeout` shows it. | This guide did not measure it: GizDB has no `pg_sleep` to make a slow statement. |
| Data written by one transaction | No 64 KiB limit: one statement writes a 1 MB value. | The example below writes it. |

```sh ops
for i in $(seq 20); do sleep 4 | psql -q & done
sleep 2
psql -At -c "SELECT 1" 2>&1 | grep -m1 -o 'sorry, too many clients already'
wait
psql -q -c "CREATE TABLE blobs (id int PRIMARY KEY, body text)"
psql -At -c "INSERT INTO blobs VALUES (1, repeat('x', 60000))"
psql -At -c "INSERT INTO blobs VALUES (2, repeat('x', 1000000))"
```

```text output
sorry, too many clients already
INSERT 0 1
INSERT 0 1
```

## Export the data

Export each table as CSV with `\copy`. The CSV has a header line:

```sh ops
psql -q -c "\copy (SELECT * FROM notes ORDER BY id) TO '$GIZDB_HOME/notes.csv' WITH (FORMAT csv, HEADER)"
cat "$GIZDB_HOME/notes.csv"
```

```text output
id,title
1,Buy milk
2,Write guide
3,Call Ana
```

Not available yet:

- `pg_dump` does not work with GizDB. It stops at its first query, with
  `ERROR: no such function: set_config`.
- There is no export to Parquet.

## Remove the service

```sh ops cleanup
systemctl --user disable --now "$GIZDB_UNIT" 2>/dev/null || true
rm -f ~/.config/systemd/user/$GIZDB_UNIT.service
systemctl --user daemon-reload
```

The data stays in `$GIZDB_HOME`. Remove that folder when you do not need the data.

## How this page is tested

A scenario test reads this page and runs its command blocks in order.

- It runs each block marked `sh ops` in order, with `/bin/sh`, in one scratch folder. A step
  fails when a command in it fails. When an `output` block follows, the step must print exactly
  that text (standard output and standard error).
- It writes each block marked `file=NAME` to the file NAME, as you do.
- It does not run the settings block. It uses its own values: `GIZDB_HOME` in the scratch folder,
  a free `PGPORT`, a random `PGPASSWORD`, and a random `GIZDB_UNIT`.
- The install command (`curl -fsSL https://gizstack.com/install | ...`) runs as written when
  `GIZDB_OPS_RELEASE=published` is set: that is the check of a published release on a clean host.
  Otherwise the suite builds, in its place, the program that the release ships (`gizdb`) from the
  source tree of the test into `$GIZDB_HOME/bin`.
- A block marked `requires=` runs only when that software is on the machine. The suite prints
  each block that it did not run, and why. It skips all steps when `psql`, `pg_isready` or a
  systemd user manager is not available.
- It always runs the block of [Remove the service](#remove-the-service) at the end.