I'd handle errors and retries by having a workflow table to track the state of each message and then have a background process come along and reinsert the relevant event into the table subscribers are listening on. You could probably also just add an extra column for tracking retries and failures and again use some background process to reinsert the relevant events.
The issue here is write amplification that some other system like RabbitMQ avoids. Most pub/sub systems have provisions for retries as part of the base system. In postgres you have to reinsert the event every time you want to retry it and you also have to track delivery status to make things robust.
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.