ITPub博客

首页 > 数据库 > PostgreSQL > PG_基础查询SQL

PG_基础查询SQL

原创 PostgreSQL 作者:adamjunior 时间:2020-09-08 15:31:55 0 删除 编辑

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





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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69959726/viewspace-2717983/,如需转载,请注明出处,否则将追究法律责任。

下一篇: PG_12.0_linux安装
请登录后发表评论 登录
全部评论
三年之病,求七年之艾。岂可得之?

注册时间:2020-01-14

  • 博文量
    22
  • 访问量
    14590