[　アトム-サイト　] データベース postgres

[ アトム-サイト ] - 管理人さんのページです。
RSS2.0 Atom

[ データベース ] 一覧へ

postgres

管理人さんのブログ　2009-07-15 12:02:57

postgresユーザになる
root@ubuntu-atsu01:~# id
uid=0(root) gid=0(root) groups=0(root)
root@ubuntu-atsu01:~# su - postgres

psqlでデータベースをいじれます
postgres@ubuntu-atsu01:~$ psql -l
List of databases
Name | Owner | Encoding
------------+----------+------

DBユーザ登録
postgres@ubuntu-atsu01:~$ createuser -P test
ここでパスワード設定。
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
CREATE ROLE

確認は
$ psql
# select usename from pg_user;

DBユーザ削除
postgres@ubuntu-atsu01:~$ dropuser test
DROP ROLE

DB生成
createdb -O www-data(オーナー指定) -E UTF-8 dbname

DB削除
dropdb dbname

↓こんなエラーが出てdropdb出来ない時は、
dropdb: database removal failed: ERROR: database "dbname" is being accessed by other users
データベースへの接続が残っている為。
再起動
# /etc/init.d/postgres restart
すれば、dropdb出来る様になります。

DBに入る
psql DB名

テーブル一覧表示
# select * from pg_tables;
# ＼d ←コレが楽

ユーザ毎のアクセス制御
/var/lib/pgsql/data/pg_hba.conf の一番下。
host all all 127.0.0.1/32 password
環境に応じて変えてやって、リスタート。
# /etc/init.d/postgresql restart
※上の行から順番に判定するので、同じ様な制御はキチンと無効化する事（コメントアウトする）。

DBのバックアップと取り込み
pg_dump dbname > backupdata
で、取り込む所に移動して
psql -d dbname < backupdata
オーナーを合わせて入れてあげる。また、テーブルのオーナーの違いでアクセスできない時はGRANTでアクセス許可してあげる。

SERIAL値と取り出し
テーブルを作ります
# CREATE TABLE test_tbl ( id SERIAL PRIMARY KEY, name text);
NOTICE: CREATE TABLE will create implicit sequence "test_tbl_id_seq" for serial column "test_tbl.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_tbl_pkey" for table "test_tbl"
CREATE TABLE
出来たテーブルを見る
# ＼d
List of relations
Schema | Name | Type | Owner
--------+--------------------------+----------+------
public | test_tbl | table | postgres
public | test_tbl_id_seq | sequence | postgres
(15 rows)
SERIAL値はINSERT等でセッションが確立された後のでないと見れない様になっている（自分がINSERTしたSERIAL値を得る事が出来る）
# SELECT CURRVAL('test_tbl_id_seq');
ERROR: currval of sequence "test_tbl_id_seq" is not yet defined in this session
実際にデータを入れてみる
# INSERT INTO test_tbl(name) VALUES('atsu');
INSERT 0 1
# INSERT INTO test_tbl(name) VALUES('hoge');
INSERT 0 1
# SELECT * FROM test_tbl;
id | name
----+--
1 | atsu
2 | hoge
(2 rows)
入れたデータのSERIAL値を得る事ができます
# SELECT CURRVAL('test_tbl_id_seq');
currval
-----
2
(1 row)
SERIAL値を自分で設定するには
# SELECT SETVAL('test_tbl_id_seq', 10);
setval
----
10
(1 row)

TIMESTAMP
小数点の付かないタイムスタンプ
CREATE TABLE test_tbl (
　……
　……
　write_time　TIMESTAMP(0)
);

１日前表示
postgres=# select now() + INTERVAL '-1 day';
?column?
---------------------------
2008-02-20 20:14:47.475886+09
(1 row)

高速化
EXPLAINを付けてSELECTする。
-> Seq Scan on... と書かれていたら全検索。
Index Scan using... と書かれていたらインデックスが使われている。
cost=0.00..xxx.xx部分が検索時間（少ない程良い）。
インデックスを作るのは
CREATE INDEX index_name ON table_name (fieldname1, fieldname2);

※ 『データベース』に繋がる関連物を視覚的に探しだせるサイトを作ってみました（よろしければクリックしてみてくださいね）。

ページ閲覧者数：30人

[ データベース ] 一覧へ

<< 2009年11月

2009年11月22日(日)