Was wondering how realtime postgres (supabase: streams all changes via websocket) could be implemented, my notes notes on what I have learnt so far.
AWS Tutorial: Send DB Changes To Kinesis
Slots give us changes since when they were created, but how do we get the exact state of database at that precise moment, so that we have a guarantee that between that if snapshot DB at that moment, and apply all changes in slot we have 100% changes in our S3 backup?
Clue 1: postgres has a command,
When a new replication slot is created using the streaming replication interface, a snapshot is exported.
But, how to get the id of this snapshot? This is how we create the slot:
SELECT * FROM pg_create_logical_replication_slot( 'regression_slot', 'test_decoding' ); slot_name | xlog_position -----------------+--------------- regression_slot | 0/16B1970 (1 row)
xlog_position the snapshot id?
According to someone its defined as:
xlog_position corresponds to the XLOG position where logical decoding starts.
Doesn’t sound like.
Another question: is there a way to not actually delete the message on
get() till it’s written safely by client, else we can have data loss.
Seems to me PostgreSQL made a mistake in its design.
The format in which those changes are streamed is determined by the output plugin used.
This is bad because it requires you to install output plugin on server. Installing plugin on server has two problems.
What they should have done is pick any streaming-compressible binary format, and allowed each client to convert it to whatever they wanted.
It would be helpful to have a way to know what changed why, say what web request/script/cron deleted something. Can we do it if everyone co-operated (and investigate non cooperating client changes)?
xidis available to output plugins: in explicit transaction, we can find web requests transaction id, and correlate all changes by a web request with DB changes, but what about in auto-commit mode? Can we simply log
txid_current()before every query (or by adding
RETURNING txid_current()), and expect it to show up in output plugin output?
select txid_current()won’t work.