テクサポと学ぶ PostgreSQL #1 導入

PostgreSQL って何？とういうところから始めます！

#PostgreSQL

#RDS for PostgreSQL

村上紗敏

2023.12.17

この記事はアノテーション株式会社 AWS Technical Support Advent Calendar 2023 17日目の記事です。今年も誕生日での投稿になります！

アノテーション、テクニカルサポートチームの村上です。
以前は、どのデータベースエンジンに対しても特段の思い入れは無かったのですが、OSS-DB Silver Ver.3.0を受験してからは、Postgre 好きなエンジニアになりました。現在、OSS-DB Gold Ver.3.0の取得に向けて更に深い部分まで勉強をしています。
普段の業務では、RDS for PostgreSQL や Aurora PostgreSQL に対してのお問い合わせに対応していますが、本シリーズでは PostgreSQL を中心に機能や動作の説明をしていきます。

本シリーズでしていくこと

PostgreSQL に対しての基本的な知識を確認しながら、時には EC2 上にインストールした PostgreSQL 15 において DDL や DML 等のコマンドを実行して動作確認をしていきます。
また、業務で RDS for PostgreSQL や Aurora PostgreSQL に対してのお問い合わせに対応しているので、設定や運用面でお役に立つような情報も合わせてご紹介していきます。

What's PostgreSQL?

数年前まで私自身も、「PostgreSQL って何？それ、おいしいの？」という感じだったので、基本的なところから始めていきます。世の中に出回っているデータベースには、様々な種類のエンジンがあります。AWS の RDS で提供されているエンジンだと、MySQL、MariaDB、SQL Server、Oracle、Db2（最近加入）等です。 PostgreSQL は、数あるデータベースエンジンの中で世界シェア 4 位のデータベースエンジンです。(2023/12/17)

DB-Engines Ranking - Trend Popularity

OSS（オープンソースソフトウェア）の中では、 MySQL に次ぐシェアとなっています。グラフからも年々シェアを拡大しているのが分かる、勢いがあるデータベースエンジンです。
次の章では、OSS を扱う上では欠かせないライセンスについて確認していきます。

PostgreSQL ライセンス

OSS と言っても、実は様々な形態のライセンスが存在します。再配布にあたり比較的強い制限がある GPL ライセンスと比べて、PostgreSQL で採用されている PostgreSQL ライセンスは BSD ライセンスをベースに作成されているので、再配布にあたり変更されたコードについて公開義務が無い比較的自由なライセンスとなっています。

GNUライセンスに関してよく聞かれる質問
 BSDライセンス【Berkeley Software Distribution License】 BSDL

PostgreSQL ライセンスについては、PostgreSQL 開発元のサイトに以下のように掲載されています。
License

PostgreSQL is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses.

PostgreSQL Database Management System (formerly known as Postgres, then as Postgres95)

Portions Copyright © 1996-2023, The PostgreSQL Global Development Group

Portions Copyright © 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

PostgreSQL ライセンスで覚えておきたいのは、以下の 3 点です。
・コードを変更しても公開の義務がなく商業用で再配布することもできる
・万が一、直接的または間接的な損害が発生しても保証をしない
・メンテナンス、サポート、更新、拡張、または修正を提供する義務を負わない

私でもコードをいじる才能があれば、いろいろ拡張して商業用で販売することができるんですね。

EC2 へ PostgeSQL をインストールしてみる

PostgreSQL についての前置きはこれぐらいにして、次は手を動かして EC2 へ PostgreSQL をインストールしてみたいと思います。
EC2（Amazon Linux 2023）のリポジトリには、PostgreSQL 15 の RPM パッケージが準備されています。

Amazon Linux 2023 RPM packages as of the 2023.2.20231113 release

前提として、EC2 はパブリックサブネット上で起動させてあり、自宅の IP アドレスからのみ SSH での接続を許可しています。EC2 への SSH 接続設定に関しては、下記ブログを参考にしてください。

まずは、yum search postgresql15を実行してみて EC2（Amazon Linux 2023）のリポジトリに PostgreSQL 15 のパッケージが用意されているか確認をしてみます。

  
$ yum search postgresql15
Last metadata expiration check: 0:02:11 ago on Sat Dec  9 02:49:09 2023.
============================================================================================================================================ Name Exactly Matched: postgresql15 =============================================================================================================================================
postgresql15.x86_64 : PostgreSQL client programs
================================================================================================================================================ Name Matched: postgresql15 =================================================================================================================================================
postgresql15-contrib.x86_64 : Extension modules distributed with PostgreSQL
postgresql15-docs.x86_64 : Extra documentation for PostgreSQL
postgresql15-llvmjit.x86_64 : Just-in-time compilation support for PostgreSQL
postgresql15-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql15-plpython3.x86_64 : The Python3 procedural language for PostgreSQL
postgresql15-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql15-private-devel.x86_64 : PostgreSQL development header files for this build of PostgreSQL server
postgresql15-private-libs.x86_64 : The shared libraries required only for this build of PostgreSQL server
postgresql15-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql15-server-devel.x86_64 : PostgreSQL development header files and libraries
postgresql15-static.x86_64 : Statically linked PostgreSQL libraries
postgresql15-test.x86_64 : The test suite distributed with PostgreSQL
postgresql15-test-rpm-macros.noarch : Convenience RPM macros for build-time testing against PostgreSQL server
postgresql15-upgrade.x86_64 : Support for upgrading from the previous major release of PostgreSQL
postgresql15-upgrade-devel.x86_64 : Support for build of extensions required for upgrade process

なんだかごちゃごちゃと表示されましたが、リポジトリ内に PostgreSQL 15 のパッケージが準備されていることが確認できたので、yum install コマンドで EC2 へインストールをしてみます。
yum コマンドを利用すれば依存関係は自動で解決してくれるので、yum install postgresql15-server を実行して PostgreSQL 15 を EC2 へインストールしてみます。

  
$ sudo yum install postgresql15-server
Last metadata expiration check: 0:37:59 ago on Sat Dec  9 02:29:47 2023.
Dependencies resolved.
=============================================================================================================================================================
 Package                                                                                Architecture                                                        V
=============================================================================================================================================================
Installing:
 postgresql15-server                                                                    x86_64                                                              1
Installing dependencies:
 libicu                                                                                 x86_64                                                              6
 postgresql15                                                                           x86_64                                                              1
 postgresql15-private-libs                                                              x86_64                                                              1

Transaction Summary
=============================================================================================================================================================
Install  4 Packages

Total download size: 17 M
Installed size: 64 M
Is this ok [y/N]: y
Downloading Packages:
(1/4): postgresql15-private-libs-15.4-1.amzn2023.0.1.x86_64.rpm
(2/4): postgresql15-15.4-1.amzn2023.0.1.x86_64.rpm
(3/4): libicu-67.1-7.amzn2023.0.3.x86_64.rpm
(4/4): postgresql15-server-15.4-1.amzn2023.0.1.x86_64.rpm
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Total
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :
  Installing       : postgresql15-private-libs-15.4-1.amzn2023.0.1.x86_64
  Installing       : postgresql15-15.4-1.amzn2023.0.1.x86_64
  Installing       : libicu-67.1-7.amzn2023.0.3.x86_64
  Running scriptlet: postgresql15-server-15.4-1.amzn2023.0.1.x86_64
  Installing       : postgresql15-server-15.4-1.amzn2023.0.1.x86_64
  Running scriptlet: postgresql15-server-15.4-1.amzn2023.0.1.x86_64
  Verifying        : postgresql15-15.4-1.amzn2023.0.1.x86_64
  Verifying        : libicu-67.1-7.amzn2023.0.3.x86_64
  Verifying        : postgresql15-private-libs-15.4-1.amzn2023.0.1.x86_64
  Verifying        : postgresql15-server-15.4-1.amzn2023.0.1.x86_64

Installed:
  libicu-67.1-7.amzn2023.0.3.x86_64                                    postgresql15-15.4-1.amzn2023.0.1.x86_64                                    postgresql1

Complete!

initdb で、データベースクラスタを作成 & 初期化した後に、systemctl start postgresql で起動します。ついでに、systemctl enable postgresql を実行して起動し続けるように設定します。
この辺りのコマンドについては、私も EC2 へ RPM パッケージを利用して PostgreSQL を作成する時はこうするぐらいの感じでやっています。
本記事では最低限の設定で PostgreSQL を作成していますが、locale のようにデータベースクラスタ作成時、またはデータベース作成時にしか指定ができない設定があることを、覚えておいていただければと思います。

$ sudo postgresql-setup --initdb --unit postgresql
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log  
$ sudo systemctl start postgresql  
$ sudo systemctl enable postgresql

RPM パッケージを利用して PostgreSQL データベースクラスタを作成した場合、PostgreSQL のデフォルトユーザー（OS でいうルートユーザー）は postgres となります。なお、OS のルートユーザーは、PostgresSQL のデフォルトユーザーにはなれません。
initdb

サーバを root として実行することはできませんので、root で initdb を実行してはいけません（実際には、実行しようとしても拒否されます）。

initdb 実行時に OS 側のユーザー postgres も同時に作成されますが、パスワードの設定が無い為、パスワードを設定します。

$ sudo passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

OS 側のユーザーを、postgre へ変更した後に、psql ツールを利用してデータベースクラスタへ接続します。
PostgreSQL への接続ツールである psql についても、今後のシリーズで詳しく取り上げていきます！

$ su - postgres
Password:
[postgres@ip-172-31-43-247 ~]$ psql
psql (15.4)

少し細かいですが、psql 利用時に表示される psql (15.4) については、psql ツールのバージョンで、PostgreSQL のバージョンではありません。PostgreSQL のバージョンを確認するには、psql --version、もしくは、select version(); を実行することで確認できます。

  
postgres=# select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.4 on x86_64-amazon-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit
(1 row)

psql ツールのメタコマンド\lで、データベースクラスタ作成時に作成されるデータベース一覧を確認してみます。
データベースクラスタ作成時には、以下の 3 つのデータベースが作成されます。なぜ、3 つのデータベースが作成されるかについても、今後説明するので楽しみにお待ちください。

  
postgres-# \l
                                             List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
(3 rows)

無事に EC2 へインストールした PostgreSQL へ接続できたので、シリーズ初回はここまでにしたいと思います。次回は、デフォルトユーザー postgres に対して PostgreSQL ログイン時のパスワードを設定してみます。