GizStack
View .mdOpen in ChatGPTOpen in Claude

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 toUseSection
Keep a copy of tables on another server, or move data with no stopLogical replicationReplicate tables
Read and write a table that another server holdsA foreign tableQuery the tables of another server
Keep some partitions of a table on another serverA foreign partitionPlace partitions on another server
Change rows on several servers in one transactionThe two-phase commitCommit on several servers
Take part in another coordinator's transactionPREPARE TRANSACTIONPrepared transactions
Keep a whole copy of one GizDB server for failoverA standbyOperate the GizDB server

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 owns the design and its invariants.

Before you start

  • A GizDB server (Operate the GizDB server). 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). 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

ReplicatedNot replicated
INSERT, UPDATE and DELETE of the published tables, each transaction whole and in orderDDL: create and change the tables on both sides yourself
The rows a table held when the subscription beganTRUNCATE, and the values of sequences
NULL, numeric, text, boolean, date and time values as text of the same formA 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 failedWhat happens
A server refuses to prepareEvery part rolls back. COMMIT returns the error.
G stops before the decisionThe transaction did not commit. After the restart G rolls back each prepared part.
G stops after the decisionThe transaction committed. After the restart G commits each prepared part.
G has standbys that confirm its commits, and they do not confirm in --sync-timeoutThe 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 stopsThe 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 preparedIts 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

QuestionQuery, 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

SymptomCause and action
CREATE SUBSCRIPTION fails with 08006The publisher is not reached: check the host, the port, the password and, on PostgreSQL, pg_hba.conf.
The subscriber's table stays emptyThe table is not in the publication, or it does not exist on the subscriber. Read the subscriber's log.
The publisher's disk fillsA slot is not read. Find it in pg_replication_slots and drop the subscription that owns it.
A foreign table answers 55000The 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 transactionThat transaction holds the row or the key. End it, then run the statement again.
A read of a large foreign table uses much memoryNo 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.