Step 1: Configure Primary Database for Replication

Edit the postgresql.conf file on your primary database server. You can find this file typically in the data directory of your PostgreSQL installation.

$ sudo nano /path/to/postgresql.conf

Add the following lines to configure replication parameters:

wal_level = hot_standby
max_wal_senders = 5
max_replication_slots = 5

Save and exit the configuration file.

Step 2: Create Replication User

Connect to your primary database using a PostgreSQL client or terminal:

$ psql -h primary_host -U postgres

Run the following SQL commands to create a replication user and grant necessary permissions:

CREATE USER repluser REPLICATION LOGIN CONNECTION LIMIT 5 PASSWORD 'password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repluser;

Exit the PostgreSQL client:

\q

Step 3: Take a Base Backup

Start a base backup to ensure that the replica has an initial consistent copy of the primary's data. Connect to your primary database and execute the following SQL commands:

SELECT pg_start_backup('initial_backup');

Now, use rsync to copy the primary's data directory to the replica server:

$ rsync -avz /var/lib/postgresql/data/ repluser@replica:/var/lib/postgresql/data/

After the rsync is completed, stop the backup process on the primary:

SELECT pg_stop_backup();

Step 4: Configure Replica Server

Edit the postgresql.conf file on your replica server:

$ sudo nano /path/to/postgresql.conf

Add the following line to enable hot standby mode:

hot_standby = on

Save and exit the configuration file.

Step 5: Configure Replica Connection

Still in the postgresql.conf file of the replica server, add the following line to specify how the replica should connect to the primary:

primary_conninfo = 'host=primary_host port=5432 user=repluser password=password'

Replace primary_host with the actual hostname or IP address of your primary server.

Step 6: Start PostgreSQL Service on Replica

Start the PostgreSQL service on the replica server:

$ sudo service postgresql start

Step 7: Verify Replication

Check the PostgreSQL logs on both the primary and replica servers to ensure that the replication process is working as expected. On the replica, you should see messages indicating that it is successfully connecting to the primary and streaming changes.

Step 8: Use Replica for Read Scaling

At this point, your replica database is set up and running. You can use the replica for read scaling purposes while directing write operations to the primary database. The replica will automatically stay up-to-date via streaming replication.