PostgreSQLの最近のブログ記事

昨夜、お客さんの DB データのメンテナンスをした。最新情報を CSV ファイルで欲しいということだったので、COPY コマンドでエクスポートしようとしたんだけど、

hogedb=# COPY m_hoge TO '/tmp/m_hoge_20180511.csv' WITH CSV DELIMITER ',' NULL AS '' HEADER FORCE QUOTE *;
ERROR:  syntax error at or near "*"
LINE 1: ...csv' WITH CSV DELIMITER ',' NULL AS '' HEADER FORCE QUOTE *;

て具合にエラーになる。FORCE QUOTE のワイルドカード文字 '*' が引っかかっているようだ。

うーん、なんで?
全カラムをダブルクォーテーションで囲むのであれば、FORCE QUOTE * じゃなかったっけ?

取り敢えず、

hogedb=# COPY m_hoge TO '/tmp/m_hoge_20180511.csv' WITH CSV DELIMITER ',' NULL AS '' HEADER FORCE QUOTE id,name,kubun,hoge1,hoge2,note,cdate,udate;
COPY 84

みたいに全項目名を書けば上手くいったけど、項目数が何十ってあるテーブルだってあるわけで、いちいちそれを羅列するの?・・・って話だなあ。

なんでワイルドカード使えんのやろ?

バージョンは PostgreSQL 8.4.20 でちょっと古いんだけど、8 の時ってワイルドカード、使えんかったっけ?

確かに、PostgreSQL 9.0.4 だったらエラーにならないなあ。
CentOS 上の PostgreSQL を Windows 8 Pro 上の A5:SQL Mk-2 64bit Edition Version 2.10.1(汎用SQL開発ツール/ER図ツール)を使って操作しているのだが、

character 0xfac9 of encoding "EUC_JP" has no equivalent in "UTF8"

のようなエラーが出て見れないテーブルがある。
元の DB が EUC-JP で作られており、それを UTF-8 に変換して結果を返そうとして PostgreSQL が出しているエラーのようである。
つまり、EUC-JP で 0xFAC9 である文字コードを、UTF-8 の 0xE6A192 に変換できないってことだな。

ちなみに問題の漢字は「桒」。「桑」の俗字なんだけど、この字を使った苗字とか存在してて(俺も一人知ってる)、「桒原」と書けば「くわばら」と読む。
一応、Shift_JIS や JIS、UTF-8、UTF-16 にも存在している文字である・・・が、「俗字」ということで、ハシラダカ「髙」なんかと同じように、OS や DBMS によって実装で色々もめている文字ではある(^^;

PostgreSQL のソースに含まれる EUC-JP→UTF-8 変換用のマッピングファイル(src/backend/utils/mb/Unicode/euc_jp_to_utf8.map)を確認すると、確かに 0xFAC9 から 0xE6A192 への変換情報は存在していないようだ。

 ~
  {0xf4a5, 0xe5879c},
  {0xf4a6, 0xe78699},
  {0x8fa2af, 0xcb98},
  {0x8fa2b0, 0xcb87},
 ~

0xF4A6 の次は、もういきなり 3バイト EUC コードの 0x8FA2AF になってる。
(0xF9A1~0xFCFE 間の 374文字は変換テーブルに載せてもらえてないようだ(^^;)

対応方法として、この間に、{0xfac9, 0xe6a192} という情報を追加してやって、もう一度 PostgreSQL をコンパイルしてやればいい。
(UTF-8→EUC-JP 変換用の utf8_to_euc_jp.map にも逆向きの情報を登録してやる必要あり)

が、これ、お客さんところの環境なので勝手にコンパイルするわけにもいかず(^^;

まあ、A5:SQL が UTF-8 への変換を要求しているのが問題なわけで、EUC-JP のまま使うのであれば問題ない。文字コード EUC-JP で SSH 接続して、ターミナル上で psql 使うしかないか(^^;

<参考>
PostgreSQL の話。

テスト用に DB のコピーを作る時、今までは「新しい DB を createdb で作成」「元の DB を pg_dump で丸々ダンプ(pg_dumpall に非ず)」「psql でダンプファイルから新しい DB にリストア」「ユーザ権限関係とか調整」とか色々作業してたんだけど、一発で DB のコピーできたんだね。

新しい DB を作る時に、

$ createdb -T old_db new_db

って感じでテンプレートデータベースとして元の DB を指定すれば、この DB の内容丸コピー(データも含めて)で新しい DB が作成されるんやね~
知らんかった。
まあ、そんなに何度もやることじゃないので、pg_dump 実行してほげほげで困らなかった(もう、この方法を知ってしまったので今後は pg_dump ほげほげ面倒くさいけれど(^^;)んだけど、今後はこの方法で一発やね。

ただ、この時、old_db がその瞬間使われていたら、

createdb: database creation failed: ERROR:  source database "old_db" is being accessed by other users

みたいなエラーが出ちゃう。
この時は、old_db へのセッションプロセスを全部 kill しちゃおう。

$ ps -auxww|grep post

とかすれば、

postgres  7186  0.0  0.0  6468 1600 pts/2    S+   14:20   0:00 psql old_db

こんなプロセスが引っかかるので、

$ kill -9 7186

とかして殺していく。
これで、createdb が問題なく出来るぞ。むふん。
仕様変更があって、既に本番稼働しているシステムに手を加えないといけなくなった。
具体的には、データベースのあるテーブルのあるカラムを VARCHAR(24) から VARCHAR(48) に変更する。

ま、使ってる DBMS が PostgreSQL なんで、psql で DB にログインして

ALTER TABLE table_hogehoge ALTER COLUMN column1 TYPE varchar(48);

実行するだけや・・・と、実際にうちのサーバにテスト環境作って確認。バッチリなので本番環境で実行してみると・・・

ERROR:  syntax error at or near "TYPE" at character 46

だって・・・(^^;

見てみたら、お客さんとこの PostgreSQL はバージョンが 7.4.19・・・
そりゃ駄目だ。上記のコマンドはメジャーバージョンが 8 からしか使えんし・・・
(ちなみにうちのテスト環境の PostgreSQL は 9.0.4)

仕方ないので、

BEGIN;
ALTER TABLE table_hogehoge ADD COLUMN new_column1 varchar(48);
UPDATE table_hogehoge SET new_column1 = CAST(column1 AS varchar(48));
ALTER TABLE table_hogehoge DROP COLUMN column1;
ALTER TABLE table_hogehoge RENAME COLUMN new_column1 TO column1;
COMMIT;

という、「新しい VARCHAR(48)なカラムを作って、元のカラムのデータを移し、元のカラムを削除した後で、新しいカラムの名前を元のカラムのものに変更」・・・という面倒臭い手順を経てサイズ変更には成功。

但し、この方法だとカラムの位置がずれるので(新しいカラムはテーブルの一番最後に追加される)、プログラムの中でテーブルの内容を二次元配列にセットしている箇所なんかは全部直さないといけなくなった。とほほ。

昔から俺は「変なサーバ」と呼んでる某地場企業のホスティングサーバで、海外のサーバ管理システムを使っているらしんだけど「セキュリティをガチガチにしたあげくに使い勝手が悪くなってる」典型みたいなサーバ(^^;
PostgreSQL も俺の方で勝手にバージョン上げたり出来ないのでどうしようもない。(何せ、本来有るはずの PostgreSQL のコマンドのいくつかも「セキュリティ上の理由」で削除されているような特殊な環境だ(^^;)

まあ、色々しがらみもあって「サーバ載せ替えて」というのも難しいので、しばらくこの「泥棒を入れないためにドアを溶接して住人も入れなくなった家」みたいなサーバとは付き合っていく必要があるのである(^^;
大した話ではないし、PostgreSQL のドキュメントにも記載があることなので、わざわざここでメモるほどのことではないのだが、うっかりエラーを出してドキっとしたので書いておく。

PostgreSQL で、既存テーブルに列を追加し、NOT NULL にする手順。

例えば、hoge テーブルに、chin と man という列を追加するのなら、以下の SQL を実行する。

ALTER TABLE hoge ADD COLUMN chin VARCHAR(1);
ALTER TABLE hoge ADD COLUMN man VARCHAR(1);

これでテーブルの末尾に上記2つの列が追加されるが、NULL も許可する形で作られている。

ADD COLUMN では、

ALTER TABLE hoge ADD COLUMN chin VARCHAR(1) NOT NULL;

というように、CREATE TABLE する時の書式(NOT NULL)が使えない。
後で、SET NOT NULL してやらないといけない。

・・・が、ADD COLUMN した後で、そのまますぐに SET NOT NULL するとエラーになる。

hoge=# ALTER TABLE hoge ALTER COLUMN chin SET NOT NULL;
ERROR:  column "chin" contains null values

「既に NULL 値がセットされちゃってるから、NOT NULL 出来ねえよ。こんにゃろ!」と PostgreSQL に怒られてるわけだ。まあ、そりゃそうだよな。設定とデータの中身がいきなり不整合ってことになっちゃうからな(^^;

そこで、一旦、

UPDATE hoge SET chin='0', man='0';

という SQL を実行して、追加した列に値(例 '0')をセットする。
これで、

ALTER TABLE hoge ALTER COLUMN chin SET NOT NULL;
ALTER TABLE hoge ALTER COLUMN man SET NOT NULL;

を実行しても、エラーは出ず、ちゃんとテーブルに NOT NULL 項目として追加される。

ああ、面倒くさい(^^;
またも、libpq.so.5 がらみのエラーで小一時間悩む・・・

実は、これで三回目である。
この問題で時間を無駄にしたのは・・・

シェル上での実行(ちなみに、root ユーザにて)ではまったく問題無いのに、CGI で実行すると、

install_driver(Pg) failed: Can't load '/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/Pg/Pg.so' for module DBD::Pg: libpq.so.5: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.8/i386-linux-thread-multi/DynaLoader.pm line 230, <DATA> line 855.
 at (eval 9) line 3
Compilation failed in require at (eval 9) line 3, <DATA> line 855.
Perhaps a required shared library or dll isn't installed where expected
 at ../hogehoge.cgi line 99

というエラーが出る。

結論から言おう。
これは、「POSTGRES_HOME のパーミッション」の問題(^^;

/usr/local/pgsql ディレクトリが 0700(所有者以外、参照すら不可)で作られているので、Apache の実行ユーザである daemon から libpq.so.5(/usr/local/pgsql/lib)の参照が出来ないために No such file or directory となっている。

つーことで、/usr/local/pgsql ディレクトリのパーミッションを 0755 に変更すれば解決。

以前(特に FreeBSD サーバメインだった頃)は、mkdir /usr/local/pgsql してから、vipw や vi /etc/group で直接ユーザー&グループの作成を行っていたので、POSTGRES_HOME(/usr/local/pgsql)が 0700 で作られることはなかったんだが、最近は手っ取り早く、

# groupadd -g 128 postgres
# useradd -g postgres -u 128 -c 'POSTGRES Admin' -d /usr/local/pgsql -m -s /bin/bash postgres

ってやっちゃうからな。これだと、/usr/local/pgsql が 0700 で作られちゃう。

ググっても、「httpd.conf で mod_env が有効になっているのを確認して SetEnv LD_LIBRARY_PATH /usr/local/pgsql/lib という記述を追加しよう」とか、「ldconfig してないんじゃないか?」的な情報が多いんだけど、俺がそんなミスするかよ!!

・・・って、俺のミスの方がよっぽど格好悪いけどな(^^; とほほほ・・・
いやあ、ついつい忘れてしまうんで、今回はブログに書いておこう。
(PostgreSQL 使いとしては今更なネタだけど(^^;)

さっき、うちのテスト用のマシン(FreeBSD 8.1-RELEASE on ASUS EeePC 4G-X(笑))で、プログラムの試験用に新しい DB 作ろうとしたんだけど(DBMS は PostgreSQL 9.0.1)、

%createdb hoge_db -E EUC_JP
createdb: database creation failed: ERROR:  new encoding (EUC_JP) is incompatible with the encoding of the template database (UTF8)
HINT:  Use the same encoding as in the template database, or use template0 as template.

って怒られた。
このマシンの DB のディフォルトの文字コードは UTF-8 なんだから、EUC-JP で新しい DB 作ろうとしても駄目やでぇってこと。

PostgreSQL の createdb は、実際には template1 という空の DB をコピーして新しい DB を作成する。
で、この template1 という DB には initdb した時に指定した文字コード情報が登録されているのだ。
だから、「UTF-8 の文字コードを使う DB をコピーして、EUC-JP の文字コードを使う DB は作れんでぇ」と怒られてる。

じゃあ、どうすればいいか?・・・だが、エラーメッセージに HINT として示されているように、template0 を使えば良いわけね。
template0 は、文字コードの情報を持っていない、本当にピュアな DB なので、他の文字コードの DB の元ネタになるってわけだね。

これは PostgreSQL 8.4 からの仕様のようなので、昔 PostgreSQL 使ってて、最近復活したって人は絶対やらかしそうな失敗(笑)
(まあ、うちみたいに、色々なお客さんの色々な文字コードの DB を作ってテストする立場の人間でないと、違う文字コードの DB を作ることもあまりないのかもしれんが)

つーことで、

%createdb hoge_db --encoding=EUC-JP --template=template0
CREATE DATABASE
%psql --list
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+----------
 hoge_db   | postgres | EUC_JP
 postgres  | postgres | UTF8
 template0 | postgres | UTF8
 template1 | postgres | UTF8
(4 rows)

で、バッチリOK!
やってもうた。小一時間、無駄にしてもうた(^^;;;

サーバ移行作業をして、あるテストサイトを動かそうとしたら、DB にアクセスするところで落ちる。
ちゃんと、DB の移行も終わってるのに!!

error_log を見てみると、

Can't locate DBD/Pg.pm in @INC

って・・・
たはっ。Pg.pm っていう Perl モジュール入れ忘れてた。(^^;
なので、CPAN でササッとインストール。
さて、今度はOKだろうとアクセスしてみると、また DB アクセスするところで落ちる。
今度は、

libpq.so.5: cannot open shared object file

って怒られた。

あ~ん?

/usr/local/pgsql/lib/libpq.so.5

は、ちゃんとあるぜぇ・・・と悩むこと小一時間(^^;;;

結局、ググってみると、自分のブログエントリー"「共有オブジェクトファイルを開けません」エラーではまった・・・"がヒットする始末。

そう、/usr/local/pgsql を 700 権限で作ってたがためのエラー。

chmod 755 /usr/local/pgsql

で、解決。

・・・インドに旅に出てきます・・・

psql -n hogedb とコマンド叩いて psql(PostgreSQL のターミナル型フロントエンド)を立ち上げ、

INSERT
INTO
    t_address
VALUES (
    '1',
    '742-0301',
    '岩国市周東町祖生',
    '2010-01-08 17:40:46'::timestamp
);

という SQL を実行しても、SELECT してみると、

hogedb=# SELECT * FROM t_address 
 uid |   post   |          address           |        cdate
-----+----------+----------------------------+------------------------
 1   | 742-0301 |                            | 2010-01-08 17:40:46
(1 rows)

という具合に住所が表示されない。
エラーは出てないので、登録されているはずだがなあ・・・

DB の Encoding は EUC_JP。

PuTTY の文字コードの設定が UTF-8/Auto-Detect Japanese になっていたので、EUC-JP に変えてみたが駄目。

Perl でちょいプロを作って、DBI/DBD 経由で書き込んでみたが同じ状況。エラーは出ないのだが、psql で該当テーブルを見てみると、日本語が表示されない。

今度は、SELECT する「ちょいプロ」作って確認してみよ・・・とプログラムを書きだしたところで、ハっと思いついて pg_dump hogedb > hogedb.txt と DB 内容のダンプを出力してみた。

そしたら、

--
-- Data for Name: t_address; Type: TABLE DATA; Schema: public; Owner: xxxxxx
--

COPY t_address (uid, post, address, cdate) FROM stdin;
1       742-0301        岩国市周東町祖生      2010-01-08 17:40:46

という具合にしっかり日本語部分も登録されているのが確認できた。

psql が日本語を表示してないだけか!?
う~ん、何でじゃろうね。
psql って、ページャーは more を使ってるんだったっけ?

なにせ、PostgreSQL 8.4.2 と CentOS 5.4 の組み合わせが初めてなのと、ここ数年、そういやあ新規開発案件は MySQL ばかりで、psql を使ってあれこれというケースもあまりなかったよ。
だから知らないのだが、最近の psql はこうなのか?

昔、psql が日本語対応してなかった時も、全然表示しないということはなかったからなあ。文字化けするだけで。

ま、取りあえず psql 使ってする作業は終わったので、これ以上原因は追及しないが、またも無駄な時間を過ごしてしまった。(^^;

阿呆だった。
誰がだ?俺がだ。

俺は駄目なヤツだ。
駄目なヤツは俺だ。

ああ、何ということだ。何ということだ。

こんな、あんな、そんな・・・ああ・・・

俺の4時間を返せぇ~

いや、何かというと、CentOS 上の Perl5.8 で PostgreSQL を DBD::Pg モジュール経由で使う設定をしたのだが、シェル上ではそのプログラムを実行出来るのに、Apache の CGI として実行すると駄目というケースが発生したのだ。

install_driver(Pg) failed: Can't load '/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Pg/Pg.so' for module DBD::Pg: libpq.so.5: 共有オブジェクトファイルを開けません: そのようなファイルやディレクトリはありません at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.
 at (eval 3) line 3
Compilation failed in require at (eval 3) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at ./test.cgi line 89

こんなヤツね。

root や postgres ユーザでシェル上で実行すれば正常に動くのに・・・
これの解決に(このことばかりやってたわけではないが)4時間もかかってしまい、こんな時間になっているわけである。とほほ。

「それは、Apache の httpd.conf に SetEnv LD_LIBRARY_PATH /usr/local/pgsql/lib って書けばいいのだよ!」とか言う?
確かに昔はそんなこともしてたけどなあ。
ちゃんと /usr/local/pgsql/lib も ldconfig で認識させているから問題ないのだよ。そんな問題ではないのだ。

ま、とは言いつつ、藁をもすがる気持ちで実は SetEnv もしてみたけどな。(^^;
もちろん意味はない。

結局、原因は単純だった。

/usr/local/pgsql のパーミッションが 0700 になっていただけ・・・(^^;
なので、root や、このディレクトリのオーナーである postgres だけしか(いくらちゃんとライブラリのパスを通していても)DBD::Pg モジュールが利用できなかったのだな・・・とほほ。

いつもなら、PostgreSQL をインストールするとき、あらかじめ root で mkdir /usr/local/pgsql して、インストール後に chown -R postgres /usr/local/pgsql とかするのよ。
そしたら、/usr/local/pgsql のパーミッションは 0755 で作成されるので問題なかったんだよ。

今回は、急ぎテスト環境を作りたかったので、postgres ユーザを作る時に、

useradd -g postgres -u 128 -c 'POSTGRES Admin' -d /usr/local/pgsql -m -s /bin/csh postgres

と、ホームディレクトリとして /usr/local/pgsql を作っちゃったのよ。これが不味かった。パーミッションが 0700 で作られてしまうからな。

・・・そうなんだよな。以前も別のエントリで書いたけど、俺は「BSD の人」なので、未だに Linux 系でホームディレクトリが 0700 で作られちゃうことに馴れてないのであります。(^^;

まったく。困った話だ。

結局、chmod 755 /usr/local/pgsql したら全て解決。

ググってみたら、けっこう同じような状況ではまっている人も多いので、恥をさらしてエントリを公開しておくのだ。

ああ、俺の貴重な睡眠時間が・・・

このアーカイブについて

このページには、過去に書かれたブログ記事のうちPostgreSQLカテゴリに属しているものが含まれています。

前のカテゴリはMySQLです。

次のカテゴリはSQLです。

最近のコンテンツはインデックスページで見られます。過去に書かれたものはアーカイブのページで見られます。

月別 アーカイブ

電気ウナギ的○○ mobile ver.

携帯版「電気ウナギ的○○」はこちら