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 |
| Read and write a table that another server holds | A foreign table | Query the tables of another server |
| Keep some partitions of a table on another server | A foreign partition | Place partitions on another server |
| Change rows on several servers in one transaction | The two-phase commit | Commit on several servers |
| Take part in another coordinator's transaction | PREPARE TRANSACTION | Prepared transactions |
| Keep a whole copy of one GizDB server for failover | A standby | Operate 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:
wal_level = logical # P publishes changes
max_prepared_transactions = 10 # P takes part in a two-phase commitA 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:
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:
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:
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.
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:
CREATE PUBLICATION dbz_publication FOR TABLE orders;connector.class=io.debezium.connector.postgresql.PostgresConnector
plugin.name=pgoutput
publication.name=dbz_publication
slot.name=dbz
table.include.list=public.ordersThe 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
- Create the tables on the new server and subscribe it to the old one.
- Wait until the new server holds the rows of the old one.
- Stop the writes of the application, wait for the last change, and point the application at the new server.
- 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:
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:
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:
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.EXPLAINshows the table asRead 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.
EXPLAINthen shows one line,Read Foreign query:, with the text P gets. For exampleSELECT region, sum(amount) FROM sales GROUP BY region ORDER BY regionreturns one row for each region, not the rows ofsales. - 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:
-- 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:
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:
- Intent. G commits a record that names the servers, before any of them prepares.
- Prepare. Each server runs
PREPARE TRANSACTION. From now it can commit or roll back its part, and keeps it over a restart. - 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
COMMITfails with that server's error. - Resolve. A task of G runs
COMMIT PREPARED(orROLLBACK 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:
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 TRANSACTIONoutside 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_subscriptionandpg_subscription_relare 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.