Postgres as Persistent storage for hight critical events

[khanakia]

I have some requirement is like i want to build a persistent database to know which queue are sent to nats, which are in processing and which are processed

think like a Task Queue table in Postgres Database
as i have critical service and we need to log every point of process and in case of anything goes wrong with Nats we can always have the ability to replay those missed critical events

Task Queue Table

id: string
event_name: string (payment_created)
status: string (created, processing, failed)
body: JSON

[alberk8]

@khanakia, Would this fit your requirement

[khanakia]

yes but i do not want to add one more managed solution.

mostly i would like to build myself via Nats

[neilalexander]

Not sure I'm understanding the discussion or why PostgreSQL would be needed here. You can publish into JetStream and wait for a puback for confirmation that JetStream received your message, and after that you can replay it up to as many times as you want if needed (depending on what retention policy you choose or what you decide to do with the message). This is a very common robust queuing pattern.

[neilalexander]

Well, if you don't delete the message manually and you don't set retention policies on the stream that will delete it, then you can either a) create a new consumer to replay all history, or b) use the message get APIs.

[alberk8]

yes when you ack on the message read back then it will be gone. The whole purpose is to use the jetstream to quickly ingest/record the event then use the consumer to persist that back to the Postgres DB.

[khanakia]

Well, if you don't delete the message manually and you don't set retention policies on the stream that will delete it, then you can either a) create a new consumer to replay all history, or b) use the message get APIs.

Yes but it will loose the context of the actual published message

Published (msg a) > consumed(msg a and sent ack and also publish a new msg) > Publish (msg a consumed) > Consume (msg a consumed and save to postgres log)

[ripienaar]

The thing to understand is that acknowledging a message does not remove it from the stream unless you choose a mode that does that.

With Limits policy streams all messages stays even after they were acknowledged and if you make a new consumer over the stream you will get the exact same messages again.

Indeed we do not track to very granular extend who and when a message was ack'd but you can see from any consumer where in the stream its up to and acknowledged.

[khanakia]

How to do that but you can see from any consumer where in the stream its up to and acknowledged

[ripienaar]

Consumer info api calls include it.