CentOS8.2に、PostgreSQLにデータベースを作成し、アプリケーションから操作できるまでの一連の操作をまとめます。
- PostgreSQL インストール
- PostgreSQL データベースの作成 (いまここ)
- PostgreSQL contribモジュールの使い方
- PostgreSQL パラメタの設定方法
- PostgreSQL ベンチマークツールの紹介
- PostgreSQL WAL(Write Ahead Log)の基本説明
- PostgreSQL バックアップとリストア
- PostgreSQL PITR(Point In Time Recovery)基本概念の説明
- PostgreSQL PITR(Point In Time Recovery)操作方法の説明
- PostgreSQL PITR(Point In Time Recovery)タイムライン操作
- PostgreSQL ストリーミングレプリケーションの最小構成
- PostgreSQL ストリーミングレプリケーションのパラメタ説明
- PostgreSQL ストリーミングレプリケーションの障害復旧
- PostgreSQL ストリーミングレプリケーションの多段構成
- PostgreSQL ストリーミングレプリケーションのDR向け設定
- PostgreSQL ストリーミングレプリケーションのコンフリクト
- PostgreSQL 自動バキューム(AUTO VACUUM)
- PostgreSQL HOT(Heap Only Tuple)
- PostgreSQL インデックスのメンテナンス
- PostgreSQL 統計情報の更新
- PostgreSQL 実行計画
- PostgreSQL スロークエリの調査方法
データベースの作成
データベースの作成 – デフォルト設定
PostgreSQLでデータベースを作成するには、OSからcreatedbコマンドを実行するか、postgresqlにログインしCREATE DABASE文を実行するかのいずれの方法を使用します。
createdbコマンドを使用する例は以下の通りです。
[postgres@centos82 ~]$ createdb newdb1 [postgres@centos82 ~]$
CREATE DATABASE文を使う例は以下の通りです。
[postgres@centos82 ~]$ psql psql (13.1) Type "help" for help. postgres=# CREATE DATABASE newdb2 ; CREATE DATABASE postgres=#
データベースが作成された事を確認します。データベース作成時にencodeを指定しない場合は、template1の値がコピーされます。template1と同じencoding, collate, ctypeが設定されている事を確認します。
[postgres@centos82 ~]$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
newdb1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
newdb2 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
[postgres@centos82 ~]$
データベースの作成 – encode指定
データベース作成時にencodeなどを明示的に指定する事もできます。createdbコマンドを使用する例は以下の通りです。
[postgres@centos82 ~]$ dropdb newdb1 [postgres@centos82 ~]$ createdb newdb1 \ > --encoding=EUC_JP \ > --lc-collate=C \ > --lc-ctype=C \ > --template=template0 [postgres@centos82 ~]$
CREATE DATABASE文を使う例は以下の通りです。
postgres=# DROP DATABASE newdb2; DROP DATABASE postgres=# CREATE DATABASE newdb2 postgres-# TEMPLATE = 'template0' postgres-# ENCODING = 'SQL_ASCII'; CREATE DATABASE postgres=#
想定通りのデータベースが作成された事を確認します。
[postgres@centos82 ~]$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+-----------+-------------+-------------+-----------------------
newdb1 | postgres | EUC_JP | C | C |
newdb2 | postgres | SQL_ASCII | ja_JP.UTF-8 | ja_JP.UTF-8 |
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
[postgres@centos82 ~]$
データベースの作成 – template1の最作成
特段の指定をしない場合はtemplate1のencodingの設定がコピーされます。この設定を変更したい場合は、template1の再作成が必要です。
template1の再作成の操作例を以下に記します。
ALTER DATABASE template1 is_template false; DROP DATABASE template1 ; CREATE DATABASE template1 TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'; ALTER DATABASE template1 is_template true;
サンプルデータベースのインポート
動作確認のためにPostgreSQLチュートリアルとして使用されるデータベースをインポートします。
まずはデータベースを作成します。
createdb dvdrental
postgresユーザで以下コマンドを実行し、サンプルデータベースをインポートします。なお、URLがリンク切れになっていましたら、ブラウザでPostgreSQLチュートリアルのサイトを開き、最新のURLを確認してください。
wget https://sp.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip unzip dvdrental.zip pg_restore -d dvdrental dvdrental.tar rm dvdrental.tar dvdrental.zip
テーブル群が作成された事を確認します。
[postgres@centos82 ~]$ psql -d dvdrental
psql (13.1)
Type "help" for help.
dvdrental=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------------+----------+----------
public | actor | table | postgres
public | actor_actor_id_seq | sequence | postgres
public | actor_info | view | postgres
public | address | table | postgres
public | address_address_id_seq | sequence | postgres
public | category | table | postgres
public | category_category_id_seq | sequence | postgres
public | city | table | postgres
ユーザの作成
一般ユーザの作成
ユーザを作成するにはOSからcreateuserコマンドを使用するか、postgresqlにログインしCREATE USER文を実行するかのいずれの方法を使用します。
createuserコマンドの使用例を以下に示します。-S(–no-superuser), -D(–no-createdb), -R(–no-createrole) -P(–pwprompt)は文字通りの意味ですが、man createuserで詳細情報を得る事もできます。
[postgres@centos82 ~]$ createuser -S -D -R -P user01 Enter password for new role: Enter it again: [postgres@centos82 ~]$
CREATE USER文を使う例は以下の通りです。また、\passwordでパスワードを設定する事もできます。
[postgres@centos82 ~]$ psql psql (13.1) Type "help" for help. postgres=# CREATE USER user02; CREATE ROLE postgres=# \password user02 Enter new password: Enter it again: postgres=#
権限の付与
user01に全権限を、user02にSELECT権限を与えます。
PostgreSQLはデフォルトでpublicという名前のスキーマに属し、以下DDLはpublicという名前のスキーマに所属する全てのテーブル, シーケンス, ファンクションに対して権限を与える設定例です。
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user01; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO user01; GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO user01; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user02;
user01とuser02に権限が付与された事を確認します。
[postgres@centos82 ~]$ psql dvdrental;
psql (13.1)
Type "help" for help.
dvdrental=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+----------------------------+----------+---------------------------+-------------------+----------
public | actor | table | postgres=arwdDxt/postgres+| |
| | | user01=arwdDxt/postgres +| |
| | | user02=r/postgres | |
public | actor_actor_id_seq | sequence | | |
public | actor_info | view | postgres=arwdDxt/postgres+| |
| | | user01=arwdDxt/postgres +| |
| | | user02=r/postgres | |
public | address | table | postgres=arwdDxt/postgres+| |
| | | user01=arwdDxt/postgres +| |
| | | user02=r/postgres | |
<omitted>
ログイン確認および権限の確認
さきほどの手順で作成したユーザでPostgreSQLへログインできるかどうかを確認します。
psqlコマンドのシンタックスは以下の通りです。ユーザ名を指定してログインするには、データベース名の後にユーザ名を指定します。
psql [option...] [dbname [username]]
ログイン動作確認をするには、–host=127.0.0.1を指定してください。これは後述のpg_hba.confのデフォルト設定が、–hostを指定した場合に接続不可になっているためです。
user01の場合はSELECTとINSERTが可能である事を確認します。
INSERTを実行するにはINSERTの権限付与だけではなくテーブルに付随するシーケンスも権限付与が必要です。
[postgres@centos82 ~]$ psql --host=127.0.0.1 dvdrental user01
Password for user user01:
psql (13.1)
Type "help" for help.
dvdrental=> SELECT * FROM actor LIMIT 3;
actor_id | first_name | last_name | last_update
----------+------------+-----------+------------------------
1 | Penelope | Guiness | 2013-05-26 14:47:57.62
2 | Nick | Wahlberg | 2013-05-26 14:47:57.62
3 | Ed | Chase | 2013-05-26 14:47:57.62
(3 rows)
dvdrental=>
dvdrental=> INSERT INTO actor(first_name, last_name) values ('taro', 'yamada');
INSERT 0 1
dvdrental=>
user02の場合はSELECTのみ可能である事を確認します。
[postgres@centos82 ~]$ psql --host=127.0.0.1 dvdrental user02
Password for user user02:
psql (13.1)
Type "help" for help.
dvdrental=> SELECT * FROM actor LIMIT 3;
actor_id | first_name | last_name | last_update
----------+------------+-----------+------------------------
1 | Penelope | Guiness | 2013-05-26 14:47:57.62
2 | Nick | Wahlberg | 2013-05-26 14:47:57.62
3 | Ed | Chase | 2013-05-26 14:47:57.62
(3 rows)
dvdrental=>
dvdrental=> INSERT INTO actor(first_name, last_name) values ('jiro', 'yamada');
ERROR: テーブル actor へのアクセスが拒否されました
dvdrental=>
リモート接続の許可
listen_addresses
PostgreSQLはデフォルトでリモートからの接続を許可していません。デフォルト設定ではローカルホスト(127.0.0.1)
に対してのみlistenしている状態です。
[root@centos82 ~]# ss -ano | grep 5432 u_str LISTEN 0 128 /tmp/.s.PGSQL.5432 29618 * 0 u_str LISTEN 0 128 /var/run/postgresql/.s.PGSQL.5432 29616 * 0 tcp LISTEN 0 128 127.0.0.1:5432 0.0.0.0:* tcp LISTEN 0 128 [::1]:5432 [::]:* [root@centos82 ~]#
postgresql.confを以下のように編集します。
$ vi ${PGDATA}/postgresql.conf
<omitted>
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
listen_addresses = '0.0.0.0'
再起動で設定を反映させます。
systemctl restart postgresql-13.service
全てのIPアドレス(0.0.0.0)に対してlitsten状態になった事を確認します。
[root@centos82 ~]# ss -ano | grep 5432 u_str LISTEN 0 128 /tmp/.s.PGSQL.5432 41022 * 0 u_str LISTEN 0 128 /var/run/postgresql/.s.PGSQL.5432 41020 * 0 tcp LISTEN 0 128 0.0.0.0:5432 0.0.0.0:*
pg_hba.conf
リモートからのアクセスを許可するには、postgresql.confのlisten_addressesを編集するだけでなく、pg_hba.confの編集も必要です。
hbaはost-based authenticationの略称で、どのデータベースへ、誰が、どのネットワークから接続する事を許可するかの定義です。METHOD列は認証方法の定義で、peer, password, md5, scram-sha-256などを指定する事ができますが、パスワードが暗号化されるmd5またはscram-sha-256が推奨です。
設定例は以下の通りです。
$ vi ${PGDATA}/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv4 remote connections:
host dvdrental user01 192.168.0.0/16 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
再起動によって設定を反映させます。
systemctl restart postgresql-13.service
リモートのホストからの接続が可能である事を確認します。
[root@centos80 ~]# psql --host=192.168.63.82 dvdrental user01 ユーザ user01 のパスワード: psql (13.1) "help"でヘルプを表示します。 dvdrental=>
クライアント側の文字コード設定
デフォルトでは文字コードはデータベースに対して設定されたencodingの値が使用されますが、クライアント側で上書きする事もできます。以下に上書き方法を3通り示します。
いずれの設定方法もコネクションが維持されている間のみ有効でコネクションが切断されればリセットされます。
\encoding
\encodingコマンドで文字コードを上書きする事ができます。
[postgres@centos82 ~]$ psql psql (13.1) Type "help" for help. postgres=# \encoding UTF8 postgres=# \encoding sjis postgres=# \encoding SJIS postgres=#
CLIENT_ENCODING
変数CLIENT_ENCODINGを設定する事で文字コードを上書く事ができます。
[postgres@centos82 ~]$ psql psql (13.1) Type "help" for help. postgres=# \encoding UTF8 postgres=# SET CLIENT_ENCODING TO 'SJIS'; SET postgres=# \encoding SJIS postgres=#
libpg
PostgreSQLに接続する時に使用するC言語ライブラリlibpgに与える変数PQclientEncodingで文字コードを指定する事ができます。高級言語を使用する場合はlibpgを直接操作する事はなく、間接的にPQclientEncodingを指定することになります。例えば、pythonを使った場合の実装例は以下のようになります。
import psycopg2
connection = psycopg2.connect(\
host='192.168.63.82',\
user='user01',\
password='P@ssw0rd',\
database='dvdrental')
connection.set_client_encoding('sjis')
