A PostgreSQL destination
A destination is a place your pipelines write to. This page is about what a pipeline does on a PostgreSQL destination that you can see from your side, and what it needs from your server.
One writer per pipeline
Section titled “One writer per pipeline”A pipeline writes into your database through one session at a time. When it starts, that session takes a PostgreSQL advisory lock that belongs to the pipeline, and every write transaction first checks, in the same transaction, that this same session still holds that lock. If two workers of one pipeline ever run at once (a deploy that overlaps a running worker, or a worker that outlives its replacement), only the one whose session holds the lock writes; the other writes nothing and stops.
What you can see:
- In
pg_stat_activity, the pipeline’s session is namedreyansh-worker <pipeline id>. - In
pg_locks, that session holds one lock of typeadvisory, in the destination’s database, while the pipeline runs. A lock with the same number in another database of the same server is a different lock, and the pipeline never looks at it.
You do not need to do anything for this. The lock is taken and let go by the pipeline, and it goes
by itself if the pipeline’s connection drops: the session asks the server to notice a dead connection
within about a minute (tcp_keepalives_idle = 30, tcp_keepalives_interval = 10,
tcp_keepalives_count = 3, tcp_user_timeout = 60000, set for its own session only).
One pipeline per table
Section titled “One pipeline per table”A table in your database is written by one pipeline. Two pipelines writing one table would
overwrite each other’s rows, so the second is refused when you create it or start it
(422 destination_table_taken), naming the pipeline that already writes the table. To sync the
table, add it to that pipeline, or point the new one at another database.
Two pipelines are judged to write one table when they write the same schema and table name (compared exactly, as PostgreSQL compares a quoted name) in the same database, which is decided this way:
- the two use the same destination, or two destinations with the same saved credential; or
- their credentials’ connection URLs name the same host, port, database and user. The host is
compared without regard to the case of its ASCII letters, an IP address in any of its spellings
(
2001:db8::1and2001:DB8:0:0::1are one), and the port as5432when none is given. The password andsslmodeplay no part. The user does, because a pooler such as Supabase’s serves many projects at one host, port and database name and tells them apart by the user alone (postgres.<project-ref>): two of your projects there are two databases, and both pipelines run.
What this cannot see. It compares what the URLs say, never where they lead, so these are not recognised as one database, and two pipelines through them are not refused:
- two different users of the same database;
- an IP address and a DNS name for the same server;
- two DNS names for it (a CNAME, a connection pooler, a proxy or a load balancer in front of it);
- a name written with and without a trailing dot (
db.example.com.), or an international name and itsxn--spelling.
So give every destination for one database the same host name and user. Comparing addresses after a DNS lookup was rejected: two different databases behind one load balancer’s address would then be refused as one, and a refusal you cannot act on is worse than one that is not made.
The comparison never opens your credentials and stores nothing about the database you can read: each credential is kept with a keyed fingerprint of its host, port, database and user, which says nothing to anyone without the key your credentials are sealed with, and is never shown by the API.
Connection poolers
Section titled “Connection poolers”A pooler is fine if it gives each client a session of its own and resets it only when the client
leaves: PgBouncer in session mode with its default server_reset_query = DISCARD ALL, or a
direct connection.
These are refused, because on them the pipeline cannot keep a second worker from writing:
- PgBouncer in transaction mode moves a client between server sessions. The pipeline stops at the first write that lands on a different session from the one holding its lock, and says so. (If an earlier client left the pipeline’s lock on the session it is handed, it stops at the start instead.)
- A pooler that resets sessions between transactions, such as PgBouncer with
server_reset_query_always = 1, releases the lock after every transaction. The pipeline stops at its first write, and says so. - PgBouncer in session mode without a reset query hands the next client the session the last one left, lock and all. The pipeline stops at the start, and says so.
What to do: connect the destination directly, or through a pooler in session mode that resets each
session when its client leaves, and not between its transactions. Neon: use the host without
-pooler. Supabase: use the direct connection, not the transaction pooler on port 6543
(Supabase’s session pooler has not been tested with this yet).
Testing the destination on its own
Section titled “Testing the destination on its own”Open the destination from Destinations, then its Test pane, and press Run the test.
The test asks for nothing, and no source is needed: it connects with the connection string you
stored, judges its host as every connection we make is judged, and signs in. It creates nothing
and stores nothing. With the API, send POST /v1/destinations/{id}/test (no body; an
Idempotency-Key, as on every change).
The answer is a list of steps, the same ones, in the same words, as the pipeline form’s test shows
for its destination: finding the server, reaching it, securing the connection, signing in, the
role’s permissions and the pipeline’s table. The last two are skipped: what a pipeline needs of the
role is asked of the table it picks, when it picks one. A connection below verify-full passes with
a warning that says how its rows travel. A database that is not UTF8 fails the permissions step
(see below).
The database’s encoding: UTF8
Section titled “The database’s encoding: UTF8”A PostgreSQL destination must be a UTF8 database. The test above fails a database in any other encoding at its permissions step (and so does the pipeline form’s test, whatever the source), and a pipeline does not start on one: that start writes nothing, and its activity says why, naming the encoding and the database. A pipeline that wrote into such a database before this rule stops at its next start in the same way.
Why it is refused rather than written as well as it can be: a pipeline’s text is Unicode, whatever
your source holds. A database in LATIN1 or WIN1252 holds about 250 characters, and PostgreSQL
refuses a row holding any other (Chinese, Japanese or Korean text, an emoji, most accented letters
outside Western European ones) whatever the column’s type or length, with error 22P05. Written
anyway, such rows would be refused one by one as they arrive, so what reaches your tables would
depend on which characters your customers happened to type. SQL_ASCII is refused too: it refuses
nothing, but it stores bytes unchecked and counts each byte as a character, so a column’s length
would cut or refuse a value by its bytes. Fivetran asks the same of a PostgreSQL database it reads
(UTF-8 encoding), and fails the sync otherwise; this destination tells you at setup instead.
A database’s encoding is set when it is created and cannot be changed, and a destination a pipeline uses cannot be moved to another database: its credential must keep reaching the same database (the same server and database name). So the way out is a UTF8 database under the same name.
1. Make a UTF8 database under the same name. Stop the pipeline. Then, as the database’s owner with
the CREATEDB privilege, or a superuser, connected to another database on the server with no session on
this one, rename the old database away and create the UTF8 one in its place, for a database named
warehouse. Run the two statements one at a time: sent together in one request (psql -c, most
query tools), the second is refused inside one transaction and the first is undone.
ALTER DATABASE "warehouse" RENAME TO "warehouse_latin1";CREATE DATABASE "warehouse" ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;Then create the pipeline’s tables in it (pg_dump --schema-only, with one -t for each of them, of the
renamed database gives their statements) and grant the pipeline’s role what it had (below). The pipeline’s
words give these statements with your database’s own name.
2. Bring the earlier rows into it. What does that depends on your pipeline’s source:
-
A PostgreSQL source (read from its log or by queries): start the pipeline. Each table it wrote into before is a new table there, so it stops, saying it is not the table the pipeline last wrote into; resume it, and the resume copies it again from your source, priced as copy work, as a re-sync is (Usage and billing). If a table does not stop (one the pipeline had not recorded yet), ask for a Re-sync of it.
-
A MySQL, Stripe or history-export pipeline continues from where it stopped, so it does not send the earlier rows again. Copy them across from the renamed database before you start the pipeline, only the pipeline’s own tables (one
-tfor each, and your own-hand-U), for example:Terminal window pg_dump --data-only -t 'public.orders' -t 'public.customers' --dbname='warehouse_latin1' \| psql --single-transaction -v ON_ERROR_STOP=1 --dbname='warehouse'A dump without
-tcopies every table of the database, ours and other pipelines’ included. Then start the pipeline: it stops once, saying each table is not the one it last wrote into, and since you put the rows back, it continues from the next start. (A history-export stream read whole at every sync does not stop: it fills itself again.) -
Or, instead of copying, what a NEW pipeline brings back depends on the source. A new Stripe pipeline (remove this one, then create one into the same destination) reads each stream from its beginning: every object Stripe still holds, not one deleted in Stripe since, and for
eventsonly Stripe’s last 30 days. A new history export of the stream reads it from its beginning, every row the export still serves. A new MySQL pipeline replays only the binary log the server still holds, so for MySQL copying across is the way.
3. Keep the renamed database until the new one holds what you need. No copy brings back a table kept in history mode’s earlier versions, or a row deleted at the source since; they are only in the renamed database.
The locale matters. An encoding must match the database’s locale, unless the locale is C, which takes
any encoding, and TEMPLATE template0 is what allows a locale or encoding other than the template’s
(PostgreSQL: CREATE DATABASE). A server
whose default encoding is LATIN1 usually has a LATIN1 locale, so ENCODING 'UTF8' alone is refused
there (“encoding UTF8 does not match locale”). The C locale exists on every server and sorts text by
its bytes; upper, lower and ILIKE also leave letters outside ASCII as they are under it. For a
language’s sort order and case rules, use a UTF-8 locale your server’s operating system has, such as
LOCALE 'en_US.UTF-8' in place of the two LC_ settings (locale names differ between operating
systems).
Or create a new destination for a UTF8 database, and a new pipeline into it: from a PostgreSQL source it copies your tables as its first run does; a new Stripe or history-export pipeline reads its streams from their beginning, as above; a new MySQL one replays only the binary log the server still holds.
The connection’s own client_encoding must be UTF8 too, which it always is unless something between us
and your server, such as a connection pooler, changes it.
What the role needs
Section titled “What the role needs”Every role may do all of the following unless someone revoked it.
-
Always: the pipeline calls
pg_catalog.pg_try_advisory_lock(bigint)andpg_catalog.pg_try_advisory_xact_lock(bigint)and readspg_locks. If any of these was revoked, the pipeline stops at the start and gives you the statements to run as a superuser:GRANT EXECUTE ON FUNCTION pg_catalog.pg_try_advisory_lock(bigint), pg_catalog.pg_try_advisory_xact_lock(bigint)TO <the destination role>;GRANT SELECT ON pg_catalog.pg_locks TO <the destination role>; -
Always: the pipeline calls
pg_catalog.pg_control_system()to read which database system each of your tables lives in, and records it with the table, so that a table made again, or a destination restored onto another server, is never written on into from where the pipeline stopped (see a pipeline’s tables). If it was revoked, the pipeline stops at the start, tells you so, and gives you the statement to run as a superuser, with your role’s name in it; it writes nothing until it can read it:GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_system() TO <the destination role>; -
Only when another session holds the pipeline’s lock at the start: the pipeline reads
pg_stat_activityto see who holds it, and callspg_terminate_backendto end a stuck worker of its own (below). If either was revoked, nothing stops at the start: the pipeline cannot name or end that session, so it waits and is told as for a session it may not end (below). To restore them:GRANT SELECT ON pg_catalog.pg_stat_activity TO <the destination role>;andGRANT EXECUTE ON FUNCTION pg_catalog.pg_terminate_backend(integer, bigint) TO <the destination role>;.
If you set transaction_timeout (PostgreSQL 17 and later) for the role or the database, make it
longer than a batch takes to write: the pipeline writes each batch in one transaction (below), and
a timeout that ends it ends the session too. The pipeline then stops that batch, says so with the
timeout’s own code (25P04), keeps nothing of it, and is restarted; it does not switch your timeout
off for its own session. Your statement_timeout applies to each statement as before.
When the lock is held
Section titled “When the lock is held”When a pipeline starts and its lock is already held:
- By another worker of the same pipeline (a session named
reyansh-worker <pipeline id>), the new worker waits up to two minutes for it to let go. If it does not (its process stopped, its machine was paused), the new worker ends that one session withpg_terminate_backend, takes the lock, and carries on. The feed then says so:destination_fence_taken_over. Nothing that session had committed is lost: the pipeline carries on from its last checkpoint and writes again what came after. - By any session the pipeline may not end (a session of yours, one of our name that another role
owns, as after the destination’s credentials changed, or one the pipeline cannot see), it is never
ended. The pipeline waits, writing nothing, for up to ten minutes. If the lock is still held then,
the feed says so (
destination_fence_held, naming the session’s process id and application), the worker stops, and the pipeline shows Retrying while it is started again on its retry schedule and waits again. Once that session lets go of the lock, the pipeline carries on at its next restart. The retries last 14 days; if the lock is still held after that, the pipeline shows Broken and stays stopped until you start it again.
Only a session whose application_name is exactly reyansh-worker <pipeline id>, that holds the
pipeline’s lock in the destination’s database, can be ended. If you give a session of your own that
exact name, it can be ended too.
Pausing or deleting a pipeline while it waits for its lock stops the wait at once.
Rows written in one transaction per batch
Section titled “Rows written in one transaction per batch”A pipeline writes each batch, up to 1,000 rows by default, in one transaction. Two things follow:
- While a batch is written, its rows stay locked until the batch commits. A transaction of yours that writes the same rows at the same time waits for it, and if the two wait on each other, PostgreSQL ends one of them as a deadlock; it can be yours. The pipeline retries its own.
- If your database refuses one row of a batch, nothing of that batch is kept at first, and the pipeline writes the batch again one row at a time, so the rows before the refused one are written and the refused one is answered as the pipeline is set to (see values your destination cannot take). A batch that holds a refused row takes two to three times as many round trips to your server as one that does not.
- A batch is written by key, one write for each row as it ends up, which is fewer writes than your
source made and not always in its order. A
UNIQUEconstraint or unique index of yours besides the key (partial or on an expression included), anEXCLUDEconstraint, or a foreign key between rows of the same table can refuse that order where your source’s own order passed: a value that moved from one row to another inside the batch, or a child written with the parent it references. When one does, the pipeline writes that batch again, in one transaction, in the order your source made its changes, and only a batch that order is refused too stops the table, as above. Such a batch takes more statements, and a trigger of yours sees every one of its changes, as your source made them (an audit trigger records more rows; the rolled-back first attempt still uses up any sequence values it took); every other batch is written as before, and the rows and bytes counted for it are the same either way. A constraint declaredDEFERRABLE INITIALLY DEFERREDis checked when the batch commits, on the rows as they end, so the order never matters to it. A foreign key from one table of the pipeline to another is not answered this way: each table’s batch is its own transaction. - If your database refuses a statement on one table (a trigger of yours that raises an error, a rule,
a constraint, a revoked permission), that table alone stops, with PostgreSQL’s words, and every other
table keeps syncing; fix the cause and resume it, and it writes everything from where it stopped. This
holds for a table with no primary key too, and for its purge after each save of the rows it holds
counted down to zero: a
BEFORE DELETEtrigger of yours that keeps one of them removes nothing of that purge and stops the table, and once the trigger lets those rows go and you resume it, the purge after its next change removes them. A purge you asked for is the one exception: it stays pending, and the table cannot be resumed while it waits, so cancel the purge, then resume the table, and ask for the purge again once the cause is fixed and the table is syncing.
Deletes, and soft delete
Section titled “Deletes, and soft delete”A table on a PostgreSQL destination is kept live by default: a row deleted in your source is deleted
here (a Stripe source sends no deletes: a row deleted there stays here as it was; a query-based pipeline finds no
row deleted in your source, so it stays here as it was too: see
Query-based capture). A truncate in your source does
what the pipeline’s truncate rule says, except that a table with no primary key is emptied whatever it says; and when
a table with a primary key is copied again, a row your source no longer has is kept, and counted, until you delete it
(one with no primary key has it removed). A table can be kept in soft delete instead, where the row stays and _reyansh_deleted_at says
when it was deleted; the pipeline then adds _reyansh_deleted_at and _reyansh_synced_at to your table
at its next start, under a short lock timeout, or, when its role may not alter the table, the table
waits for you with the ALTER to run as its owner. Rows marked deleted can be purged on request. See
Re-sync, Sync now, and a table’s sync mode.
Changing the credential
Section titled “Changing the credential”Rotating the connection string of a PostgreSQL destination its pipelines use is checked first: the new
one must reach the same database, judged by the server’s own identity rather than the URL’s text. An
alias (another host name for the same server) is accepted; another database is refused
(409 destination_would_move). When the old credential no longer connects and nothing recorded says
which database it was, the rotation asks you to confirm (409 identity_unproven), and is refused if one
of the pipelines’ tables is missing there.
Your table’s key
Section titled “Your table’s key”A table with a primary key is written by its key, so before anything is written the start asks the
destination table how it holds that key, and a table that would lose a row stops at its start (parked
destination_table_unusable where the pipeline copies it, the pipeline’s start stopped where its source sends
only new changes), told what is wrong and the statement that fixes it:
- a unique index on exactly the key’s columns, with no
WHERE, no expression and notDEFERRABLE, and the key’s columnsNOT NULL(or the indexNULLS NOT DISTINCT); - a key compared as your source sends it. A key column, or its unique index, under a non-deterministic
collation (one that ignores case or accents, for example
CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false)) would call two keys your source keeps apart, such asabcandABC, one row: the second would be written as an update of the first, and a delete of one would delete both, with no error. The fix it names isALTER TABLE ... ALTER COLUMN ... TYPE <its type> COLLATE "C"(any deterministic collation will do, an ICU one included), or, for a unique index that names its own collation, dropping it and creating it again from its own definition (which the words quote) without itsCOLLATE. A table kept in history is asked about the key column’s collation, because its versions are matched by the key; an index’s collation there can only make a write fail with an error, never merge two keys, so it is not asked. One kept as an append log is not asked, because it never matches rows by the key. A collation on a column that is not part of the key is yours: it changes how your queries compare, never which row a write lands in. The question is asked at every start, so a key column altered to such a collation while the pipeline runs is found at its next start, and what was written in between was compared under it. citextcompares without case under ANY collation, so the collation question cannot see it. Acitextcolumn is not one of the types below, so a table whose key iscitextstops at its first row with the column named, before anything is written; declare the keytextinstead.
Foreign keys of yours
Section titled “Foreign keys of yours”The pipeline creates no foreign key, so one on a destination table is yours, and PostgreSQL checks it as each batch is written: a row that references another must find it there. A table is copied in the order of its key, up to 1,000 rows (and 16 MB) to a batch, and the tables of a pipeline one after another, so the order a copy writes in is not one that knows your references:
-
A foreign key from one table of the pipeline onto another stops the pipeline at its start, before anything is written, naming the key and the
ALTER TABLE ... DROP CONSTRAINTthat removes it: a row of the referencing table could arrive before the row it references. -
A table that references itself (
employees.manager_id REFERENCES employees (id), a category tree, a thread of replies) is copied whatever order its key sorts its rows in, as long as every row a batch’s rows reference is in that batch or an earlier one: when a batch is refused, it is written once more in one transaction, every row first with its references to the table left empty and then every row as it is, so a child written before its parent, a chain written backwards and a cycle (AreferencesB,BreferencesA) are all written. A table of up to 1,000 rows of ordinary width is copied in one batch. The rows and bytes counted for such a batch are the batch’s own, once. -
What a batch cannot hold stops the table, with PostgreSQL’s words and the foreign key’s name, and is never written past:
- a row that references one in a later batch, which is common in a large table whose key sorts at random (a UUID);
- any batch out of order on a table with a trigger of yours: a trigger would see each row written without
its reference, and one that keeps the old row on
UPDATEwould keep it so, so such a table is never written that way; - any batch out of order where another foreign key references the cleared column with
ON UPDATE CASCADE,SET NULLorSET DEFAULT: clearing it would change rows of yours outside the batch; - a referencing column declared
NOT NULL, which cannot be left empty for a moment, or a key declaredMATCH FULL.
For each, the words say what to do: save the key’s definition (the words give the
pg_get_constraintdefquery), drop it while the table is copied (ALTER TABLE ... DROP CONSTRAINT ...), resume the table, and add it back once it is syncing:ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID, thenALTER TABLE ... VALIDATE CONSTRAINT ..., checks the copied rows without blocking your writes, and refuses if your source itself holds a row whose reference is missing (a source with no such key can). -
A foreign key declared
DEFERRABLE INITIALLY DEFERREDis checked when each batch commits, on the rows as they end, so the order inside a batch never matters to it; a reference to a later batch stops the table as above. -
Once a table streams, a key that refuses your source’s changes is usually declared differently from your source’s: if your source removes the rows that reference a deleted row with it (
ON DELETE CASCADE) or clears their reference (ON DELETE SET NULL), the parent’s delete reaches the destination first and your key refuses it. The table stops, told so; declare the key the same way as your source’s (orDEFERRABLE INITIALLY DEFERRED), resume, and it catches up.
This follows Fivetran, whose PostgreSQL destination creates no foreign key and whose answer to one that refuses a sync is to drop it; the pipeline writes the tables that reference themselves that it can, and names the key and what to do for the rest. Snowflake does not enforce foreign keys, so a Snowflake destination is never stopped by one.
The types your columns can have
Section titled “The types your columns can have”Your tables are yours, so each column lands in the type you declared, and the line under each column on the
pipeline’s page says how: exactly (your column’s type is your database’s, or text into text), converted
into your column’s type (an integer into a bigint, json into jsonb), or as your database’s own text
(an interval or money column you declared text). A json column stores the text it is given,
unchanged. From a PostgreSQL source that is exactly your source’s text: its spacing, its key order, a
repeated key and every escape, a \u0000 escape and an unpaired surrogate escape included. From a REST
source a field declared json arrives already written again as compact JSON (keys sorted, spacing dropped,
a repeated key’s last value kept, an escaped ordinary character written as the character), and is stored as
it arrives. A jsonb column stores the document as jsonb does: keys in its own order, a repeated key’s last
value kept, spacing its own. It refuses a \u0000 escape and an unpaired surrogate escape (written as NULL
under on_rejected_value = continue, a stop under stop), and also a number beyond numeric’s range or
nesting deeper than the server allows, which are refused like any value the column does not take, never
written as NULL. (Earlier releases wrote a json column through jsonb too, and its line said so; a row
written then keeps jsonb’s spelling until it is written again.) The pipeline writes these:
boolean, smallint, integer,
bigint, numeric (constrained or not, but not with a negative scale), real, double precision, text, varchar, char, bytea,
json, jsonb, uuid, date, timestamp and timestamptz.
A column you declared as any other type (interval, money, inet, xml, a geometry, a composite, an
array, time, …) stops its table, and the pipeline says the column has a type this destination does not
write. Declare such a column text: each value then lands exactly as your source sends it,
1 year 2 mons 3 days for an interval, $1,234.11 for money (formatted by your source’s lc_monetary),
hex EWKB for a PostGIS geometry. Casting it back in a query (::interval, ::money) is then yours to do,
in a session whose settings read it the way your source wrote it.
Blocking, hashing and filtering
Section titled “Blocking, hashing and filtering”Blocking, hashing and filtering what a pipeline syncs has the whole of it. What is particular to a PostgreSQL destination, whose tables are yours:
- Reyansh never drops your column. Blocking a column offers NULL (the default) or keep; the page shows
the
ALTER TABLE … DROP COLUMNstatement for you to run if you want it gone. - Setting a column to NULL clears it in every row of your table, rows you wrote yourself included, in
chunks by key under a 5 second lock timeout. A column you declared
NOT NULLwithout a default stops the table with the statement that lifts it, because every new row would be refused. - A hashed column keeps your type. It must hold 44 characters of text; a column that cannot stops the table with the statement that changes it, and nothing of the table is written until you resume it.
- Rows a new filter no longer matches, on a table with a primary key kept live or in soft delete, are kept or removed as you choose; kept, in soft delete, they are marked deleted by its copy all the same. Removed means every row of the table outside the filter, judged on the table’s own values and counted first, rows Reyansh did not write included; they are kept instead (in soft delete, marked deleted by its copy), and the history says why, when the filter uses a hashed column, a column whose type here differs from your database’s, or the role may not delete from the table. A table with no primary key is not asked: they are marked deleted in soft delete, removed in live.
- How each column is kept is recorded in
_reyansh_shaping_v1beside your tables, compared at every start.