Introducing PostgreSQL Read Replicas in DSM 9.1.1
In my introduction post to VMware Data Services (DSM) 9.1.1, I mentioned a new feature called PostgreSQL read replicas. In a nutshell, PostgreSQL databases provisioned by DSM can now be scaled for read workloads by adding additional Read Replicas. Previously, a PostgreSQL database cluster was limited to just 3 nodes, a primary, a single replica and a monitor. In DSM 9.1.1, we can now scale PostgreSQL databases through the addition of read replicas. As I mentioned in the introduction to DSM 9.1.1, DSM now uses the term read replica instead of secondary to refer to remote PostgreSQL database instances. They continue to be used to replicate data from a primary database. Read replicas now serve both disaster recovery and read-scaling use cases. Once remote replication is enabled on a PostgreSQL primary database, the same workflow used for DR is now used to enable the creation of PostgreSQL read replicas. In this post, I will demonstrate how to do exactly that.
First, let create a PostgreSQL cluster via VCF Automation version 9.1.1. I am going to build a PostgreSQL version 18 which is newly supported in this DSM 9.1.1 release.
During the setup, enable replication and add the configuration for the replication slot and replication user. There is a significant amount of text related to cross-region replication, when the primary and the replica are in different regions. Some firewall rules may need to be in place to enable communication between the primary and the read replica. If you are deploying the primary and the read replicas in different regions, pay particular attention to this note.
Once deployed, make a copy of the replication string. This will be needed to create the read replica.
We can now begin with the creation of a Read Replica. When it comes to the replication source section, populate the connection string saved from the primary, and add the replication slot. Note that the text related to SNAT IP address between the primary and replica is again mentioned.
Add the user connection string, the slot and any custom Certificate Authority that you might be using. The Extra Clients is only relevant if you are using the new DSM Network Security features, and creates rules in the NSX firewall to allow the IP addresses of these clients to have access to the database. See my post on DSM 9.1.1 Network Security for further information. Wait for the Read Replica to become ready:
And verify that the primary’s replication status is “Replicating”:
Now let’s test read only access to the replica. To do that, I made a simply psql script which created a table of ‘mock’ employee data and populated with some random values. Here is that script.
-- 1. Clean up existing table if you're re-running the script DROP TABLE IF EXISTS mock_employees; -- 2. Create the table structure CREATE TABLE mock_employees ( id SERIAL PRIMARY KEY, employee_code VARCHAR(10), age INT, salary NUMERIC(10, 2), department VARCHAR(50), is_active BOOLEAN, hire_date TIMESTAMP ); -- 3. Generate and insert 10,000 rows of random data INSERT INTO mock_employees ( employee_code, age, salary, department, is_active, hire_date ) SELECT -- Generates a random UUID, converts to text, and grabs the first 8 characters substr(gen_random_uuid()::text, 1, 8), -- Generates a random age between 18 and 65 floor(random() * 48 + 18)::INT, -- Generates a random salary between $30,000.00 and $130,000.00 (random() * 100000 + 30000)::NUMERIC(10, 2), -- Picks a random department from an array of options (ARRAY['Engineering', 'Sales', 'Marketing', 'HR', 'Finance', 'Legal'])[floor(random() * 6 + 1)], -- Generates a random boolean (True/False) random() > 0.5, -- Generates a random timestamp somewhere within the last 5 years (1825 days) NOW() - (random() * interval '1825 days') FROM generate_series(1, 10000); -- 4. Verify the row count and look at a sample of the data SELECT count(*) FROM mock_employees; SELECT * FROM mock_employees LIMIT 5;
I then ran the script as follows, suing the connection string to the primary:
$ psql postgresql://pgadmin:it0QI80D5Nm8x3LPUNYdiYQ6l88e0E@192.168.20.11:5432/tenant03-primary \ -f random.psql DROP TABLE CREATE TABLE INSERT 0 10000 count ------- 10000 (1 row) id | employee_code | age | salary | department | is_active | hire_date ----+---------------+-----+-----------+------------+-----------+---------------------------- 1 | 42d9150f | 45 | 114332.05 | Sales | t | 2024-12-26 20:01:48.992993 2 | da648072 | 37 | 94975.66 | Legal | f | 2023-06-21 20:20:11.392203 3 | fa4fe54e | 31 | 108578.06 | Legal | f | 2026-06-14 10:41:03.661193 4 | 469205d7 | 55 | 92738.15 | Finance | t | 2024-01-16 03:31:14.870633 5 | 6ad4f01b | 65 | 121029.85 | HR | f | 2024-02-25 17:48:11.016703 (5 rows)
Afterwards, I ran another script every 30 seconds to keep updating the table. Here is that script:
-- 1. Generate and insert 100 rows of random data INSERT INTO mock_employees ( employee_code, age, salary, department, is_active, hire_date ) SELECT -- Generates a random UUID, converts to text, and grabs the first 8 characters substr(gen_random_uuid()::text, 1, 8), -- Generates a random age between 18 and 65 floor(random() * 48 + 18)::INT, -- Generates a random salary between $30,000.00 and $130,000.00 (random() * 100000 + 30000)::NUMERIC(10, 2), -- Picks a random department from an array of options (ARRAY['Engineering', 'Sales', 'Marketing', 'HR', 'Finance', 'Legal'])[floor(random() * 6 + 1)], -- Generates a random boolean (True/False) random() > 0.5, -- Generates a random timestamp somewhere within the last 5 years (1825 days) NOW() - (random() * interval '1825 days') FROM generate_series(1, 100); -- 4. Verify the row count and look at a sample of the data SELECT count(*) FROM mock_employees; SELECT * FROM mock_employees LIMIT 5;
And here is how I ran it every 30 seconds:
$ while true do psql postgresql://pgadmin:it0QI80D5Nm8x3LPUNYdiYQ6l88e0E@192.168.20.11:5432/tenant03-primary \ -f random-100-new.psql sleep 30 done INSERT 0 100 count ------- 10100 (1 row) id | employee_code | age | salary | department | is_active | hire_date ----+---------------+-----+-----------+------------+-----------+---------------------------- 1 | 42d9150f | 45 | 114332.05 | Sales | t | 2024-12-26 20:01:48.992993 2 | da648072 | 37 | 94975.66 | Legal | f | 2023-06-21 20:20:11.392203 3 | fa4fe54e | 31 | 108578.06 | Legal | f | 2026-06-14 10:41:03.661193 4 | 469205d7 | 55 | 92738.15 | Finance | t | 2024-01-16 03:31:14.870633 5 | 6ad4f01b | 65 | 121029.85 | HR | f | 2024-02-25 17:48:11.016703 (5 rows) INSERT 0 100 count ------- 10200 (1 row) id | employee_code | age | salary | department | is_active | hire_date ----+---------------+-----+-----------+------------+-----------+---------------------------- 1 | 42d9150f | 45 | 114332.05 | Sales | t | 2024-12-26 20:01:48.992993 2 | da648072 | 37 | 94975.66 | Legal | f | 2023-06-21 20:20:11.392203 3 | fa4fe54e | 31 | 108578.06 | Legal | f | 2026-06-14 10:41:03.661193 4 | 469205d7 | 55 | 92738.15 | Finance | t | 2024-01-16 03:31:14.870633 5 | 6ad4f01b | 65 | 121029.85 | HR | f | 2024-02-25 17:48:11.016703 (5 rows) ...
Next, retrieve the connection string from the read replica. First, make sure we can read the replicated data.
$ psql postgresql://rep-user-1:91W2s6pWoT4jkEG72Gm33dRQHE5wDO@192.168.20.13:5432/tenant03-primary psql (16.15 (Ubuntu 16.15-0ubuntu0.24.04.1), server 18.4 (VMware Postgres 18.4.0)) WARNING: psql major version 16, server major version 18. Some psql features might not work. SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help. tenant03-primary=# SELECT count(*) FROM mock_employees; count ------- 10900 (1 row) tenant03-primary=# SELECT * FROM mock_employees LIMIT 5; id | employee_code | age | salary | department | is_active | hire_date ----+---------------+-----+-----------+------------+-----------+---------------------------- 1 | 42d9150f | 45 | 114332.05 | Sales | t | 2024-12-26 20:01:48.992993 2 | da648072 | 37 | 94975.66 | Legal | f | 2023-06-21 20:20:11.392203 3 | fa4fe54e | 31 | 108578.06 | Legal | f | 2026-06-14 10:41:03.661193 4 | 469205d7 | 55 | 92738.15 | Finance | t | 2024-01-16 03:31:14.870633 5 | 6ad4f01b | 65 | 121029.85 | HR | f | 2024-02-25 17:48:11.016703 (5 rows)
That appears to be successful. The table exists, the number of entries seems to match the primary and the data looks reasonably good as well. What if we now try to write something on the read replica, such as trying to create a new table for example.
tenant03-primary=# CREATE TABLE new_mock_employees (id SERIAL PRIMARY KEY); ERROR: cannot execute CREATE TABLE in a read-only transaction
As expected, this operation failed since this is a read replica and writes are not permitted. To summarise, those of you who are familiar with secondary databases from previous DSM versions should see similarities with this workflow. It is more or less identical to the creation of a secondary database for Disaster Recovery (DR) use-cases that we saw in prior releases. Once the read replica is created, it can be used by read-only workloads. This is a very useful feature for customers who wish to scale their databases for read intensive workloads.
As per the guidance in the official documentation, starting with version 9.1.1, Data Services Manager provisions each PostgreSQL cluster with support for up to 12 replication slots by default, controlled by the max_replication_slots parameter. Of these, two slots are always reserved for high availability (HA). The system holds the HA reservation regardless of whether the cluster is running as standalone or in HA mode, so that converting a standalone cluster to a primary cluster with read replicas can always succeed without changing the max_replication_slots parameter. This leaves 10 replication slots available for user workloads out of the box, implying you can have up to 10 read replicas.
