[DRAFT]How to set up database replication locally

Owned by Khamidulla Abdulkhakimov
Last updated: Feb 23, 2023
2 min read

Can be used to run backend modules with read/write functionality locally. The current article is oriented towards users with the Windows operating system.

Step-by-step guide

Set up master instance

  1. Run vagrant box 
  2. Connect to SSH: 
    1. host = 127.0.0.1
    2. port = 2222
    3. user = vagrant
    4. password = vagrant
  3. Create a role with replication permissions
    1. sudo -u postgres psql
    2. CREATE ROLE test WITH REPLICATION PASSWORD 'testpassword' LOGIN;
    3. \q
  4. Provide ability to connect to the primary db for replication
    1. sudo vim /etc/postgresql/12/main/pg_hba.conf
    2. Append the following line to the end of the file: 

      host    replication     test            10.0.2.2/32             md5

  5. Restart PostgreSQL: 
    1. sudo systemctl restart postgresql@12-main


Set up slave instance

  1. The current version of PostgreSQL used in FOLIO modules is 12.14. You need to download version 12.14 for Windows by following this link.
  2. Specify port as 5433 while installation: 
    •  
  3. Delete all files from C:\Program Files\PostgreSQL\12\data except these files:
    2. Temporarily move the remaining files to another directory
  4. Open CMD
  5. Go to postgres tools directory :
    1. cd C:\Program Files\PostgreSQL\12\bin
  6. Start replication and specify that standby:
    1. pg_basebackup -h 127.0.0.1 -p 5432 -U test -D "/Program Files/PostgreSQL/12/data" -Fp -Xs -R
    2. password = testpassword
  7. Move back temporarily moved files
  8. Increase connections limit:
    1. Open postgresql.conf in editor
    2. max_connections = 1000
    3. Save and exit
  9. Start postgres:
    • pg_ctl.exe -D "C:\Program Files\PostgreSQL\12\data" start
  10. Verify that replications is successfully:
    1. Connect to replica using standart okapi_modules db credentials. Just need to change port from 5432 to 5433