一、下载
这里我们进行postgresSQL压缩版安装，首先从官网下载.
二、安装
2.1 解压
下载后解压到需要安装的目录，这里我们安装在“D:\Program Files\PostgreSQL”。
2.2 初始化
使用cmd或者其他命令窗口，进入到PostgreSQL目录，执行以下命令：
bin\initdb -D data -U postgres -E utf8 --no-locale
参数含义可以参考帮助：
2.3 启动
bin/pg_ctl -D data -l logfile start
2.4 连接
这里我们使用navicat，具体配置如下：
注意：帐号密码默认为postgres。
三、DBA常用SQL查询语句
3.1 查看帮助命令
help --总的帮助
\h --SQL commands级的帮助
? --psql commands级的帮助
3.2 按列显示，类似MySQL的\G
Expanded display is on.
3.3 查看DB安装目录(最好root用户执行)
find / -name initdb
3.4 查看有多少DB实例在运行(最好root用户执行)
find / -name postgresql.conf
3.5 查看DB版本
cat $PGDATA/PG_VERSION
psql --version
show server_version;
select version();
3.6 查看DB实例运行状态
pg_ctl status
3.7 查看所有数据库
psql –l --查看5432端口下面有多少个DB
psql –p XX –l --查看XX端口下面有多少个DB
select * from pg_database;
3.8 创建数据库
createdb database_name
create database --创建数据库的帮助命令
create database database_name
3.9 进入某个数据库
psql –d dbname
3.10 查看当前数据库
select current_database();
3.11 查看数据库文件目录
show data_directory;
cat $PGDATA/postgresql.conf |grep data_directory
cat /etc/init.d/postgresql|grep PGDATA=
lsof |grep 5432得出第二列的PID号再ps –ef|grep PID
3.12 查看表空间
select * from pg_tablespace;
3.13 查看语言
select * from pg_language;
3.14 查询所有schema，必须到指定的数据库下执行
select * from information_schema.schemata;
SELECT nspname FROM pg_namespace;
3.15 查看表名
–只能查看到当前数据库下public的表名
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE ‘pg%’ AND tablename NOT LIKE ‘sql_%’ ORDER BY tablename;
SELECT * FROM information_schema.tables WHERE table_name=‘ff_v3_ff_basic_af’;
3.16 查看表结构
select * from information_schema.columns where table_schema=‘public’ and table_name=‘XX’;
3.17 查看索引
select * from pg_index;
3.19 查看视图
select * from pg_views where schemaname = ‘public’;
select * from information_schema.views where table_schema = ‘public’;
3.19 查看触发器
select * from information_schema.triggers;
3.20 查看序列
select * from information_schema.sequences where sequence_schema = ‘public’;
3.21 查看约束
select * from pg_constraint where contype = ‘p’
select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = ‘cc’;
3.22 查看XX数据库的大小
SELECT pg_size_pretty(pg_database_size(‘XX’)) As fulldbsize;
3.23 查看所有数据库的大小
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;
3.24 查看各数据库数据创建时间：
select datname,(pg_stat_file(format(‘%s/%s/PG_VERSION’,case when spcname=‘pg_default’ then ‘base’ else ‘pg_tblspc/’||t2.oid||‘/PG_11_201804061/’ end, t1.oid))).* from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid;
3.25 按占空间大小，顺序查看所有表的大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname=‘public’ order by pg_relation_size(relid) desc;
3.26 按占空间大小，顺序查看索引大小
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname=‘public’ order by pg_relation_size(relid) desc;
3.27 查看参数文件
show config_file;
show hba_file;
show ident_file;
3.28 查看当前会话的参数值
show all;
3.29 查看参数值
select * from pg_file_settings
3.30 查看某个参数值,比如参数work_mem
show work_mem
3.31 修改某个参数值,比如参数work_mem
alter sy stem set work_mem=‘8MB’
使用alter system命令将修改postgresql.auto.conf文件，而不是postgresql.conf，这样可以很好的保护postgresql.conf文件，加入你使用很多alter system命令后搞的一团糟，那么你只需要删除postgresql.auto.conf，再执行pg_ctl reload加载postgresql.conf文件即可实现参数的重新加载。
3.32 查看是否归档
show archive_mode;
3.33 查看运行日志的相关配置
运行日志包括Error信息，定位慢查询SQL，数据库的启动关闭信息，checkpoint过于频繁等的告警信息。
show logging_collector;–启动日志收集
show log_directory;–日志输出路径
show log_filename;–日志文件名
show log_truncate_on_rotation;–当生成新的文件时如果文件名已存在，是否覆盖同名旧文件名
show log_statement;–设置日志记录内容
show log_min_duration_statement;–运行XX毫秒的语句会被记录到日志中，-1表示禁用这个功能，0表示记录所有语句，类似mysql的慢查询配置
3.34 查看wal日志的配置，wal日志就是redo重做日志
存放在data_directory/pg_wal目录
3.35 查看当前用户
select current_user;
3.36 查看所有用户
select * from pg_user;
select * from pg_shadow;
3.37 查看所有角色
select * from pg_roles;
3.38 查询用户XX的权限，必须到指定的数据库下执行
select * from information_schema.table_privileges where grantee=‘XX’;
3.39 创建用户XX，并授予超级管理员权限
create user XXX SUPERUSER PASSWORD ‘123456’
3.40 创建角色
赋予了login权限，则相当于创建了用户，在pg_user可以看到这个角色
create role “user1” superuser;–pg_roles有user1，pg_user和pg_shadow没有user1
alter role “user1” login;–pg_user和pg_shadow也有user1了
3.41 授权
GRANT ALL PRIVILEGES ON schema schemaname TO dbuser;
grant ALL PRIVILEGES on all tables in schema fds to dbuser;
GRANT ALL ON tablename TO user;
GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;
grant select on all tables in schema public to dbuser;–给用户读取public这个schema下的所有表
GRANT create ON schema schemaname TO dbuser;–给用户授予在schema上的create权限，比如create table、create view等
GRANT USAGE ON schema schemaname TO dbuser;
grant select on schema public to dbuser;–报错ERROR: invalid privilege type SELECT for schema
USAGE：对于程序语言来说，允许使用指定的程序语言创建函数;对于Schema来说，允许查找该Schema下的对象;对于序列来说，允许使用currval和nextval函数;对于外部封装器来说，允许使用外部封装器来创建外部服务器;对于外部服务器来说，允许创建外部表。
3.42 查看表上存在哪些索引以及大小
select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in
(select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = ‘cc’);
SELECT c.relname,c2.relname, c2.relpages*8 as size_kb FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname =‘cc’ AND c.oid =i.indrelid AND c2.oid =i.indexrelid ORDER BY c2.relname;
3.43 查看索引定义
select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = ‘cc’;
select pg_get_indexdef(b.indexrelid);
3.44 查看过程函数定义
select oid,* from pg_proc where proname = ‘insert_platform_action_exist’; --oid = 24610
select * from pg_get_functiondef(24610);
3.45 查看表大小(不含索引等信息)
select pg_relation_size(‘cc’); --368640 byte
select pg_size_pretty(pg_relation_size(‘cc’)) --360 kB
3.46 查看表所对应的数据文件路径与大小
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‘empsalary’;
四、posegresql查询当前lsn
4.1 用到哪些方法：
select proname from pg_proc where proname like ‘pg_%_lsn’;
4.2 查询当前的lsn值：
select pg_current_wal_lsn();
4.4 查询当前lsn对应的日志文件
select pg_walfile_name(‘0/1732DE8’);
4.5 查询当前lsn在日志文件中的偏移量
SELECT * FROM pg_walfile_name_offset(pg_current_wal_lsn());
4.6 切换pg_wal日志
select pg_switch_wal();
4.7 清理pg_wal日志
pg_archivecleanup /postgresql/pgsql/data/pg_wal 000000010000000000000005
pg_wal日志没有设置保留周期的参数，即没有类似mysql的参数expire_logs_days，pg_wal日志永久保留，除非shell脚步删除几天前或pg-rman备份时候设置保留策略
4.8 查询有哪些slot，任意一个数据库下都可以查，查询的结果都一样
select * from pg_replication_slots;
4.9 查询表字段信息
SELECTc.table_catalog,c.table_schemaASschema_name,c.table_name,a.column_name,d.descriptionAScolumn_comment,'COMMENT ON COLUMN '||c.table_schema||'.'||c.table_name||'.'||a.column_name||' IS '''||(casewhena.column_name='id'then'主键'whena.column_namelike'%ids%'then'序列'whena.column_namelike'%id%'then'编码'whena.column_namelike'%name%'then'名称'whena.column_namelike'%create_by%'then'创建人'whena.column_namelike'%create_date%'then'创建时间'whena.column_namelike'%update_by%'then'更新人'whena.column_namelike'%update_date%'then'更新时间'whena.column_namelike'%del_flag%'then'删除标识'whena.column_namelike'%email%'then'邮件'whena.column_namelike'%phone%'then'电话'whena.column_namelike'%remark%'then'备注'whena.column_namelike'%extend%'then'扩展'whena.column_namelike'%time%'then'时间'else'1-'||a.column_name||zysjb.random_char(3)end)||''';'newcommentFROMinformation_schema.columnsaLEFTJOINpg_catalog.pg_statio_all_tables bONa.table_schema=b.schemanameANDa.table_name=b.relnameLEFTJOINpg_catalog.pg_description dONb.relid=d.objoidANDd.objsubid=a.ordinal_positionINNERJOINinformation_schema.tablescONa.table_schema=c.table_schemaANDa.table_name=c.table_nameWHEREc.table_schema='myschema'--AND c.table_name = 'gc_org'andd.descriptionisnull随机数生成函数
CREATEORREPLACEFUNCTION"zysjb"."random_int"("start_num"int4,"end_num"int4)RETURNS"pg_catalog"."int4"AS$BODY$BEGIN-- 功能，生成最小值为start_num，最大值为end_num的随机数RETURNFLOOR(start_num+random()*(end_num-start_num+1));END;$BODY$LANGUAGEplpgsql VOLATILE COST100字符串随机生成函数
CREATE OR REPLACE FUNCTION "zysjb"."random_char"("num" int4) RETURNS "pg_catalog"."varchar" AS $BODY$ DECLARE tmp VARCHAR; tmpp VARCHAR; chineseStr VARCHAR := '乾坤有序宇宙无疆星辰密布斗柄指航昼白夜黑日明月亮风驰雪舞电闪雷响'; BEGIN WHILE(num>0) LOOP SELECT substr(chineseStr,zysjb.random_int(1,char_length(chineseStr)),1) INTO tmpp; tmp := concat(tmp,tmpp); --RAISE NOTICE '核对-5：tmp:% tmpp:% %', tmp, tmpp, char_length(chineseStr) ; num := num-1; END LOOP; RETURN tmp; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 1004.10 pg中踢夜函数
CREATE OR REPLACE FUNCTION get_minutes_out_of_night_str(start_time timestamp, end_time timestamp, night_start time, night_end time) RETURNS integer AS $$ DECLARE total_minutes integer := 0; i timestamp; j time; BEGIN /* 日期：2024-07-30 author：wu.danyang 入参： start_time: 开始时间 end_time：结束时间 night_start： 夜间开始时间点 night_end： 夜间结束时间点 返回：分钟数 示例：SELECT get_minutes_out_of_night_str('2024-01-01 19:00:00', '2024-01-01 22:00:00', '20:00:00', '23:00:00') */ FOR i IN SELECT generate_series(start_time, end_time, '1 minute') loop j := TO_char(i, 'HH24:MI:SS')::time; if night_start <= night_end THEN -- RAISE NOTICE '1-j ：%', j; if j >= night_start and j < night_end THEN -- RAISE NOTICE 'night_start <= night_end ：%', j; CONTINUE; END IF; else -- RAISE NOTICE '2-j ：%', j; if (j >= night_start and j <= '23:59:59'::time) or (j >= '00:00:00'::time and j <= night_end) THEN -- RAISE NOTICE 'night_start > night_end ：%', j; CONTINUE; END IF; END IF; total_minutes = total_minutes + 1; END LOOP; RETURN total_minutes; END; $$ LANGUAGE plpgsql;
