Operate the GizDB server

Available Runs today, as shown. Roadmap

This guide installs, secures and operates GizDB on a Linux machine. The server speaks the PostgreSQL wire protocol. Use GizDB owns SQL, embedded APIs, search and streams; this guide owns server setup, drivers, backup, recovery and operational limits.

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, with the compiler: the server compiles the filters, aggregates and joins of its queries to machine code while it runs, with that compiler. The installer's --only gizdb installs the release into a folder of its own, and its bin/gizdb runs the server with the release's compiler.

Install it into $GIZDB_HOME:

curl -fsSL https://gizstack.com/install | GIZDB_INSTALL="$GIZDB_HOME" sh -s -- --only gizdb --no-modify-path > /dev/null
test -x "$GIZDB_HOME/bin/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.
• A toolchain you installed for development has the same gizdb next to giz.
• 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

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.

   shCopy

   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 --backups $GIZDB_HOME/backups --max-connections 20 --statement-timeout 30s
   EOF

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

   shCopy

   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()"

   textCopy

   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.

Other server settings

gizdb --help is the reference for the installed binary. Beyond the settings above:

Authenticate and firewall each enabled protocol listener. Do not expose --trust-all yes on a network: it bypasses password authentication.

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. The server password grants full administrator access regardless of user name. Never hand it to an application; create a limited role instead.
• 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'

Log in as app:

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"'

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'

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"

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'

SSL connection (protocol: TLSv1.3

• sslmode=verify-full makes psql check the certificate and the host name.
• By default the server also accepts connections without TLS. --tls-require yes rejects unencrypted clients. gizlang:postgres supports TLS. The shell examples here use psql for easy protocol inspection.

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'"

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"

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

Buy milk

Python (psycopg 3)

Install the driver with pip install "psycopg[binary]". 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])

python3 connect.py

Buy milk

Java (JDBC)

Put the PostgreSQL JDBC driver (postgresql-42.x.jar) in CLASSPATH. 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)); }
        }
    }
}

java Connect.java

Buy milk

Gizlang

gizlang:postgres is the PostgreSQL client of the Gizlang SDK. It supports certificate-checked TLS with sslmode=require, verify-ca and verify-full; see external PostgreSQL configuration. This local example uses the loopback interface without TLS. 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();
}

giz notes.giz

Buy milk

Standby replication and failover

A standby uses an empty folder, follows the primary's WAL and serves reads while catching up. Start this example in a separate terminal or service after the primary is ready:

GIZDB_PASSWORD="$PGPASSWORD" "$GIZDB_HOME/bin/gizdb" serve \
  --dir "$GIZDB_HOME/standby" --port 5433 --primary 127.0.0.1:5432

A standby is read-only; SELECT pg_is_in_recovery() returns t. Use SELECT pg_promote() to promote it. Before rerouting writes, isolate the old primary to avoid split brain. Test failover, lag and reconnection with your actual clients before relying on this in production.

Serve many databases

gizdb cluster --root ROOT --port PORT serves separate databases on one port, each in its own process. It also takes --bind, --socket, --password and --idle. This is not a distributed transaction coordinator: an ACID transaction across independent databases is not provided by the cluster mode.

Migrate and check data

gizdb migrate copy|verify|compare|status --source starrocks://USER@HOST:8030/DB ... provides separate steps for copying and validating migrated data. Check the source-specific arguments with gizdb --help. Coordinate writes while comparing, and keep the source as a rollback path. For flat files, see CSV import/export.

Verify or salvage a stopped database

After stopping the service, run:

"$GIZDB_HOME/bin/gizdb" verify --dir "$GIZDB_HOME/data"

verify checks log replay, stray files and constraints without changing the stopped data. gizdb salvage --dir DIR --keep COPY copies the damaged database and truncates the log at the first bad frame. Salvage can discard transactions. Preserve the original, backups and archived WAL before starting; compare the recovered state. Both commands refuse a folder still held by an active server.

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 is inside the --backups folder (here $GIZDB_HOME/backups), and it 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:

   shCopy

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

   textCopy

   BACKUP_LABEL

   The file BACKUP_LABEL shows that the backup is complete.

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

   shCopy

   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.

   shCopy

   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:

   shCopy

   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"

   textCopy

   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.

   shCopy

   mkdir "$GIZDB_HOME/backups/base-2"
   psql -q -c "BACKUP TO '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 '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"

3

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"

active
accepting connections
f
30s

There is no general HTTP metrics endpoint. The current source implements pg_stat_gizdb and pg_stat_activity, pg_stat_database and pg_stat_user_tables compatible views; availability and columns vary by the installed release. Test these on the running server before configuring dashboards:

SELECT vector_searches FROM pg_stat_gizdb;
SELECT pid, usename, state FROM pg_stat_activity;

In an installation using @gizai/cli 2.2.1, pg_stat_activity returned no rows despite another client being connected, and pg_stat_gizdb.commit_sequence was missing although it appears in the current source. Check binary/source version drift when monitoring differs.

Limits

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

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"

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

Client and export compatibility

pg_dump is not a supported backup method. A checked installation failed pg_dump --schema-only with schema with OID 13258 does not exist; another build may fail at a different catalog query. Use BACKUP TO and the WAL archive for recovery and \copy for portable CSV data. PostgreSQL wire support does not imply full catalog/extension support.

Parquet data export is implemented as COPY ... TO STDOUT (FORMAT parquet) and COPY ... TO FILE (FORMAT parquet) for supported schemas. See file queries and CSV import/export. Parquet files do not replace a full backup of data, schema and roles.

Troubleshooting

Run gizdb --help, SELECT version() and a small relevant query against the installed server binary before assuming behavior from source HEAD or documentation.

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.