前端之家收集整理的这篇文章主要介绍了
postgresql记录,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
@H_@R_
502_448@_0@安装源
yum localinstall http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm
@H_@R_
502_448@_0@安装
数据库
yum install postgresql94-server postgresql94-contrib
@H_@R_
502_448@_0@初始化
数据库
service postgresql-9.4 initdb
@H_@R_
502_448@_0@启动
数据库
service postgresql-9.4 start
@H_@R_
502_448@_0@加入开机启动
chkconfig postgresql-9.4 on
@H_@R_
502_448@_0@切换账户
su - postgres
@H_@R_
502_448@_0@进入
数据库
psql
@H_@R_
502_448@_0@
修改密码
ALTER USER postgres WITH PASSWORD 'postgres';
select * from pg_shadow ;
@H_@R_
502_448@_0@
退出数据库
\q
@H_@R_
502_448@_0@访问远程连接 vi /var/lib/pg
sql/9.4/data/postgre
sql.conf 把listen_addresses = 'localhost' 改成 listen_addresses = '*'
@H_@R_
502_448@_0@vi /var/lib/pg
sql/9.4/data/pg_hba.conf 在ipv4段
添加 host all all 0.0.0.0/32 md5
@H_@R_
502_448@_0@大表
CREATE TABLE student (student_id bigserial,name varchar(32),score smallint) ;
@H_@R_
502_448@_0@分区表
CREATE TABLE student_qualified (CHECK (score >= 60 )) INHERITS (student) ;
CREATE TABLE student_nqualified (CHECK (score < 60)) INHERITS (student) ;
@H_@R_
502_448@_0@索引
create index student_qualified_score_index on student_qualified (score);
create index student_nqualified_score_index on student_nqualified (score);
@H_@R_
502_448@_0@唯一约束
ALTER TABLE student_qualified ADD CONSTRAINT student_qualified_score_unique UNIQUE (score);
ALTER TABLE student_nqualified ADD CONSTRAINT student_nqualified_score_unique UNIQUE (score);
@H_@R_
502_448@_0@插入规则
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.*);
@H_@R_
502_448@_0@插入数据
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');
@H_@R_
502_448@_0@查看分区表数据
SELECT p.relname,c.tableoid,c.* FROM student c,pg_class p WHERE c.tableoid = p.oid
@H_@R_
502_448@_0@约束排除 cat /var/lib/pg
sql/9.4/data/postgre
sql.conf | grep constraint_exclusion
#constraint_exclusion = partition # on,off,or partition
@H_@R_
502_448@_0@打开约束排除 constraint_exclusion = partition # on,or partition
@H_@R_
502_448@_0@备份还原
$ PGPASSWORD="12357" pg_restore -U postgres -d db < pgsql.sql
$ PGPASSWORD="12357" pg_dump -U postgres -Fc db > pgsql.sql
@H_@R_
502_448@_0@参考
http://blog.chinaunix.net/uid-24774106-id-3887099.html