--查看版本详细信息
select version(); show server_version; SHOW server_version_num;
**-- 查看PostgreSQL数据库服务器端编码:
**
****show server_encoding;\
-- 查看PostgreSQL客户端工具psql编码：
show client_encoding;\
-- 指定Postgresql会话的客户端编码：
set client_encoding to 'utf8'; \
**-- 创建用户
**
create user test with password 'test';\
create user test superuser password '123456';
**-- 修改用户密码
**
ALTER USER username WITH password '新密码';
**--创建表空间
**
create TABLESPACE ts_test owner test LOCATION 'D:/PostgreSQL/data';\
**--创建数据库
**
create DATABASE dbtest owner=test ENCODING='UTF-8' tablespace=ts_yczhdata connection LIMIT 1000;\
ALTER DATABASE dbtest SET TABLESPACE ts_yczhdata；
**--授权用户
**
grant all PRIVILEGES on DATABASE dbtest to test;\
1、创建分区
/*创建索引分区表，有索引有分区
所有的主键和索引需要建立在分区内部，不能建立在表上面
*/
create table g_Voucher_P (
fCaseNum VARCHAR(255) default '' not null,
fCaseYear SMALLINT default 0 not null,
fVchID integer default 0 not null,
fID integer default 0 not null
)
partition by list (fCaseNum);
2、手动创建一个分区和分区索引
CREATE table p_0 PARTITION of g_voucher_p for values in('0');
create index IX_g_Voucher_P_VID on p_0 (
fCaseNum ASC,
fCaseYear ASC,
fVID ASC
);
3、删除分区
--先删除分区关联关系
ALTER TABLE g_voucher_p DETACH PARTITION P_C01;
--再删除实体分区表
drop table P_CO1;
4、增加一个分区
ALTER TABLE gg ATTACH PARTITION p_2 FOR VALUES IN (2)；
注意这里的子表p_2表得先建好，然后才能alter table 挂到主表gg上
--查询表空间信息\
SELECT oid, spcname, pg_get_userbyid(spcowner) AS owner, pg_tablespace_location(oid) As spclocation, spcacl, shobj_description(oid, 'pg_tablespace') AS comment\
FROM pg_tablespace WHERE spcname = 'ts_test';
--类似oracle的merge into 功能
insert into tab(a,b) values('1','2') on conflict(a) do update set b='2'
说明：conflict里是PK列\
--查询表信息
select * from pg_tables where schemaname='public';\
--查询列信息
select * from information_schema."columns" where table_schema='public';\
--查询所有索引
select * from pg_indexes where schemaname='public';
--查询分区
select * from pg_partitioned_table;
--用户函数统计信息
select * from pg_stat_user_functions;
--查询pg_hba.conf 文件信息
select * from pg_hba_file_rules;
--查询数据库文件夹名称信息（XX:\Program Files\PostgreSQL\11\data\base)下
SELECT oid,datname from pg_database ;
--查询表对应的存储位置
select pg_relation_filepath('p_auxtype');
--查询所有用户（角色）信息
select * from pg_user;
select * from pg_roles;
--查询序列
select * from pg_sequence
--定位分析消耗CPU高的SQL语句
SELECT procpid, START, now() - START AS lap, current_query FROM ( SELECT backendid, pg_stat_get_backend_pid (S.backendid) AS procpid,
pg_stat_get_backend_activity_start (S.backendid) AS START,pg_stat_get_backend_activity (S.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset () AS backendid) AS S) AS S WHERE current_query != '' ORDER BY lap DESC;
--查看会话进程
select * from pg_stat_activity where datname='dbtest';
--查询当前时间（yyyy-MM-dd hh:mm:ss）
select now()::timestamp(0)without time zone;
--查询指定分区表信息
relkind | char | r=普通表，i=索引，S=序列，t= TOAST表， v=视图，m=物化视图，c=复合类型，f =外表，p=分区表 |
|---|
SELECT
nmsp_parent.nspname AS parent_schema ,
parent.relname AS parent ,
nmsp_child.nspname AS child ,
child.relname AS child_schema
FROM
pg_inherits JOIN pg_class parent
ON pg_inherits.inhparent = parent.oid JOIN pg_class child
ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent
ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child
ON nmsp_child.oid = child.relnamespace
--查询库中所有分区表子表个数
SELECT nspname , relname , COUNT(*) AS partition_num
FROM pg_class c , pg_namespace n , pg_inherits i
WHERE
c.oid = i.inhparent AND c.relnamespace = n.oid
AND c.relhassubclass AND c.relkind = 'r'
GROUP BY 1,2 ORDER BY partition_num DESC;
--查询pg数据库启动时间和启动时长
select pg_postmaster_start_time() as starttime,CURRENT_TIMESTAMP-pg_postmaster_start_time() as uptime
--查询删除临时schema
select 'drop schema '||nspname||';' from pg_namespace where nspname like '%temp%'
--设置表nolog
alter table tablename set unlogged;
alter table tablename set logged;
PG的很多性能数据可以通过查询pg_stat_或pg_statio_开头的系统表获取
