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