# Replicate, federate and commit across servers

This guide connects a GizDB server to other servers. The other server is GizDB or PostgreSQL: GizDB speaks
PostgreSQL's replication protocol and SQL on both sides, so the steps are the same for both.

| You want to | Use | Section |
| --- | --- | --- |
| Keep a copy of tables on another server, or move data with no stop | Logical replication | [Replicate tables](#replicate-tables) |
| Read and write a table that another server holds | A foreign table | [Query the tables of another server](#query-the-tables-of-another-server) |
| Keep some partitions of a table on another server | A foreign partition | [Place partitions on another server](#place-partitions-on-another-server) |
| Change rows on several servers in one transaction | The two-phase commit | [Commit on several servers](#commit-on-several-servers) |
| Take part in another coordinator's transaction | `PREPARE TRANSACTION` | [Prepared transactions](#prepared-transactions) |
| Keep a whole copy of one GizDB server for failover | A standby | [Operate the GizDB server](gizdb-server.md#standby-replication-and-failover) |

All of these need a served database (`gizdb serve`). A database opened in a program reaches no other server: its
foreign tables answer SQLSTATE 55000, and it runs no subscription.

The steps were run with PostgreSQL 16, 17, 18 and 19 beta 1. [GizDB internals](../internals/database.md) owns the design and its invariants.

## Before you start

- A GizDB server ([Operate the GizDB server](gizdb-server.md)). The steps name it **G** and reach it at
  `127.0.0.1:5432`.
- The other server, **P**, reached at `127.0.0.1:5433`. It is a second GizDB server or a PostgreSQL server.
- A superuser on each. Only a superuser opens a replication connection or creates a subscription.

When P is PostgreSQL, set this in its `postgresql.conf` and restart it:

```text
wal_level = logical                  # P publishes changes
max_prepared_transactions = 10       # P takes part in a two-phase commit
```

A GizDB server needs no setting for either.

## Replicate tables

A **publication** names the tables whose changes a server sends. A **subscription** on another server copies those
tables once, then applies each later transaction in the publisher's order. The two tables must exist on both sides,
with the same names and columns; the table on the subscriber needs the same primary key.

### From PostgreSQL to GizDB

On P (PostgreSQL), publish the table:

```sql
CREATE TABLE orders (id int PRIMARY KEY, customer text, total numeric, at timestamptz);
CREATE PUBLICATION app FOR TABLE orders;
```

On G (GizDB), create the same table and subscribe:

```sql
CREATE TABLE orders (id int PRIMARY KEY, customer text, total numeric, at timestamptz);
CREATE SUBSCRIPTION from_p
  CONNECTION 'host=127.0.0.1 port=5433 dbname=postgres user=postgres'
  PUBLICATION app;
```

`CREATE SUBSCRIPTION` connects to the publisher once. When it cannot, the statement fails with SQLSTATE 08006 and
makes nothing. After it, a worker of G makes the slot `from_p` on P, copies the rows that `orders` holds, and then
applies each transaction of P as one transaction of G.

Check it on G:

```sql
SELECT count(*) FROM orders;
```

### From GizDB to PostgreSQL

The statements are the same with the roles changed: `CREATE PUBLICATION` on G, `CREATE SUBSCRIPTION` on PostgreSQL
with G's address. PostgreSQL's own apply worker connects to G as it connects to a PostgreSQL publisher.

On G, each subscriber has one slot. The log of G is kept from the slot's position, so a subscriber that was away
continues where it stopped. Each transaction it missed goes as the publication was when the transaction committed:
a table you took out of the publication later still sends its earlier changes.

```sql
SELECT slot_name, slot_type, confirmed_flush_lsn FROM pg_replication_slots;
```

A slot that no subscriber reads keeps the log. Drop the subscription on the subscriber when you do not need it:
`DROP SUBSCRIPTION` also drops its slot at the publisher.

### Read changes with Debezium

Debezium's PostgreSQL connector reads GizDB as it reads PostgreSQL. Create the publication on G, and set the
connector's `plugin.name` to `pgoutput`:

```sql
CREATE PUBLICATION dbz_publication FOR TABLE orders;
```

```text
connector.class=io.debezium.connector.postgresql.PostgresConnector
plugin.name=pgoutput
publication.name=dbz_publication
slot.name=dbz
table.include.list=public.orders
```

The connector reads the table's rows in the snapshot of its slot, then each change. This was run with Debezium
Server 3.0.

### Move a database with no stop

1. Create the tables on the new server and subscribe it to the old one.
2. Wait until the new server holds the rows of the old one.
3. Stop the writes of the application, wait for the last change, and point the application at the new server.
4. Drop the subscription.

To go back, do the same steps in the other direction. Do not keep a subscription in each direction at one time on
the same tables.

### When the publisher's leader changes

A GizDB publisher can be a group of voters (see [Operate the GizDB server](gizdb-server.md#standby-replication-and-failover)).
The slot of a subscription is in their log, so the voter that becomes the leader holds it. Name every voter in the
connection, as libpq does, and ask for the one that takes writes:

```sql
CREATE SUBSCRIPTION s
  CONNECTION 'host=db1,db2,db3 port=5432 dbname=postgres user=postgres target_session_attrs=read-write'
  PUBLICATION p;
```

When the leader stops, the subscriber connects to the new leader and goes on from its own position. It gets each
row one time. The options of a foreign server take a list of hosts in the same way.

### What is replicated

| Replicated | Not replicated |
| --- | --- |
| INSERT, UPDATE and DELETE of the published tables, each transaction whole and in order | DDL: create and change the tables on both sides yourself |
| The rows a table held when the subscription began | TRUNCATE, and the values of sequences |
| NULL, numeric, text, boolean, date and time values as text of the same form | A row filter or a column list of a publication: the whole row is sent |

A transaction is sent after it commits, as one message group. A very large transaction is held in memory while it is
sent.

**Give each row one writer.** When two servers both change the same row and replicate to each other, GizDB does not
decide which change stays.

## Query the tables of another server

A **foreign table** is a table of P that the queries of G can name. It is SQL access to another server's data. It is
not a copy and it is not a part of G: P owns the rows, their order of commits and their failover.

On G:

```sql
CREATE EXTENSION postgres_fdw;
CREATE SERVER p_server FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '127.0.0.1', port '5433', dbname 'postgres');
CREATE USER MAPPING FOR PUBLIC SERVER p_server OPTIONS (user 'postgres', password 'change-me');
CREATE FOREIGN TABLE accounts (id int, owner text, balance numeric) SERVER p_server;
```

The foreign table reads the table of the same name in schema `public` of P. Name another one with
`OPTIONS (schema_name '...', table_name '...')` after `SERVER p_server`.

Use it as a table:

```sql
SELECT owner, balance FROM accounts WHERE id = 7;
SELECT o.id, a.owner FROM orders o JOIN accounts a ON a.id = o.id;
INSERT INTO accounts VALUES (8, 'kim', 0);
UPDATE accounts SET balance = balance + 10 WHERE id = 8;
DELETE FROM accounts WHERE id = 8;
```

### What runs where

- The columns the query reads and each condition that compares a column with a constant (`id = 7`, `balance > 100`)
  go to P. P returns only those rows and columns. `EXPLAIN` shows the table as `Read Foreign accounts`.
- Of a text column only `=` and `<>` go to P, because P orders text by its own collation.
- A query that reads only tables of one server P runs on P whole: its joins, GROUP BY, HAVING, ORDER BY, DISTINCT
  and LIMIT. `EXPLAIN` then shows one line, `Read Foreign query:`, with the text P gets. For example
  `SELECT region, sum(amount) FROM sales GROUP BY region ORDER BY region` returns one row for each region, not the
  rows of `sales`.
- A query runs on G over the rows P returned when it also reads a table of G or of another server, has a subquery,
  uses something G cannot write for PostgreSQL with the same meaning, or orders by a text column (ORDER BY, `<`,
  `min`, `max`): P orders text by its own collation. Those rows are held in memory for the statement: put a
  condition on a large foreign table.
- G reads an answer of P page by page. An answer of more than 256 MiB fails with 54000, and G reads no more of it.
- UPDATE and DELETE run on P with your SET and WHERE. G writes the statement for P from what it parsed, with each
  value as a parameter. An expression that G cannot write for PostgreSQL with the same meaning (for example a
  function only GizDB has) is refused with 0A000. INSERT sends its rows as values.
- While P works on a request, the session waits and the other sessions of G run. A server that does not answer in
  30 s fails the statement.

### Consistency

- A statement outside a transaction block is one transaction of P.
- In a transaction block, the statements for P share one transaction of P at REPEATABLE READ. It ends with the block.
- A query over a table of G and a table of P reads two snapshots, one of each server. They are not taken at one time.

### Limits of a foreign table

`RETURNING`, `ON CONFLICT`, `DEFAULT VALUES` and COPY of a foreign table are refused (0A000). A foreign table in the
FROM of an UPDATE or DELETE of another table is refused. `IMPORT FOREIGN SCHEMA` is not supported: write each
`CREATE FOREIGN TABLE`.

## Place partitions on another server

A partition of a partitioned table can be a foreign table. Rows then go to the server of their partition:

```sql
-- on P
CREATE TABLE events_eu (id int PRIMARY KEY, region text, body text);

-- on G
CREATE TABLE events (id int, region text, body text) PARTITION BY LIST (region);
CREATE TABLE events_kr PARTITION OF events FOR VALUES IN ('kr');
CREATE FOREIGN TABLE events_eu PARTITION OF events FOR VALUES IN ('eu') SERVER p_server;

INSERT INTO events VALUES (1, 'kr', 'here'), (2, 'eu', 'there');
SELECT region, count(*) FROM events GROUP BY region;
```

An INSERT routes each row to its partition. A SELECT, UPDATE or DELETE of `events` reaches both. This places data;
it is federation, not a cluster: G does not move rows between servers, P does not fail over with G, and a read of
`events` has no single snapshot.

## Commit on several servers

A transaction block that changes rows on G and on P, or on two foreign servers, commits on all of them or on none:

```sql
BEGIN;
UPDATE orders SET total = total - 10 WHERE id = 1;       -- a table of G
UPDATE accounts SET balance = balance + 10 WHERE id = 7;  -- a table of P
COMMIT;
```

You write nothing more. At `COMMIT`, G coordinates a two-phase commit and records each step in its own log:

1. **Intent.** G commits a record that names the servers, before any of them prepares.
2. **Prepare.** Each server runs `PREPARE TRANSACTION`. From now it can commit or roll back its part, and keeps it
   over a restart.
3. **Decision.** G commits its own rows together with the decision. This one commit is the moment the transaction
   commits. When a server refused to prepare, the decision is to roll back, and `COMMIT` fails with that server's
   error.
4. **Resolve.** A task of G runs `COMMIT PREPARED` (or `ROLLBACK PREPARED`) on each server and then deletes the
   record.

A block that changed rows on one foreign server only commits there with a plain `COMMIT` when G has nothing to
write and nothing to check at its commit. A SERIALIZABLE block that read rows of G is not such a block: the server
prepares, G checks its rows and decides, and a failed check rolls the server back.

### After a failure

| What failed | What happens |
| --- | --- |
| A server refuses to prepare | Every part rolls back. `COMMIT` returns the error. |
| G stops before the decision | The transaction did not commit. After the restart G rolls back each prepared part. |
| G stops after the decision | The transaction committed. After the restart G commits each prepared part. |
| G has standbys that confirm its commits, and they do not confirm in `--sync-timeout` | The commit fails with 08007 before any server prepares. Nothing is committed at G or at a server. |
| G is a group of voters and its leader stops | The voter that wins the next term holds G's log: it commits the parts of a decision and rolls back the parts of a commit that was only begun. |
| A foreign server is away after it prepared | Its part stays prepared, and it holds its row locks there, until G reaches it again. G never ends a part by a time limit. |

G writes one line to its log for each part that waits: `gizdb: commit of GID waits for a foreign server`. The
prepared parts show on the foreign server in `pg_prepared_xacts`, with a gid that starts with `gizdb_`. Do not end
such a part by hand unless G is lost for good: G's log holds its decision. When G finds that a server no longer
holds a part it decided to commit, it writes `a foreign server holds no such prepared transaction` to its log.

`PREPARE TRANSACTION` of PostgreSQL's own `postgres_fdw` does not do this. Here the coordinator is GizDB.

## Prepared transactions

G itself can be a part of a two-phase commit that another program coordinates:

```sql
BEGIN;
INSERT INTO orders VALUES (9, 'lee', 30, now());
PREPARE TRANSACTION 'tx-9';

-- later, from any session
COMMIT PREPARED 'tx-9';      -- or ROLLBACK PREPARED 'tx-9'
SELECT gid, prepared, owner FROM pg_prepared_xacts;
```

- A prepared transaction is a record of G's log. It is still there after a restart, and on the standby that is
  promoted.
- It holds each row it wrote and each value of a unique key its rows take. Another transaction that writes one fails
  at once with SQLSTATE 40001: run it again after the prepared one ended. PostgreSQL makes that transaction wait.
- While a transaction is prepared, a change of the schema fails with SQLSTATE 55006.
- A transaction that changed the schema, used a temporary object, or wrote to a foreign server cannot be prepared
  (0A000).
- `PREPARE TRANSACTION` outside a transaction block fails with SQLSTATE 25P01.

End each prepared transaction. One that stays holds its rows and stops schema changes.

## Monitor

| Question | Query, on the server named |
| --- | --- |
| Which subscribers read from this server, and from where? | `SELECT * FROM pg_replication_slots` (publisher) |
| Which subscriptions does this server have? | `SELECT subname, subenabled, subconninfo FROM pg_subscription` (subscriber) |
| Which transactions are prepared here? | `SELECT * FROM pg_prepared_xacts` |
| Which foreign servers and tables are defined? | `SELECT * FROM pg_foreign_server`, `SELECT * FROM pg_foreign_table` |

A subscription's worker writes each failure to the server's log with the subscription's name, and tries again.

## Troubleshooting

| Symptom | Cause and action |
| --- | --- |
| `CREATE SUBSCRIPTION` fails with 08006 | The publisher is not reached: check the host, the port, the password and, on PostgreSQL, `pg_hba.conf`. |
| The subscriber's table stays empty | The table is not in the publication, or it does not exist on the subscriber. Read the subscriber's log. |
| The publisher's disk fills | A slot is not read. Find it in `pg_replication_slots` and drop the subscription that owns it. |
| A foreign table answers 55000 | The database is open in a program. Serve it with `gizdb serve`. |
| `COMMIT` fails with "prepared transactions are disabled" | Set `max_prepared_transactions` above 0 on the PostgreSQL server. |
| A statement fails with 40001 and names a prepared transaction | That transaction holds the row or the key. End it, then run the statement again. |
| A read of a large foreign table uses much memory | No condition reached the server. Compare a column with a constant, or copy the table with a subscription. |

## Limits

- Logical replication uses protocol version 1 of `pgoutput`. A transaction in progress is not streamed.
- A publication on GizDB sends whole rows of whole tables.
- `pg_stat_subscription` and `pg_subscription_rel` are not kept on a GizDB subscriber.
- A query over the tables of two servers, or of G and a server, joins on G. The rows a server returns are held in
  memory for the statement, at most 256 MiB for one answer. They are not streamed to the client.
- GizDB does not split or move the rows of a table between servers, and gives no snapshot over several servers.
