Setting up PostgreSQL master-slave replication on openSUSE Leap 15.5 for high-volume Laravel applications

Prerequisites and Initial Setup

This guide assumes you have two openSUSE Leap 15.5 servers provisioned and accessible via SSH. One will serve as the PostgreSQL master, and the other as the slave. Both servers require a static IP address. For this setup, we’ll use 192.168.1.100 for the master and 192.168.1.101 for the slave. Ensure that the PostgreSQL packages are installed on both machines. If not, use zypper install postgresql-server postgresql-contrib.

On the master server (192.168.1.100), initialize the PostgreSQL cluster if it hasn’t been done already:

sudo systemctl enable postgresql
sudo systemctl start postgresql
sudo -u postgres /usr/lib/postgresql/15/bin/initdb --pgdata=/var/lib/pgsql/data

On the slave server (192.168.1.101), perform the same initialization steps.

Configuring the PostgreSQL Master

The master server needs to be configured to accept replication connections and to log write-ahead log (WAL) information in a format suitable for streaming replication. Edit the PostgreSQL configuration file, typically located at /var/lib/pgsql/data/postgresql.conf.

# In /var/lib/pgsql/data/postgresql.conf on the master

listen_addresses = '*' # Or specify master's IP: '192.168.1.100'
wal_level = replica
wal_sync_method = fsync
wal_compression = on
wal_buffers = 16MB
max_wal_senders = 5 # Number of concurrent replication connections
max_replication_slots = 5 # Number of replication slots
hot_standby = on # Allows read queries on the slave
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/wal_archive/%f' # Example: archive to a local directory

Next, configure client authentication in /var/lib/pgsql/data/pg_hba.conf to allow replication from the slave server. We’ll create a dedicated replication user.

# In /var/lib/pgsql/data/pg_hba.conf on the master

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     repl_user       192.168.1.101/32        scram-sha-256
host    all             all             192.168.1.0/24          scram-sha-256 # For application access

After modifying these files, create the WAL archive directory and restart the PostgreSQL service on the master:

sudo mkdir -p /var/lib/pgsql/wal_archive
sudo chown postgres:postgres /var/lib/pgsql/wal_archive
sudo systemctl restart postgresql

Now, create the replication user and grant necessary privileges. Log in to PostgreSQL as the `postgres` user:

sudo -u postgres psql

Inside the `psql` prompt, execute the following SQL commands:

CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'your_strong_replication_password';
-- If you need to replicate a specific database, grant connect on that database
-- CREATE DATABASE my_laravel_db; -- if it doesn't exist
-- GRANT CONNECT ON DATABASE my_laravel_db TO repl_user;
-- For full replication, no specific database grants are strictly needed for the role itself,
-- but the user running the replication process needs to be able to connect.
\q

Configuring the PostgreSQL Slave

Before setting up replication, ensure the slave server has a clean PostgreSQL installation and is stopped. The replication process will overwrite its data directory.

sudo systemctl stop postgresql

On the slave, we need to perform an initial data synchronization from the master. The most robust method is using pg_basebackup. First, remove any existing data directory on the slave:

sudo rm -rf /var/lib/pgsql/data/*

Now, execute pg_basebackup from the slave server, connecting to the master. This command will download the entire data directory from the master.

sudo -u postgres pg_basebackup -h 192.168.1.100 -U repl_user -D /var/lib/pgsql/data -P -v -R

Explanation of flags:

• -h 192.168.1.100: Hostname or IP of the master server.
• -U repl_user: The replication user created on the master.
• -D /var/lib/pgsql/data: The target data directory on the slave.
• -P: Show progress.
• -v: Verbose output.
• -R: Create standby.signal and postgresql.auto.conf (or recovery.conf in older versions) to configure the server as a standby.

You will be prompted for the replication user’s password. If pg_basebackup -R was successful, it will create a standby.signal file in the data directory and a postgresql.auto.conf file with replication connection settings. Verify its content:

# Contents of /var/lib/pgsql/data/postgresql.auto.conf on the slave
primary_conninfo = 'user=repl_user password=your_strong_replication_password host=192.168.1.100 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbservice=postgres'
primary_slot_name = 'your_replication_slot_name' # Optional, but recommended

If you want to use a replication slot (highly recommended for preventing WAL file buildup on the master if the slave is down), create it on the master first:

sudo -u postgres psql
-- Inside psql
SELECT pg_create_physical_replication_slot('your_replication_slot_name');
\q

Ensure the primary_slot_name in postgresql.auto.conf on the slave matches the name created on the master. Also, ensure the PostgreSQL user on the slave has correct ownership of the data directory:

sudo chown -R postgres:postgres /var/lib/pgsql/data

Now, start the PostgreSQL service on the slave:

sudo systemctl start postgresql

Verification and Monitoring

Check the PostgreSQL logs on both the master and slave servers for any errors. On the master, you can check for active replication connections:

sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

On the slave, check the logs for messages indicating it’s connected to the master and streaming WAL. You can also query the slave to see if data is being replicated. If you created a database named my_laravel_db on the master, you should be able to connect to it on the slave and see its contents (assuming hot_standby = on and appropriate pg_hba.conf entries for read access).

# On the slave
sudo -u postgres psql -d my_laravel_db -c "SELECT count(*) FROM some_table;"

For Laravel applications, you’ll typically configure your database connection to use the master for writes and the slave for reads. This is usually managed within your config/database.php file.

/* In config/database.php */
'connections' => [
    'mysql' => [ // Assuming 'mysql' is your primary connection name, rename if needed
        'driver' => 'pgsql',
        'host' => env('DB_MASTER_HOST', '192.168.1.100'),
        'port' => env('DB_PORT', '5432'),
        'database' => env('DB_DATABASE', 'your_db_name'),
        'username' => env('DB_USERNAME', 'your_db_user'),
        'password' => env('DB_PASSWORD', 'your_db_password'),
        'charset' => 'utf8',
        'prefix' => '',
        'schema' => 'public',
        'sslmode' => 'prefer',
    ],

    'pgsql_read' => [ // A new connection for read replicas
        'driver' => 'pgsql',
        'host' => env('DB_SLAVE_HOST', '192.168.1.101'),
        'port' => env('DB_PORT', '5432'),
        'database' => env('DB_DATABASE', 'your_db_name'),
        'username' => env('DB_USERNAME', 'your_db_user'), // Use a read-only user if possible
        'password' => env('DB_PASSWORD', 'your_db_password'),
        'charset' => 'utf8',
        'prefix' => '',
        'schema' => 'public',
        'sslmode' => 'prefer',
    ],
],

/* In config/database.php - Replication configuration */
'redis' => [
    // ... other redis configs
],

'pgsql_replication' => [
    'read' => [
        '1' => [
            'host' => env('DB_SLAVE_HOST', '192.168.1.101'),
            'port' => env('DB_PORT', '5432'),
            'database' => env('DB_DATABASE', 'your_db_name'),
            'username' => env('DB_USERNAME', 'your_db_user'),
            'password' => env('DB_PASSWORD', 'your_db_password'),
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
            'sslmode' => 'prefer',
        ],
    ],
    'write' => [
        'host' => env('DB_MASTER_HOST', '192.168.1.100'),
        'port' => env('DB_PORT', '5432'),
        'database' => env('DB_DATABASE', 'your_db_name'),
        'username' => env('DB_USERNAME', 'your_db_user'),
        'password' => env('DB_PASSWORD', 'your_db_password'),
        'charset' => 'utf8',
        'prefix' => '',
        'schema' => 'public',
        'sslmode' => 'prefer',
    ],
],

Ensure your .env file is updated with the correct master and slave IP addresses:

DB_MASTER_HOST=192.168.1.100
DB_SLAVE_HOST=192.168.1.101
DB_PORT=5432
DB_DATABASE=your_db_name
DB_USERNAME=your_db_user
DB_PASSWORD=your_db_password

Advanced Considerations and Troubleshooting

Replication Lag: Monitor replication lag using pg_stat_replication on the master and by checking the slave’s logs. Tools like PMM (Percona Monitoring and Management) or custom scripts can help automate this. If lag is consistently high, investigate network latency, slave I/O performance, or if the slave is overloaded with read queries.

Failover: This setup is for replication, not automatic failover. For high availability, consider tools like Patroni, repmgr, or Pacemaker. Manual failover involves stopping writes to the master, promoting the slave (using pg_ctl promote or pg_promote()), and reconfiguring other slaves and applications to point to the new master.

WAL Archiving: The archive_command on the master is crucial for point-in-time recovery and for slaves that might fall far behind. Ensure the archive destination is reliable and has sufficient space. If using pg_basebackup -R, it might configure restore_command on the slave automatically. If not, you’ll need to configure it manually in postgresql.conf or postgresql.auto.conf to allow the slave to fetch archived WAL files.

Security: Use strong passwords for replication users. Consider using SSL for replication connections by setting ssl = on in postgresql.conf on the master and configuring sslmode appropriately in pg_hba.conf and the client connection strings.

Resource Allocation: Ensure both master and slave servers have adequate CPU, RAM, and fast I/O (SSDs are highly recommended for database workloads). Tune PostgreSQL parameters like shared_buffers, work_mem, and checkpoint settings based on your server’s resources and workload.

Laravel Specifics: For read/write splitting in Laravel, you can leverage the pgsql_replication configuration array and use the DB::connection('pgsql_replication') facade. For more advanced scenarios, consider packages like spatie/laravel-db-pure-php or custom query builders that can intelligently route read queries to replicas.