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 'レコード挿入時刻';
- docker-composeの操作
#ディレクトリに移動 $ 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 3月 12 11:38:38 myserver1 systemd[1]: Starting A high performance web server and a reverse proxy server... 3月 12 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が上限らしいので、文字列長をトリムしてから代入する必要がある。
４．ステート取得
（１）最後に更新された日時を調べる
（１）全テーブルの件数を取得
（１）
９．各種エラーメッセージ
見慣れないエラーメッセージが出た時の、処置例を残します。
（１）日本語文字を含むSQLを実行したときに"UTF8" has no equivalent in encoding "WIN1252"
ポータブル版PostgreSQLでの使用例です。
- PostgreSQL Portable download | SourceForge.net
- 【PostgreSQL】インストールなしで使用する（ポータブル版） | PostgresWeb - ポスグレウェブ
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%"
