Basics of Replication

Created at: 2025-05-25

Source: https://www.highgo.ca/2023/04/03/setting-up-a-postgresql-replica-server-locally/

Physical Replication With Streaming

1. Log onto psql with an admin user.
2. Create a replication user and a table to play with.

   CREATE ROLE rep_user WITH REPLICATION LOGIN PASSWORD 'rep_pass';
   CREATE TABLE t1(a INT, b INT);
   INSERT INTO t1 VALUES (1,2);
   

3. Update the build/data/postgresql.conf by adding this line:

   listen_addresses = 'localhost'
   

4. Update the build/data/pg_hba.conf by adding this line:

   host  replication   rep_user  localhost   md5
   

5. Run pg_basebackup to create a rep/ folder with the replica data.

   build/bin/pg_basebackup -h localhost \
                           -U rep_user \
                           -X stream \
                           -C \
                           -S replica_1 \
                           -v \
                           -R \
                           -W \
                           -D build/rep
   

6. Update build/rep/postgresql.conf and set the port number to something different than the main server.

   port = 5454
   

7. Still in build/rep/postgresql.conf set primary_conninfo to (change the user accordingly):

   primary_conninfo = 'dbname=postgres user=postgres host=localhost port=5432 sslmode=disable'
   

8. Create the sandby.signal file:

   touch build/rep/standby.signal
   

9. Restart the primary Postgres server.
10. In a second terminal, start the replica.

    build/bin/pg_ctl -D build/rep start
    

11. Log onto the replica with:

    build/bin/psql --port=5454 --dbname=postgres
    

12. Stop the replica with:

    build/bin/pg_ctl -D build/rep stop