Database connection dropping
I have a postgres sql db on GCP with a connection string.
i have set up publications on this table using sql commands from the docs.
for some reason, pulse keeps dropping the connection. I see "Unexpected Error Occured" on the console and a DIsconnected status for Database connection
anyu and all help would be appreciated
41 Replies
Hi @plnkerxd
Can you please share your Github handle? Also, do you have
sslmode=require parameter in your database connection string? If yes, can you please remove it and try again?github handle:
oscarthedev15
i will try this now thank you
Thanks for sharing the Github handle. I'm checking the logs now
postgresql://pulse:@104.197.47.139:5432/indexer
this is the connection string i am using
the console does say i am connected but i am not seeing any new entries when i run the script:
import { PrismaClient } from "@prisma/client";
import { withPulse } from "@prisma/extension-pulse";
const apiKey: string ="";
const prisma = new PrismaClient().$extends(withPulse({ apiKey }));
const main = async () => {
console.log("Starting...");
const subscriptions = await prisma.organization.subscribe();
if (subscriptions instanceof Error) {
console.error(subscriptions);
return;
}
for await (const event of subscriptions) {
console.log(event);
}
};
main();
Can you connect to your database via a tool like PgAdmin or TablePlus?
Is your database publicly accessible?
yeah i can connect to it via pgAdmin
the pulse console is showing db connected but the events do not look right and neither is the script catching them
the publication was created by a different user but stil accesable to the pulse user.... is this a problem/
Can you please confirm if the Pulse user have access to the replication slot created by the original publication user?
Use this to check existing slots
Is this happening in environment named
Developmentof project named Nameless Indexer?yes
checking on this now
Have you enabled logical replication in your GCP instance?
yes have this enabled
actually dropped the connection again with the unexpected error occured flag
the only publication i have looks like this
We seem to be receiving events, However, I also see this error:
When you run the SQL to list all publication slots, do you see this slot as active?
i am seeing that publication in my pgadmin
and when i run the sql command given
oh actually the active status is "f"
can you activate it using this command
indexer=> SELECT pg_replication_slot_advance('prisma_pulse_0821ec22');
ERROR: function pg_replication_slot_advance(unknown) does not exist
LINE 1: SELECT pg_replication_slot_advance('prisma_pulse_0821ec22');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
My apologies. Please drop the slot first
then recreate it
we want the status to be t?
yes
cool got that and the status on console is back to green
created a custom_prisma publication using the prisma acount now.
so there are two publications i can see in pgadmin, both owned by pulse user
Great.
Since you now have two publications owned by the pulse user, you need to ensure that both publications include the tables that Pulse needs to subscribe to.
they should have the same exact tables?
do i still need both publications then?
what is the point of the custom_prisma? can i just alter the prisma_pulse_0821ec22
If the tables in custom_prisma and prisma_pulse_0821ec22 are identical, there is no need for both publications. You can simplify by using just one publication.
ok and then prisma pulse will only read those tables?
Yes, that’s correct
ok thank you i will try to test now
You’re welcome
not looking like its working at all
Replication slot status
Unavailable
is showing on my console and the script is still not working to connect to pulse
Can you confirm which replication slot Pulse is currently configured to use?
how can i check this?
seeing Replication slot status
Unavailable
what is the correct way to configure pulse to only listen to specific tables?
looks like this publication prisma_pulse_0821ec22 is by default all tables -- can i confugre this at all
To confirm which replication slot Prisma Pulse is currently configured to use, you can follow these steps:
1. Navigate to the Prisma Data Platform console.
2. Go to your project environment configuration for Pulse.
3. In the Database replication section, look for the Publication name field. This field will show the name of the publication (replication slot) that Pulse is currently using.
To configure pulse to listen to specific tables, you will need to create a custom publication in your database that includes only the specific tables you want to monitor. You can do this by running a SQL query in your database.
This creates a publication named
custom_publication that only listens to changes in the users and departments tables.
After creating the publication, navigate to the console, to configure Pulse. In your project environment configuration for Pulse, toggle the Automatic setup button to Advanced setup in the Database replication section.
In the Publication name field that appears, enter the name of your custom publication (e.g., custom_publication).
Click Enable Pulse to apply the configuration.
By following these steps, Pulse will only listen to the tables specified in your custom publication.Prisma Pulse: General instructions | Prisma Documentation
Instructions to use your database with Prisma Pulse
ok i will try
i purged all my events yet i still see them in the console
still not seeing anything out of my listening script
Apologies for the delayed response. Can you create an insert from prisma studio to see if it's logged to the console
yes will try now
that did work
Nice
might be an application issue then, thank you for your help
is there any reason why a seperate application (rust script) that writes to the db is not getting picked up by pulse but my direct writes to the same db get picked up ?
Are the tables your rust script is writing to included in the publication slot? Pulse only captures changes for tables and operations specified in the publication.
I got it set up using subscribe and it works
any reason why subscribe would work but stream wouldnt/
I got it set up using subscribe and it worksI'm glad to hear that using subscribe works for you 🎉
any reason why subscribe would work but stream wouldntDid you enable Event persistence when setting up Pulse from the console?
To use
stream(), Event persisitence needs to be enabled. https://www.prisma.io/docs/pulse/api-reference#notesPulse: API reference | Prisma Documentation
API reference documentation for Pulse.
yes that is enabled
