Previously we setup BiDirectional replication for PostgreSQL.
You might have various legitimate reasons to do so and probably will get to that in another blog.
Overall picking this type of replication can be influenced a lot by the nature of your application, the need for active active dr scenarios and even cases of migration .
Since we have replication in place it would be great to examine the case of an outage and how we can utilise the native fail-over functionality of the PostgreSQL drivers.
We shall change the conflict resolution strategy to last_update_wins. This way between two simultaneous updates in each database the update with the max commit timestamp will be the one chosen one.
listen_addresses = '*' port = 5432 max_connections = 20 shared_buffers = 128MB temp_buffers = 8MB work_mem = 4MB wal_level = logical max_wal_senders = 3 track_commit_timestamp = on shared_preload_libraries = 'pglogical' pglogical.conflict_resolution = 'last_update_wins'
We need to spin up the compose services with the new changes:
docker compose up
Docker Compose V2 is out there with many good features, you can find more about it on the book I authored:
A Developer’s Essential Guide to Docker Compose.
Take note that based on the programming language and the driver, this functionality might not always be available. The concept is that when you configure the connection pool to establish connection to the database you can configure two hosts. The first host will be the primary one and the secondary host will be the one to fail-over once the primary host gets offline. The fail-over can be interchangeable, essentially the driver tries to find the first available host.
Python and the driver psycopg2 offer this functionality. We shall implement an app using the flask api. The app will give two endpoints, one for fetching an employee’s salary and one to increment the salary by 1:
from flask import Flask
from psycopg2.pool import SimpleConnectionPool
app = Flask(__name__)
postgreSQL_pool = SimpleConnectionPool(1, 20, user="postgres",
password="postgres",
host="localhost,localhost",
port="5432,5431",
database="postgres",
options="-c search_path=test_schema")
@app.route('/employee/<employee_id>/salary/increment', methods=['POST'])
def increment_salary(employee_id):
conn = postgreSQL_pool.getconn()
cur = conn.cursor()
cur.execute("""
UPDATE employee SET salary=salary + %s WHERE id = %s;
""", (1, employee_id))
conn.commit()
cur.close()
postgreSQL_pool.putconn(conn)
return '', 204
@app.route('/employee/<employee_id>/salary')
def index(employee_id):
conn = postgreSQL_pool.getconn()
cur = conn.cursor()
cur.execute("""
SELECT salary FROM employee WHERE id=%s;
""", employee_id)
salary = cur.fetchone()[0]
cur.close()
postgreSQL_pool.putconn(conn)
return str(salary), 200
Let’s example the SimpleConnectionPool, we can see two hosts separated with a comma (it’s localhost since it’s our local docker compose running) and on the port section the respective host ports are separated by comma.
We can run the app
flash run
And on another terminal issue the calls using curl
$ curl -X POST http://localhost:5000/employee/1/salary/increment $ curl http://localhost:5000/employee/1/salary
Overall the salary will increase and we should see that on the get request.
Now let’s shut down one database
docker compose stop postgres-b
The first call after this operation will be a failed one, however the connection will be reinitialized and point to the secondary host.
% curl http://localhost:5000/employee/1/salary <!doctype html> <html lang=en> <title>500 Internal Server Error</title> <h1>Internal Server Error</h1> <p>The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.</p> % curl http://localhost:5000/employee/1/salary 1254.23
The same functionality applies for other drivers. Take for example the Java driver configuration on a spring boot application.
spring.datasource.url=jdbc:postgresql://localhost:5432,localhost:5431/postgres?currentSchema=test_schema spring.datasource.username=postgres spring.datasource.password=postgres
On the jdbc url we add two hosts comma delimited localhost:5432,localhost:5431
Then we can implement an application with the same functionality.
package com.egkatzioura.psqlfailover.repository;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class EmployeeRepository {
private final JdbcTemplate jdbcTemplate;
public EmployeeRepository(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public void incrementSalary(Long employeeId, float increment) {
jdbcTemplate.update("UPDATE employee SET salary=salary+? WHERE id=?",increment, employeeId);
}
public Float fetchSalary(Long employeeId) {
return jdbcTemplate.queryForObject("SELECT salary FROM employee WHERE id=?",new Object[]{employeeId},Float.class);
}
}
package com.egkatzioura.psqlfailover.controller;
import com.egkatzioura.psqlfailover.repository.EmployeeRepository;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class EmployeeController {
private final EmployeeRepository employeeRepository;
public EmployeeController(EmployeeRepository employeeRepository) {
this.employeeRepository = employeeRepository;
}
@PostMapping("/employee/{id}/salary/increment")
public void incrementSalary(@PathVariable Long id) {
employeeRepository.incrementSalary(id,1f);
}
@GetMapping("/employee/{id}/salary")
public Float fetchSalary(@PathVariable Long id) {
return employeeRepository.fetchSalary(id);
}
}
Thanks to the replication the changes should have reached the other database. You can start and restart the compose services in a round robin fashion. The changes will be replicated and thus every time there is a fail-over the data will be there.
While we start and stop the databases docker compose stop postgres-b, we can issue requests using curl:
$ curl -X POST http://localhost:8080/employee/1/salary/increment $ curl http://localhost:8080/employee/1/salary
Eventually the java driver handles the fail-over even more gracefully. Instead of failing on the first request during the fail-over instead it will fisr to connect to the other host and give back the results.
That’s it. You setup BiDirectional replication on PostgreSQL and you managed to take advantage of the driver capabilities to fail-over to different hosts. Hope you had some fun!
Emmanouil Gkatziouras 