How to restore a postgres database?

[williamdclt]

Because SpiceDB uses postgres's internal txids, it's been identified that standard restore operations (pg_dump/pg_restore) do not work as the new database would not have the same txids. This in itself is fairly unexpected, I don't think that anybody who's not privy to the internals of spicedb would expect it.

As far as I can see, there's no documentation of how to restore a spicedb database. A quick search (and some awareness of discussions in Discord) led me to Zed's backup/restore, and spicedb's BulkExportRelationships/BulkImportRelationships. However, I couldn't find documentation of what do these actually do (does zed backup include the schema? How does the zed operation relate to the spicedb one?), how to use them, what's the expected load on the database, and the Bulk operations are flagged as "experimental" so I'm not confident with them.

Things I'd love:

• A full documentation of how to backup/restore a database
• Ideally (not sure that's possible with the current design), making sure we can use standard tooling like pg_restore with spicedb. At least making sure that the "freshest" snapshot can get used in a restored database, even if other snapshots are unusable

[josephschorr]

does zed backup include the schema?

Yes, it does a full backup of the schema and all its relationships, but not relationship history

How does the zed operation relate to the spicedb one?

The zed operation calls the SpiceDB APIs

the Bulk operations are flagged as "experimental" so I'm not confident with them.

This is mostly in case we decide to change the API contract itself; they do work

[vroldanbet]

@williamdclt a newspicedb repair command was built specifically to address this problem: #1642. It should become available in the upcoming 1.28.0 version.

[FernandoArteaga]

Hey @williamdclt thanks for asking. I was facing the same issue and it was driving me nuts

[FernandoArteaga]

Hey @vroldanbet I tried to run the commando to repair the spicedb restore but I got this error, do you know or have any idea what it can be? Thanks

PS: I use Postgres v14.10

$ spicedb datastore repair transaction-ids

1:24AM ERR terminated with errors error="ERROR: function max(xid8) does not exist (SQLSTATE 42883)"
command terminated with exit code 1

[vroldanbet]

I believe that function is only available from a specific Postgres version, can't tell which one from the top of my head. What I can say is that we've started recommending using >= 15 because it addresses some issues with the query planner that affected GC.

[FernandoArteaga]

Thanks for the feedback!