Contents
- Disaster recovery: restore -> repoint -> recover
- The qualifiers (read these before you page anyone)
- The story, end to end
- pinecone.recover_from_remote(tbl, overwrite => false, ghosts => 'report')
- Access-method tables: misses, on_miss => 'quarantine', and VACUUM
- pinecone.repoint(tbl, new_index)
- Checking backup readiness — pinecone.backup_readiness(tbl)
- Tables with a partition column
- Restoring the Postgres database (pg_dump / pg_restore)
- Restoring both Postgres and Pinecone together
- Who can run these
- Detached storage — pinecone.detach_storage(tbl, force => false) / pinecone.attach_storage(tbl)
- See also
Disaster recovery: restore -> repoint -> recover
For a table on the remote engine (pinecone.promote-managed, any sync_mode, or a pg_pinecone
access-method index — however it got there: pinecone.create_index(engine => 'remote'),
pinecone.set_engine(..., 'remote'), or the raw CREATE INDEX ... USING pinecone) whose Pinecone index
is gone, or whose local vector column was damaged independently of Pinecone (storage corruption, a
mistaken bulk update, a restored-from-an-old-dump database).
A document index (pinecone.track_docs) is the other way round: Postgres holds every document, so
nothing is recovered from Pinecone. After a restore or a lost index, pinecone.backfill_docs(tbl,
clear => true) rebuilds it from the table — see the
runbook.
The qualifiers (read these before you page anyone)
- RPO is never zero.
pinecone.recover_from_remoterestores only what the remote index currently holds. If you got there by restoring a Pinecone backup first, your RPO isbackup-schedule interval + 15 minutes— Pinecone backups only include vectors written at least 15 minutes before the backup was taken, and backups run on a schedule, not continuously. There is no path to zero data loss here; plan capacity/alerting around the schedule interval, not around this tool closing the gap for you. - Restore is always to a new index. Pinecone has no in-place restore —
create-index-from-backupalways creates a brand-new index.pinecone.repointis the second step that re-points a table’s registration at it; there is no third option. - Pinecone documents no backup durability SLA — the gate verifies a backup exists and is
fresh, not a contractual guarantee. A backup existing,
Ready, and recent is the strongest claim pg_pinecone (or Pinecone’s own API) can make about it; treat backups as best-effort, and keep the local vector column populated wherever losing it would actually hurt. - Recover only fixes damage that hasn’t reached Pinecone yet. If a local vector went
NULLthrough the sync trigger (anUPDATE ... SET vec = NULL, aDELETE, mis-set metadata that triggered a delete), that already propagated as a delete to the remote index before you ever runrecover_from_remote— by the time you recover, Pinecone may no longer have that row’s vector either. Recovering after the fact restores only what survived that propagation, not what existed before it.
The story, end to end
-- 1. Something is wrong with the live index (deleted, corrupted, whatever) and you have a backup.
-- Outside pg_pinecone, via the Pinecone console/API: restore the backup to a NEW index, e.g. "docs-restored".
-- 2. Re-point the table's registration at it. Validates dimension AND metric first; refuses a mismatch.
SELECT pinecone.repoint('docs', 'docs-restored');
-- 3. Pull vectors from the (now-repointed) index back into the local table.
SELECT pinecone.recover_from_remote('docs');
-- 4. Watch it to completion (async — one page per worker drain round, resumable across worker
-- restarts; a NOTICE reports final counts when it finishes).
SELECT table_name, backlog, queue_state FROM pinecone.status() WHERE table_name = 'docs';
recover_from_remote also stands alone, with no backup/repoint involved at all — e.g. local
storage lost some vectors independently, but the live Pinecone index (whatever it’s currently
registered against) still has them. Skip straight to step 3.
pinecone.recover_from_remote(tbl, overwrite => false, ghosts => 'report')
overwrite => false(default): a row whose local vector is already non-NULLis left alone — protects a fresher local write racing this job on a table that’s still being written to during the recovery. Passoverwrite => trueto force every fetched vector to win instead (typical when you know the whole table’s local data is suspect, e.g. right after a restore).ghosts => 'report'(default): a remote id with no matching local row is counted (see the completion NOTICE / the job’s ownpinecone.outboxrow while in flight) but left alone.ghosts => 'delete': additionally enqueues that id’s removal from the remote index.- Works on any promoted table,
synced_by = 'trigger'or the pg_pinecone access method (CREATE INDEX ... USING pinecone); a table with a partition column is recovered namespace by namespace. The access-method path REQUIRES application-level write quiescence for the duration of the job — see the next section for why, what happens when it’s violated, and how to get out. - Progress is visible the same way any other outbox job is (
pinecone.status()’sbacklog/queue_state; the job’s ownpinecone.outboxrow while queued). ANOTICEon completion reportsrecovered/skipped (already non-NULL locally)/ghosts/misses/quarantinedcounts (aWARNINGinstead when anything was quarantined).
Access-method tables: misses, on_miss => 'quarantine', and VACUUM
An access-method table’s remote ids are heap TIDs (physical row positions). Any concurrent
non-HOT write — an update to any indexed column, on any index — silently relocates a row to a
new TID, so the job’s fetched ids stop matching. That’s a “miss”, and misses while a row’s local
vector is NULL are how data actually dies: never VACUUM the table while an AM-path recover is
parked on misses — the index’s ambulkdelete remote-DELETEs every dead TID it reaps, and for a
relocated row whose local vector is still NULL, the remote copy under the old TID is the ONLY
copy. This is why AM-path recover requires application-level write quiescence in the first place.
Do not take table locks to quiesce. A SHARE (or stronger) lock blocks the recover job’s own
write-back UPDATE too — the retry just freezes for as long as you hold it. Pause writes at the
application level instead (stop the writers; no lock).
on_miss picks what a chunk does with missed ids:
'park'(default): fail the chunk; the job parks afterpinecone.outbox_max_attempts. Safe — but if rows genuinely relocated, retrying is a DEAD END: the stale TIDs stay listed in the remote namespace and miss again on every retry, forever, while any writes made since queue behind the parked job.'quarantine': copy each missed id’s fetched vector (+ metadata) intopinecone.recover_quarantine— stored asreal[]straight from the fetched f32s, byte-exact, no text round-trip — then ADVANCE past the chunk. The job completes with aWARNINGand the quarantine count. Re-attaching the bytes to rows is yours to do: the quarantinedremote_idis the STALE heap position, so only application knowledge can match bytes back to rows.
The full remediation sequence when an AM-path recover parks on misses:
-- 1. Quiesce application writes to the table (stop the writers — NO table locks).
-- 2. Switch the parked job to quarantine (updates the queued job in place, cursor preserved):
SELECT pinecone.recover_from_remote('my_table', on_miss => 'quarantine');
-- 3. Resume it:
SELECT pinecone.outbox_retry('my_table');
-- 4. It completes with a WARNING; residual misses are now in pinecone.recover_quarantine:
SELECT remote_id, quarantined_at FROM pinecone.recover_quarantine
WHERE relid = 'my_table'::regclass;
-- 5. Re-attach manually. The join key is application knowledge — e.g. a natural key you keep in
-- metadata, or (as here) a pre-loss snapshot of your own:
UPDATE my_table t
SET embedding = q.embedding::vector
FROM pinecone.recover_quarantine q, my_snapshot o
WHERE q.relid = 'my_table'::regclass
AND t.id = o.id
AND t.embedding IS NULL
AND q.embedding = o.embedding::real[];
DELETE FROM pinecone.recover_quarantine WHERE relid = 'my_table'::regclass;
-- 6. Only NOW is VACUUM safe again (the job is complete and every byte is accounted for).
VACUUM my_table;
For access-method tables specifically, quarantined metadata is usually NULL (the AM pushes no
metadata), so step 5 genuinely depends on your own snapshot/keys — quarantine’s guarantee is that
the bytes survive, not that re-attachment is automatic.
pinecone.repoint(tbl, new_index)
Updates pinecone.tables.index_name for tbl — a registry ROW change, nothing else. Requires
tbl to be mode = 'remote' exactly (not mid-'promoting'), and validates new_index’s
dimension and metric against the registry row before touching anything; refuses a mismatch with a
clear error. Refuses while a 'recover' job is queued for tbl (the job’s persisted pagination
cursor belongs to the current index). The namespace is left untouched (Pinecone backup restores
preserve namespaces); so is a partitioned table’s family
<namespace>:*.
Checking backup readiness — pinecone.backup_readiness(tbl)
A read-only diagnostic for a promoted table — it never restores, deletes, or otherwise touches
anything (no shed verbs, no storage state). Every Pinecone call it makes is read-only
(describe_index_stats, list_backups, get_backup_schedule), so it’s safe to run with a
ControlPlaneViewer-scoped key. Returns three rows, one per check:
SELECT * FROM pinecone.backup_readiness('docs');
-- check | status | detail
-- ----------+--------+------------------------------------------------------------------
-- sync | ok | live(remote namespace 'public.docs')=12000 local(non-NULL vector rows)=12000 diff=0 outbox_backlog=0
-- coverage | ok | newest Ready backup 'bkp-1' created_at=... namespace_count=1 dimension=1536; this table's namespace is the ONLY namespace in the index
-- freshness| ok | backup age=612s window=90000s (schedule interval=86400s + max(25%,1h)=21600s + 15min staleness allowance); backup created_at=...
- sync — the remote index’s live per-namespace vector count (
describe_index_stats) against the local table’s non-NULL-vector row count. A nonzero difference is'warn'ifpinecone.outboxhas a nonzero (or invisible-to-this-role) backlog that could explain it — in-flight writes haven’t reached Pinecone yet — and'fail'only when the backlog is visibly zero (a genuine drift, not a race). - coverage — the newest
Readybackup’snamespace_countmust be >= the index’s live namespace count (a smaller one means the backup predates a namespace that exists now — a restore from it would silently omit that namespace), and itsdimensionmust match this table’s registered dimension. Backups are whole-index:detailalso reports whether this table’s namespace is the index’s only one, or the index is shared (a restore brings back every tenant sharing it, not just this table’s data). - freshness — the newest
Readybackup’s age against the window, verbatim:
window = schedule_interval + max(25% of schedule_interval, 1 hour) + 15 minutes
(the 15 minutes is Pinecone’s own staleness allowance — backups only include vectors written at least that long before the backup was taken; see qualifier 1, above). No configured schedule is itself a failing check — absent a schedule, worst-case RPO is unbounded, and pg_pinecone has no schedule-creation SQL surface yet (configure one via the Pinecone console/API, or take manual backups before relying on backup-based DR).
A remote-call failure — a plan-gated 403 (backups are Standard/Enterprise-only), an auth failure,
a network error, or a schedule/timestamp shape pg_pinecone can’t parse — degrades only that row’s
status to 'unavailable (...)', quoting the server’s own message in detail; it never aborts
the other two checks and never raises. The only thing that raises is tbl not being a promoted,
tracked table. Same ACL gate as repoint/recover_from_remote below (SECURITY INVOKER, gated
by pinecone.tables’s own privileges) — nothing extra to grant. For a table with a partition column
it counts the whole family.
Tables with a partition column
A partition column gives every value its own
namespace, so recovery is per namespace. The steps above are the same; they work on the family
<namespace>:*.
repointkeeps the prefix and changes the index. It registers the namespaces the new index holds under the prefix and the registry lacks (adopted N), so a restore that lostpinecone.partitionsstill recovers every tenant.recover_from_remotewalks the registered namespaces in order, a page at a time, and a worker round keeps walking untilpinecone.backfill_round_msis spent (many small tenants take a few rounds, not one each). The job keeps the namespace and the page token, so an unfinished recovery (a worker restart, a parked job retried withoutbox_retry) resumes at the cursor instead of starting over. A vector is written back only to a row of that namespace’s partition: an id (or an access-method TID) that belongs to another tenant’s row by now is a ghost (or a miss, seeon_miss), never a restore, and the partition column is never read from remote metadata. The completion NOTICE addsnamespaces N.backup_readinesscounts the whole family, names namespaces the registry does not list, and warns above 2,000 namespaces in the index: that per-backup figure is unverified, so restore a test copy before you rely on it.- A tenant removed with
delete_partitionafter the backup comes back with the restore: delete it again (runbook).
Restoring the Postgres database (pg_dump / pg_restore)
pg_restore creates pg_pinecone before vector (extensions go in name order), so
CREATE INDEX ... USING pinecone (embedding vector_cosine_ops) used to find no opclass. An event
trigger now creates the opclasses when vector is created, and they belong to the extension, so a
dump leaves them out and the restore ends clean. The same membership means DROP EXTENSION vector
CASCADE drops pg_pinecone too: drop pg_pinecone first if you only mean to reinstall pgvector. pinecone.secret and the queues are not dumped:
store the key again, and pinecone.sync repairs what a queue held.
A restored access-method index rewrites the family it points at. The registry row is dumped and
the restore rebuilds the index, which queues a delete_all and a backfill for the index and namespace
the row names (the whole family of a partitioned table). They wait while the database has no key;
once it has one, the restored database empties that family and fills it with its own heap’s TIDs,
and when the row names production’s index that is production’s data. Restore next to production only
into an index of its own, before the key goes in: repoint a trigger-lane table; for an access-method
table, which repoint refuses, DROP INDEX the restored one (it forgets the row, discards the queued
rebuild and leaves Pinecone alone) and create_index(.., engine => 'remote', index_name => 'restored').
Restoring both Postgres and Pinecone together
The two sections above each assume one side survived — Pinecone gone, Postgres
live, or Postgres restored, Pinecone
live. A full-cluster loss is neither: you
restore a pg_dump/basebackup and a Pinecone backup, taken at different times, and must land on
one consistent state. Order is load-bearing, and the default direction of reconciliation depends on
storage.
Order (do not reorder)
A restored registration rewrites the family it names the instant a key is present (see
above): the rebuilt index queues a
delete_all + backfill against whatever index its row names. If that is your freshly-restored
Pinecone index, you overwrite it with the staler dumped heap. So:
- Restore the Postgres DB with no key.
pinecone.secretis not dumped, so this is the default — just do not store it yet. Every queued rebuild waits. - Sever the restored registration from the restored index before the key goes in.
- Trigger lane:
SELECT pinecone.repoint(tbl, 'restored'); - AM lane (
repointrefuses):DROP INDEXthe restored one — it forgets the row and discards the queued rebuild, Pinecone untouched (see above) — thenSELECT pinecone.create_index(.., engine => 'remote', index_name => 'restored');.
- Trigger lane:
- Store the key —
SELECT pinecone.set_api_key('...');. - Reconcile in the direction set by
storage(next). - Verify (below).
Direction of truth — by storage
storage |
Authoritative copy | Reconcile with |
|---|---|---|
full |
the local heap column | pinecone.sync(tbl) — local -> remote |
detached |
the Pinecone index | pinecone.attach_storage(tbl) (or recover_from_remote) — remote -> local |
full means the heap column is a complete copy, self-consistent as of the dump; push it out and the
restored index matches it. detached means the local column is NULL — Pinecone is the only full
copy, so pull it back.
Tie-break. If the other backup is clearly newer (compare the dump’s time against the backup’s
created_at), invert the default: a full table whose Pinecone backup is newer ->
recover_from_remote(tbl, overwrite => true). Pick ONE direction. Running both clobbers whichever
copy was fresher, and nothing restores writes made after the older of the two backups — worst-case
RPO is that older timestamp (plus Pinecone’s 15-minute lag,
qualifier 1).
Verify
SELECT * FROM pinecone.backup_readiness(tbl); -- the `sync` row must read diff=0, outbox_backlog=0
Then one real query per lane.
Partitioned tables
Everything above holds per namespace for a partition column, with two joint-restore specifics:
repointadopts the namespaces the restored index holds under the prefix (adopted N), so a dump that lostpinecone.partitionsstill re-registers every tenant before you reconcile.- Reconcile walks the family
<namespace>:*and resumes at its cursor; runbackup_readinessfor the whole-family counts and heed its >2,000-namespace warning before trusting a single backup.
Who can run these
Every function on this page — including detach_storage/attach_storage below — runs as the
caller (SECURITY INVOKER) and reads/writes pinecone.outbox /
pinecone.tables, neither of which grants PUBLIC anything — so in practice they are
superuser/extension-owner operations (or a role explicitly granted those tables); there is no
separate table-owner check beyond that ACL gate. The recover job’s local write-backs themselves
execute as the recovered table’s OWNER (with SECURITY_RESTRICTED_OPERATION), so a table owner’s
own triggers/constraints never gain the worker’s privileges.
Detached storage — pinecone.detach_storage(tbl, force => false) / pinecone.attach_storage(tbl)
detach_storage sheds a promoted table’s local vector column (NULLs it in place; the rows and
every other column survive, so JOIN hydration keeps working) and flips it to
storage = 'detached' — Pinecone becomes the ONLY full copy of that column. attach_storage
reverses it, re-hydrating from Pinecone (the recover_from_remote machinery) and flipping back to
storage = 'full' once the recover job finishes.
Read-only until attach. While detached, both the id column and the vector column reject any
write that would change their value: an id change ERRORs unconditionally (the old id’s remote
vector would be deleted with nothing local left to replace it); giving the vector column an actual
new non-NULL value ERRORs too (it’s remote-only while detached — a local-only write can never
reach Pinecone’s authoritative copy); a write that sets it to NULL (the shed pass itself, or any
other one) stays a silent no-op, never a remote delete. Everything else keeps working: a
metadata-only UPDATE on a NULL-vector row (setMetadata, merge-only), DELETE, and TRUNCATE
are all unaffected by storage. attach_storage lifts both guards in the same transaction its
recover job’s last chunk commits.
Owner-tier caveat. The id guard is a
BEFORE UPDATE ... FOR EACH ROWtrigger. A table owner’s ownBEFORE UPDATErow trigger that fires afterpg_pinecone_detached_id_guard(Postgres fires per-row triggers in name order) can still mutateNEW.<id>after the guard has already inspected the old/new ids — re-opening the same “id changed while detached” hole the guard closes. This is the same trust tier asALTER TABLE ... DISABLE TRIGGERor a directUPDATE pinecone.tables: it requires table ownership, which Pinecone for Postgres does not defend against here. Don’t add id-mutating row triggers to a detached table. (This could be hardened by re-checking the id in anAFTER EACH ROWguard that no owner trigger can run after; not built.)
Two preconditions worth calling out before you run detach:
- The id column MUST have a single-column
UNIQUEindex orPRIMARY KEY—detach_storagerefuses without one, and this is never bypassable, not even withforce => true. Without a continuously-enforced unique constraint, anINSERTof an existing id with aNULLvector would create two local rows sharing one id, andattach_storagewould then write the single fetched remote vector onto BOTH rows — silently fabricating data. Add one first, e.g.CREATE UNIQUE INDEX ON mytable (id);. Keep it in place for the whole detached period: the id column is trigger-immutable while detached, but that trigger does not protect the constraint from DDL —DROPping the UNIQUE index/PK after detaching re-opens exactly the duplicate-id fabrication path above (a dup-idINSERTwith aNULLvector becomes possible again). Only drop it afterattach_storagehas completed. - Drop any local pgvector (HNSW/IVFFlat) index on the vector column first. Once the column is NULL it indexes nothing, and leaving it in place makes every subsequent write churn a useless index. Pinecone for Postgres does not drop it for you (it will not silently drop a user’s index); do it yourself before detaching.
The RPO qualifiers above apply verbatim: while detached, Pinecone is the only copy, so your
worst-case data loss is still backup-schedule interval + 15 min, never zero. force => true
bypasses only the backup-coverage gate (a custody choice) — never the id-uniqueness, mode, or
synced_by preconditions. A forced detach with no fresh backup can be permanently unrecoverable
if Pinecone loses the index.
backup_gate = 'forced' is a permanent audit mark, not a live status. pinecone.status() shows
storage, backup_gate ('covered' or 'forced'), detached_at, detach_backup_id, and a
computed worst-case rpo for a detached table — all stamped once, at detach time, from that one
gate check, and never recomputed: status() makes no network call of its own, so a 'forced'
mark stays 'forced' even after a fresh backup later appears. Run pinecone.backup_readiness(tbl)
any time for the live answer to “would the gate pass today” — it never rewrites the stamped
backup_gate value.
See also
runbook.md— parked-queue recovery,NO WORKER, backlog monitoring; applies to a'recover'outbox job exactly like any other op.reference.md— API-key/mode requirements for every function above.