一、下载这里我们进行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 start2.4 连接这里我们使用navicat具体配置如下注意帐号密码默认为postgres。三、DBA常用SQL查询语句3.1 查看帮助命令help --总的帮助\h --SQL commands级的帮助? --psql commands级的帮助3.2 按列显示类似MySQL的\GExpanded display is on.3.3 查看DB安装目录(最好root用户执行)find / -name initdb3.4 查看有多少DB实例在运行(最好root用户执行)find / -name postgresql.conf3.5 查看DB版本cat $PGDATA/PG_VERSIONpsql --versionshow server_version;select version();3.6 查看DB实例运行状态pg_ctl status3.7 查看所有数据库psql –l --查看5432端口下面有多少个DBpsql –p XX –l --查看XX端口下面有多少个DBselect * from pg_database;3.8 创建数据库createdb database_namecreate database --创建数据库的帮助命令create database database_name3.9 进入某个数据库psql –d dbname3.10 查看当前数据库select current_database();3.11 查看数据库文件目录show data_directory;cat $PGDATA/postgresql.conf |grep data_directorycat /etc/init.d/postgresql|grep PGDATAlsof |grep 5432得出第二列的PID号再ps –ef|grep PID3.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.dattablespacet2.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_settings3.30 查看某个参数值,比如参数work_memshow work_mem3.31 修改某个参数值,比如参数work_memalter 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有user1pg_user和pg_shadow没有user1alter 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 schemaUSAGE对于程序语言来说允许使用指定的程序语言创建函数;对于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 iWHERE 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 24610select * from pg_get_functiondef(24610);3.45 查看表大小(不含索引等信息)select pg_relation_size(‘cc’); --368640 byteselect pg_size_pretty(pg_relation_size(‘cc’)) --360 kB3.46 查看表所对应的数据文件路径与大小SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname ‘empsalary’;四、posegresql查询当前lsn4.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 000000010000000000000005pg_wal日志没有设置保留周期的参数即没有类似mysql的参数expire_logs_dayspg_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_nameidthen主键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时间else1-||a.column_name||zysjb.random_char(3)end)||;newcommentFROMinformation_schema.columnsaLEFTJOINpg_catalog.pg_statio_all_tables bONa.table_schemab.schemanameANDa.table_nameb.relnameLEFTJOINpg_catalog.pg_description dONb.relidd.objoidANDd.objsubida.ordinal_positionINNERJOINinformation_schema.tablescONa.table_schemac.table_schemaANDa.table_namec.table_nameWHEREc.table_schemamyschema--AND c.table_name gc_organdd.descriptionisnull随机数生成函数CREATEORREPLACEFUNCTIONzysjb.random_int(start_numint4,end_numint4)RETURNSpg_catalog.int4AS$BODY$BEGIN-- 功能生成最小值为start_num最大值为end_num的随机数RETURNFLOOR(start_numrandom()*(end_num-start_num1));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(num0) LOOP SELECT substr(chineseStr,zysjb.random_int(1,char_length(chineseStr)),1) INTO tmpp; tmp : concat(tmp,tmpp); --RAISE NOTICE 核对-5tmp:% 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 authorwu.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;