Copy a Postgres table or view

You have a table or a view in a PostgreSQL database of your own — a mirror of a ticketing system, a reporting view a job refreshes — and you want to ask Prism questions about it. This page connects it as a context stream that Prism copies on a schedule: one dated copy per run, kept for as long as you say, every question answered from the copy in Prism's own memory. Your database sees one statement per scheduled run and nothing between runs.

That is the right shape for a source that knows only "now" and whose views are expensive to evaluate. It is the wrong shape for a database that already holds its own history and is cheap to read: that one wants to be read in place, and reading a Postgres in place is not something this release does. If your table carries history and you would rather not hold a copy, say so before connecting it; the copy will still work, it will just be a copy.

1. What to ask your DBA for

What	Detail
One view (or table)	Prism reads exactly one, through the SELECT you write into the recipe, and what that SELECT returns is the whole of what Prism ever sees. Cut a view if the table has columns you would rather not hand over: the view is your access control
A read-only login	One service login for the appliance, never one per reader. Give it USAGE on the schema and SELECT on the one view, and nothing else. The grant is the control; the session Prism opens is read-only as well, but that is defence in depth, not the fence
A role, if you separate them	If the login should assume a role for the read (SET ROLE), you need its name and a grant of that role to the login. The recipe takes it as a connection value — see the session settings below. If not, the login's own privileges are what the read runs under
The network path	The appliance's pods have to reach the database's host and port. A pg_hba.conf rule that admits the appliance's address for this login, over TLS, is the usual shape
TLS	Whether the server presents a certificate, and whether it chains to a root the appliance carries. See sslmode below — the default encrypts and does not verify, which is right for a private CA and wrong for nothing
The statement_timeout you will tolerate	The one statement a copy runs is bounded by a ceiling you set in the recipe, and the ceiling covers the whole of the copy — every fetch from the cursor, not each on its own. Ask how long the view takes to evaluate cold, and set the ceiling above that with room: a server-side cursor evaluates the view without parallel workers, so it can take several times longer than the same SELECT run whole on a server that parallelises it
idle_in_transaction_session_timeout for this login	Prism holds one read-only transaction open for the whole copy, and between two fetches it is writing the previous chunk into its own memory — idle, from the server's side. If the login's (or the cluster's) idle_in_transaction_session_timeout is shorter than that gap, the server ends the session mid-copy and the copy is abandoned, by name. Ask for it to be set for this login above a few seconds, or leave the recipe's fetch_size large so the gaps are few

2. The five connection values

You enter these on the source in Admin → Context streams, not in the chart. They are values about your database, and the appliance keeps them beside the source rather than in configuration, so the probe that switches a source on is a probe of the connection that will copy.

Value	What it is
db_host, db_port	The server. Port 5432 is the default when you leave it empty
db_name	The database the session is opened to. Required: a Postgres session is opened to one database and there is no "the login's default"
db_schema	The schema your view lives in, spelled exactly as it was created — Sales is not sales. It becomes the session's search_path, quoted, and the probe checks the session actually landed there (see section 5)
db_user	The login. Configuration, not a secret: it appears in the server's log on every failed sign-in, and somebody has to be able to read it to fix one

The password is entered on the same form and is stored sealed; it is never a connection value.

The session settings, and changing them after you register

The lines in this table are not the address of your database but how Prism sets up its session once it is signed in. The three in the recipe are marked YOURS, and these lines — with the connection parameter role_parameter names — are the only part of the connection you can change after the source is registered.

Line	What it does	The recipe's value
sslmode	The TLS posture of the connection, in PostgreSQL's own meanings: require encrypts and does not verify the server's certificate, which is the right posture for a private-CA or self-signed server; this release carries no root certificate for a source's server, so verify-ca and verify-full cannot pass and are refused before anything is dialled. disable is clear text, and so is allow — even against a server that does offer TLS, because it tries an unencrypted connection first and only upgrades if the server refuses one. prefer is clear text against a server that does not offer TLS: it connects unencrypted rather than fail. Against a server that does not offer TLS at all, require fails rather than falling back to clear text. Whichever you choose, the probe reports whether the session it opened was actually encrypted, and the source's Overview shows it	require
statement_timeout	The ceiling on the one statement a copy runs, over the whole copy (section 1 says why a cursor needs more room than your console). A view that cannot be copied inside it is not copied at all. Deleting the line removes the ceiling on the copy as a whole. Two bounds still apply: a statement_timeout your DBA set on the login or the cluster, and Prism's own bound on each fetch. A single fetch that returns nothing for an hour ends the copy with a message that says so (ingest.engine.databaseFetchTimeoutSeconds). A copy that keeps delivering rows is never stopped by that bound, however long it runs	PT10M — ten minutes
read_only_role	On Postgres this line does nothing. It records the name of a role you might grant, and Prism issues no SET ROLE for it: the session runs with the login's own privileges, whatever it says. It is not a requirement to create a role of that name	prism_reader
role_parameter	Not in the recipe. If the session should assume a role, this is the line that does it: it names a connection parameter holding the role, and the session issues SET ROLE to it — the probe then signs in as that role and reports it. It replaces read_only_role; the two cannot both be set	—

How to change one after registering. Edit the source's manifest — Admin → Context streams, or PUT /api/admin/sources/<id>/manifest with the edited document. Three things happen together:

• the stored password is kept, so there is nothing to re-enter — if one was stored. A password whose probe fails is never kept, and under sslmode: require a server with no TLS fails every probe, so on that path nothing was stored: make the edit, then enter the password again, which probes it under the new settings;
• the probe verdict is withdrawn and the source goes back to registered, because that verdict was about the old settings;
• the next probe runs under the new settings. Probe it, then approve and enable it again.

To have the session assume a role, make one edit: delete the read_only_role line, add role_parameter: db_role, and declare db_role under parameters. Give it the role's name either as the declaration's default (db_role: {type: string, default: <your role>}), which is what the page can send, or through the API as "parameters": {"db_role": "<your role>"} beside the manifest. That body key takes only a parameter a session setting names.

What cannot be changed that way. The server (db_host, db_port), the database, the schema, the login, and the stored credential's name are what the probe verified the password against. An edit that changes any of them is refused and names the line; changing one is deleting the source and registering it again, which also means entering the password again.

Before you register, then, the three questions to settle with your DBA are the ones this table raises: does the server offer TLS, how long does the view take to evaluate cold, and should the read run under a role. None of them is final — but a probe under the wrong answer fails, and it is quicker to start right.

3. What the recipe needs your view to have

• A key. One column, or a combination, that identifies a row uniquely within one copy. In the worked example it is the ticket number. The copy is one row per key per day.

  Uniqueness is checked against your rows, not taken on trust, and a copy that fails it is refused. Two rows sharing a key cannot both be stored — the second would overwrite the first — so a copy that read 100 rows and can store only 98 is refused with the two numbers, the key, and what to do, rather than landing 98 and reporting 100. The previous copy stays in place and the source reads stale, which is the safe direction: a short copy that reports itself complete makes every figure drawn from it quietly low.

  The self test shows the refusal's sentence, and Admin → Context streams does not yet (prism #1445). Since 0.16.0 the self test's CONTEXT STREAMS section has a line for every source in your registry — unless the registry itself could not be read, which its source registry line says — and a switched-on source with a failure recorded since its last completed run says so there, with the first 160 characters of the reason. This refusal is a sentence Prism composed, so it is shown as written; a raw driver error is shown as its type alone. The Context streams page shows none of it: the source keeps its usual word, and nothing on that page says this run was refused. For the whole of what is recorded, read the ingest block of the diagnostic bundle — download it from the self test page — under this source's name, as last_failure.error.

  Confirm it before you register, with the key your recipe declares:

  SELECT count(*), count(DISTINCT (<your key columns>)) FROM <your view>;

  If the two numbers differ, the key is not a key for this result set. Do not simply pick a column that looks unique: a nearly unique key is the case this refusal exists for, because it loses a handful of rows out of thousands and nothing else would show it.

  Before the stream has ever run, either fix works — make the SELECT return one row per key (GROUP BY, DISTINCT ON, or a filter), or declare a wider key that is genuinely unique. Note that "ever run" is the boundary, not "ever copied successfully": the table is created on the first attempt, before any rows land, so a run that was refused has already fixed the key in place.

  After that, prefer the SELECT. The key you declare becomes the copy table's primary key, and it is set when that table is created; adding a column to the key in your recipe does not change the table that already exists, and every run afterwards fails with "there is no unique or exclusion constraint matching the ON CONFLICT specification" — a message that names nothing you edited. If you do want a wider key on a stream that has already run, the table has to be recreated, which discards the dated copies it holds: ask us before doing it, because for most views the one-row- per-key SELECT is the same outcome without losing the history.

• An event time, a timestamp the trends group by — when the ticket was opened, in the example. If the column is a timestamp without time zone, say in the recipe which zone it is written in (naive_timezone); a timestamptz needs nothing.

• The person, by the identifier your directory uses — an employee id, in the example. This is what lets a question about a ticket become a question about a person or a team. Without it the copy is still a copy; it just does not correlate.

• Everything else is yours to include or leave out.

Postgres folds an unquoted column name to lower case, and so does Prism. If your view has a mixed-case column, quote it in the recipe's SELECT and alias it in lower case; a name that does not match the result set exactly is refused by name before a row is read, rather than silently becoming an empty column.

What a column lands as. A numeric keeps its digits, a timestamptz its instant, a date its date, json/jsonb its text. Into a string column: a uuid lands as its text, an array or a composite as one JSON document (nested to any depth), a bytea as Postgres's hex text (\x0102), a range as Postgres's own form ([1,10)), a bit string as its bits, an inet as its text. An interval lands as an ISO 8601 duration, with one caveat: the driver flattens months to thirty days before Prism sees the value, so 1 month 15 days arrives as P45D — cast the column to text in the view if the months matter. Anything else, cast to text in the SELECT and it lands as that text.

If your server's encoding is SQL_ASCII

Ask your DBA, or run SHOW server_encoding; as the service account. Most servers answer UTF8, and nothing in this section applies to them.

A SQL_ASCII server stores whatever bytes a writer sends and never checks them. Prism's Postgres client always asks for text in UTF-8, so the server checks each value on the way out. Before 0.15.1, one value that was not valid UTF-8 made the server refuse the whole statement with 22021, and the stream copied nothing. The usual cause is an emoji written by a Java or ODBC application as CESU-8. Your own SQL client usually shows the same row without complaint, because it asks for a different encoding and nothing is checked.

On a SQL_ASCII server, Prism now reads the text columns as bytes and decodes them itself, one value at a time:

• valid UTF-8 is kept exactly as it is;
• an emoji written as CESU-8 becomes the character it stands for;
• any other invalid byte becomes � (U+FFFD). The value's other characters are kept.

The copy completes. When any value in a stream needed one of the last two steps, the run log has one line for that stream, like this:

  changes: 3 text value(s) repaired (CESU-8 surrogate pairs rejoined), 1 with invalid bytes replaced by U+FFFD — …

Limits: when the stream is refused instead

In three cases, repairing or replacing a value would give a wrong copy, so the stream is refused instead. Each refusal names the column and stops only its own stream. The source's other streams still copy.

What holds the bad bytes	What happens	What to do
The key of a keyset-paged stream, on the last row of a page	Refused. The next page starts from that key, and the repaired key is not what your server holds, so starting from it could skip rows. A repaired key in the middle of a page copies normally.	Use paginate: none, the recipe's default, which reads the whole view in one pass. Or key the stream on a column that holds no such text.
A column declared identity:, when the value could only be read by replacing bytes	Refused. Two different people, such as employee ids E + one stray byte and E + another, would both become E� and be treated as one. The directory crosswalk refuses such a pull the same way: it keeps the identities it pulled last time, and uses them until the value is repaired. A CESU-8 emoji is repaired exactly and is never refused.	Repair the value at the source.
The key, where two keys become the same text after repair, for example one emoji stored as CESU-8 and the same emoji as valid UTF-8	Refused because the key is no longer unique. The refusal says the key is not unique and to return one row per key from the SELECT; here the SELECT already does, and it is the repair that made two keys equal.	Repair one of the two values at the source.

The source is never changed. To find the rows at the source, run the stream's SELECT in your own session after set client_encoding to 'UTF8';. Postgres then refuses at the first such value and names its bytes.

What this does not cover. On a SQL_ASCII server this applies only to columns of these types: text, varchar, char(n), name, json, jsonb, xml, citext, and domains over them. A column of any other type that holds invalid bytes, such as an array, a composite, an enum or a tsvector, is still refused with 22021. Cast it to text in the view's SELECT and it is read like any other text column. A WIN1252 server has the same kind of problem with a different code. Five byte values have no character in that encoding, and a value that holds one of them is refused with 22P05. Prism does not repair that case. Fix the value at the source, or leave the column out of the SELECT.

4. The worked example, and which parts are yours

Open the recipe from Add a context stream — it is Service desk (Postgres copy). Every line you have to decide about carries one of three markers, in a comment on the line itself:

• STRUCTURAL — Prism needs this. Delete it and something stops working, and the marker says what.
• YOURS — yours to decide: the view, every column's name as your view spells it, the schedule, how long to keep copies, the TLS posture, the ceiling.
• OURS — there are none in this recipe. The example is fictional; every line is either Prism's or yours.

The SELECT is the heart of it. Replace the view name and the column names with your own, keep the aliases the recipe's from: lines expect (or change both together), and keep it a plain SELECT — no writes, no semicolon; the recipe is refused otherwise.

How the copy is taken is set by one line, and the default is the one to keep:

paginate: {type: none, fetch_size: 1000}

none means one statement, streamed through a server-side cursor inside one read-only transaction. A view over a dozen joins is evaluated once per copy however many rows it returns. The other two strategies the recipe can name, keyset and offset paging, re-run the statement once per page; measured on a real Postgres that is one evaluation of the view against eleven for a hundred rows at ten a page. They exist for a plain table with a unique key that is cheaper to page than to stream, and for nothing else.

What one copy date promises, and the one case where it promises less. When a source has several views, they are all read inside that one read-only transaction, so every view in a dated copy shows your database as it was at the same instant. That holds for every run that copies cleanly. If a run refuses one view — it did not finish inside the statement timeout, say — the session ends with it, and the views copied after it are read from a new transaction a moment later. Their rows carry the same as_of as everything else, so nothing in the data shows it: in that date's copy, the views before the refused one and the views after it are not from a single instant. It matters only if you correlate two views within one dated copy, and the next run that copies cleanly replaces that date's copy from one transaction again.

What is never split is a single view: each view's rows come from one transaction, however many pages the copy takes, refusal or no refusal.

schedule is a cron expression in UTC; keep is how long copies are held (P90D is ninety days). A copy of N rows taken daily and kept ninety days is about 90 N rows in Prism's memory; Limits sizes it.

How a question reads the copies is set by two lines you should keep. The table holds every copy Prism has taken, one row per ticket per copy, so a count over the whole table would count each ticket once per day it was copied: ten tickets copied on two days would answer twenty, and a window on the ticket's own date would not remove the second copy, because both copies carry the same date. So the recipe's model declares is_latest_snapshot, true for the rows of the most recent copy, and applies it by default:

default_filters:
  - is_latest_snapshot: true

Every question reads the latest copy unless it says otherwise, and "how many tickets were opened last week" is the latest copy's rows opened last week, each ticket once. A question that genuinely means between copies — did the open backlog grow since last Monday's copy — names the field in its own filters (is_latest_snapshot: {is_null: false}) and groups by as_of; naming the field is what lifts the default, and grouping by as_of alone does not. The field's own line names the table a second time, inside its subquery; if you rename the stream's table, change both.

5. Register it, and what the probe does and does not prove

Sign in as a super admin and open Admin → Context streams. Pick the recipe, open Review the manifest and replace the document with yours. Enter the connection values and the password, then probe.

Then approve the source, and only then enable it — enable is refused from probed.

A passing probe means three things and no more:

• the server was reachable, on the TLS posture the recipe asks for;
• the credential was not rejected;
• the session Prism got is the one it asked for — the database you named, and the schema you named.

The third is worth its own sentence, because Postgres does not refuse a search_path naming a schema that does not exist, or one your login cannot see. It accepts the setting and quietly resolves past it, and every real statement then fails to find your view. So Prism reads the session's schema back and refuses on your behalf: a probe that says the schema resolved to nothing means the schema is not there for this login, or the login (or the role it assumes) has no USAGE on it.

The probe's encryption reading is about Prism's own connection. The source's Overview says whether the connection Prism opened was encrypted, because sslmode is editable on a registered source and two of its values are clear text. It is read on Prism's side of the wire, so behind a connection pooler — PgBouncer, pgpool, RDS Proxy — it describes Prism's link to the pooler, and says nothing about the pooler's own link to the database. Where you connect through one, the posture that matters for the second hop is configured on the pooler and is yours to check.

A passing probe does not mean your view exists, that the login can read it, or that any column you named is real. The first copy finds that out, and refuses by name — the view, the column, the privilege — never with an empty copy.

6. Verify it — with your questions, against your figures

Prism ships three example questions on the recipe; they are written for the fictional example and you should replace them with yours. Good ones are the ones where you already know the number: how many tickets were opened last week, against your own report for the same week; how many were resolved last month by group, against your service desk's own view. Ask them on the verification screen and mark each correct, wrong or skipped. Only a question you have marked correct earns the badge a reader sees beside an answer.

If something is wrong

• The copy loads nothing and the job is still running — see A source loads nothing, writes no error, and its job is still running. From 0.16.0, wait for the bound: most stalled copies then stop with a line saying what stopped them.
• The probe says 28P01 or 28000 — the password, the login name, or a pg_hba.conf rule that does not admit this login from the appliance's address.
• The probe says 3D000 — the database you named does not exist for this login.
• The probe, or the first copy, refuses saying the server does not offer TLS — under the default sslmode: require, a server without TLS is a refusal that names this line, on the first attempt and without a retry. A test instance without TLS is the usual way to meet it. Confirm with your DBA before writing disable or allow, both of which are clear text wherever they are used, and prefer, which is clear text against such a server; the probe reports which it got. You can write it on a registered source by editing its manifest — see the session settings — and then probe again.
• The first copy refuses with 42P01 — the view is not in the schema the session landed in.
• The first copy refuses with 42501, insufficient privilege — the login can see the view and may not read it. Grant SELECT on it to the login; or, if your standards grant it to a role the login does not inherit, set role_parameter to that role (see the session settings), so the session assumes it.
• The first copy refuses with 42703, naming a column — the column is not in the result set as spelled. Postgres folded it, or the alias differs from the from: line.
• The first copy says the statement did not finish inside statement_timeout — raise the ceiling, or ask your DBA whether the view can be materialised or indexed; the copy is not retried on a timeout, because a statement too slow once is too slow six times. The ceiling spans the whole copy — the cursor is drained under one clock, not one per fetch — and a cursor evaluates the view without parallel workers, so a view that runs in seconds on your console when it is parallelised can need a longer ceiling here. If the message says the ceiling is one the source's role or cluster carries, the recipe set none and the login's own statement_timeout fired: set one in the recipe, above it. Either change is an edit of the source's manifest, followed by a probe, approve and enable — and a copy is next attempted at the schedule's time, or when you ask it to run now.
• The probe says it could not assume the role (42501, 22023 or 42704) — the role role_parameter names does not exist, or is not granted to the login: create it, or grant it to the login.
• A copy is refused as SESSION-CLOSED, naming idle_in_transaction_session_timeout — the server ended the session between two fetches, while Prism was writing a chunk into its own memory. Ask your DBA to set idle_in_transaction_session_timeout for this login above a few seconds (section 1), or raise the recipe's fetch_size so the gaps are fewer and the chunks larger. Nothing of the copy was kept.
• A copy is refused with 22021 or 22P05: a text value will not convert to UTF-8. Prism's Postgres client always asks the server for UTF-8. Your own SQL client may ask for something else, and then it shows the same rows without complaint. On a SQL_ASCII server Prism repairs most such values itself (see If your server's encoding is SQL_ASCII), so there this code means one of the cases that section lists as not covered. To see the value, run the stream's SELECT in your own session after set client_encoding to 'UTF8';. Postgres then names the bytes and the encoding. Usually the fix is one of three: leave the column out of the SELECT (or cast it to text on a SQL_ASCII server), repair the row at the source, or ask your DBA about the database's encoding.
• A copy stops with no answer to connect, session setup or begin within 60s — the server, or something between it and Prism, stopped answering at that step. None of those steps touches your view, so each has a fixed 60-second limit. The copy is tried six times, each failed attempt logs database error (no answer to … within 60s), and then it stops, about nine minutes after it began. Press Test on the source: if Test also fails, the network path is down. If Test passes, look for a firewall, NAT or load balancer that drops idle connections.
• A copy stops saying the statement did not finish inside the engine's own 3600s bound on one step of the statement — the manifest sets no statement_timeout, and one step of the statement returned nothing for an hour. A copy that keeps delivering never hits this. The step can be waiting on your server (a lock on the view's tables, from a reload or a refresh, or a queue); the view can need longer than an hour before its first row, because it sorts or aggregates everything first; or something between Prism and your server dropped the connection without closing it. Prism turns on TCP keepalive on the connection, so a connection whose far end has gone away usually fails within about two minutes instead. For the long view, set statement_timeout in the manifest to how long the copy needs, which replaces this bound.
• A copy is refused for a wall clock that "never happened" — a timestamp without time zone holds a value inside the hour the clocks skipped in the zone you declared. Convert the column to timestamptz in the view's own SELECT (AT TIME ZONE '<zone>') and drop naive_timezone.

When the code is not in this list

Prism never prints your server's own error message. The message can quote the statement, the login and sometimes a value. The message is also the fastest way to find the cause, so read it yourself, in a session set up the way the copy sets one up. Sign in as the service account and run:

set client_encoding to 'UTF8';
select set_config('statement_timeout', '<the recipe''s statement_timeout, in ms>', false);
select set_config('default_transaction_read_only', 'on', false);
select set_config('TimeZone', 'UTC', false);
select set_config('role', '<the role>', false);          -- only if the recipe sets role_parameter
set search_path to "<your schema>";
begin isolation level repeatable read read only;
<the stream's SELECT, with the window values filled in>;

The copy runs the same settings in this order. The first line is the one your own client does not normally send.