Hi, I have a cluster of PostgreSQL 9.2.21, where there is one master and one slave with streaming replication. I have few points and questions about the replication, and was hopping you guys could share your opinions, suggestions and experiences.
> Before I start; Yes! I know... PG 9.2? Really? Well... we're working on a > migration project.. We're considering either EnterpriseDB or RDS (we're > already in EC2 instances in AWS). My current architecture is: master (AZ1) --> read slave (AZ2) --> hot-standby (AZ2) - They are all using streaming replication. All read-only queries are sent to the read slave. Sometimes the replication lag between the master and the slaves reaches up to 10 minutes. I understand that the lag is expected in any replication scenario, and below you can find some suggestions that I think would help to minimize the lag time. - Having the read slave in the same AZ as its master - for better network throughput; - Having the latest PostgreSQL version to get its best performance - For the replication, we use Streaming Replication. A native PostgreSQL solution that was first introduced in PostgreSQL 9.0 version. - So, that means that we are using its very very early version. Many improvements have been introduced since 9.x which we’re not taking advantage of. - Having the latest Operational System behind PostgreSQL to get its best IO performance - We’re still on Ubuntu 16.04.2 for both Master and Slaves. Again, a lot of performance improvements were introduced in the new Ubuntu version 20.x, which we’re not taking advantage of. - Consider changing the read slave to be synchronous and not asynchronous - Consider having multiple slaves and not just one big instance - Consider spreading the load between the master and the slaves with a Pooling software (PGPOOL) - Currently this is done at the application level (PHP) - The master should also do read-only queries. Why not? Do you agree? Do you have any other suggestions? Is there anything I could do now to minimize the replication lag, or since we're working on a migration there is no point wasting our time? --- Regards, Lucas > This message is encrypted. Both the Public Key and the GPG encrypted message > are included in this email so that you can verify its origin.
publickey - root@sud0.nz - 0xC5E964A1.asc
Description: application/pgp-keys
signature.asc
Description: OpenPGP digital signature
