postgresql记录
发布时间:2020-12-13 17:27:07 所属栏目:百科 来源:网络整理
导读:安装源 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 postgre
安装源 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,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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |