0

Im setting up a replication database with postgres. and it seems there are much informations about how to setting up, but I still can't find how to operate it in application.

What I want is to 'select' only from the repl db, and 'insert/update/delete' from the source db. In this case, should I have to connect to repl db everytime I select, and connect to source db in other cases, "in application level"? Or is there any way to make these all automated?

I mean, it seems there must be way to make it automated, because if there are really many repl db(more than 2), it must be working so.

please help me, thank you all.

4
  • Open two separate connections with your client? Development questions are however off topic here. Mar 7, 2023 at 9:26
  • How to implement that is primarily on what programming / development language and framework you're using. That is off-topic here. - Generally though you want to avoid the overhead of the application having to open a new DB connection whenever a query is made, but use connection pooling but even with connection pooling you still manually tune for each query if it needs to use the read-only DB connection or the read-write DB connection.
    – HBruijn
    Mar 7, 2023 at 10:59
  • From the infrastructure side: typically a load balancer gets added to the mix and your load balancer distributes the DB connections over one of the available RO replica's and you configure your application to connect to the load balancer rather than encoding a list of RO replica's in your application and attempt to do load balancing from your application
    – HBruijn
    Mar 7, 2023 at 11:04
  • What I want is to 'select' only from the repl db, and 'insert/update/delete' from the source db. In this case, should I have to connect to repl db everytime I select, and connect to source db in other cases, "in application level"? Or is there any way to make these all automated? I'm not sure why you want to do that, but most don't do that and I'm not aware of any available solutions that do it. If that is what you want to do, yes, you would need to handle that in your application code.
    – Greg Askew
    Mar 7, 2023 at 13:21

1 Answer 1

1

Q: What I want is to 'select' only from the repl db, and 'insert/update/delete' from the source db. In this case, should I have to connect to repl db everytime I select, and connect to source db in other cases, "in application level"? Or is there any way to make these all automated?

In general, choosing between a primary server and a read-only replicate is done by the application.

In some limited cases, when applications do simple things in terms of SQL commands and transactions and they really want this to be transparent, pgpool-II can be installed and configured as a load balancer between servers in streaming replication. It automatically detects which queries can be sent to a read-only replicate by analyzing them on the fly. From "What is Pgpool-II?":

Load balancing

Pgpool-II distributes read queries over multiple PostgreSQL servers to gain higher performance. This feature is called load balancing. Write queries are sent to either the primary server (in streaming replication mode) or all servers (in native replication mode and snapshot isolation mode). See Section 3.3.2 for more details about these mode. In any case, Pgpool-II automatically distinguish the read queries from the write queries.

See Load Balancing in the documentation for the limitations and caveats.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .