Jignesh Shah's Blog: Setting up PostgreSQL 11 Beta 1 in Amazon RDS Database Preview Environment

PostgreSQL 11 Beta 1 has been out for more than couple of weeks. The best way to experience it is to try out the new version and test drive it yourself.

Rather than building it directly from source, I take the easy way out and deploy it in the cloud. Fortunately, it is already available in Amazon RDS Database Preview Environment.

For this post I am going to use the AWS CLI since it is easy to understand the command line and copy/paste it and also easier to script it for repetitive testing. To use the Database Preview environment, the endpoint has to be modified to use https://rds-preview.us-east-2.amazonaws.com/ instead of the default for the region.

Because there might be multiple PostgreSQL 11 beta releases possible, it is important to understand which build version is being deployed. I can always leave it to the default which typically would be the latest preferred version but lot of times I want to make sure on the version I am deploying. The command to get all the versions of PostgreSQL 11 is describe-db-engine-versions.

$ aws rds describe-db-engine-versions --engine postgres --db-parameter-group-family postgres11 --endpoint-url https://rds-preview.us-east-2.amazonaws.com/

{

"DBEngineVersions": [

{

"Engine": "postgres",

"DBParameterGroupFamily": "postgres11",

"SupportsLogExportsToCloudwatchLogs": false,

"SupportsReadReplica": true,

"DBEngineDescription": "PostgreSQL",

"EngineVersion": "11.20180419",

"DBEngineVersionDescription": "PostgreSQL 11.20180419 (68c23cba)",

"ValidUpgradeTarget": [

{

"Engine": "postgres",

"IsMajorVersionUpgrade": false,

"AutoUpgrade": false,

"EngineVersion": "11.20180524"

}

]

{

"Engine": "postgres",

"DBParameterGroupFamily": "postgres11",

"SupportsLogExportsToCloudwatchLogs": false,

"SupportsReadReplica": true,

"DBEngineDescription": "PostgreSQL",

"EngineVersion": "11.20180524",

"DBEngineVersionDescription": "PostgreSQL 11.20180524 (BETA1)",

"ValidUpgradeTarget": []

}

]

}

From the above, I see there are two versions 11.20180419 and 11.20180524. The versions are based on datestamp with the description showing the tag information of the version. Since I am interested in the BETA1 version I use the version 11.20180524.

$ aws rds create-db-instance --endpoint https://rds-preview.us-east-2.amazonaws.com --allocated-storage 100 --db-instance-class db.t2.small --db-name benchdb --master-username SECRET --master-user-password XXXXX --engine postgres --engine-version 11.20180524 --db-instance-identifier pg11beta1

Once deployed, I can always get the endpoint of the instance as follows:

$ aws rds describe-db-instances --endpoint=https://rds-preview.us-east-2.amazonaws.com --db-instance-identifier pg11beta1 |grep Address

"Address": "pg11beta1.XXXXXX.us-east-2.rds-preview.amazonaws.com"

In my account I have already added my client to my default security group,

$ psql -h pg11beta1.XXXX.us-east-2.rds-preview.amazonaws.com -U pgadmin -d benchdb -c 'SELECT VERSION()'

version

PostgreSQL 11beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

It is hard to test a database without any data. Normally I would just use pgbench directly on it and load data. But then I thought let me try a different way of loading data in this instance of the database similarly to how people will typically do from a different production setup. For this purpose, I will need to setup a production database before I proceed.

Before I create a production database instance, I first create a custom parameter group so that I can enable my typical settings that I use in a production database. In the preview environment I created a PostgreSQL 11 database family parameter group and edit the group to change some of the parameters as follows:

rds.logical_replication = 1

and saved the group.
Next, I create my production instance using the newly created parameter group.

$ aws rds create-db-instance --allocated-storage 100 --db-instance-class db.t2.small --engine postgres --db-name benchdb --master-username pgadmin --master-user-password SECRET --db-instance-identifier pg11prod

It is still empty so I filled it up with my production data.

$ pgbench -i -s 100 -h pg11prod.XXX.us-east-2.rds-preview.amazonaws.com -U pgadmin benchdb

Now, I have a typical setup with one production instance and another empty test instance. I know have to figure how to get the data into my test instance. I could always dump all data using pg_dump and restored it on the new instance but this time I am going to try logical replication.

For setting up logical replication between two instances I first need to recreate the schema on the other instance. pg_dump provides a flag -s to just dump the schema with no data. I dump the schema from the production setup

$ pg_dump -s -h pg11prod.XXXX.us-east-2.rds.amazonaws.com -U pgadmin benchdb > schema.txt

and then load the schema into my test setup

$ psql -h pg11beta1.XXXX.us-east-2.rds-preview.amazonaws.com -U pgadmin -d benchdb -f schema.txt

Now, I want to actually setup logical replication between the two users. For this I need a replication user. I could use the master password but that is too risky. So, I create a new user with read only privileges on the tables in the database and give it replication rights that will work in Amazon RDS.

$ psql -h pg11prod.XXXXX.us-east-2.rds-preview.amazonaws.com -U pgadmin benchdb

benchdb=> CREATE USER repluser WITH PASSWORD 'SECRET';

CREATE ROLE

benchdb=> GRANT rds_replication TO repluser;

GRANT ROLE

benchdb=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO repluser;

GRANT

Next, I have to setup a publication for all tables in the production database

benchdb=> CREATE PUBLICATION pgprod11 FOR ALL TABLES;
CREATE PUBLICATION

One more thing to add here is to change the inbound rules of the security group of the production instance to allow the test instance to connect.

On my test instance I need to create a subscription to subscribe to all changes happening on my production setup.

$ psql -h pg11beta1.XXXXX.us-east-2.rds-preview.amazonaws.com -U pgadmin benchdb

benchdb=> CREATE SUBSCRIPTION pg11beta1 CONNECTION 'host=pg11prod.XXXXXX.us-east-2.rds-preview.amazonaws.com dbname=benchdb user=repluser password= SECRET' PUBLICATION pgprod11;
NOTICE: created replication slot "pg11beta1" on publisher
CREATE SUBSCRIPTION

Note if the command itself is taking a long time to execute then typically it means that it cannot connect to the production instance. Check the security group to make sure the rule to allow your test instance to connect is set properly. If the connection is allowed then the command returns instantaneously. However, the actual data might be loading behind the scenes.

After some time, I can see that my test instance has all the initial data from the production setup.

benchdb=> select count(*) from pgbench_branches;
count
-------
100
(1 row)

benchdb=> select count(*) from pgbench_history;
count
-------
0
(1 row)

(The table pgbench_history is typically empty after a fresh setup of pgbench)

Now let's run application workload on our production database pg11prod

$ pgbench -c 10 -T 300 -P 10 -h pg11prod.XXXXX.us-east-2.rds-preview.amazonaws.com -U pgadmin -U pgadmin benchdb

As the load starts (after the initial vacuum), log into the test instance and check for changes. With pgbench default test, it is easy to verify changes by counting entries in pgbench_history.

$ psql -h pg11beta1.XXXXX.us-east-2.rds-preview.amazonaws.com -U pgadmin benchdb
psql (10.4 (Ubuntu 10.4-2.pgdg16.04+1), server 11beta1)
Type "help" for help.

benchdb=> select count(*) from pgbench_history;
count
-------
2211
(1 row)

benchdb=> select count(*) from pgbench_history;
count
-------
10484
(1 row)

This is a simple test to see changes are being propagated from the production instance to the test instance.

I, finally, have a logical replication using a read-only user between two PostgreSQL 11 instances in Amazon RDS Database Preview Environment.