yano3nora
7/2/2017 - 9:08 AM

[postgresql: note] PostgreSQL note. #postgresql

[postgresql: note] PostgreSQL note. #postgresql

postgresql.md
content_copyfile_download
  • Rendered
  • Source

OVERVIEW

postgresql.org

Ingres という RDBMS の後継で PostgreSQL ということらしい。MySQL に並んで有名な OSS なオブジェクト関係データベースシステム。オブジェクトの取り扱いや大量データの分析、3 種類の JOIN アルゴリズム、再帰問い合わせやストアドプロシージャに Python など外部スクリプトが使えるなど、多機能が売り。ポートは 5432 がデフォ。

PostgreSQLとMySQL、使うならどっち？ データベース専門家が8つの視点で徹底比較！

Features

SQLの観点から「Oracle Database」「PostgreSQL」「MySQL」の特徴を整理しよう！

Tips & References

主キーを指定して INSERT すると AutoIncrement ずれます

PostgreSQL ちゃんは MySQL と違い「主キーが指定されなかったら自動インクリメント」して「テーブルの連番を 1 っこずらす」という動作をするため、主キーを指定しながら INSERT したのち再度主キーを指定せず INSERT すると死ぬ。そのぐらいやってよ ... 。

内部的にはカラムのデフォルト値に「現在の Sequence をいれつつ Sequence を + 1 する」という関数が入っているイメージ。特定カラムについて値を指定した場合「当然デフォルト値は無視される」ためにこのような現象になる。

ID を指定するケースは「複雑なリレーションを持った初期データ投入」や「初期ユーザ投入」などで割合必要になってくるため、Rails や Django など PostgreSQL との親和性が高い FW 利用時は要注意。

基本的に仕様なので「主キー指定しちゃたら手動で変更する」しかない。Sequence 名は a5mk2 などで当該テーブル主キーの「デフォルト値」から確認可能。

# DB の Sequence 名 'sequence_name' について
# 現在のシーケンスを 1000 へ変更 ...
# 今 DB で 1000 まで ID 使ってるよ的な感じ
#
SELECT setval('sequence_name', 1000);  # Next 1001

COMMANDS

※ほぼ未検証

ログイン
su - postgres
データーベースを作成
createdb database_name
データベース一覧を表示
\l
データーベースへ接続
psql database_name
テーブル一覧を表示
\d
テーブルカラムの一覧表示
\dt
テーブルを削除
drop table <TABLE_NAME>;
ユーザーを作成
createuser user_name
ユーザーを作成 with パスワード
create user ${USER_NAME} with password '${PASS_WORD}';
ユーザー (Role)に権限を付与
grant select, insert, update, delete on <TABLE_NAME> to <USER_NAME>;
ユーザー一覧を表示
\du
ログアウト
\q

pg_dump / pg_restore

https://qiita.com/mkyz08/items/55b34f0580533907fea6

  • pg_dump バイナリ形式で database を dump してバックアップ
    • database クラスタ全体、ユーザ・ロールなどはバックアップできないので注意
    • あくまでもメインスキーマのデータだけを dump する
  • pg_restore 上記 dump バイナリから database をリストア

Heroku Postgres で取得可能なバックアップもこの形式で落ちてくるのでローカルでサクッとリストア → テストとかできる。

# Dump
$ pg_dump testdb > test.dmp

# Restore (Heroku で DL したやつを Docker コンテナ db にいれる想定)
$ pg_restore --verbose --clean --no-acl --no-owner -h db -d postgres -U postgres ebf1b6f9-8fa3-4b06-89be-a041f7cc2b71

pgbench

pgbench
pgbenchはPostgreSQL上でベンチマーク試験を行う単純なプログラムです。 これは同一のSQLコマンドの並びを何度も実行します。複数の同時実行データベースセッションで実行することもできます。 そして、トランザクションの速度（1秒当たりのトランザクション数）の平均を計算します。
ぜんぜんわからない。俺たちは雰囲気でpgbenchを使っていた。

# 初期化 (-s 10 で 150MB くらいのデータセット作成してる)
$ pgbench -i -s 10 -q -h ${HOSTNAME} -U ${USERNAME} dbname

# 負荷テスト実行 (-c 2 でクライアント数を、-T でテスト時間を指定)
$ pgbench -c 20 -T 30 -h ${HOSTNAME} -U ${USERNAME} dbname

INSTALLATION

yumでPostgreSQLをインストールしてみよう

mac なら brew でおｋ。

$ brew install postgresql

# centos 6.5
# SETUP: postgresql + phpPgAdmin
sudo yum -y remove postgresql* 
sudo yum -y install postgresql-server postgresql-devel
sudo service postgresql initdb -D /var/lib/pgsql/data
sudo yum --enablerepo=remi,epel,rpmforge,remi-php71 -y install phpPgAdmin php-pdo php-pgsql php-mcrypt
sudo sed -i 's/ident/trust/g' /var/lib/pgsql/data/pg_hba.conf
sudo sed -i '$atimezone = '\''Asia/Tokyo'\''' /var/lib/pgsql/data/postgresql.conf
sudo sed -i 's/Allow from 127.0.0.1/Allow from All/g' /etc/httpd/conf.d/phpPgAdmin.conf
sudo sed -i 's/\(^.*conf\[.extra_login_security.\] =\) true/\1false/' /etc/phpPgAdmin/config.inc.php
sudo chkconfig postgresql on
sudo service postgresql start

# Shoud change password on batch after.

Batch

PostgresqlInstall.sh

#!/bin/bash

PW="password"

expect -c "
  set timeout -1
  spawn /bin/sh -c \"sudo passwd postgres\"
  expect \"New password:\"
  send \"${PW}\n\"
  expect \"Retype new password\"
  send \"${PW}\n\"
  expect eof exit 0
"
sudo -u postgres psql -c "alter user postgres password '${PW}';"

CreateDatabase.sh

#!/bin/bash

DB_NAME="app"
DB_USER="app"
DB_PASS="password"

sudo -u postgres psql -c "create database ${DB_NAME};"
sudo -u postgres psql -c "alter database ${DB_NAME} set timezone to 'Asia/Tokyo';"
sudo -u postgres psql -c "create user ${DB_USER} with password '${DB_PASS}';"
sudo -u postgres psql -c "grant select, insert, update, delete on database \"${DB_NAME}\" to ${DB_USER};"