0

I have a PostgreSQL repmgr Master-Slave setup with HOT standby enabled. In such repmgr configuration isnt it possible to configure the postgresql.conf in Primary and secondary DIFFERENTLY WHILE HOT STANDBY WILL BE ON. I mean parameters below:

  • shared_buffers,
  • max_parallel_workers,
  • max_worker_process,
  • max_connections

etc MUST have to be same or different? If it is different then I saw the DB in secondary through some Errors. So, it ill require same H/W (Resources like RAM, CPU same) setup for both master-slave which is costly enough.

Please, I cant start my slave with less config and need a solution to work on less H/W Slave environment to be worked as HOT standby.

Improve this question
2
  • No, it's not required. But if you expect your standby to take over in case the primary fails, then it makes sense that it has the same hardware, so that you can expect the same performance after a switch over.
    – user1822
    Sep 9, 2022 at 16:31
  • Yes, I knew the fail-over scenario. But, surely it will through you error if it is a Hot standby but not Warm standby.
    – fahad_dba
    Sep 9, 2022 at 20:37

2 Answers 2

Reset to default
0

There are only a few settings that must not be lower on the standby than on the primary:

  • max_connections

  • max_worker_processes

  • max_wal_senders

  • max_prepared_transactions

  • max_locks_per_transaction

None of these except max_connections can be a resource problem, and you can use a connection pool to deal with that. All other parameters can be lower. As long as you don't intend to failover to that standby, that is no problem.

Improve this answer
2
  • Yes, I can control max_connection with connection pool. Isn't max_worker_processes depends upon CPU(no. of core)? But i got error even when there was a less shared buffers/effective_cache_size than that in primary which are definitely resource consuming.
    – fahad_dba
    Sep 9, 2022 at 20:36
  • Please show the error message.
    – Laurenz Albe
    Sep 11, 2022 at 11:30
0

Unfortunately yes, you need the stand by to be pretty much the same or better hardware than the master. I too was hoping that I could use a lesser stand-by just to track the activity but it turns out that the majority of the work load is still in applying the WAL. If your stand-by is of lesser power, it will fall behind when the master is churning at high sustained transaction rate. And at some point the stand-by will give. Of course if the transaction rate is bursty, you can just work with a large enough WAL retention and hope the stand-by will catch up. But if you are operating on sustained high rate, you cannot do that.

Improve this answer
3
  • Thank you for your response. Please, can you share me the best practice if I want to keep a large enough WAL retention. As per as I know using replication slots is a good way and if there require to modify any other parameters ?
    – fahad_dba
    Sep 12, 2022 at 14:36
  • It's an interesting discussion. Those replication slots need space too. In my case I had dozens of transactions per second and the database grew quite fast. When the stand-by fell behind, I recreated it by rsync of the data files from the master. This was actually quite fast. I feel that WAL replication is very resource intensive on the stand-by and it seems an rsync based replication should be possible instead.
    – Gunther Schadow
    Sep 13, 2022 at 3:24
  • Yes, you are correct. It is always a good choice to go for rsync for passive. Thank you.
    – fahad_dba
    Sep 14, 2022 at 4:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.