Memo/PostgreSQL


実行時間の計測


pg_repack: ディスク使用量の削減


アップグレード


MySQLとのコマンド比較


クエリ結果を見やすく表示


テーブルやカラムにコメントを付ける


PostGIS


PostgreSQL9.x をCentOSにインストール

  1. OSに合わせてリポジトリを選択 http://yum.postgresql.org/repopackages.php
  2. インストール
    rpm -ivh http://yum.postgresql.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm
    yum install postgresql91 --enablerepo=pgdg91
    ldconfig

カンマ区切りのカラムから分割して取得

IDnumebr
11,2,3,4,5
26,7,8,9,10

PostgreSQL9.0をrpmからインストール


textsearch_sennaで全文検索


9.1でレプリケーション


9.0でレプリケーション


データベースサイズの確認

SELECT pg_database_size('データベース名')/1024/1024 || 'MB';

チューニング/最適化


pgbench


Senna + Ludiaで全文検索


カンマ区切りで取得


階層問い合わせ

ツリー構造で欲しい場合等。connectby()関数

$ su
# yum install postgresql-contrib
# exit
$ psql -U postgres doms -f /usr/share/pgsql/contrib/tablefunc.sql
$ psql -U postgres doms_dev -f /usr/share/pgsql/contrib/tablefunc.sql
$ psql -U postgres doms_tests -f /usr/share/pgsql/contrib/tablefunc.sql

シーケンスを最大値で更新

SELECT setval('シーケンス名', max(id)) FROM テーブル名;

正規表現


月末の取得


バージョンの取得


invalid multibyte character for locale HINT:

sha1


日付と時刻型の比較

created_atがtimestamp型で、指定日以下の行が欲しい場合。
castするか、秒まで指定しないと当日分は抽出されない。


あいまい検索


月で絞り込み

秒を日付に変換

時間間隔型

CREATE TABLE time_limit
(
	time interval
);

INSERT INTO time_limit VALUES ('0 day');
INSERT INTO time_limit VALUES ('1 day');
INSERT INTO time_limit VALUES ('30 days');
INSERT INTO time_limit VALUES ('1 year');
INSERT INTO time_limit VALUES ('10 years');

select CURRENT_TIMESTAMP + time from time_limit;
ptmp=> select CURRENT_TIMESTAMP + time from time_limit;
           ?column?            
-------------------------------
 2007-07-12 15:54:54.308539+09
 2007-07-13 15:54:54.308539+09
 2007-08-11 15:54:54.308539+09
 2008-07-12 15:54:54.308539+09
 2017-07-12 15:54:54.308539+09
(5 rows)

numericを'HH:MI:SS'に変換する

クエリーをcsv出力

圧縮ダンプ/リストア

カラムのデフォルト値変更

alter table テーブル名 alter カラム名 set default 'デフォルト値';
alter table テーブル名 alter カラム名 drop default;

ラージオブジェクトの情報を取得する

SELECT
    loid as "ID",
    pg_catalog.obj_description(loid, 'pg_largeobject') as  "Description"
FROM
    (SELECT DISTINCT
        loid
    FROM
        pg_catalog.pg_largeobject
    ) x
ORDER BY
    "ID"

カラム情報を取得する

SELECT
    attname,
    format_type(atttypid, atttypmod) as format_type,
    col_description(attrelid, attnum) as col_description
FROM
    pg_attribute,
    pg_class c
WHERE
    c.oid = attrelid AND
    attnum > 0 AND
    c.relname = 'tablename' ;

ログをsyslogに出力する

  1. postgresql.confの編集
    $ su - postgres
    $ vi data/data/postgresql.conf
    ----------------------------------
    syslog = 2                      # range 0-2; 0=stdout; 1=both; 2=syslog
    syslog_facility = 'LOCAL0'
    syslog_ident = 'postgres'
    ----------------------------------
  2. syslog.confの編集。/var/log/messagesには出力しないようにする
    # vi /etc/syslog.conf
    ----------------------------------
    *.info;mail.none;authpriv.none;cron.none;local0.none    /var/log/messages
    local0.*    /var/log/postgresql.log 
    ----------------------------------
    # service syslog restart
  3. logrotateに追加
    # vi /etc/logrotate.d/syslog
    1行目の行末に追加
    ----------------------------------
    /var/log/postgresql.log
    (
    ...
    )
    ----------------------------------
  4. 再起動
    # service syslog restart
    # service postgres restart
  5. ログは
    /var/log/message

パスワードファイル

VACUUM

条件を指定して一括Updateする

PostgreSQLでは、Update文にFrom句が指定できるので、柔軟な更新が可能です。
(OracleやMySQLでは使えません)

pgpoolで可用性を上げる

スクリプトをUTF-8で書く場合の注意点

sqlスクリプトファイルをUTF-8で保存する際に、BOM付きで保存すると、インポートの際にエラーが出ます。(PostgreSQL 7.4.7確認)
特に害は無いようですが、気になるようならば、BOM無し(UTF-8N)で保存すると良いようです。

$ psql -U apache -h localhost scott
scott=> \encoding UTF-8
scott=> \i import.sql

psqlのログを出力する

参照制約の取得

SELECT
	conname,
	pg_catalog.pg_get_constraintdef(oid) as condef
FROM
	pg_catalog.pg_constraint r
WHERE
	r.contype = 'f'

シーケンス一覧の取得

SELECT
 c.relname as "Name"
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'S'
     AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
     AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1;

シーケンスの値をテーブルの値で更新する

テーブルの値を用いて、シーケンスの値を更新する時は、bigintにcastする必要がある。

シーケンス関数

select nextval('sample_seq');次の値の取得
select currval('sample_seq');現在値の取得。nextval実行後のみ有効。
select setval('sample_seq', 1);値設定。

psqlが内部で発行しているsqlを表示する

psqlに「-E」オプションを付けて起動する。

psql -E
testdb=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
 c.relname as "Name",
 CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
 u.usename as "Owner"
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
     AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
     AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

bytea型を\copyで出力した形式と同等な文字列を生成する

bytea型を\copyで出力すると「\\000\\001...」とエスケープされるが、 同等の処理をする関数が存在しない。
例えば、PHPのpg_escape_bytea()は、いくつかの制御文字をエスケープしないので、 TSV形式に出力できない。

そこで、以下の関数を利用すれば、\copy句の高速インポートができるようになる。

標準的な設定

$ su - postgres
$ cd data
$ vi postgresql.conf

tcpip_socket = true

$ vi pg_hba.conf

# Unix ドメインソケットを使用する接続
local   all         all                                             trust

# TCP/IP ネットワークを使用する接続
host    all         all         127.0.0.1         255.255.255.255   trust 
$ pg_ctl restart

PostgreSQL 7.4 -> 7.3へリストアする

7.3でバックアップしたデータを、7.3へリストアするとエラーが頻発します。
どうやら、シーケンスの構文が違うようです。
そこで、PLAINテキストで出力し、

pg_dump データベース名 > データベース名.backup

以下の文字を削除します。

   INCREMENT BY 1
   NO MAXVALUE
   NO MINVALUE

PostgreSQL 7.3で文字化けする場合

データベースがUNICODE(UTF-8)、PHPがEUC-JPの場合、PostgreSQL7.3と7.4では挙動が違います。

PostgreSQL文字化けの有無
7.3有り
7.4無し

どうも7.4ではPHP内部文字コードに合わせて自動で変換してくれるようです。
7.3では「pg_set_client_encoding("EUC_JP");」等明示的に指定してあげないと、文字化けします。
 内部文字コードを取得し、PostgreSQLに渡す例

pg_set_client_encoding(mb_internal_encoding());

Oracleから移植する時のTIPS

Oracleから移植する際に同等の事をする関数は、

OraclePostgreSQL
SYSDATECURRENT_TIMESTAMP
NVLCOALESCE
DECODECASE 〜 WHEN 〜 THEN

to_date()とto_timestamp()の違い

Oracleと違い、日付関数により結果が違うので注意。
よって、timestamp型のフィールドと比較する際はto_timestamp()を用いる事。

関数結果
to_date('2005/01/27 20:27:01', 'YYYY/MM/DD HH24:MI:SS')2005/01/27
to_timestamp('2005/01/27 20:27:01', 'YYYY/MM/DD HH24:MI:SS')2005/01/27 20:27:01

クライアントの文字コードを指定する

DBがUnicodeでターミナルがEUCだとselect時に文字化けします。
これを指定するには「PGCLIENTENCODING」を設定します。

phpPgAdmin

文字化けする場合は

/etc/php.iniのmbstring.internal_encodingやmbstring.http_outputを確認する。
mbstring.http_outputがEUC-JPでも、mbstring.internal_encoding=UTF-8にする事で文字化けを解消できた。

/etc/httpd/conf/httpd.conf

<Directory "/var/www/html/phpPgAdmin">
       php_value mbstring.internal_encoding UTF-8
</Directory>

httpdの再起動。

service httpd restart

設定ファイル

phpPgAdmin/conf/config.inc.php

設定を追加するには、$conf['servers'][1]['desc']などと添え字を増やす。

変数名意味
$conf['servers'][0]['desc']表示上の名前
$conf['servers'][0]['host']接続先名 or ip。空白でローカルホストに接続
$conf['servers'][0]['port']ポート。デフォルト5432
$conf['servers'][0]['defaultdb']デフォルトで接続するDB
$conf['servers'][0]['pg_dump_path']pg_dumpのパス
$conf['servers'][0]['pg_dumpall_path']pg_dumpallのパス

PostgreSQL操作ツール

pgAdminIIIhttp://www.pgadmin.org/download/windows.phpWindows用クライアント。8.0対応、日本語可。一応7.4も操作できた。
phpPgAdminhttp://phppgadmin.sourceforge.net/?page=downloadphp上で操作するツール。7.4、8.0対応。日本語可。
csehttp://www.hi-ho.ne.jp/tsumiki/PostgreSQL、Oracle、MySQL、ODBC等扱えるツール。日本語。

TSV(タブ区切りテキスト)ファイルへのインポート/エクスポート

CSVファイルへエクスポート

psqlの\copyコマンドを使う。

\copy テーブル名 to 'csvファイル名' with delimiter as ',' null as ''

CSVファイルのインポート

psqlの\copyコマンドを使う。
カンマの数とテーブルのカラム数は一致させる必要がある。
漢字が含まれているファイルはEUC-JP/LFにしておく必要がある。
「null as ''」句でNULLを挿入できる。

\copy テーブル名 from 'csvファイル名' with delimiter as ',' null as ''

iptablesの設定

psqlでTCP/IP接続でエラーが出る場合は、iptablesを確認する。

psql -U "ユーザー名" -d "db名"で接続できないとき

以下のようなエラーが出て、接続できない場合は、pg_hba.confを編集し、PostgreSQLを再起動させます。
標準の認証はIDENT認証なので、これをpasswordやmd5にします。

エラー:

-bash-2.05b$ psql -U ユーザー名 -d データベース名
psql: FATAL:  IDENT authentication failed for user "ユーザー名"

修正:

$ vi /var/lib/pgsql/data/pg_hba.conf

# ident認証からパスワード認証へ変更
#local  all    all             ident   sameuser
local  all    all             password   sameuser

$ pg_ctl restart

OracleのスキーマをPostgreSQLで使えるSQLに変換する

PostgreSQLのソース中にPerlで書かれたツールがあります。

postgresql-7.4.6/contrib/oracle/ora2pg.pl

既存のOracleDBに接続して、PostgreSQLのSQLに変換してくれます。
ora2pg.pl を開いて、下記を修正します。

BEGIN {
    $ENV{ORACLE_HOME} = 'オラクルホームのパス';
}
# Initialyze the database connection
my $dbsrc = 'dbi:Oracle:host=localhost;sid=OracleのSID';
my $dbuser = 'ユーザー名';
my $dbpwd = 'パスワード';
my $schema = new Ora2Pg (
debug => 0,
...
);

実行は以下の通りです。成功すると、output.sqlが生成されます。

perl ora2pg.pl

PostgreSQL7.3からdatetime型が使えなくなった

PostgreSQL7.3からdatetime型が使えなくなり、timestamp型に変わった。
ただ、それでは困る場合、ユーザー定義型で強引にdatetime型を作ってしまう方法がある。

INSERT INTO pg_catalog.pg_type SELECT 'datetime', typnamespace, typowner,
typlen, typbyval, typtype, typisdefined, typdelim, typrelid, typelem,
typinput, typoutput, typreceive, typsend, typalign, typstorage,
typnotnull, typbasetype, typtypmod, typndims, typdefaultbin, typdefault
from pg_catalog.pg_type where typname = 'timestamp';

INSERT INTO pg_catalog.pg_type SELECT '_datetime', typnamespace,
typowner, typlen, typbyval, typtype, typisdefined, typdelim, typrelid,
typelem, typinput, typoutput, typreceive, typsend, typalign, typstorage,
typnotnull, typbasetype, typtypmod, typndims, typdefaultbin, typdefault
from pg_catalog.pg_type where typname = '_timestamp';

Windows用コマンドラインツール

・Linuxと同じようなpsqlコマンドがwindowsで使えるようになる。
http://cre-ent.skcapi.co.jp/~saito/developer/PostgreSQL-7.4.3/psql-windows/

ODBCドライバについて

開発元が異なるバージョンが複数存在する


添付ファイル: fileget_nocase_text.php 4210件 [詳細]

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2020-02-17 (月) 15:09:02