Linux,  PostgreSQL

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

零 说明

在前面,我们学会了如何手工编译PostgreSQL源代码的方式在Linux服务器安装数据库,也学会了如何正确的手工启停PostgreSQL数据库。

接下来,我们来看看一个交互式的PostgreSQL数据库管理工具的使用,psql。

一 psql工具的使用

1 用psql连接数据库

其中,

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

因此,上述命令表示的是以postgres用户连接监听运行在本地机器上的5432的名为postgres的数据库。命令行上,并没有要求输入数据库密码,为什么?因为我们的pg_hba.conf文件里配置了

这个,在前面的那篇源码安装数据库里讲过。

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

OS上有个名为pg13的用户,在我们以该用户安装、创建数据库之后,数据库内部会自动创建一个同名用户pg13,且该用户是数据库的超级管理员。也就是说,当我们以操作系统上的哪个用户来安装和创建数据库,默认会在数据库内部创建一个同名的数据库用户。当然,简单起见,我们通常以postgres这个用户来创建和初始化数据库。

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

2 关于psql工具的更多帮助

我们在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)。

需要注意的是,psql命令行工具默认对DML语句是自动提交事务的。也就是说,默认情况下,是开启事务并自动提交的。如果我们在执行SQL语句时,想要手工控制事务的话,我们可以通过begin;来显示开启事务,然后执行SQL语句,通过end;或者commit;来提交事务,想回滚事务的话,则通过rollback;。注意,这里的命令后面的英文分号是必需的。自动提交事务跟Oracle数据库的SQL*PLUS命令行工具还是有明显差别的,熟悉Oracle的朋友,可能需要稍微注意一下。

执行外部SQL脚本的命令:

  • 其中的\i是表示执行外部命令,\!表示的是在psql命令行上临时执行shell命令;
  • Oracle数据库使用的是@跟sql脚本路径名,MySQL数据库使用的是”source 外部sql脚本路径名”。
4 交互式SQL窗口中获取更多帮助

在psql交互式SQL窗口中,可以通过执行\h或者\help来获取完整的帮助信息。也可以执行类似\h create来查看更多关于create使用的帮助命令和解释说明。

二 数据库常用维护管理命令

1 查看数据库版本号:
2 查看所有数据库信息:
3 查看数据库启动时间信息:
4 查看用户信息:
5 显示所有的表:
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数据库。

一条评论

留言