PostgreSQL備忘録 #PostgreSQL

シーケンスの確認

# select last_value from [シーケンスid];

コマンドラインからsql実行

-cオプションを使う。

psql [データベース名] -c "[sql文]"

データベースサイズ確認

下記sqlを実行して、datidを控える。

# select datid, datname from pg_stat_database;
 datid |  datname
-------+-----------
 10819 | postgres
 16384 | hogehoge
     1 | template1
 10818 | template0

データディレクトリのdatidディレクトリがデータベースを構成するファイル。

$ cd /var/lib/pgsql/data/16384
$ du -k

ファイル実行

# \i [ファイル名]

文字コード変更

sjisとかutf8とかeuc_jpとか。

# \encoding [文字コード]

結果の行列表示切り替え

表示カラムが多いとき、見やすくなる。

# \x

コマンドからのsqlファイル実行

-AF ,をつけると、区切り文字が,になり、csv出力になる。

$ psql [データベース名] < [sqlファイル]

$ psql [データベース名] < [sqlファイル] -AF ,

パスワード省略

sqlを実行するときにパスワードを入力しないといけないので、バッチ実行時に困る。
実行ユーザのホームディレクトリに.pgpassを作成しておくと、自動で認証してくれる。
パーミッションは600にすること。

$ vi ~/.pgpass

hostname:port:database:username:password

$ chmod 600 ~/.pgpass

テーブル定義

# \d [テーブル名]

テーブル一覧

# \dt

データベース切り替え

# \c [データベース名]

データベース一覧

# \l

インポート

cオプションでインポート前にデータベースクリア

# pg_restore -c -h [ホスト名] -U [ユーザ名] -d [データベース名] [dumpファイル名]

エクスポート

Fcオプションは、バイナリ指定（Fpでテキスト指定）
bオプションでラージオブジェクトも含める

$ pg_dump -h [ホスト名] -U [ユーザ名] -b -Fc [データベース名] > [dumpファイル名]

データベース作成

言語も設定すること（PL/pgSQL）

$ createdb -U [ユーザ名] [データベース名]
$ createlang -d [データベース名] -U [ユーザ名] plpgsql

centosにインストール

レポジトリの登録

$ sudo yum localinstall http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm

インストール

$ sudo yum install postgresql93 postgresql93-server --enablerepo=pgdg93

db初期化

$ /etc/rc.d/init.d/postgresql-9.3 initdb

設定変更

$ vi /var/lib/pgsql/9.3/data/postgresql.conf

# 受け入れサーバの指定
listen_address = 'localhost'

# ログの出力形式（時間　ユーザ　データベース）
log_line_prefix = '%t %u %d'

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

# パスワード認証を許可（peerをmd5に書き換え）
local   all             all                                     md5

起動

$ sudo /etc/rc.d/init.d/postgresql-9.3 start

自動起動設定

$ sudo chkconfig postgresql-9.3 on

パスワード設定

$ sudo passwd postgres

ユーザ作成、db作成

$ su - postgres
$ psql
# create user [ユーザ名];
# alter role [ユーザ名] with password '[パスワード]';
# alter role [ユーザ名] with createdb;
# create database [データベース名] owner [ユーザ名] encoding '[文字コード]';

PostgreSQL備忘録