26 thoughts on “Waiting for 9.1 – Synchronous replication”

1. Any particular reason you’re not using pg_basebackup to set up your slaves? Just wondering if we’re missing some critical feature you think is needed there 😉

2. @Magnus:
   I’m just more used to rsync. Besides – it’s just 2 directories on the same machine, using pg_basebackup would be overkill 🙂

3. great write up. Thanks 🙂

4. But wait a minute!

   Synchronous replication does NOT guarantee that the change will be *visible* on the slave at the time the commit completes on the master. It merely guarantees that the WAL will have been transferred to the slave and written durably to disk. It’s unsafe to rely on the assumption that it will have also been replayed.

5. @Robert:
   Thanks for clarification. The distinction is pretty important.

6. Hi, I’m curious about your comment regarding latency being lower because there is no TCP traffic in your example. But in your example there will instead be contention for CPU and disk, so wouldn’t it be quite possible that having separate machines for the slave might actually result in better performance despite having to marshal everything via TCP? I mean one would presumably be running gigabit Ethernet between the machines… Latency is pretty low, no?

7. @Mark:
   usually, aside from test-environment, I don’t imagine anyone running master and slave on the same server, so there is no real comparison.

8. The commands on master are hanging:
   psql -p 4001 -d postgres -c “create user replication with replication”
   psql -p 4001 -d postgres -c “select pg_start_backup(‘x’)”

   When I kill them, I get the message:

   WARNING: canceling wait for synchronous replication due to user request
   DETAIL: The transaction has already committed locally, but may not have been replicated to the standby.

9. @AJ:
   And? Perhaps you should ask on some mailing list, or even report a bug?

10. @DEPESZ:
    Do you remember it hanging for you when you tried out the steps ?
    Maybe it is expected to hang as slave(s) is not up when these commands are executed.

11. No, it didn’t hang for me.

12. What are the changes need to be done if I setup the slave1 in another machine?

13. @Dohan:
    hmm .. I thought it’s obvious. You need to add different IP in pg_hba.conf for replication, and change ip in primary_conninfo on slave.

14. Ok tx I did it. Now I setup the cluster in three machines each having one pg instance. I am going to test the failover scenario. I used trigger_file setting to make a slave to accept read/writes when the master goes down. Currently only that setting not work because there is no slaves configured in the new master as old one. So it hangs at a INSERT. How can I deal with this? And how can I start old master safely as a slave without stopping current master?

15. @Dohan:
    When you switched slave to become master, it’s basically standalone pg. So you have to setup replication from scratch, just the other way around.

16. @# AJ
    the master can hang when the slave is in catchup mode after starting and cannot get the WAL file from the master pg_xlog or archive (00000001000000000000005C in this case)

    postgres 9537 0.0 0.0 459548 1764 ? Ss 15:28 0:00 postgres: startup process waiting for 00000001000000000000005C

    this results in an error entry in the current log in pg_log

17. Thanks for an awesome article on getting PG sync replication setup and running in minutes.

    Here are my questions.

    1. I don’t think synchronous_replication variable is any more valid. At least PG 9.1.2 doesn’t have it. Synchronous_commit is on by default.

    2. Can I have sync replication with standby “*” setup as above and master node run without any standby ?

    3. I want to configure timeout so that if standby sync takes more than few seconds, master wants to break the sync and continue on its own.

    Help me with the above.

    thanks,
    ManG

18. @Mang:
    1. yes. the synchronous_replication guc has been removed, and it’s logic is now in sunchronous_commit.

    2. why don’t you just try it?

    3. i don’t think it’s possible.

19. Hi,

    I have a PG 9.1.2 Master Slave with synchronous replication setup. They are all working fine as expected. I do have a case where I want to flip Master to non replication mode whenever its slave is not responding. I have set replication_timeout to 5s and whenever salve is not responding for for more than 5s, i see the master detecting it. But, the transactions on the master is stuck till the slave comes back. To get over it, I reloaded the config on master with synchronous_commit = local. Further transactions on the master are going thru fine with this local commits turned on.

    Here are my questions:

    1. Transaction which was stuck right when slave going away never went thru even after I reloaded master’s config with local commit on. I do see all new transactions on master are going thru fine, except the one which was stuck initially. How to get this stuck transaction complete or return with error.

    2. Whenever there is a problem with slave, I have to manually reload master’s config with local commit turned on to get master go forward. Is there any automated way to reload this config with local commit on on slave’s unresponsiveness ? tcp connection timeouts, replication timeouts all detect the failures, but i want to run some corrective action on these failure detection.

    your help is much appreciated.

    thanks.

20. Hi Depesz,

    your views on my question dated ‘Jan 13, 2012’ please ? Any help would be much appreciated.

    thanks,
    Manoj

21. @ManG: these were more of a “Support” questions. While it might look like, I don’t provide support for PostgreSQL on my blog. I might sometimes answer questions, but your questions are either impossible to answer without knowing a lot of details about your situation, or trivial to check on your own – so I don’t see much point in doing the check on my side, and providing you with ready answer – it’s a matter who puts effort into checking thing.

22. Our application is written in Java and uses standard JDBC driver API ( behind the scenes postgres jdbc provider) to connect to postgres.

    I am wondering how to turn on and off synchronous_replication for some transactions only.
    This way I can have the replication enabled for some important tables and not for other tables.

    Do you happen to know how to set this when using jdbc drivers synchronous_replication

    In your example, I see that you are using psql to set the same. But wondering how to set this in the application that typically does not use psql.

23. @JWolf:

    Well, SET … is just an sql query. So you can run it the same way you run any other query. No idea how that works in JDBC, but I don’t see why it would be different than (for example) calling update or delete.

24. makes sense. Thanks,
    Any plans on writing a blog on horizontal shards using postgres

25. @JWolf:
    hmnmm … like this one: https://www.depesz.com/2011/12/02/the-secret-ingredient-in-the-webscale-sauce/

26. Thanks! nicely written. Your blogs are way cool!

Comments are closed.