Step-by-Step: Converting Postgre Sync Replication to Async in Production

Simple Steps

📋 Prerequisites

Postgre 9.1 or higher.
Superuser access to the primary database.
Existing replication setup.

🚀 Quick Change Steps (No Restart Required)

Step 1: Connect to Primary Database
p -U postgres -h your-primary-server

Step 2: Check Current Sync Status
SELECT name, setting FROM pg_settings WHERE name IN ('synchronous_commit', 'synchronous_standby_names');

Step 3: Change to Asynchronous Mode
-- Disable synchronous commit
ALTER SYSTEM SET synchronous_commit = 'off';

-- Clear synchronous standby names
ALTER SYSTEM SET synchronous_standby_names = '';

-- Reload configuration (no restart needed)

SELECT pg_reload_conf();

Step 4: Verify the Change
-- Confirm settings changed

SELECT name, setting FROM pg_settings WHERE name IN ('synchronous_commit', 'synchronous_standby_names');

-- Check replication status (should show 'async')

SELECT application_name, sync_state, state FROM pg_stat_replication;

📝 Configuration File Method (Alternative)

Edit postgre.conf on Primary:
sudo nano /etc/postgre/14/main/postgre.conf
Change these lines:
conf
synchronous_commit = off
synchronous_standby_names = ''

Reload Configuration:
sudo systemctl reload postgres
# or
sudo pg_ctl reload

🧪 Testing the Change

Test 1: Performance Check
-- Time an insert operation
\timing on
INSERT INTO test_table (data) VALUES ('async test');
\timing off

Test 2: Verify Async Operation
-- Check replication lag
SELECT application_name, sync_state, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_lag FROM pg_stat_replication;

Test 3: Data Consistency Check

# On primary:
p -c "SELECT COUNT(*) FROM your_table;"

# On standby:
p -h standby-server -c "SELECT COUNT(*) FROM your_table;"

⚠️ Important Considerations

Before Changing:

Understand the risks: Async replication may cause data loss during failover
Check business requirements: Ensure async meets your data consistency needs
Monitor performance: Async should improve write performance but may increase replication lag

After Changing:

Monitor replication lag regularly
Set up alerts for significant lag increases
Test failover procedures to understand potential data loss scenarios
Review application behavior - some apps may need adjustments

🔄 Reverting to Synchronous Mode

If you need to switch back:

ALTER SYSTEM SET synchronous_commit = 'on';
ALTER SYSTEM SET synchronous_standby_names = 'your_standby_name';

SELECT pg_reload_conf();

🎯 When to Use This Change

Good candidates for Async:

Read-heavy workloads where write performance matters
Non-critical data that can tolerate minor data loss
High-volume logging or analytics data
Geographically distributed systems with high latency

Keep Synchronous For:

Financial transactions.
User account data.
Critical business operations.
Systems requiring zero data loss.

💡 Pro Tips

Use a hybrid approach: Some systems support both sync and async simultaneously.
Monitor continuously: Use tools like pg_stat_replication to watch lag times.
Test thoroughly: Always test replication changes in a staging environment first.
Document the change: Keep records of why and when you changed replication modes.
The change from sync to async takes effect immediately and requires no downtime, making it a relatively low-risk operation that can significantly improve write performance for appropriate use cases.