====== postgresql ======

[[postgresql-paas]]

=====postgresqlのバックアップリストア=====

  * ドキュメント http://www.postgresql.jp/document/8.4/

  * 容量見積 http://lets.postgresql.jp/documents/tutorial/gihyo_rensai/5

  * レスポンスチューニング http://d.hatena.ne.jp/glxs/20070201

http://php.y-110.net/wiki/index.php?PostgreSQL%A1%A7%A5%C1%A5%E5%A1%BC%A5%CB%A5%F3%A5%B0%B4%AA%BD%EA

  * vacumm reindex http://blog.jolt.jp/2006/06/postgresql_vacuum_full.html

  * drop create でvacumm を迂回。
    http://mikasaya754.com/blog/2010/03/postgresql-vacuum/

  * PostgreSQLが重い！あなたならどうしますか？ http://dqn.sakusakutto.jp/2011/12/postgresql_1.html#more

=====動かないときのチェック=====

====SELinux====

http://lets.postgresql.jp/documents/tutorial/centos/3

  restorecon -R /var/lib/pgsql/data/


=====インストール 

====yum====

yumでPostgreSQLをインストールしてみよう http://lets.postgresql.jp/documents/tutorial/yum/

====rpm====

  * http://yum.pgsqlrpms.org/8.4/ ここから最新のものを入手 pgdg-redhat-8.4-2.noarch.rpm

rootユーザーで以下のコマンドを実行しインストールする

  rpm -ivh pgdg-redhat-8.4-2.noarch.rpm 　
  yum update
  yum install postgresql-server

  ====================================================================================================================
  Package                          Arch                Version                           Repository             Size
  ====================================================================================================================
  Installing:
   postgresql-server                i386                8.4.4-2PGDG.el5                   pgdg84                4.5 M
  Installing for dependencies:
 postgresql                       i386                8.4.4-2PGDG.el5                   pgdg84                1.4 M
   postgresql-libs                  i386                8.4.4-2PGDG.el5                   pgdg84                200 k
  
  Transaction Summary
  ====================================================================================================================
  Install       3 Package(s)
  Upgrade       0 Package(s)

PostgreSQLインストール http://wiki.livedoor.jp/khazad_lefty/d/PostgreSQL%A5%A4%A5%F3%A5%B9%A5%C8%A1%BC%A5%EB

====初期設定====

initdb でデータベースシステムを作成する

  # su - postgres
  $ initdb -D /var/lib/pgsql/data
  エンコード、ロケール指定
  ($ initdb --encoding=UTF8 --no-locale -D /var/lib/pgsql/data)

====ユーザー作成====

  # su - postgres
  $ createuser -P newuser
  新しいロールのパスワード：
  もう一度入力してください：
  新しいロールをスーパーユーザとしますか?  (y/n)n
  新しいロールにデータベース作成権限を与えますか? (y/n)y
  新しいロールにロールを作成する権限を与えますか? (y/n)n


====接続の設定====

redhat は /var/lib/pgsql/data 配下

===listen対象のホストを設定===

・postgres.conf

  listen_addresses = '*'

===接続に対する認証方法を設定===

・pg_hba.conf

  host    all         all         127.0.0.1/32          trust
  host    all         all         0.0.0.0/0             password

trust は認証せずに接続　passwordはパスワード接続

===ファイアーウォール===

接続用ポートを開く（redhatの設定）

  lokkit -q --port=5432:tcp

*セキュリティ設定(pg_hba.conf) http://www.postgresql.jp/document/8.4/html/auth-pg-hba-conf.html

*接続と認証(postgres.conf) http://www.postgresql.jp/document/8.4/html/runtime-config-connection.html

*認証方式 http://www.postgresql.jp/document/8.4/html/auth-methods.html#AUTH-PASSWORD

 PostgreSQLデータベースパスワードはオペレーティングシステムのユーザパスワードとも別のものです。
 各データベースユーザのパスワードはpg_authidシステムカタログテーブルの中に格納されます。
 CREATE USER foo WITH PASSWORD 'secret';
 のように、パスワードはSQLコマンド
 CREATE USERとALTER USERを使って管理できます。
 デフォルトでは、パスワードが設定されない場合、
 格納されるパスワードはNULLとなり、そのユーザのパスワード認証は常に失敗します。
 

===postgresユーザーのパスワード変更===

  su - postgres
  psql 
  alter user postgres with password '設定するパスワード';


=====再起動方法=====

*起動

  ( $/etc/init.d/postgresql start )
  $pg_ctl start

*再起動

  ( $/etc/init.d/postgresql restart )
  $pg_ctl restart

*停止

  ( $/etc/init.d/postgresql stop )
  $pg_ctl stop


=====文字列の扱い=====

PostgreSQL 文字列 http://www.ne.jp/asahi/hishidama/home/tech/postgres/string.html

* 文字列長

char,varcharの長さの指定は、テーブルのエンコードタイプの文字数。 つまり、SQL_ASCIIの時はバイト単位（全角文字は２バイト、半角文字は１バイト）となり、 SJIS・EUC_JP・UNICODEの時は全角文字も半角文字も１文字となる。 length関数で返ってくる数値も同じ。

エンコード
|タイプ	|全角1文字	|半角1文字|
|SQL_ASCII	|2	|1|
|SJISEUC_JP UNICODE	|1	|1|

http://lets.postgresql.jp/documents/technical/text-processing/2

=====データベース作成=====

  #psql postgres
  postgres=#create database
          newdb
          with owner = newuser;

れぞれのユーザに、ユーザ名と同じ名前のスキーマを作成することができます。
デフォルトの検索パスが $user で始まることを思い出してください。これはユーザ名に解決されます。
つまり、各ユーザが個別のスキーマを持っていると、デフォルトでそれぞれのスキーマにアクセスすることになります。

  #psql newdb newuser
  newdb=>create schema
       newschema
       authorization newuser;


=====その他=====

====レスポンスチューニング====

    http://d.hatena.ne.jp/bgvillea/20100125/p1
    reindexとVacuum Fullについては今まで通り手動でやる必要がある。 ただし、通常Vacuum Fullは行う必要がほとんどないとのことで、行わなければいけない状況(max_fsm_pagesのアラートが出るなど)が発生するのは何らかの設定が間違ってる可能性が高いとのコト。 また、その状況に陥った場合はpg_dumpしてDBを作り直した方がよいとのことでした。

    チューニングで特に大事な設定項目。

     shared_buffers（物理メモリの10～20%） 
     checkpoint_segments（10～16の範囲（増やし過ぎるとWALがディスクサイズを消費するので注意）） 
     checkpoint_completion_target（更新が多い場合は0.6～0.9） 
     bgwriter_all_maxpages（更新メイン：～40、参照メイン：100～） 
     bgwriter_percent（更新メイン：～5、参照メイン：5～10）


====データベースクラスタとは====

    ディスク上に確保したデータベース格納領域
    ファイルシステムの観点から見ると、「全てのデータが格納される1つのディレクトリ」となる
    そのディレクトリ配下にデータベース全てのデータが格納される
    任意の場所に作成可能（特にデフォルトの場所はない）


====PostgreSQLのアンインストール====

    yum remove で、インストールした関連プログラム（postgresql-server,postgresql,postgresql-libsの3つ）を削除する。
    /var/lib/pgsql/ に関連ファイルが残っているので削除（問題がなければディレクトリごと削除）

====Dataの場所(Disk)を変更する。====

  データディレクトリの場所についてはdata_directoryが-DとPGDATAを上書きし、
  設定ファイルの場所については上書きしないことに注意してください。 

つまり pg_ctl -D で指定された場所に、postgresql.conf を配置し、
postgresql.confのdata_directoryの場所にdataクラスタをおけばよい。

=====postgresqlで”select count(*) from table_name”が遅い対応=====

http://vmemo.blog36.fc2.com/blog-entry-421.html

select count(*) from table_name where id>0;
と、idは正の整数なんで、全部検索されるハズの一見意味のない条件を付けてみる。
これが効果があるようで、速度が劇的に改善。


=====文字コード=====
initdb コマンドでデータベースクラスタ作成時に指定するが、 createdbでも個別に指定できる。

  createdb -E UTF8 -T template0 testdb

というように、template0を指定してcreatedbを行う。