query replica postgresql

Hello,

For security reasons, users must query a replica database that is updated by an intermediate replica.
We must ensure performance and that requests do not impact replication. How to proceed ?

Thank you

Regards
bibi92Asked:
Who is Participating?
 
Doug WaltonDatabase AdministratorCommented:
If you don't have any already you should have some monitoring on the server itself to alert when the resources on the machine have been high for a while (CPU, disk, RAM).  If one of them is at 100% for too long replication could start slowing down.

One other thing you can do would be write a script that checks pg_stat_activity for select queries that have been running for a while.  You could kill them automatically with the same script, but it would probably be safer to send a notification to yourself and kill it manually if it needs to be killed.

select * from pg_stat_activity where current_date - query_start >= interval '10 minutes';

Open in new window


There are also some free postgres monitoring tools out there that can provide some detailed graphs, but a couple scripts to do the things I said above would probably be all you need for a replica database.
0
 
bibi92Author Commented:
Thank you best regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.