hotch-potch, Note to self

いろいろ作業記録

PostgreSQL, Note to self

1.はじめに

PostgreSQLを仕事で使うことが増えてきたので、いろいろハマった内容を覚え書きしてます。(随時追記)

(実行環境)

$ uname -a
Linux dicom-sv11 5.13.0-27-generic #29~20.04.1-Ubuntu SMP Fri Jan 14 00:32:30 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

2.Docker活用

(1)Dockerイメージ内のpsqlコマンドを使う

ホスト側に、PostgreSQLのパッケージのインストールが不要となる。

参考

例:psqlコマンドで中に入る

$ /usr/bin/docker run \
    --rm -ti \
    -v `pwd`:/tmp \
    -w /tmp \
    --net=host \
    postgres:14 \
    env PGPASSWORD=<password> \
    /usr/bin/psql \
    -U <user> -h localhost -d <database> -p <port>
psql (14.5 (Debian 14.5-1.pgdg110+1))
Type "help" for help.

pacsdb=# exit

docker runするときに、-tiオプション(ttyとインタラクティブモードを有効)を忘れないこと。

例:sqlファイルを与えて実行

$  /usr/bin/docker run \
    --rm -t \
    -v $(pwd):/tmp \
    -w /tmp \
    --net=host postgres:14 \
    env PGPASSWORD=<password> \
    /usr/bin/psql --echo-all \
    -U <user> -h localhost -d <database> -p <port> -f <sql file>

sql file example

--- ページャを無効化
\pset pager off

---レコード件数
SELECT
  count(*) 
FROM
  files
; 

例:ダンプ

$ /usr/bin/docker run \
    --rm -t \
    -v `pwd`:/tmp \
    -w /tmp \
    --net=host \
    postgres:12.8 \
    env PGPASSWORD=<password> \
    /usr/bin/pg_dump [option] <database> -U <user> -h localhost -p <port> -f ./dumpfile.out 

オプションの例:pg_dump

オプション 意味
-Fp plain形式(SQL)でダンプ(デフォルト)
-Fc カスタム形式でのダンプ。通常はこれを選んでください
-Ft tar形式でのダンプ
--no-acl --no-owner アクセス権限(grant/revokeコマンド)のダンプを抑制、

例:リストア

  • ダンプ時-Fc-Ftを指定していた場合
$ /usr/bin/docker run \
    --rm -t \
    -v `pwd`:/tmp \
    -w /tmp \
    --net=host \
    postgres:12.8 \
    env PGPASSWORD=<password> \
    /usr/bin/pg_restore -d <database> -U <user> -h localhost -p <port> -v -c --if-exists  ./dumpfile.out 
オプション 意味
-v リストアの進捗がコンソール上に表示
-c データベース内のテーブルが削除されてからリストアが実行
--if-exists -cに付加して、DROP文にIF EXISTSが追加され、削除対象のテーブルが存在しない場合にDROP文が実行されなくなる

上記を実行して下記のようなエラーが出た場合は-Fpオプション(あるいはオプション省略)でダンプされています。

pg_restore: error: input file appears to be a text format dump. Please use psql.
  • -Fpオプションでダンプした場合は、psqlコマンドに直接、ダンプファイルを送り込む。
$ /usr/bin/docker run \
    --rm -t \
    -v `pwd`:/tmp \
    -w /tmp \
    --net=host \
    postgres:12.8 \
    env PGPASSWORD=<password> \
    /usr/bin/psql -d <database> -U <user> -h localhost -p <port> ./dumpfile.out 

(2)pdAdminをdocker-composeで導入

ディレクトリとファイルの準備

~/
  +- hoge/  (基準ディレクトリ)
    +- docker-compose.yml
    +- initdb/
      +- createtable.sql
  • docker-compose.ymlファイルの中身
# DockerCompose対応バージョン
version: '3'

# サービスの定義
services:

  # データベース
  postgres:
    # 使用するイメージ
    image: postgres:12.8
    # データベースコンテナのコンテナ名
    container_name: hoge_db
    # コンテナが止まったら再起動する
    restart: always
    # データベースコンテナのホスト名
    hostname: hogepgsqlsv
    # 環境変数
    environment:
      # データベースのユーザ
      POSTGRES_USER: hogeuser
      # データベースのデータベース側パスワード
      POSTGRES_PASSWORD: hogedbpassword
      # データベースのスーパーユーザ側パスワード
      PGPASSWORD: hogeuserpassword
      # データベースの名前
      POSTGRES_DB: hogedb
      # タイムゾーン
      TZ: "Asia/Tokyo"
    # ホスト側に接続するポート(ホスト側:コンテナ側)
    ports:
      - 35432:5432
    # ホスト側に接続するディレクトリ(ホスト側:コンテナ側)
    volumes:
      # データベースのデータ領域
      - postgres_hoge:/var/lib/postgresql/data
      # 初回起動時の初期化SQLファイルの置き場所(不要時にはコメントアウト)
      - ./initdb:/docker-entrypoint-initdb.d
    # コンテナ間をつなぐ仮想ネットワーク名
    networks:
      - hoge_net

  #pgadmin4
  pgadmin:
    # 使用するイメージ
    image: dpage/pgadmin4
    # データベースコンテナのコンテナ名
    container_name: hoge_pgadmin
    # コンテナが止まったら再起動する
    restart: always
    # データベースコンテナのホスト名
    hostname: hogepgadminsv
    # 環境変数
    environment:
      # データベースのユーザ(メールアドレス形式)
      PGADMIN_DEFAULT_EMAIL: hoge@fuga.foo
      # データベースのデータベース側パスワード
      PGADMIN_DEFAULT_PASSWORD: hogepassword
      # タイムゾーン
      TZ: "Asia/Tokyo"
    # ホスト側に接続するポート(ホスト側:コンテナ側)
    ports:
      - 38000:80
      # コンテナ間をつなぐ仮想ネットワーク名
    networks:
      - hoge_net
    depends_on:
      - postgres

# ホスト側のボリューム(データ保存先)定義
volumes:
  postgres:
    driver_opts:
      type: none
      # マウント先のディレクトリはあらかじめ作っておくこと
      # パーミッションは、docker-composeコマンドを実行するユーザに合わせておく
      device: /var/hoge/pgsql
      o: bind

# コンテナ間をつなぐ仮想ネットワーク名
networks:
  hoge_net:
  • createtable.sqlファイルの中身
--データベースのコメントを追加
COMMENT ON DATABASE hogedb IS 'for hoge database'; 

--テーブル差サンプル:
CREATE TABLE IF NOT EXISTS rawdata (
    --共通
    id          serial,
    organization    text,
    hostname    text,
    observed_at timestamp,
    node        text,
    --個別
    stdout      text,
    stderr      text,
    --付属情報
    insert_at   timestamp,
    --キー
    primary key (id),
    --重複を許可しない
    unique (observed_at, organization, hostname, node)
);
COMMENT ON TABLE rawdata IS 'for データ';
COMMENT ON COLUMN rawdata.id IS 'unique serial';
COMMENT ON COLUMN rawdata.organization IS '組織名';
COMMENT ON COLUMN rawdata.hostname IS 'ホスト名';
COMMENT ON COLUMN rawdata.observed_at IS 'クライアントでの観測時刻';
COMMENT ON COLUMN rawdata.node IS 'ノード名';
--
COMMENT ON COLUMN rawdata.stdout IS '標準出力';
COMMENT ON COLUMN rawdata.stderr IS '標準エラー出力';
--
COMMENT ON COLUMN rawdata.insert_at IS 'レコード挿入時刻';
#ディレクトリに移動
$ cd ~/hoge

# イメージのビルド:
$ docker-compose build

# 初回起動:
$ docker-compose up -d

# コンテナ一時停止・破棄なし:
$ docker-compose stop
# コンテナ再開:
$ docker-compose start
# コンテナ停止と破棄:
$ docker-compose down
# コンテナ停止と破棄+ボリュームも削除:
$ docker-compose down -v

# 起動中のコンテナのシェルに入る:
$ docker exec -it hoge_db /bin/bash
# ログのリアルタイム表示
$ docker logs -f hoge_db 

pgAdminのイメージをアップデート

$ cd ~/hoge

$ docker pull dpage/pgadmin4
Using default tag: latest
latest: Pulling from dpage/pgadmin4
59bf1c3509f3: Already exists 
6b2ef5224799: Pull complete 
…
Digest: sha256:70502b796a96f41859fa45c4cc82870d236075b47a6842163d7925c0ed9cbecd
Status: Downloaded newer image for dpage/pgadmin4:latest
docker.io/dpage/pgadmin4:latest

$ docker-compose down
[+] Running 3/3
 ⠿ Container hoge_pgadmin   Removed                                                                                                                 1.2s
 ⠿ Container hoge_db        Removed                                                                                                                 0.3s
 ⠿ Network docker_hoge_net  Removed

$ docker-compose up -d
[+] Running 3/2
 ⠿ Network docker_hoge_net  Created                                                                                                                 0.2s
 ⠿ Container hoge_db        Created                                                                                                                 5.9s
 ⠿ Container hoge_pgadmin   Created      

(3)pdAdminをnginxでリバースプロキシ

nginxをインストール

$ sudo apt -y install nginx

設定

$ sudo cp -p /etc/nginx/sites-available/default /etc/nginx/sites-available/default.original
$ sudo nano /etc/nginx/sites-available/default
# Default server configuration
#
server {
       listen 80 default_server;
       listen [::]:80 default_server;
…
       root /var/www/html;

       # Add index.php to the list if you are using PHP
       index index.html index.htm index.nginx-debian.html;

       server_name myserver1;

…

       #for pgAdminの書き方
       location /pgadmin/ {
               proxy_set_header X-Script-Name /pgadmin;
               proxy_set_header Host $http_host;
               proxy_redirect off;
               proxy_pass http://127.0.0.1:38000/;
       }

       #for 普通のアプリの場合の例
       location /app/ {
               proxy_pass http://127.0.0.1:8080/myapphoge/;
       }
}

設定の反映

$ sudo systemctl restart nginx
$ sudo systemctl status nginx 
● nginx.service - A high performance web server and a reverse proxy server
    Loaded: loaded (/lib/systemd/system/nginx.service; enabled; vendor preset: enabled)
    Active: active (running) since Fri 2022-03-12 11:38:39 JST; 2s ago
      Docs: man:nginx(8)
   Process: 701445 ExecStartPre=/usr/sbin/nginx -t -q -g daemon on; master_process on; (code=exited, status=0/SUCCESS)
   Process: 701446 ExecStart=/usr/sbin/nginx -g daemon on; master_process on; (code=exited, status=0/SUCCESS)
  Main PID: 701447 (nginx)
     Tasks: 5 (limit: 9309)
    Memory: 4.5M
    CGroup: /system.slice/nginx.service
            ├─701447 nginx: master process /usr/sbin/nginx -g daemon on; master_process on;
            ├─701448 nginx: worker process
            ├─701449 nginx: worker process
            ├─701450 nginx: worker process
            └─701451 nginx: worker process

312 11:38:38 myserver1 systemd[1]: Starting A high performance web server and a reverse proxy server...
312 11:38:39 myserver1 systemd[1]: Started A high performance web server and a reverse proxy server.

3.Python活用・psycopg3

最近、psycopg2→psycopg3に進んだ模様。

https://www.psycopg.org/psycopg3/

(1)pipインストール

バージョン番号は不要。

$ pip install psycopg

実行時にインポートエラー

couldn't import psycopg 'c' implementation: No module named 'psycopg_c'
couldn't import psycopg 'binary' implementation: No module named 'psycopg_binary'

以下を追加でインストール

$ sudo apt install libpq-dev 
$ pip install psycopg

(2)基本コード

#必要なライブラリをインポート
from typing import List
import psycopg

# 接続文字列
__conninfo = "host=localhost port=5432 dbname=<database> user=<username> password=<password> connect_timeout=10"

# 問い合わせSQL
# 例:テーブルのレコード件数をすべて取得
__query = '''
SELECT
  relname,n_live_tup
FROM
  pg_stat_user_tables 
WHERE
  schemaname='public'
'''

def select(conninfo: str, query: str) -> List:
    '''問い合わせ関数
    --
    Args:
        conninfo (str): 接続文字列
        conninfo (str): SQL
    Returns:
        List: 問い合わせ結果の配列
    '''
    try:
        # 接続
        with psycopg.connect(conninfo) as conn:
            retval = []
            # カーソルを取得
            with conn.cursor() as cur:
                try:
                    # クエリの実行
                    cur.execute(f"{query}")
                    # 反映
                    conn.commit()
                    # 結果を代入
                    retval = cur.fetchall()
                except:
                    # ロールバック
                    conn.rollback()
                    # 例外の内容表示
                    import traceback
                    traceback.print_exc()
    except:
        # 例外の内容表示
        import traceback
        traceback.print_exc()

    # 結果を返す
    return retval


# 問い合わせ実行
retval = select(__conninfo, __query)
# 問い合わせ結果を全件表示
for row in retval:
    print(row)

(3)unique制約ありのtext型の上限

CREATE TABLE IF NOT EXISTS chkproc (
    id          serial,
    observed_at timestamp,
    jresult     smallint,
    dpskey      text,

    primary key (id),
    unique (observed_at, draw)
)

INSERTの時に、長い文字列を与えるとエラーが出た。

psycopg.errors.ProgramLimitExceeded: index row requires 9424 bytes, maximum size is 8191

8191Bytesが上限らしいので、文字列長をトリムしてから代入する必要がある。

4.ステート取得

(1)最後に更新された日時を調べる

(1)全テーブルの件数を取得

(1)

9.各種エラーメッセージ

見慣れないエラーメッセージが出た時の、処置例を残します。

(1)日本語文字を含むSQLを実行したときに"UTF8" has no equivalent in encoding "WIN1252"

ポータブル版PostgreSQLでの使用例です。

CREATETABLEを実行の際に、COMMENTなどにUTF日本語文字を含むSQLを実行したときに以下のメッセージが出ました。

SQL   : character with byte sequence 0xe5 0x85 0xb1 in encoding "UTF8" has no equivalent in encoding "WIN1252"

原因と処置

データベースファイルの文字コードが対応できていないため、一番最初の、データベースの初期化initdbからやり直す必要があります。

コマンドライン、あるいはBATファイルで下記を実行します。

ポイントは--encoding=UTF8をオプションに追加する点です。 (そのほかはログのメッセージなどを強制的に英語にします)

set DATADIR="./data"
set USER="postgres"
set PASSWORDFILE="pw.txt"

"%CD%\bin\initdb" -D "%DATADIR%" -U "%USER%" --lc-collate="C" --lc-ctype="C" --encoding=UTF8 --pwfile="%PASSWORDFILE%"