Q：命令行执行文件里面的语句
psql -U galax -W "weihua@123" -d krm -p 5432 -f xxx.sql
Q：docker 本地运行 psql
1、获取最新的 postgreSql 镜像。
2、创建一个挂载卷 volume：docker volume create my_volume_01
docker volume create my_volume_01 # my_volume_01 是自定义的 volume 的名字
docker volume ls # 查看所有的 volume
docker volume inspect my_volume_01 # 查看 volume 具体在宿主机的目录信息
举例：
[root@centos7 ~]# docker volume ls
DRIVER VOLUME NAME
local my_volume_01
[root@centos7 ~]# docker volume inspect my_volume_01
[
{
"CreatedAt": "2022-08-23T04:41:24-04:00",
"Driver": "local",
"Labels": {},
"Mountpoint": "/var/lib/docker/volumes/my_volume_01/_data",
"Name": "my_volume_01",
"Options": {},
"Scope": "local"
}
]
[root@centos7 ~]#
3、运行 psql 镜像：
docker run -d --name=postgres_local -p 5432:5432 -v postgres-volume:/var/lib/postgresql/data -e POSTGRES_PASSWORD=weihua@123 postgres:latest
1）默认用户为 postgres，也可以通过 -e POSTGRES_USER=user_name 来指定。
2）-e POSTGRES_PASSWORD 必须指定，超级管理员密码。
3）运行起来之后，需要进入到容器中创建数据库：
docker exec -it <image_id> psql -h <host_ip> -U <user_name>
举例：
[root@centos7 ~]# docker exec -it 0c990bb6c998 psql -h localhost -U postgres
psql (14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.
postgres=#
4）创建完数据库，就可以使用 Navicate 等数据库管理软件添加访问了。
Q：常用命令
1、登录数据库
$ psql -U galax krm（登录krm数据库）
输入密码：（一般是 root 或 gandalf 的登录密码，或者 SingleLOUD!1，最早的情况）
$ psql -U galax vrm（登录vrm数据库）
2、查看数据：
查看数据库：\l
切换数据库：\c DB_NAME
查看数据表列表：\d
查看数据表详情：\d TABLE_NAME;
退出：\q
切换显示：\x // 再次输入则取消
设置schema：ALTER SESSION SET current_schema = xxx; // 默认进入的是 public
3、创建数据库：
CREATE DATABASE dbname;
Q：windows 启动命令
pg_ctl -D "D:\dev\psql\postgresql-12.7-2-windows-x64-binaries\pgsql\data" -l "D:\dev\psql\postgresql-12.7-2-windows-x64-binaries\pgsql\log\mylog.log" start
默认用户名：s00574212
默认密码: postgres
默认数据库: postgres
需要在安装目录新建data和log目录。关掉启动窗口，数据库结束。除非安装服务。
Q：统计数据库或表的磁盘空间占用
1、统计数据库中各表占用磁盘大小：
SELECT table_schema
OR '.'
OR table_name AS table_full_name
, pg_size_pretty(pg_total_relation_size('"'
OR table_schema
OR '"."'
OR table_name
OR '"')) AS size
FROM information_schema.tables
ORDER BY pg_total_relation_size('"'
OR table_schema
OR '"."'
OR table_name
OR '"') DESC
或者：
SELECT relname, pg_size_pretty(pg_relation_size(relid))
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY pg_size_pretty(pg_relation_size(relid)) DESC
统计单个表的占用磁盘空间的大小：
#单个表方法一：
select pg_relation_size('public.tbl_krm_image_namespace');
#单个表方法二：
select pg_size_pretty(pg_total_relation_size('public.tbl_krm_image_namespace'));
注：还可以在上面查询的基础上，增加schema的过滤项，比如：wheretable_schema='public'，就可以只查看 public 下面的相关表的磁盘空间占用情况。
2、统计数据库大小：
1）单个库查询：
select pg_database_size('db_name');
2）查询所有的库：
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;
或者：
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner
, CASE
WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY CASE
WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC
Q：查询 psql 中的表结构信息
1、进入数据库：psql -U galax krm，回车输入密码
2、显示某个表的详细信息，包括字段、索引、外键约束等等：=>\d TABLE_NAME
举例：
KRM=> \d tbl_krm_cluster;
Table "PUBLIC.TBL_KRM_CLUSTER"
Column | Type | Modifiers
------------------------------+-----------------------------+---------------------------------
ID | CHARACTER VARYING(255) | not null
CREATE_TIME | TIMESTAMP WITHOUT TIME ZONE | not null
CREATE_BY | CHARACTER VARYING(255) |
UPDATE_TIME | TIMESTAMP WITHOUT TIME ZONE |
UPDATE_BY | CHARACTER VARYING(255) |
CA_CERT | TEXT |
CAN_AUDIT | BOOLEAN |
CLIENT_CERT | TEXT |
CLIENT_KEY | TEXT |
CNI_CATEGORY | CHARACTER VARYING(32) | not null
CRI_CATEGORY | CHARACTER VARYING(32) | not null
CRI_VERSION | CHARACTER VARYING(32) |
DESCRIPTION | CHARACTER VARYING(255) |
DNS | CHARACTER VARYING(255) |
ENDPOINT | CHARACTER VARYING(255) |
REGISTRY_ID | CHARACTER VARYING(1024) |
MANAGE_ADDRESS | CHARACTER VARYING(32) |
MASTER_COUNT | INTEGER |
MASTER_SCHEDULABLE | BOOLEAN |
NAME | CHARACTER VARYING(64) | not null
NODE_ARCH | CHARACTER VARYING(255) |
NODE_PROVIDER | CHARACTER VARYING(255) |
NODE_TYPE | CHARACTER VARYING(255) |
NTP | CHARACTER VARYING(255) |
K8S_OSVERSION | CHARACTER VARYING(64) |
POD_CIDR | CHARACTER VARYING(32) | not null
K8S_PREVERSION | CHARACTER VARYING(32) |
IAAS_PVC_DATASTORAGE_ID | CHARACTER VARYING(64) |
SERVICE_CIDR | CHARACTER VARYING(32) | not null
SHARED_BUSINESS_ADDRESS | CHARACTER VARYING(32) |
STATUS | CHARACTER VARYING(64) |
K8S_VERSION | CHARACTER VARYING(32) |
MANAGEHOSTNETWORK_ID | CHARACTER VARYING(255) |
SHAREDBUSINESSHOSTNETWORK_ID | CHARACTER VARYING(255) |
SHARED_BUSINESS_ADDRESS_NAME | CHARACTER VARYING(32) |
MANAGE_ADDRESS_NAME | CHARACTER VARYING(32) |
K8S_PRE_OSVERSION | CHARACTER VARYING(64) | default NULL::CHARACTER VARYING
CLUSTER_UPGRADE_ID | CHARACTER VARYING(255) |
Indexes:
"TBL_KRM_CLUSTER_PKEY" PRIMARY KEY, BTREE (ID)
"UK_GP1T67WBGQKCE01YC44JXITJ6" UNIQUE CONSTRAINT, BTREE (NAME)
Foreign-key constraints:
"FK62VQAOD40NSVXKNR0RYTC80EI" FOREIGN KEY (SHAREDBUSINESSHOSTNETWORK_ID) REFERENCES TBL_KRM_HOST_NETWORK(ID)
"FK90IRY2XFPCMMYR6HNGUNGVGDY" FOREIGN KEY (MANAGEHOSTNETWORK_ID) REFERENCES TBL_KRM_HOST_NETWORK(ID)
"TBL_KRM_CLUSTER_TO_UPGRADE_PKEY" FOREIGN KEY (CLUSTER_UPGRADE_ID) REFERENCES TBL_KRM_CLUSTER_UPGRADE(ID)
Referenced by:
TABLE "TBL_KRM_CLUSTER_HOST_NETWORK" CONSTRAINT "FK2UDBD8SNMOHHYQG1NJ8FBHS6R" FOREIGN KEY (CLUSTER_ID) REFERENCES TBL_KRM_CLUSTER(ID)
TABLE "TBL_KRM_NODE" CONSTRAINT "FKFEFNCW07BJGKR3RJUBDLCJ7AM" FOREIGN KEY (CLUSTER_ID) REFERENCES TBL_KRM_CLUSTER(ID)
TABLE "TBL_KRM_NODEPOOL" CONSTRAINT "FKO3QX0I7BA32OKS2Q5PET982X3" FOREIGN KEY (CLUSTER_ID) REFERENCES TBL_KRM_CLUSTER(ID)
TABLE "TBL_CLUSTER_QUORUM" CONSTRAINT "FKPPNUQR7X4NMNCXO153PR8B9CO" FOREIGN KEY (CLUSTER_ID) REFERENCES TBL_KRM_CLUSTER(ID)