ウェブインコ

インコの技術メモ

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 DB名
sudo -u postgres psql -f /home/hoge/pagedata.dat -d hoge

■パスワードの設定
passwd postgres
パスワード入力×2
(root以外の人が su - postgres としたとき用)

■ユーザー定義関数の情報
一覧表示
\df
\df 正規表現
ex.)
\df sp_* とすると、sp_で始まる名前のもの一覧を表示
詳細表示
\df+ 関数名

■ヘルプ
\h
\?

■レコードの追加
insert into レコード名 values('値1','値2','値3');

■レコードの更新
update レコード名 set カラム名='値1' where カラム名='';

■DB名の変更
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 テーブル名 テーブルの構造を表示します。

■DB→ファイル、ファイル→DB
データベースのダンプ(ラージオブジェクト無し)
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

■DB一覧
psql -l

■DBを掴む
psql testdb;
左端が testdb になります。以下はその後のコマンド。

■テーブル一覧、詳細
\d
\d tablename

■データ表示
select * from use_data;
うまくいかない時は2連発で実行するとうまく行く時があります。

■閉じる
\q

■ユーザーの削除
dropuse apache

■レコードの更新例
update user_data set optin=1;
update blog_plugin set bp_title='ショップ情報' where blog_plugin_id='profile';