0

I am asking for guidance.

I want to set up a replication between 3 servers : 1 master A, 2 slaves B and C. The master streams to 2 slaves. A to B is logical replication, whereas A to C is streaming replication

After I read some articles and I am now aware that streaming replication involves pg_basebackup, with the Slave pulling data from the Master.

Question :

  1. I read the articles here and here. Which one is correct, "ALTER SYSTEM SET listen_addresses TO '*';" OR listen_addresses = 'your_primary_IP_address'. I am just confused.

  2. What are some considerations before I proceed the replication that involves 3 servers ?

Thank you postgresql 12, Ubuntu 20.04

1 Answer 1

1

Your question asks how to enable network connections to your PostgreSQL cluster. You can use either method you describe, they are roughly equivalent for most cases. Changing listen_addresses requires a server restart, so plan accordingly.

The one consideration is if your host is multi-homed, that is, connected to multiple networks. If it is, and you don’t want to expose PostgreSQL to all of the networks, then specify the interface IPs of the networks you want to bind PostgreSQL to, and leave out the ones you don’t.

The special address ‘*’ tells PostgreSQL to bind to all available addresses. In most cases, that is just the server’s single IP. (And localhost but that is less important.)

If you have a firewall enabled then you will also need to allow connections through the firewall.

There are serious security implications to enabling network connections. NEVER expose PostgreSQL to the unfiltered public Internet. Your database will be hacked and turned into a cryptocurrency miner if that happens. Use a firewall to limit access to just the replication clients.

The DigitalOcean article doesn’t mention it, so it might be in there by default on Ubuntu, but you need an entry in pg_hba.conf telling PostgreSQL how to authenticate network clients. If you get authentication errors in the PostgreSQL log when the replication clients try to connect then that is likely the issue.

4
  • ok. thankyou @dwhitemv . how about the considerations I should think of related to the replication involving 3 servers ? I just edited the question. thanks again
    – padjee
    Oct 24, 2022 at 22:11
  • Can you be more specific? Not sure what “consideration” you are worried about.
    – dwhitemv
    Oct 25, 2022 at 2:38
  • ok. What I am worried is that I can not achieve a smooth streaming replication. What do I have to pay attention to? maybe minimum memory, bandwidth, minimum processor, settings etc. The database is not large, currently around 500 GB max (and growing soon) and will be accessed concurrently by 700 users max
    – padjee
    Oct 25, 2022 at 8:28
  • Hard to say without digging deep into your workload. 700 concurrent connections is a LOT for a database. But think of it this way — the write load on the clients will be the same as the server as the queries are the same. Please ask a new question if you want to discuss it further.
    – dwhitemv
    Oct 25, 2022 at 15:04

Your Answer

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

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