PG_基础查询SQL

1.===================================创建数据库====================================== create database englishdb with encoding 'UTF8' LC_COLLATE='en_GB.UTF8' LC_CTYPE='en_GB.UTF8' TEMPLATE=template0; create database chinadb with encoding 'EUC_CN' LC_COLLATE='C' LC_TYPE='C' TEMPLATE=template0; create database chinadb with encoding 'EUC_CN' LC_COLLCATE='zh_CN' LC_CTYPE='zh_CN' TEMPLATE=template0; --复制数据库 create database TESTBAK TEMPLATE TEST ; 2.===================================表空间====================================== --数据库表空间 select oid,* from pg_tablespace; --创建表空间（已经创建用户lpp） create tablespace lpp owner lpp location '/u01/data04'; \c lpp create schema lpp; alter schema lpp owner to lpp; alter table test set schema jf; \dn   List of schemas   Name  |  Owner    --------+----------  lpp    | lpp  public | postgres --表空间路径 \db \db+ --修改表的表空间 alter table test set tablespace tbs01; --修改默认表空间 alter database app set default_tablespace to tbl_app; alter user app set default_tablespace to tbl_app; --查看某个表具体物理路径 select pg_relation_filepath('app.t4'); 3.================================规则（触发器）================================= --增加 create rule rule_mytab_insert as on insert to mytab do also insert into mytab_log(oprtype,oprtime,new_id,new_note) values ('i',now(),new.id,new.note); --修改 create rule rule_mytab_update as on update to mytab do also insert into mytab_log(oprtype,oprtime,old_id,new_id,old_note,new_note) values ('u',now(),old.id,new.id,old.note,new.note); --删除 create rule rule_mytab_delete as on delete do also insert into mytab_log(oprtype,oprtime,old_id,old_note) values ('d',now(),old.id,old.note); 4.================================创建索引================================= --设置一个部分索引以排除普通数值 create index access_log_client_ip_ix on access_log(client_log) where not(client_ip > inet '192.168.100.0' and client_ip < inet '192.168.100.255'); --设置一个部分索引以排除不感兴趣的数值 create index orders_unbilled_index on orders(order_nr) where billed is not true; --设置一个部分唯一索引 create unique index tests_success_constraint on tests (subject,target) where success; 5.==================================序列=================================== create sequence seqtest01; select nextval('seqtest01'); select currval('seqtest01'); --查询上一个seq select lastval(); --查询下一个seq select nextval('seqtest01'); --改变序列的初始值 select setval('seqtest01',1); --查询下一个seq select nextval('seqtest01'); 6.==================================咨询锁 advisory lock======================= --会话1 select pg_advisory_lock(1); --会话2（卡住） begin; select pg_advisory_xact_lock(1); --会话1 select pg_advisory_unlock(1); 7.======================================外部表==================================== --创建外部表 create extension file_fdw; create server file_fdw_server foreign data wrapper file_fdw; create foreign table passwd( username text, pass text, uid int4, gid int4, gecos text, home text, shell text ) server file_fdw_server options(format 'text',filename '/etc/passwd',delimiter ':',null ''); --查询外部表 select * from passwd order by uid asc limit 10; 8.===============================postgres_fdw（远程访问）================================ -----本地创建对象 postgres用户本地安装postgres_fdw插件 create extension postgres_fdw; psql -UJF -d  test create table ts08(id int,note text); insert into ts08 values (1,'abcafad'); ----远程服务器创建对象 create extension postgres_fdw; create server postgres_fdw_server foreign data wrapper postgres_fdw options (host '10.212.24.171',dbname 'test',port '5432'); create user mapping for current_user server postgres_fdw_server options(user 'JF',password 'Oracle123'); 创建外部表 create foreign table fts08 (id int ,note text) server postgres_fdw_server options(table_name 'ts08'); 9.====================================数据库性能视图=================================== \x select * from pg_stat_activity; select * from pg_stat_all_tables; select * from pg_stat_sys_tables; select * from pg_stat_user_tables; select * from pg_stat_all_indexes; select * from pg_stat_sys_indexes; select * from pg_stat_user_indexes; 10.=======================================PgBouncer====================================== yum install pgbouncer http://pgfoundry.org 11.=======================================基本函数====================================== --绝对值 select abs(2),abs(-3.3),abs(-33); --平方根 select SQRT(2),SQRT(40); --case when select case 2   when 1 then 'one'   when 2 then 'two' else 'more' end; --查看版本 select version(); --查看视图的定义 select pg_get_viewdef('viewname', true); 12.=======================================数据库备份还原====================================== --基础备份 pg_basebackup --pgdata=/home/postgres/backup --host=127.0.0.1 --tablespace-mapping=/u01/data02=/home/postgres/data2  --tablespace-mapping=/u01/data03=/home/postgres/data3 --tablespace-mapping=/u01/postgresql-12.0/data=/home/postgres/data1 --verbose --progress 该备份可以直接启动 chmod +R 750 /home/postgres/backup pg_ctl start -D /home/postgres/backup psql --按schema备份&&恢复 pg_dump -h 10.212.24.103 -p 5432 -Fc -v --schema=jf -f /home/postgres/backup0902 -U jf test