【PG】psql工具使用及日常维护管理

前序【PG】CentOS7源码安装PostgreSQL13.3 【PG】手工启停PostgreSQL数据库

psql工具同比于Oracle的sqlplus

本文主要源自墨天轮的一套非常好的快速上手视频，推荐观看原版视屏及博客！

视频链接：PostgreSQL DBA快速上手教程
作者博客：http://www.knockatdatabase.com/category/postgresql/
官方文档：https://www.postgresql.org/docs/current/install-procedure.html

一、psql工具的使用

1. psql连接数据库

--日常登录本机
psql

--登录完整方式
psql -h localhost -p 5432 -d postgres -U postgres

-- -h选项表示host，要连接数据库服务器名或者IP地址；远程访问数据库，应写对应服务器IP，本机可忽略
-- -p选项表示port，数据库运行端口，默认5432，可在postgres.conf配置文件里修改，restart数据库生效
-- -d选项表示database，要连接访问的数据库名
-- -U选项表示username，我们以哪个用户来访问数据库

注意：该方式登录并没有输入密码，原因为在pg_hba.conf文件中配置了环路地址# IPv4 local

另外，当我们创建postgres用户，使用该用户安装数据库软件、初始化数据库的时候，默认会在数据库里创建同名的数据库用户

-- \du 列出所有角色/用户
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# select usename from pg_user;
 usename
----------
 postgres
(1 row)

2. 查看帮助

psql --help

显示内容太多，计划单独学习整理一篇。举例：

--列出数据库集群里的所有数据库
psql -l

所有新create 数据库都以上图中template1为模板.
这里有一篇说明：https://www.cnblogs.com/abclife/p/13908650.html

3. psql中执行SQL语句

Data Definition Language(create|drop|truncate)；

Data Control Language(grant|revoke)；

Data Manipulate Language(insert|update|delete)；

Transaction Control Language(commit|rollback|savepoint)。

需要知道的：

• psql下默认为DML语句自动事务提交
• begin;来显示开启事务
• 通过end;或者commit;来提交事务
• 回滚事务rollback;
• 以上英文分号是必需的
• \i 是执行外部sql脚本，例如:postgres=# \i 1.sql
• \! 表示的是在psql命令行上临时执行shell命令

4. psql中的帮助

\h
\help
\h create database

二、常用维护管理命令

仅列出部分，详细信息通过psql –help和\h 命令查看

--查看数据库版本
select version();

--查看所有数据库信息，\l+ 比 \l显示内容多了size、Tablespace、Description
\l+

--查看数据库启动时间信息
select pg_postmaster_start_time();

--查看用户信息
\du

--显示所有的表
\d

--查看表大小
\dt+ 表名

--显示表结构，如果表上有索引，也一起显示出来
\d 表名

--查看索引大小
\di+ 索引名称

--创建新用户，创建新用户t_user，具有login访问数据库的权限
create user xkk login password 'xkk';

--创建数据库
create database 数据库名 owner 拥有者;

--切换到新创建数据库
\c 数据库名 用户名

--查看当前使用哪个账户连接到哪个数据库
\c

--查看视图、函数、表空间
分别是\dv,\df,\db