Edited at

PostgreSQL備忘録

More than 3 years have passed since last update.


シーケンスの確認

# 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


ログイン

オプションは省略できる

$ psql -h [ホスト名] -U [ユーザ名] -d [データベース名]


インポート

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 '[文字コード]';