i am experimenting BDR project. As BDR does asynchronous replication, i have a query regarding bdr.synchronous_commit=on option.
Will aforementioned configuration in postgresql.conf makes the replication synchronous?
No. It does not make replication synchronous. It makes the BDR downstream apply process commit and flush to disk each transaction it replays as soon as it has replayed it, instead of waiting until it's time to send replay feedback to the upstream. This means there's a pause for fsync with each applied xact, which performs terribly for high transaction rates. By default with synchronous_commit off BDR instead sends confirmation lazily as the transactions actually flush to disk.
See bdr_send_feedback in the source code for details.
If you want synchronous replication you can use synchronous_standby_names to make replication to exactly one peer server synchronous (ish). You have to set an application_name in the connection dsn on the peer server so you can identify it in synchronous_standby_names. It is not necessary to set bdr.synchronous_commit = on. Doing so may result in quicker confirmation of commit on the upstream at the cost of lower overall performance.
There is no way to get n-safe synchronous replication because PostgreSQL doesn't support that, and BDR uses PostgreSQL's synchronous replication support. You can only have one synchronous replica active at a time and PostgreSQL will automatically choose the first reachable one in the replica list.
Does this require any other setting? any side effect for using this setup?
The usual downsides of synchronous replication. A potentially significant performance hit, the upstream stopping when the downstream isn't reachable, etc.
Personally I don't think it's a good idea to try to combine BDR and synchronous replication. There are too many pitfalls, especially around the 1-synchronous-replica limitation. It'll be better if/when core gets support for n-safe synchronous replication.
