psql工具使用及数据库常用维护管理
零 说明
在前面，我们学会了如何手工编译PostgreSQL源代码的方式在Linux服务器安装数据库，也学会了如何正确的手工启停PostgreSQL数据库。
接下来，我们来看看一个交互式的PostgreSQL数据库管理工具的使用，psql。
一 psql工具的使用
1 用psql连接数据库
[postgres@iZbp1anc2b2vggfj0i0oovZ ~]$ which psql /data/postgres/13.2/bin/psql [postgres@iZbp1anc2b2vggfj0i0oovZ ~]$ psql -h localhost -p 5432 -d postgres -U postgres psql (13.2) Type "help" for help. postgres=#
其中，
- psql是PostgreSQL软件安装家目录下的bin路径下的可执行程序;
- -h选项表示host，要连接数据库服务器名或者IP地址；如果要访问的数据库在远端，不在本地服务器上，则这里应该用那台机器的IP地址；如果是云服务器的话，则用云服务商提供的域名字符串即可；
- -p选项表示port，数据库运行在哪个端口上，默认是5432，这个可以在postgres.conf配置文件里修改，但是需要restart数据库才生效；
- -d选项表示database，我们要连接访问的数据库名；
- -U选项表示username，我们以哪个用户来访问数据库。
因此，上述命令表示的是以postgres用户连接监听运行在本地机器上的5432的名为postgres的数据库。命令行上，并没有要求输入数据库密码，为什么？因为我们的pg_hba.conf文件里配置了
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust
这个，在前面的那篇源码安装数据库里讲过。
另外，当前操作系统上有个postgres用户，当我们用这个用户安装数据库软件、初始化数据库的时候，默认在数据库里也创建了一个同名的数据库用户。比如，另外一套数据库环境：
[pg13@centos-master ~]$ id
uid=1108(pg13) gid=1107(pg13) 组=1107(pg13)
[pg13@centos-master ~]$ psql -d postgres -U pg13 -p 5413
psql (13.1)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
es_migrate | | {}
pg13 | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# select usename from pg_user;
usename
------------
pg13
es_migrate
(2 rows)
postgres=# OS上有个名为pg13的用户，在我们以该用户安装、创建数据库之后，数据库内部会自动创建一个同名用户pg13，且该用户是数据库的超级管理员。也就是说，当我们以操作系统上的哪个用户来安装和创建数据库，默认会在数据库内部创建一个同名的数据库用户。当然，简单起见，我们通常以postgres这个用户来创建和初始化数据库。
当然，如果我们想快速的直接访问本地服务器上的数据库的话，可以直接一个psql命令即可：
[postgres@iZbp1anc2b2vggfj0i0oovZ ~]$ psql psql (13.2) Type "help" for help. postgres=#
2 关于psql工具的更多帮助
[postgres@iZbp1anc2b2vggfj0i0oovZ ~]$ psql --help
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "postgres")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
...
...
For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
[postgres@iZbp1anc2b2vggfj0i0oovZ ~]$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
[postgres@iZbp1anc2b2vggfj0i0oovZ ~]$ 我们在shell命令行上直接执行psql –help，获取更多完整的帮助说明，这里不一一介绍。一个简单的例子，psql -l，list当前数据库集群里的所有数据库的信息。
3 psql中执行SQL语句
当我们以psql连接到PostgreSQL数据库服务器上，我们就可以在交互式环境下，执行所有我们想要执行的SQL语句，比如:
Data Definition Language(create|drop|truncate)；
Data Control Language(grant|revoke)；
Data Manipulate Language(insert|update|delete)；
Transaction Control Language(commit|rollback|savepoint)。
postgres=# create table emp(id int,name varchar); CREATE TABLE postgres=# insert into emp values(1,'huangwei'); INSERT 0 1 postgres=# select * from emp; id | name ----+---------- 1 | huangwei (1 row) postgres=#
需要注意的是，psql命令行工具默认对DML语句是自动提交事务的。也就是说，默认情况下，是开启事务并自动提交的。如果我们在执行SQL语句时，想要手工控制事务的话，我们可以通过begin;来显示开启事务，然后执行SQL语句，通过end;或者commit;来提交事务，想回滚事务的话，则通过rollback;。注意，这里的命令后面的英文分号是必需的。自动提交事务跟Oracle数据库的SQL*PLUS命令行工具还是有明显差别的，熟悉Oracle的朋友，可能需要稍微注意一下。
执行外部SQL脚本的命令：
postgres=# \i ext.sql
current_timestamp
-------------------------------
2021-03-29 15:56:05.878625+08
(1 row)
postgres=# \! cat ext.sql
select current_timestamp;
postgres=# - 其中的\i是表示执行外部命令，\!表示的是在psql命令行上临时执行shell命令；
- Oracle数据库使用的是@跟sql脚本路径名，MySQL数据库使用的是”source 外部sql脚本路径名”。
SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 29 16:03:30 2021 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @ext SYSDATE ------------------- 2021/03/29 16:03:34 SQL> ! cat ext.sql select sysdate from dual; SQL>
4 交互式SQL窗口中获取更多帮助
在psql交互式SQL窗口中，可以通过执行\h或者\help来获取完整的帮助信息。也可以执行类似\h create来查看更多关于create使用的帮助命令和解释说明。
postgres=# \h
Available help:
ABORT ALTER TEXT SEARCH TEMPLATE CREATE PUBLICATION DROP FUNCTION IMPORT FOREIGN SCHEMA
ALTER AGGREGATE ALTER TRIGGER CREATE ROLE
...
...
ALTER TEXT SEARCH CONFIGURATION CREATE OPERATOR FAMILY DROP EXTENSION EXPLAIN VALUES
ALTER TEXT SEARCH DICTIONARY CREATE POLICY DROP FOREIGN DATA WRAPPER FETCH WITH
ALTER TEXT SEARCH PARSER CREATE PROCEDURE DROP FOREIGN TABLE GRANT
postgres=# \h create database
Command: CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LOCALE [=] locale ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ] ]
URL: https://www.postgresql.org/docs/13/sql-createdatabase.html
postgres=# 二 数据库常用维护管理命令
1 查看数据库版本号：
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
(1 row)
postgres=# 2 查看所有数据库信息：
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7613 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7449 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7449 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(3 rows)
postgres=# 3 查看数据库启动时间信息：
postgres=# select pg_postmaster_start_time(); pg_postmaster_start_time ------------------------------- 2021-03-29 14:29:53.595057+08 (1 row) postgres=#
4 查看用户信息：
postgres=# \du
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
es_migrate | | {}
pg13 | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# 5 显示所有的表：
postgres=# \c es_migrate es_migrate
You are now connected to database "es_migrate" as user "es_migrate".
es_migrate=> \d
List of relations
Schema | Name | Type | Owner
------------+----------------------------------------------+----------+------------
es_migrate | bak_bill_status_baiwei | table | es_migrate
es_migrate | batch_job_execution | table | es_migrate
es_migrate | batch_job_execution_context | table | es_migrate
es_migrate | batch_job_execution_params | table | es_migrate
es_migrate | batch_job_execution_seq | sequence | es_migrate
...6 查看表大小：
postgres=# \dt+ emp
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+------+-------+----------+-------------+-------+-------------
public | emp | table | postgres | permanent | 16 kB |
(1 row)
postgres=#
7 查看表结构：
postgres=# \d emp
Table "public.emp"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | |
name | character varying | | |
postgres=#
8 查看索引大小：
postgres=# create index idx_id_emp on emp(id);
CREATE INDEX
postgres=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+------------+-------+----------+-------
public | idx_id_emp | index | postgres | emp
(1 row)
postgres=# \di+ idx_id_emp
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+------------+-------+----------+-------+-------------+-------+-------------
public | idx_id_emp | index | postgres | emp | permanent | 16 kB |
(1 row)
postgres=#
9 创建新用户：
postgres=# create user t_user login password 't_user';
CREATE ROLE
postgres=#
创建1个新用户t_user，具有login访问数据库的权限，密码跟用户名相同。执行该命令的用户，必须得有create user的权限。
10 创建和使用数据库：
postgres=# create database testdb owner t_user;
CREATE DATABASE
postgres=# \c testdb t_user
You are now connected to database "testdb" as user "t_user".
testdb=> \c
You are now connected to database "testdb" as user "t_user".
testdb=> create table test_table(id int);
CREATE TABLE
testdb=>
创建名为testdb的数据库，其owner是上面创建的用户t_user。然后，以t_user来访问testdb数据库，并且创建了一张表。执行该命令的用户，必须得有create database的权限。
11 查看视图、函数、表空间
分别是\dv,\df,\db
es_migrate=> \dv
List of relations
Schema | Name | Type | Owner
------------+--------------------+------+------------
es_migrate | t_es_bdm_area_view | view | es_migrate
es_migrate | v_locks_monitor | view | es_migrate
(2 rows)
es_migrate=> \df
List of functions
Schema | Name | Result data type | Argument data types
| Type
------------+-----------------------+-------------------+--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------+------
es_migrate | func_insert_oa_import | void |
| func
es_migrate | func_voucher_import | void | sdate_src character varying, edate_src character varying, ssdate_src character varying, reimb_no_src character vary
ing, invoice_no_src character varying, bill_no_src character varying, state_src character varying | func
es_migrate | gaoxiao_delete_all | void | corpid character, newcorpid character, pflag character
| func
es_migrate | uuid | character varying |
| func
(4 rows)
es_migrate=> \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | pg13 |
pg_global | pg13 |
(2 rows)
es_migrate=>
三 小结
两个重要的帮助查看命令的方式。
- 操作系统上的shell命令行：psql –help
- psql命令行上的\h掌握这2个快速查看帮助的方式，再查看官方文档，便可以快速上手PostgreSQL数据库。
一条评论
d
老哥，写的真好。学习了