Setting up PostgreSQL 12 Beta 3 for Testing in Amazon RDS

One of the amazing things about the PostgreSQL community is launching releases like clockwork. On 8/8/2019 the PostgreSQL community not only launched the minor versions for PostgreSQL 11 and old major versions but also a new Beta 3 version for upcoming PostgreSQL 12.

On AWS, you can check versions of PostgreSQL available in your region as follows:

       

$ aws rds describe-db-engine-versions --engine postgres --query 'DBEngineVersions[*].EngineVersion'
[
    "9.3.12", 
...  
    "11.2", 
    "11.4"
]
       
 

You will not see any beta versions out there. Pre-release versions for PostgreSQL in AWS  are available in the Database Preview Environment within US East 2 (Ohio).  If you are using the cli you have to add the region us-east-2 and also the url endpoint  rds-preview.us-east-2.amazonaws.com to your CLI commands.

       

$ aws rds describe-db-engine-versions --engine postgres \
  --query 'DBEngineVersions[*].EngineVersion' \
  --region us-east-2 --endpoint https://rds-preview.us-east-2.amazonaws.com
[
    "12.20190617", 
    "12.20190806"
]
       
 

The versions displayed are bit cryptic but they denote the  major version followed by date  when the build was synced for the preview release. The version description will be more friendly to read than the version itself.

       

$ aws rds describe-db-engine-versions --engine postgres \
  --query 'DBEngineVersions[*].DBEngineVersionDescription' \
  --region us-east-2 --endpoint https://rds-preview.us-east-2.amazonaws.com
[
    "PostgreSQL 12.20190617 (BETA2)", 
    "PostgreSQL 12.20190806 (BETA3)"
]
       
 

Lets deploy an instance of PostgreSQL 12 Beta 3 aka version 12.20190806.

       

$ aws rds create-db-instance  \
--engine postgres  --engine-version 12.20190806 --db-instance-identifier mypg12b3 \
--allocated-storage 100 --db-instance-class db.t2.small     \
--db-name benchdb  --master-username pgadmin  --master-user-password SuperSecret \
--region us-east-2 --endpoint  https://rds-preview.us-east-2.amazonaws.com  

       
 

After couple or few minutes the end point will be available and can be queried as follows:

       

$ aws rds describe-db-instances  --db-instance-identifier mypg12b3 --query 'DBInstances[].Endpoint' \
--region us-east-2 --endpoint  https://rds-preview.us-east-2.amazonaws.com 
[
    {
        "HostedZoneId": "ZZOC4A7DETW6VV", 
        "Port": 5432, 
        "Address": "mypg12b3.c9zz9zzzzzzz.us-east-2.rds-preview.amazonaws.com"
    }
]

       
 

If you have a default vpc security group defined in US East 2 (Ohio), you should be able to use the latest psql client to connect to it based on your default rules. If you do not have a default vpc security group, a new security group is created for you to which you have to add your client to the database instance. The security group will be in your US-East-2 (Ohio) region EC2 security groups for the preview environment.

 Once you have your client added to the security group, your client will be able to connect to the database as follows:

       

$ psql -h mypg12b3.c9zz9zzzzzzz.us-east-2.rds-preview.amazonaws.com -d benchdb -U pgadmin 
Password for user pgadmin: 
psql (10.4, server 12beta3)
WARNING: psql major version 10, server major version 12.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

benchdb=> select version();
                                                  version                                                  
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 12beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)

benchdb=> 
       
 

For this given major version, the supported extensions can be queried as follows:

       

benchdb=> show rds.extensions;
                                                                                                                                                                                                        
                                                                                                    rds.extensions                                                                                      
                                                                                                                                                                                                        
              
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------
 address_standardizer, address_standardizer_data_us, amcheck, aws_commons, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, fuzzystrmatch, hstore, hstore_plperl,
 intagg, intarray, ip4r, isn, jsonb_plperl, log_fdw, ltree, pageinspect, pg_buffercache, pg_freespacemap, pg_prewarm, pg_similarity, pg_stat_statements, pg_trgm, pg_visibility, pgcrypto, pgrouting, pg
rowlocks, pgstattuple, pgtap, plperl, plpgsql, pltcl, postgis, postgis_tiger_geocoder, postgis_topology, postgres_fdw, prefix, sslinfo, tablefunc, test_parser, tsm_system_rows, tsm_system_time, unacce
nt, uuid-ossp
(1 row)
       
 

Extensions are created using your master username login as follows:

       

benchdb=> CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
benchdb=> CREATE EXTENSION postgis;
CREATE EXTENSION
benchdb=> CREATE EXTENSION postgis_topology;
CREATE EXTENSION
       
 

To verify the versions of the extensions that I have created.

       
benchdb=> select * from pg_extension;
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion  |   extconfig   |          extcondition           
-------+--------------------+----------+--------------+----------------+-------------+---------------+---------------------------------
 14299 | plpgsql            |       10 |           11 | f              | 1.0         |               | 
 16402 | pg_stat_statements |       10 |         2200 | t              | 1.7         |               | 
 16410 | postgis            |       10 |         2200 | f              | 3.0.0alpha3 | {16712}       | {"WHERE NOT (                  +
...
       |                    |          |              |                |             |               | )"}
 17418 | postgis_topology   |       10 |        17417 | f              | 3.0.0alpha3 | {17421,17434} | {"",""}
(4 rows)
       
 

One of the recent enhancements available since PostgreSQL 11 release in Amazon RDS is that pg_stat_statements library is now loaded by default unless explicitly disabled. This means I can immediately use pg_stat_statements after creating the extension.

       
benchdb=>     
select * from pg_stat_statements order by total_time desc limit 4;
 userid | dbid  |       queryid        |               query               | calls | total_time  |  min_time   |  max_time   |  mean_time  | stddev_time | rows | shared_blks_hit | shared_blks_read | s
hared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time 
--------+-------+----------------------+-----------------------------------+-------+-------------+-------------+-------------+-------------+-------------+------+-----------------+------------------+--
-------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------
     10 | 16384 | -6310040060866956228 | select pg_start_backup($1, $2)    |     1 | 4934.715563 | 4934.715563 | 4934.715563 | 4934.715563 |           0 |    1 |               0 |                0 |  
                 0 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0
     10 | 16384 |  4124339773204179264 | select pg_stop_backup()           |     1 | 4656.211207 | 4656.211207 | 4656.211207 | 4656.211207 |           0 |    1 |               0 |                0 |  
                 0 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0
  16394 | 16396 | -2030728853740878493 | CREATE EXTENSION postgis          |     1 |  691.652456 |  691.652456 |  691.652456 |  691.652456 |           0 |    0 |           71359 |              247 |  
               835 |                 707 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0
  16394 | 16396 | -2651058291501154175 | CREATE EXTENSION postgis_topology |     1 |   61.100119 |   61.100119 |   61.100119 |   61.100119 |           0 |    0 |            8539 |               26 |  
                57 |                  37 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0
(4 rows)

       
 

Note the instances in preview environment are meant for development and testing for 60 days with your applications to try out new features and optimize them for PostgreSQL 12 !



This blog represents my own view points and not of my employer, Amazon Web Services.