postgreSQL
特性：
1. 支持多种插件；
2. 默认数据库为：postgres
3. public是每个database都会默认创建的，里面存放的是所有schema都会调用的公共资源迁移问题汇总：
1. 迁移之前优先先迁移pubulic数据库 ---> 因为其他的schema的创建会依赖与public库的【插件和seq序列等对象资源】
常用命令：
\dg 查询有哪些roles
\df+ 查询函数详细信息
\df 查询有哪些函数function
\？ 查询支持哪些命令
\c - 【用户】 当前用户切换到哪个用户
select user 显示当前连接用户
\c 切换数据库
\l+ 列出所有数据库的大小
\l 列出当前所有数据库
\q 退出
\dt 查询表
\du 查询用户拥有哪些权限
\dT 查询extension
\dx 查询当前数据库有哪些extension
\ds 查询有哪些seq序列
\ddp 查询系统默认权限
\dn 查询schema有哪些
\d+ orders 查询表的详细信息(包含分区键等信息)
\password + 用户 修改用户密码[超级用户才能修改普通用户密码\在duk修改]
select user; 查询当前登入用户
select * from pg_roles; 查询用户权限详细情况
select count(1) from pg_stat_activity; 查询当前连接数
show max_connections; 查询最大连接数
max_connections = 500 配置最大连接数
set search_path to schemaname; 切换schema
<<<-----------------------------------------------------------------------------------
SELECT schema_name,
pg_size_pretty(sum(table_size)::bigint) as "disk space",
round((sum(table_size) / pg_database_size(current_database())) * 100,2)
as "percent(%)"
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,
pg_total_relation_size(pg_catalog.pg_class.oid) as table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid) t
GROUP BY schema_name
ORDER BY "percent(%)" desc;
----------------------------------------------->>> 查询每个schema的大小按降序排列
grant all on schema [schemaname] to [username];
----------------------------------------------->>> 给用户授权给某个schema
create extension [extensionname];
----------------------------------------------->>> 创建插件
create sequence if not exist "[seq_xxxx_xxx]";
----------------------------------------------->>> 创建seqence序列
drop type 【typename];
----------------------------------------------->>> 删除类型
drop extension [extensionname];
----------------------------------------------->>> 删除指定插件
drop schema [schemaname] cascade;
----------------------------------------------->>> 强制删除schema及其下面的依赖，cascade模式，表示关联下游
/u01/pgsql/bin/pg_dumpall -p 3009 -U 超级用户 -f my_dumpall.sql --inserts --schema-only
----------------------------------------------->>> 导出pg所有数据库的【系统数据】，对象定义(模式)不包含数据
/u01/pgsql/bin/pg_dumpall -p [dukIP] -U [supperuser] -l [database] -f my_dumpall.sql --inserts --schema-only
----------------------------------------------->>> 添加-l参数从指定的数据库获取
/u01/pgsql/bin/pg_dumpall -p 3009 -U pg38072 -f my_dumpall.sql --inserts --roles-only；
----------------------------------------------->>> 只导出权限信息
/u01/pgsql/bin/psql -p 端口 -U pg$cust_id -f my_dumpall.sql
----------------------------------------------->>> 从[天基]导入数据文件
psql -h ip地址 -p 端口 -U 超级用户 -d postgres -f mysqldump.sql
----------------------------------------------->>> 从ECS导入数据文件
select * from information_schema.table_privileges where grantee='[user_name]';
----------------------------------------------->>> 查看哪些用户拥有哪些表的权限
export PGPASSWORD='postgres密码'
----------------------------------------------->>> 把PGPASSWORD写入环境变量实现免密
psql -h ip地址 -p 端口 -U 用户 -d 数据库名 -o [执行结果输出至文件的文件名]
----------------------------------------------->>> 将psql执行的查询结果都定向输出值-o指定的文件中
export PGPASSWORD='postgres密码'
----------------------------------------------->>> 免密登入PG数据库
查询分区表：
select n.nspname || '.' || c.relname from pg_partition p, pg_class c, pg_namespace n where p.parrelid = c.oid and c.relnamespace = n.oid;
查询非分区表：
select n.nspname || '.'|| relname from pg_class c, pg_namespace n where c.relkind = 'r' and c.relname not in (select partitiontablename from pg_partitions) and c.relname not in (select tablename from pg_partitions) and c.relnamespace = n.oid;
<<<-----------------------------------------------------------------------------------
update pg_database
set datallowconn = 'false'
where datname = 'modelcenter'; # 关闭数据库的连接
select pg_terminate_backend(pid) from pg_stat_activity where datname = 'modelcenter';
drop database modelcenter；
----------------------------------------------->>> 强制删除数据库
select pg_database.datname,pg_database_size(pg_database.dataname) AS size from pg_database;
----------------------------------------------->>> 查询数据库的大小
PG:
psql:
psql -h xxx -p3433 -U admin_db -d modelcenter
pg_dump --username=admin_db -h xxxx -p3433 --format=plain --file=ywztext ywztext
查询表大小：
select relname,pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
统计每张表行数：
select schemaname,relname,n_live_tup as estimatedcount from pg_stat_user_tables order by n_live_tup desc;
查询每个schema大小：
select schema_name,pg_size_pretty(sum(table_size)::bigint) as "disk space",round ((sum(table_size) / pg_database_size(current_database())) *100,2) as "percent(%)" from(select pg_catalog.pg_namespace.nspname as schema_name,pg_total_relation_size(pg_catalog.pg_class.oid) as table_size from pg_catalog.pg_class join pg_catalog.pg_namespace on relnamespace=pg_catalog.pg_namespace.oid) t group by schema_name order by "percent(%)" desc;
导出某个数据库的对象信息，过滤数据
/u01/pgsql/bin/pg_dumpall -U pg6018 -p 3001 -l dataaccess -s -f 5.sql --inserts
查询所有数据库的大小：
select pg_database.datname,pg_database_size(pg_database.datname)AS size from pg_database;
获取schema名字：
psql -h 20.50.2.223 -U admin_db -d postgres -p 3433 -f schema.sql |tail -38|head -36|awk -F '[ ]+' '{print $2}'\
获取正在运行的pid进程：
select datname,pid,state,query from pg_stat_activity where state='active';
查询用户对应的表的权限
select * from indormation_schema.table_privileges where grantee='admin_db';
ECS网卡信息：for i in `seq 1000`;do echo -e '\n---->>>'; ifconfig |grep RX|awk 'NR==3{print $5}';sleep 10;done
修改schema名字：
alter schema pms20_test rename to pms2022;
强制删除数据库：
update pg_database
set datallowconn = 'false'
where datname = 'modelcenter';
select pg_terminate_backend(pid)
from pg_stat_activity
where datname = 'modelcenter';
drop database modelcenter;
强行删除schema：
drop schema pms20 cascade;
查询速率：
cd /opt
export PGPSWORD='xxxx'
for i in `seq 1000`;do echo -e "\n<<<--`date`-->>>\n---<<modelcenter3>>---";psql -h 20.50.2.223 -U admin_db -d test -p 3433 -f 123.sql|grep -w modelcenter|echo `awk -F '|' '{print $2}'`/1024/1024|bc; sleep 5;done
目标端修改密码:
#/u01/pgsql/bin/psql -U pg6019 -p 3002 -d postgres
psql (10.14)
Type "help" for help.
postgres=#
postgres=# \password auror
导出对象：
u01/pgsql/bin/pg_dumpall -U pg38074 -p 3007 -f data_check.sql -s --inserts
<-------------------------------
pgdump导出：
time /u01/pgsql/bin/pg_dump -U pg38072 -p 3009 -j 180 -Fd -d modelcenter -n modelcenter -a -b -Z 5 -f modelcenter
导入还原：
time /u01/pgsql/bin/pg_restore -U pg6019 -p 3002 -d modelcenter -j 300 -v "modelcenter"
在pg数据库中创建只读用户可以采用如下方法。大体实现就是将特定schema的相关权限赋予只读用户。
--创建用户
CREATE USER readonly WITH ENCRYPTED PASSWORD '123456';
--设置用户默认开启只读事务
ALTER USER readonly SET default_transaction_read_only = ON;
--设置用户登录权限
ALTER USER readonly WITH login;
--将schema中usage权限赋予给readonly用户,访问所有已存在的表
GRANT usage ON SCHEMA xyh TO readonly;
--将schema中表的查询权限赋予给readonly用户,访问所有已存在的表
GRANT SELECT ON ALL tables IN SCHEMA xyh TO readonly;
--未来访问xyh模式下所有新建的表：
ALTER DEFAULT privileges IN SCHEMA xyh GRANT SELECT ON tables TO readonly;
--查数据库
select * from pg_database;
--查模式
select exists(select 1 from pg_namespace where nspname='xyh');
select * from pg_catalog.pg_class WHERE relkind='f';
relkind： r = 普通表，i = 索引， S = 序列，t = TOAST 表，v = 视图， m = 物化视图， c = 组合类型， f = 外部表， p = 分区表
# 查询foreign table及其options
select * from information_schema.foreign_table_options;
# 查询user_mappings 及其options
select * from information_schema.user_mapping_options;
# 查询 foreign server及其options
select * from information_schema.foreign_server_options;
#按表大小排序 use切换到库下执行
select datname,pg_size_pretty(size) from (select pg_database.datname, pg_database_size(pg_database.datname) AS size
from pg_database order by size desc) as view ;
#查询schema下所有表
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
#查询表结构
SELECT column_name FROM information_schema.columns WHERE table_name ='table_name';
通过SQL语句查询
"select * from pg_tables" —— 得到当前db中所有表的信息（这里pg_tables是系统视图）
grant all privileges on database ktxmch to ktchsys;
必须要给用户设置对scheam的使用权限
grant USAGE on SCHEMA public to test ;
批量造分区表数据
1.
CREATE TABLE orders (
id serial,
user_id int4,
create_time DATE
) PARTITION BY RANGE (create_time) (START (date '1992-01-01') INCLUSIVE END (date '2000-01-01'));
2.
INSERT INTO orders (user_id, create_time) select 1000, generate_series('1992-01-01'::date, '1992-04-01'::date, '1 minute');pg常用命令
于 2022-08-16 23:08:19 首次发布
1416
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
