PostgreSQL 10.6でテーブルにバイナリデータを挿入する

カテゴリ：OSSセットアップ | ソフトウェア：PostgreSQL | タグ：

最終更新日：2021/01/01 | 公開日：2019/07/14

概要

　PostgreSQLのテーブルにbytea型のカラムを定義して、バイナリデータを挿入するテストをやってみます。挿入するデータは何でも良いのですが、psqlから参照できるPostgreSQLのログファイル（テキスト）としました。より本格的に試すにはJavaやPHPなどのプログラムからデータの挿入・取り出しができる方が良いのですが、手軽に試したかったので、psqlのINSERTでデータを挿入し、COPY TOコマンドで取り出すことにしました。

　なお、PostgreSQLは10.6がインストールされて初期設定された状態から始めています。データベースやユーザはまだ作成していない環境を利用したので、CREATE ROLEやCREATE DATABASEから始めています。

構成

検証環境

　サーバにはAmazonのEC2インスタンスを利用しました。 AmazonマシンイメージはAWS標準のものではなく、AWS Marketplaceから取得しています。サーバのスペックは以下のとおりです。

■サーバスペック

項目	内容
Amazonマシンイメージ	CentOS 7 (x86_64) - with Updates HVM
インスタンスタイプ	t2.micro
vCPU	1
メモリ	1GiB
ディスク	SSD 8GiB
リージョン	アジアパシフィック (東京)

サーバ構成

OSバージョン

CentOS Linux release 7.5.1804 (Core)

ソフトウェア・パッケージ一覧

postgresql10-libs-10.6-1PGDG.rhel7.x86_64.rpm
postgresql10-server-10.6-1PGDG.rhel7.x86_64.rpm
postgresql10-10.6-1PGDG.rhel7.x86_64.rpm

手順

環境準備

データベースユーザ（ロール）の作成

　データベースの所有者となるデータベースユーザ（名前はpguser01）を作成します。操作は管理者ユーザのpostgresユーザで実行します。 OSのpostgresユーザとデータベースユーザのpostgresユーザが紐づいています。

$ sudo su -
Last login: Sat Dec 29 14:51:05 UTC 2018 on pts/0
# su - postgres
Last login: Sat Jul  6 16:32:14 UTC 2019 on pts/0
-bash-4.2$ psql
psql (10.6)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# create role pguser01 with login password 'Pass-123';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 pguser01  |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#

データベースの作成

　テスト用のデータベース（名前はtest_db）を作成します。データベースの所有者はpguser01とします。

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

postgres=# create database test_db owner pguser01;
CREATE DATABASE
postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 test_db   | pguser01 | UTF8     | C       | C     |
(4 rows)

postgres=#

作成したデータベースへ接続

　作成したデータベースに接続します。データベースユーザはpostgresのままです。

postgres=# \c test_db
You are now connected to database "test_db" as user "postgres".

スキーマの作成

　スキーマ（名前はデータベースユーザ名と同じpguser01）を作成し、デフォルトで存在するpublicスキーマは削除します。

test_db=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

test_db=# create schema pguser01 authorization pguser01;
CREATE SCHEMA
test_db=# \dn
   List of schemas
   Name   |  Owner
----------+----------
 pguser01 | pguser01
 public   | postgres
(2 rows)

test_db=# drop schema public;
DROP SCHEMA
test_db=# \dn
   List of schemas
   Name   |  Owner
----------+----------
 pguser01 | pguser01
(1 row)

test_db=# \q

テーブルの作成

　テスト用のテーブル（名前はtest01）を作成します。カラムは2つで、1つ目が連番を振るserial型（正確にはデータ型ではないですが）のid列、 2つ目がバイナリデータを格納するbytea型のdata列とします。 id列は主キーとします（今回のテストにはあまり関係ないです）。

-bash-4.2$ psql -U pguser01 test_db;
Password for user pguser01:　←pguser01のパスワードを入力
psql (10.6)
Type "help" for help.

test_db=> \dt
Did not find any relations.
test_db=> create table test01(
test_db(> id serial primary key,
test_db(> data bytea
test_db(> );
CREATE TABLE
test_db=> \dt
          List of relations
  Schema  |  Name  | Type  |  Owner
----------+--------+-------+----------
 pguser01 | test01 | table | pguser01
(1 row)

test_db=> \q

データベースの管理者ユーザにサーチパスの設定

以降の操作ではpsqlでOSファイルにアクセスするのですが、管理者ユーザ以外で実行するとファイルアクセス権限がないとエラーになるため、データベースの管理者ユーザ（ユーザ名：postgres）でログインして操作します。また、以降の操作でスキーマ名を明示的に指定しなくても済むように、サーチパスにスキーマ名pguser01を設定して作業を開始します。

-bash-4.2$ psql -U postgres test_db
psql (10.6)
Type "help" for help.

test_db=# select current_schema();
 current_schema
----------------

(1 row)

test_db=# set search_path = pguser01;
SET
test_db=# select current_schema();
 current_schema
----------------
 pguser01
(1 row)

test_db=#

事前確認

汎用ファイルアクセス関数でファイルリストの取得

　今回は試しにPostgreSQLのログファイルをバイナリデータとしてテーブルに挿入してみます。それに先立ってpg_ls_dir()という汎用ファイルアクセス関数でファイルリストを取得し、目的のファイル（PostgreSQLのログファイル）に関数でアクセスできることを確認します。この関数を実行するときのカレントディレクトリはデータベースクラスタのディレクトリになっているのかな（？）。 pg_ls_dir()では絶対パスを指定することができなかったので、データベースクラスタのディレクトリからの相対パス（log）を関数の引数に指定します。すると、今回の例ではログファイルが2つ存在していることが確認できました。

test_db=# select pg_ls_dir('log');
        pg_ls_dir
-------------------------
 postgresql-20181229.log
 postgresql-20190707.log
(2 rows)

test_db=#

汎用ファイルアクセス関数でファイルの内容をbytea型で返す

　PostgreSQLのログファイルを、テーブルのbytea型のカラムに挿入するために、ログファイルの内容をbytea型で取得できることを確認します。これにはpg_read_binary_file()関数を使用します。

test_db=# select pg_read_binary_file('log/postgresql-20181229.log');




                                                                                          pg_read_binary_file





------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
 \x323031382d31322d32392032333a33363a32332e373533204a5354205b323438325d204c4f473a202064617461626173652073797374656d20776
173207368757420646f776e20617420323031382d31322d32392032333a33363a3231204a53540a323031382d31322d32392032333a33363a32332e3
73537204a5354205b323437395d204c4f473a202064617461626173652073797374656d20697320726561647920746f2061636365707420636f6e6e6
56374696f6e730a323031382d31322d32392032333a35363a32362e373632204a5354205b323437395d204c4f473a202072656365697665642066617
3742073687574646f776e20726571756573740a323031382d31322d32392032333a35363a32362e373634204a5354205b323437395d204c4f473a202
061626f7274696e6720616e7920616374697665207472616e73616374696f6e730a323031382d31322d32392032333a35363a32362e373637204a535
4205b323437395d204c4f473a2020776f726b65722070726f636573733a206c6f676963616c207265706c69636174696f6e206c61756e63686572202
8504944203234383929206578697465642077697468206578697420636f646520310a323031382d31322d32392032333a35363a32362e373638204a5
354205b323438335d204c4f473a20207368757474696e6720646f776e0a323031382d31322d32392032333a35363a32362e383734204a5354205b323
437395d204c4f473a202064617461626173652073797374656d206973207368757420646f776e0a
(1 row)

test_db=#

バイナリデータの挿入

PostgreSQLのログファイルをbytea型のカラムに挿入

　PostgreSQLのログファイルをpg_read_binary_file()関数でbytea型に変換してbytea型のカラムに挿入します。ログファイルが2つ存在していたので、2回に分けて2つとも挿入します。

test_db=# insert into test01 (data) values (pg_read_binary_file('log/postgresql-20181229.log'));
INSERT 0 1
test_db=# insert into test01 (data) values (pg_read_binary_file('log/postgresql-20190707.log'));
INSERT 0 1

事後確認

テーブルにデータが2件挿入されたことを確認

　テストのために作成したtest01テーブルにデータが2件挿入されていることを確認します。

test_db=# select count(*) from test01;
 count
-------
     2
(1 row)

test_db=#

テーブルへ挿入されたデータの確認

　SELECTでテーブルに挿入されているデータを表示します。2件のバイナリデータが格納されています。

test_db=# select * from test01;
 id |







                                                                                          data









----+-------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------
  1 | \x323031382d31322d32392032333a33363a32332e373533204a5354205b323438325d204c4f473a202064617461626173652073797374656d
20776173207368757420646f776e20617420323031382d31322d32392032333a33363a3231204a53540a323031382d31322d32392032333a33363a32
332e373537204a5354205b323437395d204c4f473a202064617461626173652073797374656d20697320726561647920746f2061636365707420636f
6e6e656374696f6e730a323031382d31322d32392032333a35363a32362e373632204a5354205b323437395d204c4f473a2020726563656976656420
666173742073687574646f776e20726571756573740a323031382d31322d32392032333a35363a32362e373634204a5354205b323437395d204c4f47
3a202061626f7274696e6720616e7920616374697665207472616e73616374696f6e730a323031382d31322d32392032333a35363a32362e37363720
4a5354205b323437395d204c4f473a2020776f726b65722070726f636573733a206c6f676963616c207265706c69636174696f6e206c61756e636865
722028504944203234383929206578697465642077697468206578697420636f646520310a323031382d31322d32392032333a35363a32362e373638
204a5354205b323438335d204c4f473a20207368757474696e6720646f776e0a323031382d31322d32392032333a35363a32362e383734204a535420
5b323437395d204c4f473a202064617461626173652073797374656d206973207368757420646f776e0a
  2 | \x323031392d30372d30372030313a32323a33332e333836204a5354205b3834375d204c4f473a202064617461626173652073797374656d20
776173207368757420646f776e20617420323031382d31322d32392032333a35363a3236204a53540a323031392d30372d30372030313a32323a3333
2e333939204a5354205b3833375d204c4f473a202064617461626173652073797374656d20697320726561647920746f2061636365707420636f6e6e
656374696f6e730a323031392d30372d30372030313a33393a32372e343030204a5354205b313137325d204552524f523a20206d7573742062652073
757065727573657220746f20676574206469726563746f7279206c697374696e67730a323031392d30372d30372030313a33393a32372e343030204a
5354205b313137325d2053544154454d454e543a202073656c6563742070675f6c735f64697228272e27293b0a323031392d30372d30372030313a34
323a35302e303837204a5354205b313137325d204552524f523a20206d7573742062652073757065727573657220746f20676574206469726563746f
7279206c697374696e67730a323031392d30372d30372030313a34323a35302e303837204a5354205b313137325d2053544154454d454e543a202073
656c6563742070675f6c735f64697228272f746d7027293b0a323031392d30372d30372030313a34353a33372e363638204a5354205b313137325d20
4552524f523a20206d7573742062652073757065727573657220746f20726561642066696c65730a323031392d30372d30372030313a34353a33372e
363638204a5354205b313137325d2053544154454d454e543a202073656c6563742070675f726561645f62696e6172795f66696c6528272f7661722f
6c6f672f6d6573736167657327293b0a323031392d30372d30372030313a34393a33392e373431204a5354205b313230325d204552524f523a202061
62736f6c7574652070617468206e6f7420616c6c6f7765640a323031392d30372d30372030313a34393a33392e373431204a5354205b313230325d20
53544154454d454e543a202073656c6563742070675f6c735f64697228272f7661722f6c6f672f27293b0a323031392d30372d30372030313a35353a
31302e343039204a5354205b313230325d204552524f523a202072656c6174696f6e20227465737430312220646f6573206e6f742065786973742061
74206368617261637465722031330a323031392d30372d30372030313a35353a31302e343039204a5354205b313230325d2053544154454d454e543a
2020696e7365727420696e746f20746573743031202864617461292076616c756573202870675f726561645f62696e6172795f66696c6528276c6f67
2f706f737467726573716c2d32303138313232392e6c6f672729293b0a
(2 rows)

test_db=#

バイナリデータをファイル化

　テーブルに挿入されているbytea型のバイナリデータを取り出してファイル化します。 JavaやPHPなどのプログラムからファイル化したいところですが、確認の簡略化のためCOPY TOコマンドを使用します。ファイル化する際のファイル名は引数で固定的に指定していますが、データを挿入する際にファイル名もテーブルに格納しておいて、ファイル化する際に使用するのが一般的でしょうか。

test_db=# copy ( select data from test01 where id = 1 ) to '/tmp/postgresql-20181229.log' (format binary);
COPY 1
test_db=# copy ( select data from test01 where id = 2 ) to '/tmp/postgresql-20190707.log' (format binary);
COPY 1
test_db=# \q

ファイル化したバイナリデータの確認

　COPY TOコマンドでファイル化したバイナリデータをLinux上で確認します。実行結果を見ての通り、COPY TOコマンドの場合、ファイル内にヘッダとトレーラが付与されるため、テーブルに挿入した際のファイルの内容（ログファイルの状態）と同一にはなりません。ただ、動作確認のためだけなので今回はこれで良しとします。

-bash-4.2$ cd /tmp/
-bash-4.2$ ls -l
total 8
-rw-r--r--. 1 postgres postgres  605 Jul  6 18:08 postgresql-20181229.log
-rw-r--r--. 1 postgres postgres 1072 Jul  6 18:08 postgresql-20190707.log
drwx------. 3 root     root       17 Jul  6 16:22 systemd-private-0959e591bf4f45aa96c91d03ea531885-chronyd.service-vZ5Yx7
-bash-4.2$ cat postgresql-20181229.log
PGCOPY
�
B2018-12-29 23:36:23.753 JST [2482] LOG:  database system was shut down at 2018-12-29 23:36:21 JST
2018-12-29 23:36:23.757 JST [2479] LOG:  database system is ready to accept connections
2018-12-29 23:56:26.762 JST [2479] LOG:  received fast shutdown request
2018-12-29 23:56:26.764 JST [2479] LOG:  aborting any active transactions
2018-12-29 23:56:26.767 JST [2479] LOG:  worker process: logical replication launcher (PID 2489) exited with exit code 1
2018-12-29 23:56:26.768 JST [2483] LOG:  shutting down
2018-12-29 23:56:26.874 JST [2479] LOG:  database system is shut down
��-bash-4.2$
-bash-4.2$ cat postgresql-20190707.log
PGCOPY
�
2019-07-07 01:22:33.386 JST [847] LOG:  database system was shut down at 2018-12-29 23:56:26 JST
2019-07-07 01:22:33.399 JST [837] LOG:  database system is ready to accept connections
2019-07-07 01:39:27.400 JST [1172] ERROR:  must be superuser to get directory listings
2019-07-07 01:39:27.400 JST [1172] STATEMENT:  select pg_ls_dir('.');
2019-07-07 01:42:50.087 JST [1172] ERROR:  must be superuser to get directory listings
2019-07-07 01:42:50.087 JST [1172] STATEMENT:  select pg_ls_dir('/tmp');
2019-07-07 01:45:37.668 JST [1172] ERROR:  must be superuser to read files
2019-07-07 01:45:37.668 JST [1172] STATEMENT:  select pg_read_binary_file('/var/log/messages');
2019-07-07 01:49:39.741 JST [1202] ERROR:  absolute path not allowed
2019-07-07 01:49:39.741 JST [1202] STATEMENT:  select pg_ls_dir('/var/log/');
2019-07-07 01:55:10.409 JST [1202] ERROR:  relation "test01" does not exist at character 13
2019-07-07 01:55:10.409 JST [1202] STATEMENT:  insert into test01 (data) values (pg_read_binary_file('log/postgresql-20181229.log'));
��-bash-4.2$