#PGSQLPhriday 001 Truth and Lies!

This post will be related to #PGSQLPhriday!  Here are the rules/requirements for the post. 

“ For this first event, I want you to write a blog post that shares three of your go-to Postgres best practices, but write it in the form of the game Two truths and a lie.

For example, perhaps there are three configuration settings you always modify whenever setting up a new cluster. You could share why these settings are important to modify and how you find the correct values, but for one of them, use Ilya’s comical approach to teach us the correct way to set the value. (ie. “I never set shared_buffers to more than 128MB to conserve server resources.” )

You can read the full post from Ryan here.  https://www.softwareandbooz.com/pgsql-phriday-001-invite/

For my 3 tips I wanted to talk about logical replication and configuration.  I do a lot of work with DW/Reporting/replication.  I figured it would be good to talk about some of the key parameters you need to know when setting up logical replication.  I will keep this in the form of 2 truths and a lie, I’ll reveal at the bottom which one is which.  

wal_level = logical

Probably one of the most key things to set for logical replication is your wal_level. For logical replication it has to be set to Logical.  I typically have this set to “logical” for most of my servers.  I rarely have a server that does not participate in logical replication and it’s best to just set this up front. If you know that you won’t be doing any replication then something like minimal is a good idea but I personally don’t run into this scenario often.  You do have to restart the server for this setting. This is one of the reasons I keep it on my main checklist for a server to make sure it’s configured right when the server starts. 

max_logical_replication_workers = 1 

Keeping this at a low number means you are not utilizing as many server resources doing silly things like moving data!   You should keep this as low as possible on your subscriber,  this way you can automatically throttle your logical replication and it can always stay behind.  

max_replication_slots = 10+ 

The default for max_replication_slots is 10, I frequently find this is very low for the servers I’m trying to do logical replication for.  Since this also requires a restart I will frequently start this out at 25 so that I can avoid restarting simply to add new replications.  Along with this parameter you should always move “max_wal_senders”.  This should be the same number + any physical replication slots you have as well.  Make sure these are moved together.  

Let’s talk about Truth and Lies now!  Pretty sure most of you out there would have sensed the lie already but let me make it very clear.  

max_logical_replication_workers = 1 

Please do NOT make this a low number.   You Want this to be a higher number so that your logical replication can keep up.  If you have a LOT of publications/Subscriptions you should think about increasing this.  Be careful though this does take from “max_worker_processes”.  If that number isn’t higher as well then you will run into problems.  It’s hard to put specific good guidelines around this since it really depends on how much replication work you are doing and how many resources you have on the server. These are numbers to adjust and test and see what works for your system.  

Thank you to Ryan for starting up #PGSQLPhriday,  It was nice to get writing again and I am looking forward to more of them in the future! 

Advertisement