EC2
RDS
Aurora
PostgreSQLNotes on installing psql on EC2 and then connecting to an already-built Aurora (PostgreSQL) environment.
Commands to Run
sudo yum search postgresql
sudo yum -y install postgresql.x86_64
psql -h <endpoint> -U <username> -d <database_name>
OS Information
[ec2-user@donald-dev-ec2-bastin ~]$ uname -a
Linux donald-dev-ec2-bastin x.xx.xxx-xxx.xxx.amzn2.x86_64 #1 SMP Thu Aug 15 15:29:58 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
Search for PostgreSQL client programs with yum search
From the results, postgresql.x86_64 is identified as PostgreSQL client programs.
[ec2-user@donald-dev-ec2-bastin ~]$ sudo yum search postgresql
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
================================================================================= N/S matched: postgresql ==================================================================================
freeradius-postgresql.x86_64 : Postgresql support for freeradius
pcp-pmda-postgresql.x86_64 : Performance Co-Pilot (PCP) metrics for PostgreSQL
postgresql.x86_64 : PostgreSQL client programs
postgresql-contrib.x86_64 : Extension modules distributed with PostgreSQL
postgresql-devel.x86_64 : PostgreSQL development header files and libraries
postgresql-docs.x86_64 : Extra documentation for PostgreSQL
postgresql-jdbc.noarch : JDBC driver for PostgreSQL
postgresql-jdbc-javadoc.noarch : API docs for postgresql-jdbc
postgresql-libs.x86_64 : The shared libraries required for any PostgreSQL clients
postgresql-libs.i686 : The shared libraries required for any PostgreSQL clients
postgresql-odbc.x86_64 : PostgreSQL ODBC driver
postgresql-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql-plpython.x86_64 : The Python2 procedural language for PostgreSQL
postgresql-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql-static.x86_64 : Statically linked PostgreSQL libraries
postgresql-test.x86_64 : The test suite distributed with PostgreSQL
postgresql-upgrade.x86_64 : Support for upgrading from the previous major release of PostgreSQL
qt-postgresql.x86_64 : PostgreSQL driver for Qt's SQL classes
qt-postgresql.i686 : PostgreSQL driver for Qt's SQL classes
qt3-PostgreSQL.x86_64 : PostgreSQL drivers for Qt 3's SQL classes
qt3-PostgreSQL.i686 : PostgreSQL drivers for Qt 3's SQL classes
qt5-qtbase-postgresql.x86_64 : PostgreSQL driver for Qt5's SQL classes
qt5-qtbase-postgresql.i686 : PostgreSQL driver for Qt5's SQL classes
PyGreSQL.x86_64 : A Python client library for PostgreSQL
apr-util-pgsql.x86_64 : APR utility library PostgreSQL DBD driver
libdbi-dbd-pgsql.x86_64 : PostgreSQL plugin for libdbi
perl-DBD-Pg.x86_64 : A PostgreSQL interface for perl
php-pgsql.x86_64 : A PostgreSQL database module for PHP
python-psycopg2.x86_64 : A PostgreSQL database adapter for Python
python-psycopg2-debug.x86_64 : A PostgreSQL database adapter for Python 2 (debug build)
python-psycopg2-doc.x86_64 : Documentation for psycopg python PostgreSQL database adapter
redland-pgsql.x86_64 : PostgreSQL storage support for Redland
rhdb-utils.x86_64 : Miscellaneous utilities for PostgreSQL - Red Hat Edition
tcl-pgtcl.x86_64 : A Tcl client library for PostgreSQL
Name and summary matches only, use "search all" for everything.
[ec2-user@donald-dev-ec2-bastin ~]$
Install PostgreSQL client programs
[ec2-user@donald-dev-ec2-bastin ~]$ sudo yum -y install postgresql.x86_64
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
amzn2-core | 2.4 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package postgresql.x86_64 0:9.2.24-1.amzn2.0.1 will be installed
--> Processing Dependency: postgresql-libs(x86-64) = 9.2.24-1.amzn2.0.1 for package: postgresql-9.2.24-1.amzn2.0.1.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-9.2.24-1.amzn2.0.1.x86_64
--> Running transaction check
---> Package postgresql-libs.x86_64 0:9.2.24-1.amzn2.0.1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
============================================================================================================================================================================================
Package Arch Version Repository Size
============================================================================================================================================================================================
Installing:
postgresql x86_64 9.2.24-1.amzn2.0.1 amzn2-core 3.0 M
Installing for dependencies:
postgresql-libs x86_64 9.2.24-1.amzn2.0.1 amzn2-core 235 k
Transaction Summary
============================================================================================================================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 3.3 M
Installed size: 17 M
Downloading packages:
(1/2): postgresql-libs-9.2.24-1.amzn2.0.1.x86_64.rpm | 235 kB 00:00:00
(2/2): postgresql-9.2.24-1.amzn2.0.1.x86_64.rpm | 3.0 MB 00:00:00
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 15 MB/s | 3.3 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql-libs-9.2.24-1.amzn2.0.1.x86_64 1/2
Installing : postgresql-9.2.24-1.amzn2.0.1.x86_64 2/2
Verifying : postgresql-9.2.24-1.amzn2.0.1.x86_64 1/2
Verifying : postgresql-libs-9.2.24-1.amzn2.0.1.x86_64 2/2
Installed:
postgresql.x86_64 0:9.2.24-1.amzn2.0.1
Dependency Installed:
postgresql-libs.x86_64 0:9.2.24-1.amzn2.0.1
Complete!
[ec2-user@donald-dev-ec2-bastin ~]$
Connect to Aurora (PostgreSQL)
When connecting, you need to allow connections from the EC2 bastion server to PostgreSQL via security groups.
[ec2-user@donald-dev-ec2-bastin ~]$ psql -h <endpoint> -U <username> -d <database_name>
Password for user master:
psql (9.2.24, server 10.7)
WARNING: psql version 9.2, server version 10.0.
Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.
aurorapostdb=>
Other Notes
If you run yum -y install postgresql.x86_64 right after building an EC2 instance, it references the default repository, so the psql version will be old. In that case, manually add a repository to download the desired psql version.
Reference:
Installing PostgreSQL on EC2 (Amazon Linux2) | my opinion is my own https://zatoima.github.io/postgresql-ec2-insatll.html