# 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.

Operate the GizDB server

Coming In development. This is the design it ships to. Roadmap

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 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).

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:

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:

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

text

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):

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

text

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.

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.

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

Write the unit file, and start the service:

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

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.
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.
`--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.
`--max-connections N`	The maximum number of client sessions at one time. Refer to 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:

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'

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

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

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

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

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.

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

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.

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

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. 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.

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

new certificate works

Connect from clients

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

psql

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

text

Buy milk

Node.js (node-postgres)

Install the driver in your project with npm install pg. 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();

node connect.mjs

text

Buy milk

Python (psycopg 3)

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

python

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])

python3 connect.py

text

Buy milk

Java (JDBC)

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

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)); }
        }
    }
}

java Connect.java

text

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

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();
}

giz notes.giz

text

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.

Make a base backup:

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

BACKUP_LABEL

The file BACKUP_LABEL shows that the backup is complete.

Work continues. Then a mistake deletes every note:

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"

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.

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.

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

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

1|Buy milk
2|Write guide
3|Call Ana

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

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.
The installer replaces $GIZDB_HOME/bin/gizdb in one step. The running server keeps the old binary until the restart.

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

Monitor the server

These checks work today:

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

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.

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

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:

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

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

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 at the end.