Memo/PostgreSQL

Memo/PostgreSQL †

• Memo/PostgreSQL/constraint
• Memo/PostgreSQL/pgpool
• Memo/PostgreSQL/pgpool-II

• 記事
  • PostgreSQLでTRUNCATEをROLLBACKした際の内部の挙動を追いかけてみた ｜ Developers.IO

---

role/user権限 †

• roleにもパスワード指定ができる。
• user/groupはroleの別名。
• 推奨: roleに権限を付与。userにroleを付与。

• PostgreSQL ユーザーとロールの管理 | Amazon Web Services ブログ

• readonly role(user)

  CREATE ROLE readonly WITH LOGIN PASSWORD '****';
  GRANT CONNECT ON DATABASE mydatabase TO readonly;
  GRANT USAGE ON SCHEMA public TO readonly;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

• role(user)がもっている権限一覧

  SELECT 
      grantee,
      table_schema,
      TABLE_NAME,
      privilege_type,
      grantor
  FROM information_schema.table_privileges
  WHERE grantee = 'readonly';

記事:

• RDS PostgreSQLで参照専用のユーザを作成する（複数のスキーマがあっても一発で） #PostgreSQL - Qiita

---

psqlオプション †

• -t: タプル(値)のみ出力。ヘッダ等は出力しない
• -A: 桁合わせのためのスペースを出力しない
• -F '|':区切り文字指定

---

psqlでパスワード指定 †

• PostgreSQL: Documentation: 16: 34.15. Environment Variables

• 環境変数 "PGPASSWORD" に設定する。historyに残らないようにreadを使う

  read -sp "PGPASSWORD: " PGPASSWORD
  export PGPASSWORD
  
  psql -U readonly -d example -h localhost
  
  # Or
  PGPASSWORD="***" psql -U readonly -d example -h localhost

• -w: パスワードなし
• -W: パスワードプロンプト必須

---

データの匿名化/難読化/マスク処理 †

• 本番データをマスク処理して、検証データを作成するなど

• GitHub - GreenmaskIO/greenmask: PostgreSQL dump and obfuscation tool
  • Backup/Restoreに対応

---

他DBからの移行 †

記事:

• MySQLからPostgreSQLに移行する際のTips - そーだいなるらくがき帳

---

実行時間の計測 †

• 記事
  • PostgreSQLのpsqlで実行時間だけ欲しい場合 - なからなLife

---

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

• pg_repack/pg_repack_jp.rst at master · reorg/pg_repack
  • PostgreSQL 9.1 以降

• 記事
  • RDS Postgresqlのディスク使用量を削減する(pg_repack) ｜ DevelopersIO

---

アップグレード †

• 記事
  • 3TB超のCacooのPostgreSQL 9.3を9.5にアップグレードした話

---

MySQLとのコマンド比較 †

• 記事
  • MySQLとPostgreSQLコマンド比較表 - Qiita

---

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

• '\x': MySQLの'\G'相当。

  test=# \x
  拡張表示は on です
  test=# select * from users;
  test=# select * from users;
  -[ RECORD 1 ]
  id   | 1
  name | satou
  -[ RECORD 2 ]
  id   | 2
  name | suzuki

---

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

• COMMENT

  createdb -U postgres -E utf8 test
  psql -U postgres test
  # \encoding utf8
  # create table users (id int);
  # COMMENT ON TABLE  users IS 'ユーザ';
  # COMMENT ON COLUMN users.id IS 'ユーザID';

• psqlコマンドで一覧を見るとき

  test=# \encoding utf8
  
  test=# \d+                                                                                                                              
                      リレーションの一覧
   スキーマ | 名前  |    型    |  所有者  | サイズ  |  説明  
  ----------+-------+----------+----------+---------+--------
   public   | users | テーブル | postgres | 0 bytes | ユーザ
  (1 行)
  
  test=# \d+ users                                                                                                                               
                テーブル "public.users"
   カラム |   型    | 修飾語 | ストレージ |   説明   
  --------+---------+--------+------------+----------
   id     | INTEGER |        | plain      | ユーザID
  OID を持つ: no

---

PostGIS †

• 記事
  • geo - 国土交通省のデータを使って、緯度経度から市区町村までを取り出す - Qiita [キータ]
  • [ThinkIT] 第1回：MapServerとは (1/3)

---

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

• 環境
  • CentOS 6.3 x86_64
  • PostgreSQL 9.1

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

---

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

• cloudpackブログ - cloudpack（クラウドパック）Amazon EC2などクラウドの導入設計、運用・保守サービス: Postgres カンマ区切りを分割

• PostgreSQL 8.3以降で regexp_split_to_table() 関数が使える

  SELECT id,regexp_split_to_table(NUMBER, ',') AS NUMBER FROM hoge;
  ---------------------------
  ID,NUMBER
  1 1  
  1 2  
  1 3  
  1 4  
  1 5  
  2 6  
  2 7  
  2 8  
  2 9  
  2 10  
  ---------------------------

---

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

• CentOS5.6

  # PostgreSQL 9.0用リポジトリ
  rpm -Uvh http://yum.pgsqlrpms.org/9.0/pgdg-centos-9.0-2.noarch.rpm
  perl -p -i -e 's#enabled\s*=\s*1#enabled=0#' "/etc/yum.repos.d/pgdg-90-centos.repo"
  
  
  # PostgreSQL9.0でapr依存でエラーがでるので、compat-postgresql-libsをインストール
  ARCH=`uname -i`
  local arch="i686"
  if [ "$ARCH" == "" ]; then
  	$arch=$ARCH
  fi
  wget -q http://www.pgrpms.org/9.0/redhat/rhel-5.0-$ARCH/compat-postgresql-libs-4-1PGDG.rhel5.$arch.rpm
  rpm -Uvh --force compat-postgresql-libs-4-1PGDG.rhel5.$arch.rpm
  rm -f compat-postgresql-libs-4-1PGDG.rhel5.$arch.rpm
  
  yum -y install postgresql postgresql-server postgresql-contrib --enablerepo=pgdg90
  
  su - postgres -c 'initdb --no-locale --encoding=UTF8'
  
  service postgresql-9.0 restart
  chkconfig postgresql-9.0 on

• bin
  • /usr/pgsql-9.0/bin/
• data
  • /var/lib/pgsql/9.0/data/

---

textsearch_sennaで全文検索 †

• textsearch_senna

• 記事
  • textsearch_sennaインストールめも - あられねこのめも

---

9.1でレプリケーション †

• 高可用性、負荷分散およびレプリケーション
  • 中間一致検索でインデックスを使うには 9.1 の段階では、ソースコード中の設定を一部書き換えて、再コンパイルする必要がある

• 記事
  • suz-lab - blog: PostgreSQL(9.1)でレプリケーション
  • 第21回しくみ勉強会：全文検索（後編） - PostgreSQL 雑記 - postgresqlグループ
  • PostgreSQL 9.1 の新機能  Let's Postgres

---

9.0でレプリケーション †

• 高可用性、負荷分散およびレプリケーション

• 記事
  • PostgreSQL 9.0 の新機能  Let's Postgres
  • クイックスタート
  • レプリケーション

• CentOS5.5へPostgreSQL9.0をyumでインストールする - bose999の試験管の中の話

  rpm -Uvh http://yum.pgsqlrpms.org/9.0/pgdg-centos-9.0-2.noarch.rpm
  perl -p -i -e 's#enabled\s*=\s*1#enabled=0#' "/etc/yum.repos.d/pgdg-90-centos.repo"
  
  yum install postgresql-server --enablerepo=pgdg90

---

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

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

---

チューニング/最適化 †

• 記事
  • SQLを速くするぞ―お手軽パフォーマンス・チューニング

• あなたの遅延はどこから？ SQLから！ 〜患部に止まってすぐ効くSQLレビューチェックリスト 年初め特大サービス号〜 - ANDPAD Tech Blog

• RDBのトラブルの現場を追え！ / rdb-Troubleshooting - Speaker Deck
• PostgreSQLのチューニング事例 - Qiita
• PostgreSQLパフォーマンスチューニングのために　〜重いSQLの見つけ方〜 : アシアルブログ

---

pgbench †

• 【コラム】Yet Another 仕事のツール (51) PostgreSQLとMySQLのベンチマークテスト(1) - pgbench | エンタープライズ | マイコミジャーナル

• pgbench 8.1.18-2.el5_4.1

  yum install postgresql-server postgresql-contrib --disablerepo=pgdg83
  service postgresql start
  su - postgres
  createdb pgbench
  pgbench -i pgbench
  
  100クライアント(-c)が100トランザクション(-t)を実行した場合のベンチマーク
  pgbench -c 100 -t 100 pgbench
  
  dropdb pgbench

---

Senna + Ludiaで全文検索 †

• PostgreSQLで全文検索 — Papasan's Home

• mecab, mecab-ipadic, senna, ludiaセットのludia-withdepsが便利

---

カンマ区切りで取得 †

• 単純な例

  select array_to_string(ARRAY(select t from tbl1), ',');

• 関連テーブルを挟んだ場合

  select
    users.id
    , (
        select
          array_to_string(
            ARRAY(
              select
                groups.name
              from
                groups
                , users_groups
              where
                groups.id = users_groups.compgroup_id
                and users.id = users_groups.company_id
                 ),
          ',')
      ) as name
  from
    users
  

• ある列以外の全ての列をSELECTする方法(又は複数行をカンマ区切りで連結する方法)

---

階層問い合わせ †

ツリー構造で欲しい場合等。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

• connectby()関数の使い方
• 生涯一書生 PostgreSQLで階層問い合わせ
• ■PostgreSQL階層問合せを使ってみる

---

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

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

---

正規表現 †

• 「type=数字」で数字部分を取り出す。「#」はエスケープに使う文字。なぜか\dとかは使えない

  select substring( var from 'type=([0-9]+)' for '#') as num from hoge;

• 条件に使う

  select * from hoge where var ~ 'type=(\\d+)'

---

月末の取得 †

• 月末

  SELECT to_char(to_date('20050101', 'YYYYMMDD') + interval '1 month' - interval '1 day', 'YYYYMMDD');

• www.ikepon.jp: PostgreSQL アーカイブ

---

バージョンの取得 †

• SQLでバージョン取得

  =# select version();
                                                               version                                                              
  ----------------------------------------------------------------------------------------------------------------------------------
   PostgreSQL 7.3.4-RH on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.3.2 20031022 (Red Hat Linux 3.3.2-1)
  (1 row)
  ----

• バージョン番号だけ取得

  =# select substring(version() from 'PostgreSQL[ ]+([0-9]+\.[0-9]\.[0-9])');
   substring 
  -----------
   7.3.4
  (1 row)
  

• psqlのバージョン

  $ psql --version
  psql (PostgreSQL) 7.3.4-RH
  contains support for command-line editing
  

---

invalid multibyte character for locale HINT: †

• psql (PostgreSQL) 8.1.11 でエラー

  Notice: Tried 'SELECT * FROM articles WHERE LOWER(articles.title) = ? LIMIT 1'. Got: [-1] ERROR: invalid multibyte character for locale HINT: The server's LC_CTYPE locale is probably incompatible with the database encoding. in /var/www/akelos/lib/AkActiveRecord/AkDbAdapter.php on line 226

• 030 WEB DESIGN: PostgreSQLアーカイブ どうやら、initdbの時の文字コードが問題の模様

  # pg_controldata /var/lib/pgsql/data/
  
  LC_COLLATE:                           ja_JP.eucjp
  LC_CTYPE:                             ja_JP.eucjp
  
  # service postgresql stop
  # su - postgres
  $ rm /var/lib/pgsql/data
  $ initdb --locale=C /var/lib/pgsql/data
  $ exit
  # service postgresql start
  # pg_controldata /var/lib/pgsql/data/
  
  LC_COLLATE:                           C
  LC_CTYPE:                             C

• しあわせ太郎のCGI光房 postgresql.confを変更して、再起動でも良いらしい
  • /var/lib/pgsql/data/postgresql.conf

    lc_messages = 'C'                       # locale for system error message 
                                            # strings
    lc_monetary = 'C'                       # locale for monetary formatting
    lc_numeric = 'C'                        # locale for number formatting
    lc_time = 'C'                           # locale for time formatting
    

sha1 †

• PostgreSQLでSHA1

• PostgreSQL 8.0以降なら、md5は select md5('〜'); で取得できる。

• postgresql-python, postgresql-perl 等が必要

• plpythonu (8.0以降はUntrustedな言語なのでuが付く)

  CREATE LANGUAGE plpythonu;
  CREATE OR REPLACE FUNCTION SHA1HASH( TEXT ) RETURNS CHAR(40) AS '
  import sha
  hash = sha.new( args[0] )
  return hash.hexdigest()
  ' LANGUAGE plpythonu;

• plperl

  CREATE LANGUAGE plperl;
  CREATE OR REPLACE FUNCTION SHA1HASH( TEXT ) RETURNS CHAR(40) AS '
  use Digest::SHA1 qw(sha1);
  return sha1($args[0]);
  ' LANGUAGE plperl;
  

---

日付と時刻型の比較 †

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

• PostgreSQL による日付・時刻・時間の計算・演算のまとめ - hoge256ブログ

• 取得できない

  select * from authors where created_at <= '2008-07-04';

• 取得できる

  select * from authors where created_at <= '2008-07-04 23:59:59';
  select * from authors where cast(created_at as date) <= '2008-07-04';

---

あいまい検索 †

• 半角から全角へ変換。
  • createlang -U postgres plpgsql データベース名
  • get_nocase_text.php

• 前方一致の場合インデックスを作成するとさらに高速に
  • CREATE INDEX test_tbl_textdata ON test_tbl (get_nocase_text(textdata));
  • SELECT * FROM test_tbl WHERE get_nocase_text(textdata) LIKE get_nocase_text('Ａ４') || '%';

• PostgreSQLで全角半角を区別しない問い合わせ

• SQLであいまい検索
  • ひらがな、カタカナ、全角、半角対応。

• PostgreSQL 用全文検索関連ソフトウェア - SRA OSS, Inc. 日本支社
  • ユーザ定義 C 関数
  • PostgreSQL 8.2 で動作検証
  • BSDライセンス
  • normalize(), wakachi()等

---

月で絞り込み †

• 月と同じ

  "SELECT * FROM table_A WHERE date_trunc('month', ins_date) = to_date('".$year."-".$month."-01"', 'YYYY-MM-DD')"

• 月以下

  "SELECT * FROM table_A WHERE date_trunc('month', ins_date) <= to_date('".$year."-".$month."-01"', 'YYYY-MM-DD')"

秒を日付に変換 †

• int型の項目に秒が入っていて、出力する時に日付(HH:MI:SS)とかにしたいとき。PostgreSQL 7.4.x

  select colum1 * INTERVAL '1 second' from table1;
  ----
  00:01:00
  ----

• epoch関数がPostgreSQL 8.x で追加

  SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';

時間間隔型 †

• 1秒、1日、1年等の時間間隔を格納できる。
• 格納した値で、日付計算が可能

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'に変換する †

• numeric -> intervalが使えれば良いのだがうまくいかなかったので力ずく。FMを付けないと空白が強制的に出力される

  select
  	to_char(sec/3600,'FM00')
  	|| ':' || to_char(sec%3600/60,'FM00')
  	|| ':' || to_char(sec%60,'FM00')
  from
  	(select 45296 as sec) tmp

  • 結果

    ?column? 
    ----------
    12:34:56
    (1 row)

• もっと簡単にできないものか。
  • OK interval '1 second'
  • NG interval sec || ' second'

クエリーをcsv出力 †

• psqlから実行する場合

  $ psql dbname
  dbname=# \o tablenams.csv
  dbname=# \a
  Output format is unaligned.
  dbname=# \pset fieldsep ','
  Field separator is ','.
  dbname=# select * from tablename;
  dbname=# \o

  • \o で出力先を指定
  • \a で桁そろえをしない
  • \pset fieldsep でセパレータを指定
  • '\t' ならタブ区切り
  • select 文で対象データを抽出
  • 最後の、\o で出力先を戻します。

• コマンドライン
  • --csv: psql v12.12 に存在した
  • -A 桁そろえをしない
  • -F セパレータを指定

    $ psql -c 'SELECT * FROM tablename;' -A -F,

圧縮ダンプ/リストア †

• ダンプ

  pg_dump dbname | gzip > filename.gz

• 全データベース

  pg_dumpall -U postgres | gzip > postgresql.YYYYMMDD.gz

• リストア

  cat filename.gz | gunzip | psql dbname

• 異なるオーナーのDBにインポート場合

  pg_dump -O 元DB名 | gzip > filename.gz
  cat filename.gz | gunzip | psql -U 対象のユーザー名 新DB名

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

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

パスワードファイル †

• $HOME/.pgpass ファイルにパスワードを記述する事で、vacuum等をcronで処理したい時などにパスワードを毎回入力する必要がなくなります。

  $ vi ~/.pgpass
  ----------------------
  $ 0600 ~/.pgpass
  $ hostname:port:database:username:password
  *:*:*:postgres:postgresユーザのパスワード
  *:*:*:apache:apacheユーザのパスワード
  ----------------------
  $ chmod 600 ~/.pgpass

VACUUM †

• データベースの不要領域の回収、およびオプションによるデータベースの解析
• 毎日4:25に全てのDBに対し、バキュームする場合
  

  25 4 * * * (/usr/bin/vacuumdb -a -z -U postgres) > /dev/null 2>&1

• 毎日4:25に全てのDBに対し、完全バキュームする場合
  (-fオプションだとその間ロックされるため、更新できなくなる)

  25 4 * * * (/usr/bin/vacuumdb -a -z -f -U postgres) > /dev/null 2>&1

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

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

• 例：testtblのnameを4桁固定。'0'埋めにする

  update testtbl set name = ('0' || testtbl.name)
  from
  	(select * from testtbl where char_length(name) < 4) tmp
  where 
  	testtbl.code = tmp.code

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のログを出力する †

• windowsの場合
  

  (psql -h localhost [DB名] < 入力ファイル.log) 2>&1) > psql.log

参照制約の取得 †

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する必要がある。

• 例：deptテーブルのdeptnoの最大値をシーケンスに設定する場合。
  

  × select setval('sample_seq', max(deptno)) from dept;
  ○ select setval('sample_seq', cast(max(deptno) as bigint)) from dept;
  ○ select setval('sample_seq', (select cast(max(deptno) as bigint) from dept));

• 確認環境
  • psql (PostgreSQL) 7.3.9-RH
  • psql (PostgreSQL) 7.4

シーケンス関数 †

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句の高速インポートができるようになる。

• PHPでの例
  

  	/**
  	* バイナリデータをPostgreSQLの\copy形式でエスケープする
  	*
  	* @param mixed &$data	: バイナリデータ
  	* @return mixed $result	: エスケープされた文字列
  	*
  	* @note
  	* 以下のようなpsqlの\copyコマンドで読み込める形式にバイナリを変換する事ができる。\n
  	* @code
  	* \copy テーブル名 from 'tsvファイル名' with delimiter as '\t' null as ''
  	* @endcode
  	*
  	* @notice
  	* \copyコマンドで使うことを目的。\n
  	* 標準のpg_escape_bytea()はいくつかの制御文字列(\t,\r,\n等)をエスケープしてくれないため、
  	* 改行を1行の区切りとした、tsv形式等に利用できない。\n
  	* ただし、通常のsql文にはpg_escape_bytea()を使う事。\n
  	*/
  	function pg_escape_bytea_copy(&$data)
  	{
  		$result = null;
  		foreach(unpack("C*", $data) as $key => $val){
  			if( $val == 0134 ){
  				$val = '\\\\\\\\';
  			}else if( (000 <= $val && $val <= 037) || (0177 <= $val && $val <= 0377) ){
  				$val = sprintf("\\\\%03o", $val);
  			}else{
  				$val = pack("C", $val);
  			}
  			$result .= $val;
  		}
  		return $result;
  	}

標準的な設定 †

$ 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では挙動が違います。

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

pg_set_client_encoding(mb_internal_encoding());

Oracleから移植する時のTIPS †

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

to_date()とto_timestamp()の違い †

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

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

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

• bashの場合
  .bash_profile に以下の行を追加します。
  

  export PGCLIENTENCODING=EUC-JP

• windowsの場合
  batファイル等の中で、
  

  set PGCLIENTENCODING=EUC-JP

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['extra_login_security'] = false;

• デフォルト言語を日本語に

  $conf['default_lang'] = 'japanese';

• 接続用設定

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

PostgreSQL操作ツール †

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

• インポート
  

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

• エクスポート
  

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

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を確認する。

• iptables

  # vi /etc/sysconfig/iptables
  -A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT
  
  # service iptables restart

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

• http://www.samse.fr/GPL/ora2pg/ora2pg.html

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ドライバについて †

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

• psqlODBC(PgFoundry)
• psqlODBC 日本語対応版(新生開発)
  PostgreSQL 7.4.x 〜 8.1 (2006-03-22 (水) 17:28:07現在)

Software

• 自作ソフト
• wiki自作プラグイン
• 利用規定
• ランキング
• 雑誌等掲載履歴

Programming

Game

雑記

連絡先

---

• MenuBar
• RightBar
• :admin
  

人気の10件

最新の10件

---