安装源
yum localinstall http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm
安装数据库
yum install postgresql94-server postgresql94-contrib
初始化数据库
service postgresql-9.4 initdb
启动数据库
service postgresql-9.4 start
加入开机启动
chkconfig postgresql-9.4 on
切换账户
su - postgres
进入数据库
psql
修改密码
ALTER USER postgres WITH PASSWORD 'postgres';
select * from pg_shadow ;
退出数据库
\q
访问远程连接 vi /var/lib/pgsql/9.4/data/postgresql.conf 把listen_addresses = 'localhost' 改成 listen_addresses = '*'
vi /var/lib/pgsql/9.4/data/pg_hba.conf 在ipv4段添加 host all all 0.0.0.0/32 md5
大表
CREATE TABLE student (student_id bigserial, name varchar(32), score smallint) ;
分区表
CREATE TABLE student_qualified (CHECK (score >= 60 )) INHERITS (student) ;
CREATE TABLE student_nqualified (CHECK (score < 60)) INHERITS (student) ;
索引
create index student_qualified_score_index on student_qualified (score);
create index student_nqualified_score_index on student_nqualified (score);
唯一约束
ALTER TABLE student_qualified ADD CONSTRAINT student_qualified_score_unique UNIQUE (score);
ALTER TABLE student_nqualified ADD CONSTRAINT student_nqualified_score_unique UNIQUE (score);
插入规则
CREATE OR REPLACE RULE insert_student_qualified
AS ON INSERT TO student
WHERE score >= 60
DO INSTEAD
INSERT INTO student_qualified VALUES(NEW.*);
CREATE OR REPLACE RULE insert_student_nqualified
AS ON INSERT TO student
WHERE score < 60
DO INSTEAD
INSERT INTO student_nqualified VALUES(NEW.*);
插入数据
INSERT INTO student (name,score) VALUES('Jim',77);
INSERT INTO student (name,score) VALUES('Frank',56);
INSERT INTO student (name,score) VALUES('Bean',88);
INSERT INTO student (name,score) VALUES('John',47);
INSERT INTO student (name,score) VALUES('Albert','87');
INSERT INTO student (name,score) VALUES('Joey','60');
查看分区表数据
SELECT p.relname,c.tableoid,c.* FROM student c, pg_class p WHERE c.tableoid = p.oid
约束排除 cat /var/lib/pgsql/9.4/data/postgresql.conf | grep constraint_exclusion
#constraint_exclusion = partition # on, off, or partition
打开约束排除 constraint_exclusion = partition # on, off, or partition
备份还原
$ PGPASSWORD="12357" pg_restore -U postgres -d db < pgsql.sql
$ PGPASSWORD="12357" pg_dump -U postgres -Fc db > pgsql.sql
参考 http://blog.chinaunix.net/uid-24774106-id-3887099.html