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:
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.
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.
