This project demonstrates a PostgreSQL 16 primary–standby (master–replica) setup using Docker Compose and streaming replication, with a TypeORM API for testing database operations.
. ├── docker-compose.yml ├── primary/ │ ├── data/ # Primary database volume (ignored in git) │ └── init-replication.sh # Script to configure primary for replication ├── replica/ │ ├── data/ # Replica database volume (ignored in git) │ └── replica-entrypoint.sh # Script to bootstrap replica via pg_basebackup ├── src/ # TypeORM API source code └── package.json # Node.js dependencies and scripts git clone <your-repo-url> cd <your-repo> # Use npm script to setup directories and permissions npm run setupnpm install# Start both primary and replica containers npm run start-postgress # Or manually: docker compose up -dAfter the containers are running, migrate the database:
npm run migration:runnpm startThe API will be available at http://localhost:3000
npm run setup # Setup directories npm run start-postgress # Start PostgreSQL containers npm run migration:run # Run database migrations npm start # Start API serverdocker compose downnpm run cleanThe TypeORM API provides endpoints for testing database operations across the master-replica setup.
curl -X POST http://localhost:3000/users \ -H "Content-Type: application/json" \ -d '{ "name": "John Doe", "email": "john.doe@example.com" }'Expected Response:
{ "id": 1, "name": "John Doe", "email": "john.doe@example.com", "createdAt": "2024-01-20T10:30:00.000Z" }curl -X GET http://localhost:3000/usersExpected Response:
[ { "id": 1, "name": "John Doe", "email": "john.doe@example.com", "createdAt": "2024-01-20T10:30:00.000Z" } ]curl -X GET http://localhost:3000/users/1docker exec -it pg-primary psql -U postgres -c \ "SELECT pid, usename, application_name, client_addr, state, sync_state FROM pg_stat_replication;"Expected:
state = streamingsync_state = async(orsyncif configured)
docker exec -it pg-replica psql -U postgres -c "SELECT pg_is_in_recovery();"Expected: t (true)
docker exec -it pg-replica psql -U postgres -c \ "SELECT status, conninfo, last_msg_send_time, last_msg_receipt_time FROM pg_stat_wal_receiver;"Expected: status = streaming with recent timestamps.
docker exec -it pg-primary psql -U postgres -c \ "SELECT application_name, client_addr, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"docker exec -it pg-replica psql -U postgres -c \ "SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;"Test replication by creating a user via the API (writes to primary) and then checking if it appears on the replica:
Create user via API:
curl -X POST http://localhost:3000/users \ -H "Content-Type: application/json" \ -d '{"name": "Test User", "email": "test@example.com"}'Check on replica directly:
docker exec -it pg-replica psql -U postgres -c \ "SELECT * FROM users ORDER BY id DESC LIMIT 1;"Expected: The newly created user should appear on the replica.
On primary:
docker exec -it pg-primary psql -U postgres -c \ "CREATE TABLE IF NOT EXISTS repl_test(id serial PRIMARY KEY, t text); \ INSERT INTO repl_test(t) VALUES ('hello from primary');"On replica:
docker exec -it pg-replica psql -U postgres -c "SELECT * FROM repl_test;"Expected: the inserted row should appear on the replica.
npm run migration:generate -- src/migrations/NewMigrationNamenpm run migration:runnpm run migration:revertPromote the standby to a new primary:
docker exec -it pg-replica psql -U postgres -c "SELECT pg_promote();"After promotion, you'll need to update your application's database connection to point to the new primary.
docker compose downnpm run cleanThis will stop containers and remove all database data from both primary and replica.
- This setup is for local development/demo only.
- The API connects to the primary database for both reads and writes.
- For production:
- Use strong passwords and restrict replication connections in
pg_hba.conf. - Consider replication slots to prevent WAL loss.
- Enable TLS for secure connections.
- Use HA tools like Patroni, repmgr, or pgpool for automated failover and management.
- Implement read/write splitting to direct reads to replica and writes to primary.
- Add proper error handling and connection pooling in the API.
- Use strong passwords and restrict replication connections in