インコの技術メモ
PostgreSQL
■ CentOS 6.x に PostgreSQL 9.6
// インストール
cd /usr/local/src
wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-centos96-9.6-3.noarch.rpm
rpm -ivh ./pgdg-centos96-9.6-3.noarch.rpm
yum install postgresql96-server postgresql96-devel postgresql96-contlib
// バージョン確認
psql --version
// アカウントの確認
su postgres
exit
// 初期起動時
service postgresql-9.6 initdb
// 起動
service postgresql-9.6 start
su - postgres
// DB 一覧
psql -l
// 終了
\q
exit
■ Ident認証に失敗しました
localhost を指定すると接続できない
↓
cd /var/lib/pgsql/9.4/data
cp pg_hba.conf pg_hba.conf-20150715
↓
ident を password に変更
vi
#local all all peer
local all all password
# IPv4 local connections:
#host all all 127.0.0.1/32 ident
host all all 127.0.0.1/32 password
# IPv6 local connections:
#host all all ::1/128 ident
host all all ::1/128 password
# Allow replication connections from localhost, by a user with the
↓
/etc/init.d/postgresql-9.4 restart
psql -U honyarara -h localhost -d honyararadb
■ ファイルに出力
\o hoge.dat
select * from m_hoge;
\q
■ 文字コード確認
\l
■ テーブル毎の容量を調べる。
psql hoge;
select relid,reltname from pg_stat_all_tables;
cd /var/lib/pgsql/data/base/などのディレクトリに移動。
selectで見つけたoidのファイルの容量がそうです。
du -ah 16384/16482
■ 急に繋がらなくなった
クライアントさんがサーバーを何かしたらしい。
PHPでnewするところでこけていることを突き止めた。あとは野生の感でPostgreSQLだと判断する。
ゴミプログラムを作ってみたら以下のエラー。
Permission denied Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
なんでじゃ。
su - postgres
psql databasename
コマンドラインでは繋がる。
DBを再起動。
pg_ctl stop -D /usr/local/pgsql/data
ps aux | grep po ←変なのが残って無いか確認
pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/logfile -o "-i" start
ダメ。
ll -a /tmp/.s.PGSQL.5432
書き込み可になってるし。
adodbの設定でホスト(127.0.0.1)を指定する。
$conn->Connect('127.0.0.1', 'postgres', 'password', 'databasename');
治った。
■ ユーザー作成
ユーザー名：hoge
su - postgres
createuser hoge
■ パスワード設定
ユーザー名/パスワード：hoge/hage
su - postgres
psql hogedb
alter user hoge password 'hage';
■ psqlしたら以下のエラー
Is the server runnning locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
↓
インストールした直後とか、新しいサーバーで。というわけで最初の初期化をまだしていない。
インストールした場所(/usr/local/pgsql/dataとか/var/lib/pgsql/dataとか)が空っぽならまず間違いない。
su - postgres
initdb /var/lib/pgsql/data （←↓インストールした場所）
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
psql -l
↓
template1とかできていればOK。
■ tsvからデーターを挿入
以下の様なファイルを作る
pagedata.dat
copy m_pagedata (url,keywords,description, title) from stdin;
/index.php あああ いいい ううう
/user/index.php えええ おおお かかか
EUCで保存、又は変換
sudo -u postgres psql -f ディレクトリ名/pagedata.dat -d ＤＢ名
sudo -u postgres psql -f /home/hoge/pagedata.dat -d hoge
■ パスワードの設定
passwd postgres
パスワード入力×２
（root以外の人が su - postgres としたとき用）
■ ユーザー定義関数の情報
一覧表示
\df
\df 正規表現
ex.)
\df sp_* とすると、sp_で始まる名前のもの一覧を表示
詳細表示
\df+ 関数名
■ ヘルプ
\h
\?
■ レコードの追加
insert into レコード名 values('値１','値2','値3');
■ レコードの更新
update レコード名 set カラム名='値1' where カラム名='';
■ ＤＢ名の変更
su postgres;
psql postgres;
alter database 元のデータベース名 rename to 新しいデータベース名;
■ /etc/init.d/pgsql が無いので止めれない
別のマシンのpgsqlのファイルの中を見てコマンドを探す。
ちなみにpostgresqlの停止は
su - postgres
pq_ctl stop -D /var/lib/pgsql/data
■ ユーザ一覧
su - postgres
psql template1
select usename from pg_user;
■ テーブル名の変更
alter table testm rename to abc;
■ バージョンを調べる
su postgres
psql databasename
select version();
■ よく使うコマンド
su - postgres; psql testdb; の後
\d データベース内のテーブルを表示します。
\d テーブル名 テーブルの構造を表示します。
■ ＤＢ→ファイル、ファイル→ＤＢ
データベースのダンプ（ラージオブジェクト無し）
sudo -u postgres pg_dump hoge > /home/hoge/hoge_local.dump
データベース削除
sudo -u postgres dropdb hoge
データベース作成
sudo -u postgres createdb -E EUC_JP hoge;
ファイル→データベース（ラージオブジェクト無し）
sudo -u postgres psql -f /home/hoge/hoge.dump -d hoge
■ ダンプ テーブル構造のみ
sudo -u postgres pg_dump -s hoge > hoge.dump
■ ダンプ テーブル毎
sudu -u postgres pg_dump -t tablename dbname > tablename.dump
■ 起動方法
su - postgres
■ ＤＢ一覧
psql -l
■ ＤＢを掴む
psql testdb;
左端が testdb になります。以下はその後のコマンド。
■ テーブル一覧、詳細
\d
\d tablename
■ データ表示
select * from use_data;
うまくいかない時は２連発で実行するとうまく行く時があります。
■ 閉じる
\q
■ ユーザーの削除
dropuse apache
■ レコードの更新例
update user_data set optin=1;
update blog_plugin set bp_title='ショップ情報' where blog_plugin_id='profile';
