Thursday, August 29, 2019

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.

18 comments:

Unknown said...

Did you realize there is a 12 word phrase you can say to your partner... that will induce deep emotions of love and instinctual appeal for you deep within his chest?

That's because deep inside these 12 words is a "secret signal" that triggers a man's instinct to love, look after and look after you with all his heart...

=====> 12 Words Will Trigger A Man's Desire Instinct

This instinct is so built-in to a man's mind that it will make him try better than ever before to make your relationship as strong as it can be.

Matter of fact, fueling this influential instinct is absolutely important to achieving the best ever relationship with your man that the moment you send your man one of the "Secret Signals"...

...You'll instantly find him expose his mind and heart to you in a way he never experienced before and he will perceive you as the one and only woman in the world who has ever truly fascinated him.

Balajee Nanduri said...

Well Written. Keep sharing more and more AWS Online Training
AWS Online Training India
AWS Online Training Hyderabad

bill.wood said...

With the combination of these parts, scientists can get a deeper insight into the data. machine learning courses in hyderabad

trainingcourses said...

It was good experience to read about dangerous punctuation. Informative for everyone looking on the subject.
business analytics course

Maneesha said...

You finished certain solid focuses there. I did a pursuit regarding the matter and discovered essentially all people will concur with your blog.
data scientist course

dataanalyticscourse said...

I find your opinion quite interesting, but the other day I stumbled upon completely different advice from another blogger, I need to think that one through, thanks for posting.
360DigiTMG certification on data analytics

360digiTMG Training said...

We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work

Data Science Training in Hyderabad

Mike Johnson said...

Do you think to make video about it and post on youtube. I know that it is very easy to make your video popular on youtube right now. You just can buy youtube likes and views from here https://viplikes.net

Abdulla said...

I read this article. I think You put a great deal of exertion to make this article.
buy instagram followers

Imon Kearns said...

Sankey diagram is a very useful visualization to show the flow of data. Can you shed light which tools are best and easy to use to draw Sankey Diagram.

James Zicrov said...

This is an exclusive post about PostgreSQL and the use of foreign data wrapper actually overcomes and helps people solve the most complex problems and errors.

SSIS postgresql read

Bhuvana said...

The Extraordinary blog went amazed by the content that they have developed in a very descriptive manner. This type of content surely ensures the participants explore themselves.

Data Science Training in Hyderabad

trainingcourses said...

I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
digital marketing courses in hyderabad with placement

Data Science Training in Hyderabad said...

This is such a great post, and was thinking much the same myself. Another great update…

Data Science Training in Hyderabad

data science said...

Great to become visiting your weblog once more, it has been a very long time for me. Pleasantly this article i've been sat tight fosuch a long time. I will require this post to add up to my task in the school, and it has identical subject along with your review. Much appreciated, great offer. data science course in nagpur

Mike Johnson said...

Can you make a video how you do it? You can publish such video on tiktok and youtube and get many likes from this site https://soclikes.com/

cloud computing course in bangalore said...

Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing. data scientist course in delhi

trainingcourses said...

First You got a great blog .I will be interested in more similar topics. i see you got really very useful topics, i will be always checking your blog thanks.
data science training in malaysia