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'

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 to your CLI commands.


$ aws rds describe-db-engine-versions --engine postgres \
  --query 'DBEngineVersions[*].EngineVersion' \
  --region us-east-2 --endpoint

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
    "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  


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 
        "HostedZoneId": "ZZOC4A7DETW6VV", 
        "Port": 5432, 
        "Address": ""


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 -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();
 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)


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


benchdb=> show 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;
benchdb=> CREATE EXTENSION postgis;
benchdb=> CREATE EXTENSION postgis_topology;

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.

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.


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

traininginstitute 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

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

traininginstitute 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

Data Scientist Course in Dombivli 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

traininginstitute 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

토토사이트 said...

I was looking for another article by chance and found your article오공슬롯 I am writing on this topic, so I think it will help a lot. I leave my blog address below. Please visit once. said...

Exellent your post. i reaching daily on your blogs. thanks for sharing this informative information.
Buy YouTube Views
Buy YouTube Subscribers
Buy YouTube Live Stream Views
Buy Facebook Live Views
Buy Instagram Reels Views
Buy Instagram Live Views
Buy Instagram Followers
Buy Facebook Views
Buy 500 YouTube Views

data science said...

All things considered I read it yesterday yet I had a few musings about it and today I needed to peruse it again in light of the fact that it is very elegantly composed.

Data Science said...

Amazingly by and large very interesting post. I was looking for such an information and thoroughly enjoyed examining this one. Keep posting.
An obligation of appreciation is all together for analytics course in gwalior

PMP Training in Malaysia said...

360DigiTMG, the top-rated organisation among the most prestigious industries around the world, is an educational destination for those looking to pursue their dreams around the globe. The company is changing careers of many people through constant improvement, 360DigiTMG provides an outstanding learning experience and distinguishes itself from the pack. 360DigiTMG is a prominent global presence by offering world-class training. Its main office is in India and subsidiaries across Malaysia, USA, East Asia, Australia, Uk, Netherlands, and the Middle East.

kumar said...

I will truly value the essayist's decision for picking this magnificent article fitting to my matter.Here is profound depiction about the article matter which helped me more.

Charlie Puth said...

I truly appreciate the time and work you put into sharing your knowledge. I found this topic to be quite effective and beneficial to me. Thank you very much for sharing. Continue to blog.

Data Engineering Solutions 

Artificial Intelligence Solutions

Data Analytics Solutions

Data Modernization Solutions

PMP Training in Malaysia said...

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon. digital marketing training

technologyforall said...

Best Online Data Science Course training with job assistance and internship opportunities .At ‘Technology For All’, we believe in sharing knowledge. Our focus is not just on teaching but involving you in each aspect of learning. With us, you get an opportunity to innovate on projects, work on case studies, gain quality industry knowledge, professional perspective and become job-ready.

Professional Career Technology said...

Start your Data Science Training from anywhere you like with 360DigiTMG, A world-class curriculum, LMS Access, real-time project, and assignments that will help you in bagging a good-paying job. Enroll now!

Data Science Course in Bangalore with Placement

Career Program and Skill Development said...

Data Analytics is a great field with multiple job opportunities. Start your career right with 360DigiTMG. Enroll today.

Best Data Science Training institute in Bangalore

Professional Academic Institute said...

With decision making becoming more and more data-driven, learn the skills necessary to unveil patterns useful to make valuable decisions from the data collected. Also, get a chance to work with various datasets that are collected from various sources and discover the relationships between them. Ace all the skills and tools of Data Science and step into the world of opportunities with the Best Data Science training institutes in Bangalore.

Data Science Course in Jaipur

Career Academic institute said...

360DigiTMG is the best Data Science Course Institute to start your Data Science journey. A world-class curriculum, LMS access, real-time projects, assignments will help you upscale your skills and bag your dream job.

Business Analytics Course in Jodhpur

Educational Training and Learning said...

Get a comprehensive overview of Data Science and learn all the essential skills including collecting, modeling, and interpreting data. Register with Data Science institute Bangalore and build a strong foundation for a career where you will be involved in uncovering valuable information for your organization. Learn Python, Machine Learning, Big Data, Deep Learning, and Analytics to take center stage in Data Science.

Data Science Training in Delhi

Educational Courses said...

Learn the fundamentals of Data Science and master the skills to handle epic amounts of data collected by organizations today. Gain expertise in organizing, analyzing, and transforming data to uncover its hidden power. Drive your career forward with a Data Science course in Bangalore and learn to uncover insights to present critical findings using visualization tools. Not only this, avail the best-in-class content delivered by stellar faculty who use a blended approach of theory as well as practical knowledge to ensure all the concepts are crystal clear.

Data Science Course in Delhi

Professional Courses and Training said...

Gain expertise in the relevant and current practices, challenges, and research involved in the field of Data Science. Learn all the skills from non-coding essentials to data science and machine learning by enrolling in the Data Science course in Bangalore. Using real data sets across a range of domains enhances your skills in supervised and unsupervised learning, neural networks, sampling techniques, support vector machines, and more. Get certified in Data Science to get one step ahead towards a lucrative career.

Data Scientist Course in Delhi

Islamic Info said...

Excellent for this particular beneficial and awesome blog page. I am profoundly glad to seek out this style of suggestions.
url opener
online filmek

newsmedia said...

Thank you for shearing post amazing this is post very useful for me.
New Media
Kiran Pichai
Latest News
Latest News
Latest News
Latest News
Latest News
Latest News

newsmedia said...

Thank you for shearing post amazing this is post very useful for me.
Adarsh Park Heights
Adarsh Park Heights price
Adarsh Property
Sumadharu Folium