前提

・RDS(Aurora PostgreSQL)が作成済みであること
・動作確認用OS:RedhatLinux 8.4

※RDS(Aurora PostgreSQL)作成したい場合、下記のブログを参照
CloudFormationでRDS(Aurora PostgreSQL リージョン別クラスター)を構築する

Aurora(PostgreSQL)に接続する手順

1.SSHでEC2を接続し、OSのバージョンを確認します。

cat /etc/redhat-release
Red Hat Enterprise Linux release 8.4 (Ootpa)

2.psqlのインストール

コマンド:

sudo yum search postgresql
sudo yum -y install postgresql.x86_64

実行例:

[ec2-user@ip-10-30-101-27 ~]$ sudo yum search postgresql
Updating Subscription Management repositories.
Unable to read consumer identity

This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.

Last metadata expiration check: 6:42:39 ago on Fri 05 Aug 2022 09:36:24 PM UTC.
=========================================================== Name & Summary Matched: postgresql ============================================================
postgresql.x86_64 : PostgreSQL client programs
freeradius-postgresql.x86_64 : Postgresql support for freeradius
pcp-pmda-postgresql.x86_64 : Performance Co-Pilot (PCP) metrics for PostgreSQL
postgresql-contrib.x86_64 : Extension modules distributed with PostgreSQL
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-odbc.x86_64 : PostgreSQL ODBC driver
postgresql-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql-plpython3.x86_64 : The Python3 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-server-devel.x86_64 : PostgreSQL development header files and libraries
postgresql-static.x86_64 : Statically linked PostgreSQL libraries
postgresql-test.x86_64 : The test suite distributed with PostgreSQL
postgresql-test-rpm-macros.x86_64 : Convenience RPM macros for build-time testing against PostgreSQL server
postgresql-upgrade.x86_64 : Support for upgrading from the previous major release of PostgreSQL
qt5-qtbase-postgresql.x86_64 : PostgreSQL driver for Qt5's SQL classes
qt5-qtbase-postgresql.i686 : PostgreSQL driver for Qt5's SQL classes
================================================================ Name Matched: postgresql =================================================================
postgresql-odbc-tests.x86_64 : Testsuite files for psqlodbc
postgresql-upgrade-devel.x86_64 : Support for build of extensions required for upgrade process
=============================================================== Summary Matched: postgresql ===============================================================
apr-util-pgsql.x86_64 : APR utility library PostgreSQL DBD driver
libpgtypes.x86_64 : Map PostgreSQL database types to C equivalents
libpgtypes.i686 : Map PostgreSQL database types to C equivalents
libpq.i686 : PostgreSQL client library
libpq.x86_64 : PostgreSQL client library
libpq-devel.x86_64 : Development files for building PostgreSQL client tools
libpq-devel.i686 : Development files for building PostgreSQL client tools
perl-DBD-Pg.x86_64 : A PostgreSQL interface for perl
php-pgsql.x86_64 : A PostgreSQL database module for PHP
postfix-pgsql.x86_64 : Postfix PostgreSQL map support
python-psycopg2-doc.x86_64 : Documentation for psycopg python PostgreSQL database adapter
python2-psycopg2.x86_64 : A PostgreSQL database adapter for Python 2
python2-psycopg2-debug.x86_64 : A PostgreSQL database adapter for Python 2 (debug build)
python2-psycopg2-tests.x86_64 : A testsuite for A PostgreSQL database adapter for Python 2
python3-psycopg2.x86_64 : A PostgreSQL database adapter for Python 3
python38-psycopg2.x86_64 : A PostgreSQL database adapter for Python 3
python38-psycopg2-doc.x86_64 : Documentation for psycopg python PostgreSQL database adapter
python38-psycopg2-tests.x86_64 : A testsuite for A PostgreSQL database adapter for Python 2
python39-psycopg2.x86_64 : A PostgreSQL database adapter for Python 3
python39-psycopg2-doc.x86_64 : Documentation for psycopg python PostgreSQL database adapter
python39-psycopg2-tests.x86_64 : A testsuite for A PostgreSQL database adapter for Python 2
rubygem-pg.x86_64 : A Ruby interface to the PostgreSQL RDBMS
[ec2-user@ip-10-30-101-27 ~]$ sudo yum -y install postgresql.x86_64
Updating Subscription Management repositories.
Unable to read consumer identity

This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.

Last metadata expiration check: 6:43:10 ago on Fri 05 Aug 2022 09:36:24 PM UTC.
Dependencies resolved.
===========================================================================================================================================================
 Package                    Architecture           Version                                                Repository                                  Size
===========================================================================================================================================================
Installing:
 postgresql                 x86_64                 10.21-2.module+el8.6.0+15342+53518fac                  rhel-8-appstream-rhui-rpms                 1.5 M
Installing dependencies:
 libpq                      x86_64                 13.5-1.el8                                             rhel-8-appstream-rhui-rpms                 198 k
Enabling module streams:
 postgresql                                        10

Transaction Summary
===========================================================================================================================================================
Install  2 Packages

Total download size: 1.7 M
Installed size: 6.2 M
Downloading Packages:
(1/2): libpq-13.5-1.el8.x86_64.rpm                                                                                         2.8 MB/s | 198 kB     00:00
(2/2): postgresql-10.21-2.module+el8.6.0+15342+53518fac.x86_64.rpm                                                          16 MB/s | 1.5 MB     00:00
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                      9.0 MB/s | 1.7 MB     00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                   1/1
  Installing       : libpq-13.5-1.el8.x86_64                                                                                                           1/2
  Installing       : postgresql-10.21-2.module+el8.6.0+15342+53518fac.x86_64                                                                           2/2
  Running scriptlet: postgresql-10.21-2.module+el8.6.0+15342+53518fac.x86_64                                                                           2/2
  Verifying        : libpq-13.5-1.el8.x86_64                                                                                                           1/2
  Verifying        : postgresql-10.21-2.module+el8.6.0+15342+53518fac.x86_64                                                                           2/2
Installed products updated.

Installed:
  libpq-13.5-1.el8.x86_64                                      postgresql-10.21-2.module+el8.6.0+15342+53518fac.x86_64

Complete!
[ec2-user@ip-10-30-101-27 ~]$

3.Aurora(PostgreSQL)へ接続

1.接続コマンドを実行し、パスワードが要求されます。
2.パスワードを入力し、接続できることを確認します。

コマンド:

psql -h <エンドポイント> -U <ユーザ名> -d <DB名>

説明
エンドポイント:AWSコンソール上のRDS画面を開き、対象DBクラスターを選択し、「エンドポイント名」をコピペする。
下記の画像を参照

ユーザ名:Aurora(PostgreSQL)作成した時に指定したユーザ
DB名:構築時設定したDB名
CloudFormationでRDS(Aurora PostgreSQL リージョン別クラスター)を構築するで作成する場合、ユーザ名とDB名を下記の画像に参照

file

file

実行例:

[ec2-user@ip-10-30-101-27 ~]$ psql -h test-rds-dev-aurora-posgre.cluster-ro-c5qwrqga2axq.ap-northeast-1.rds.amazonaws.com -U postgres -d test_db
Password for user postgres:
psql (10.21, server 13.7)
WARNING: psql major version 10, server major version 13.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

test_db=>

まとめ

今回、EC2(Red Hat Linux)からpsqlでAurora(PostgreSQL)に接続する手順を紹介しました。次回は、EC2からAurora(PostgreSQL)へのデータ導入方法を紹介したいと思います。

以上です。
少しでもお役に立てれば幸いです。

Last modified: 2022-08-06

Author