psql工具使用及数据库常用维护管理

一 psql工具的使用
1 用psql连接数据库
1
[postgres@iZbp1anc2b2vggfj0i0oovZ ~]$ which psql
2
/data/postgres/13.2/bin/psql
3
[postgres@iZbp1anc2b2vggfj0i0oovZ ~]$ psql -h localhost -p 5432 -d postgres -U postgres
4
psql (13.2)
5
Type “help” for help.
6
•
7
postgres=#
其中，

psql是PostgreSQL软件安装家目录下的bin路径下的可执行程序;
-h选项表示host，要连接数据库服务器名或者IP地址；如果要访问的数据库在远端，不在本地服务器上，则这里应该用那台机器的IP地址；如果是云服务器的话，则用云服务商提供的域名字符串即可；
-p选项表示port，数据库运行在哪个端口上，默认是5432，这个可以在postgres.conf配置文件里修改，但是需要restart数据库才生效；
-d选项表示database，我们要连接访问的数据库名；
-U选项表示username，我们以哪个用户来访问数据库。
因此，上述命令表示的是以postgres用户连接监听运行在本地机器上的5432的名为postgres的数据库。命令行上，并没有要求输入数据库密码，为什么？因为我们的pg_hba.conf文件里配置了

1
# TYPE DATABASE USER ADDRESS METHOD
2
•
3
# “local” is for Unix domain socket connections only
4
local all all trust
5
# IPv4 local connections:
6
host all all 127.0.0.1/32 trust
这个，在前面的那篇源码安装数据库里讲过。

另外，当前操作系统上有个postgres用户，当我们用这个用户安装数据库软件、初始化数据库的时候，默认在数据库里也创建了一个同名的数据库用户。比如，另外一套数据库环境：

1
[pg13@centos-master ~]$ id
2
uid=1108(pg13) gid=1107(pg13) 组=1107(pg13)
3
[pg13@centos-master ~]$ psql -d postgres -U pg13 -p 5413
4
psql (13.1)
5
Type “help” for help.
6
•
7
postgres=# \du
8
List of roles
9
Role name | Attributes | Member of
10
————+————————————————————+———–
11
es_migrate | | {}
12
pg13 | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
13
•
14
postgres=# select usename from pg_user;
15
usename
16
————
17
pg13
18
es_migrate
19
(2 rows)
20
•
21
postgres=#
OS上有个名为pg13的用户，在我们以该用户安装、创建数据库之后，数据库内部会自动创建一个同名用户pg13，且该用户是数据库的超级管理员。也就是说，当我们以操作系统上的哪个用户来安装和创建数据库，默认会在数据库内部创建一个同名的数据库用户。当然，简单起见，我们通常以postgres这个用户来创建和初始化数据库。

当然，如果我们想快速的直接访问本地服务器上的数据库的话，可以直接一个psql命令即可：

1
[postgres@iZbp1anc2b2vggfj0i0oovZ ~]$ psql
2
psql (13.2)
3
Type “help” for help.
4
•
5
postgres=#
2 关于psql工具的更多帮助
1
[postgres@iZbp1anc2b2vggfj0i0oovZ ~]$ psql –help
2
psql is the PostgreSQL interactive terminal.
3
•
4
Usage:
5
psql [OPTION]… [DBNAME [USERNAME]]
6
•
7
General options:
8
-c, –command=COMMAND run only single command (SQL or internal) and exit
9
-d, –dbname=DBNAME database name to connect to (default: “postgres”)
10
-f, –file=FILENAME execute commands from file, then exit
11
-l, –list list available databases, then exit
12
…
13
…
14
For more information, type “\?” (for internal commands) or “\help” (for SQL
15
commands) from within psql, or consult the psql section in the PostgreSQL
16
documentation.
17
•
18
Report bugs to <pgsql-bugs@lists.postgresql.org>.
19
PostgreSQL home page: <https://www.postgresql.org/>
20
[postgres@iZbp1anc2b2vggfj0i0oovZ ~]$ psql -l
21
List of databases
22
Name | Owner | Encoding | Collate | Ctype | Access privileges
23
———–+———-+———-+————-+————-+———————–
24
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
25
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
26
| | | | | postgres=CTc/postgres
27
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
28
| | | | | postgres=CTc/postgres
29
(3 rows)
30
•
31
[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)。

1
postgres=# create table emp(id int,name varchar);
2
CREATE TABLE
3
postgres=# insert into emp values(1,’huangwei’);
4
INSERT 0 1
5
postgres=# select * from emp;
6
id | name
7
—-+———-
8
1 | huangwei
9
(1 row)
10
•
11
postgres=#
需要注意的是，psql命令行工具默认对DML语句是自动提交事务的。也就是说，默认情况下，是开启事务并自动提交的。如果我们在执行SQL语句时，想要手工控制事务的话，我们可以通过begin;来显示开启事务，然后执行SQL语句，通过end;或者commit;来提交事务，想回滚事务的话，则通过rollback;。注意，这里的命令后面的英文分号是必需的。自动提交事务跟Oracle数据库的SQL*PLUS命令行工具还是有明显差别的，熟悉Oracle的朋友，可能需要稍微注意一下。

执行外部SQL脚本的命令：

1
postgres=# \i ext.sql
2
current_timestamp
3
——————————-
4
2021-03-29 15:56:05.878625+08
5
(1 row)
6
•
7
postgres=# \! cat ext.sql
8
select current_timestamp;
9
postgres=#
其中的\i是表示执行外部命令，\!表示的是在psql命令行上临时执行shell命令；
Oracle数据库使用的是@跟sql脚本路径名，MySQL数据库使用的是”source 外部sql脚本路径名”。
1
SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 29 16:03:30 2021
2
•
3
Copyright (c) 1982, 2009, Oracle. All rights reserved.
4
•
5
•
6
Connected to:
7
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
8
With the Partitioning, OLAP, Data Mining and Real Application Testing options
9
•
10
SQL> @ext
11
•
12
SYSDATE
13
——————-
14
2021/03/29 16:03:34
15
•
16
SQL> ! cat ext.sql
17
select sysdate from dual;
18
•
19
SQL>
4 交互式SQL窗口中获取更多帮助
在psql交互式SQL窗口中，可以通过执行\h或者\help来获取完整的帮助信息。也可以执行类似\h create来查看更多关于create使用的帮助命令和解释说明。

1
postgres=# \h
2
Available help:
3
ABORT ALTER TEXT SEARCH TEMPLATE CREATE PUBLICATION DROP FUNCTION IMPORT FOREIGN SCHEMA
4
ALTER AGGREGATE ALTER TRIGGER CREATE ROLE
5
…
6
…
7
ALTER TEXT SEARCH CONFIGURATION CREATE OPERATOR FAMILY DROP EXTENSION EXPLAIN VALUES
8
ALTER TEXT SEARCH DICTIONARY CREATE POLICY DROP FOREIGN DATA WRAPPER FETCH WITH
9
ALTER TEXT SEARCH PARSER CREATE PROCEDURE DROP FOREIGN TABLE GRANT
10
postgres=# \h create database
11
Command: CREATE DATABASE
12
Description: create a new database
13
Syntax:
14
CREATE DATABASE name
15
[ [ WITH ] [ OWNER [=] user_name ]
16
[ TEMPLATE [=] template ]
17
[ ENCODING [=] encoding ]
18
[ LOCALE [=] locale ]
19
[ LC_COLLATE [=] lc_collate ]
20
[ LC_CTYPE [=] lc_ctype ]
21
[ TABLESPACE [=] tablespace_name ]
22
[ ALLOW_CONNECTIONS [=] allowconn ]
23
[ CONNECTION LIMIT [=] connlimit ]
24
[ IS_TEMPLATE [=] istemplate ] ]
25
•
26
URL: https://www.postgresql.org/docs/13/sql-createdatabase.html
27
•
28
postgres=#
二数据库常用维护管理命令
1 查看数据库版本号：
1
postgres=# select version();
2
version
3
——————————————————————————————————–
4
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
5
(1 row)
6
•
7
postgres=#
2 查看所有数据库信息：
1
postgres=# \l+
2
List of databases
3
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
4
———–+———-+———-+————-+————-+———————–+———+————+——————————————–
5
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7613 kB | pg_default | default administrative connection database
6
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7449 kB | pg_default | unmodifiable empty database
7
| | | | | postgres=CTc/postgres | | |
8
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7449 kB | pg_default | default template for new databases
9
| | | | | postgres=CTc/postgres | | |
10
(3 rows)
11
•
12
postgres=#
3 查看数据库启动时间信息：
1
postgres=# select pg_postmaster_start_time();
2
pg_postmaster_start_time
3
——————————-
4
2021-03-29 14:29:53.595057+08
5
(1 row)
6
•
7
postgres=#
4 查看用户信息：
1
postgres=# \du
2
List of roles
3
Role name | Attributes | Member of
4
————+————————————————————+———–
5
es_migrate | | {}
6
pg13 | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
7
•
8
postgres=#
5 显示所有的表：
1
postgres=# \c es_migrate es_migrate
2
You are now connected to database “es_migrate” as user “es_migrate”.
3
es_migrate=> \d
4
List of relations
5
Schema | Name | Type | Owner
6
————+———————————————-+———-+————
7
es_migrate | bak_bill_status_baiwei | table | es_migrate
8
es_migrate | batch_job_execution | table | es_migrate
9
es_migrate | batch_job_execution_context | table | es_migrate
10
es_migrate | batch_job_execution_params | table | es_migrate
11
es_migrate | batch_job_execution_seq | sequence | es_migrate
12
…
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

操作系统上的shell命令行：psql –help
psql命令行上的\h掌握这2个快速查看帮助的方式，再查看官方文档，便可以快速上手PostgreSQL数据库。

psql工具使用及数据库常用维护管理 – 数据库敲门人 (knockatdatabase.com)

原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/237401.html

psql工具使用及数据库常用维护管理

相关推荐

发表回复