PostgreSQL教程

PostgreSQL常用命令(持续更新)

本文主要是介绍PostgreSQL常用命令(持续更新),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

PostgreSQL 10.18 on Windows

--

 

PostgreSQL\10\bin 下可执行exe文件(部分):

psql.exe

createdb.exe

dropdb.exe

initdb.exe

createuser.exe

dropuser.exe

 

本文主要展示 psql的使用,以及进入PostgreSQL控制台的命令使用。

 

超级用户(角色):postgres

注,下文如未特别说明,都是使用此用户进行操作。

 

基本使用

使用psql连接DB服务器

帮助信息:

>psql --help

访问本机DB服务器:

>psql -U postgres
用户 postgres 的口令:
psql (10.18)
输入 "help" 来获取帮助信息.

postgres=#

 

帮助信息:help

postgres=# help
您正在使用psql, 这是一种用于访问PostgreSQL的命令行界面
键入: \copyright 显示发行条款
       \h 显示 SQL 命令的说明
       \? 显示 pgsql 命令的说明
       \g 或者以分号(;)结尾以执行查询
       \q 退出
postgres=#

postgres=# \copyright
PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group
......省略......

postgres=# \h
可用的说明:
  ABORT                            CREATE USER MAPPING
  ALTER AGGREGATE                  CREATE VIEW
  ALTER COLLATION                  DEALLOCATE
  ALTER CONVERSION                 DECLARE
......省略......

postgres=# \?
一般性
  \copyright            显示PostgreSQL的使用和发行许可条款
  \crosstabview [COLUMNS] 执行查询并且以交叉表显示结果
  \errverbose            以最冗长的形式显示最近的错误消息
  \g [文件] or;     执行查询 (并把结果写入文件或 |管道)
......省略......

 

\q 退出脚本。 

 

\h 显示 SQL脚本,\h + 命令,可以查看脚本语法:

postgres=# \h ABORT
命令:       ABORT
描述:       中止目前的事务
语法:
ABORT [ WORK | TRANSACTION ]


postgres=# \h SELECT
命令:       SELECT
描述:       从数据表或视图中读取数据
语法:
[ WITH [ RECURSIVE ] with查询语句(with_query) [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( 表达式 [, ...] ) ] ]
    [ * | 表达式 [ [ AS ] 输出名称 ] [, ...] ]
    [ FROM from列表中项 [, ...] ]
    [ WHERE 条件 ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING 条件 ]
......省略......

 

\? 分页显示 一些命令——非SQL脚本,下面将介绍更多这些命令。

 

说明,
出现“-- More  --”时,
按 Enter 看下一行,
按 空格 看下一页,
按 q 结束查看。
其中有很多后面要介绍的命令。

 

执行 \? 中查到的一些命令,目前主要用了查看 数据库的信息。

当前连接信息:

postgres=# \conninfo
以用户 "postgres" 的身份, 在主机"localhost", 端口"5432"连接到数据库 "postgres"

注,目前只有一个 postgres 数据库,后面再新建数据库后,再讲 切换。

查看数据库列表:\list \list+ \l \l+ (是 L 不是 一)

postgres=# \l
                                                        数据库列表
   名称    |  拥有者  | 字元编码 |            校对规则            |             Ctype              |       存取权限
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
 postgres  | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 template0 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
 template1 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
(3 行记录)


postgres=# \l+
                                                                                          数据库列表
   名称    |  拥有者  | 字元编码 |            校对规则            |             Ctype              |       存取权限        |  大小   |   表空间   |                    描述                  
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |                       | 7575 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +| 7441 kB | pg_default | unmodifiable empty database
           |          |          |                                |                                | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +| 7441 kB | pg_default | default template for new databases
           |          |          |                                |                                | postgres=CTc/postgres |         |            |
(3 行记录)

 

列出表、视图、序列:

选项说明:S = 显示系统对象, + = 其余的详细信息

postgres=# \d
Did not find any relations.

加了 S ,显示出了表、视图等信息
postgres=# \dS
                             关联列表
  架构模式  |              名称               |  类型  |  拥有者
------------+---------------------------------+--------+----------
 pg_catalog | pg_aggregate                    | 数据表 | postgres
 pg_catalog | pg_am                           | 数据表 | postgres
 pg_catalog | pg_amop                         | 数据表 | postgres
 pg_catalog | pg_amproc                       | 数据表 | postgres
 pg_catalog | pg_attrdef                      | 数据表 | postgres
 pg_catalog | pg_attribute                    | 数据表 | postgres
 pg_catalog | pg_auth_members                 | 数据表 | postgres
 pg_catalog | pg_authid                       | 数据表 | postgres
 pg_catalog | pg_available_extension_versions | 视图   | postgres
 pg_catalog | pg_available_extensions         | 视图   | postgres
 ......省略......
 
 再添加 加号(+),显示了更多信息,包括表存储空间
 postgres=# \dS+
                                       关联列表
  架构模式  |              名称               |  类型  |  拥有者  |    大小    | 描述
------------+---------------------------------+--------+----------+------------+------
 pg_catalog | pg_aggregate                    | 数据表 | postgres | 48 kB      |
 pg_catalog | pg_am                           | 数据表 | postgres | 40 kB      |
 pg_catalog | pg_amop                         | 数据表 | postgres | 80 kB      |
 pg_catalog | pg_amproc                       | 数据表 | postgres | 64 kB      |
 pg_catalog | pg_attrdef                      | 数据表 | postgres | 8192 bytes |
 pg_catalog | pg_attribute                    | 数据表 | postgres | 416 kB     |
 pg_catalog | pg_auth_members                 | 数据表 | postgres | 40 kB      |
 pg_catalog | pg_authid                       | 数据表 | postgres | 40 kB      |
 pg_catalog | pg_available_extension_versions | 视图   | postgres | 0 bytes    |
 pg_catalog | pg_available_extensions         | 视图   | postgres | 0 bytes    |
 ......省略......
 

 

\d[S+]  名称   :   描述表,视图,序列,或索引

postgres=# \dS pg_attribute
              数据表 "pg_catalog.pg_attribute"
     栏位      |   类型    | Collation | Nullable | Default
---------------+-----------+-----------+----------+---------
 attrelid      | oid       |           | not null |
 attname       | name      |           | not null |
 atttypid      | oid       |           | not null |
 attstattarget | integer   |           | not null |
 attlen        | smallint  |           | not null |
 attnum        | smallint  |           | not null |
 attndims      | integer   |           | not null |
 attcacheoff   | integer   |           | not null |
 atttypmod     | integer   |           | not null |
 attbyval      | boolean   |           | not null |
 attstorage    | "char"    |           | not null |
 attalign      | "char"    |           | not null |
 attnotnull    | boolean   |           | not null |
 atthasdef     | boolean   |           | not null |
 attidentity   | "char"    |           | not null |
 attisdropped  | boolean   |           | not null |
 attislocal    | boolean   |           | not null |
 attinhcount   | integer   |           | not null |
 attcollation  | oid       |           | not null |
 attacl        | aclitem[] |           |          |
 attoptions    | text[]    |           |          |
 attfdwoptions | text[]    |           |          |
索引:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)


postgres=#
postgres=#
postgres=#
postgres=# \dS pg_available_extensions
         视图 "pg_catalog.pg_available_extensions"
       栏位        | 类型 | Collation | Nullable | Default
-------------------+------+-----------+----------+---------
 name              | name |           |          |
 default_version   | text |           |          |
 installed_version | text |           |          |
 comment           | text |           |          |


postgres=#

 

添加  加号(+) 会显示更多:

postgres=# \dS+ pg_attribute
                            数据表 "pg_catalog.pg_attribute"
     栏位      |   类型    | Collation | Nullable | Default |   存储   | 统计目标 | 描述
---------------+-----------+-----------+----------+---------+----------+----------+------
 attrelid      | oid       |           | not null |         | plain    |          |
 attname       | name      |           | not null |         | plain    |          |
 atttypid      | oid       |           | not null |         | plain    |          |
 attstattarget | integer   |           | not null |         | plain    |          |
 attlen        | smallint  |           | not null |         | plain    |          |
 attnum        | smallint  |           | not null |         | plain    |          |
 attndims      | integer   |           | not null |         | plain    |          |
 attcacheoff   | integer   |           | not null |         | plain    |          |
 atttypmod     | integer   |           | not null |         | plain    |          |
 attbyval      | boolean   |           | not null |         | plain    |          |
 attstorage    | "char"    |           | not null |         | plain    |          |
 attalign      | "char"    |           | not null |         | plain    |          |
 attnotnull    | boolean   |           | not null |         | plain    |          |
 atthasdef     | boolean   |           | not null |         | plain    |          |
 attidentity   | "char"    |           | not null |         | plain    |          |
 attisdropped  | boolean   |           | not null |         | plain    |          |
 attislocal    | boolean   |           | not null |         | plain    |          |
 attinhcount   | integer   |           | not null |         | plain    |          |
 attcollation  | oid       |           | not null |         | plain    |          |
 attacl        | aclitem[] |           |          |         | extended |          |
 attoptions    | text[]    |           |          |         | extended |          |
 attfdwoptions | text[]    |           |          |         | extended |          |
索引:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)


postgres=#

 

查看PostgreSQL数据库连接及数量

postgres=# select * from pg_stat_activity;

这个表的信息很多,可以只 查询其中部分字段:

postgres=# select datid,datname,pid,usename,state,client_addr,query from pg_stat_activity;
 datid | datname  |  pid  | usename  | state  | client_addr |                                      query
-------+----------+-------+----------+--------+-------------+---------------------------------------------------------------------------------
       |          | 18904 |          |        |             |
       |          | 18556 | postgres |        |             |
 12938 | postgres | 16576 | postgres | active | ::1         | select datid,datname,pid,usename,state,client_addr,query from pg_stat_activity;
 16394 | lib1     | 13592 | postgres | idle   | 127.0.0.1   | SELECT id,name,age,email FROM guser WHERE id=$1
 16394 | lib1     | 11140 | postgres | idle   | 127.0.0.1   | SET application_name = 'PostgreSQL JDBC Driver'
 16394 | lib1     | 14976 | postgres | idle   | 127.0.0.1   | SET application_name = 'PostgreSQL JDBC Driver'
 16394 | lib1     |  9508 | postgres | idle   | 127.0.0.1   | SET application_name = 'PostgreSQL JDBC Driver'
 16394 | lib1     |  6728 | postgres | idle   | 127.0.0.1   | SET application_name = 'PostgreSQL JDBC Driver'
 16394 | lib1     | 12988 | postgres | idle   | 127.0.0.1   | SET application_name = 'PostgreSQL JDBC Driver'
 16394 | lib1     | 15288 | postgres | idle   | 127.0.0.1   | SET application_name = 'PostgreSQL JDBC Driver'
 16394 | lib1     | 12872 | postgres | idle   | 127.0.0.1   | SET application_name = 'PostgreSQL JDBC Driver'
 16394 | lib1     | 13108 | postgres | idle   | 127.0.0.1   | SET application_name = 'PostgreSQL JDBC Driver'
 16394 | lib1     | 19376 | postgres | idle   | 127.0.0.1   | SET application_name = 'PostgreSQL JDBC Driver'
       |          | 14144 |          |        |             |
       |          |  9304 |          |        |             |
       |          |   992 |          |        |             |
(16 行记录)

注意,其中有10个idle的连接,这个来自本机的一个Spring Boot程序。默认的数据库拦截池使用 HikariCP,十个连接。

 

更换为Druid数据库后:配置了更少的初始数据库连接(当然,HikariCP也可以配置),,这次只有8个了

lib2=# select datid,datname,pid,usename,state,client_addr,query from pg_stat_activity;
 datid | datname |  pid  | usename  | state  | client_addr |                                      query
-------+---------+-------+----------+--------+-------------+---------------------------------------------------------------------------------
       |         | 18904 |          |        |             |
       |         | 18556 | postgres |        |             |
 16402 | lib2    | 15936 | postgres | active | ::1         | select datid,datname,pid,usename,state,client_addr,query from pg_stat_activity;
 16394 | lib1    |  2940 | postgres | idle   | 127.0.0.1   | SELECT id,name,age,email FROM guser WHERE id=$1
 16402 | lib2    | 18808 | postgres | idle   | 127.0.0.1   | SELECT id,sn,name FROM device WHERE id=$1
       |         | 14144 |          |        |             |
       |         |  9304 |          |        |             |
       |         |   992 |          |        |             |
(8 行记录)

 

数据备份与恢复

方式1:导出sql文件,导入也是sql文件

文本文件导入导出使用 psql

# 导出数据库 lib1
D:\>pg_dump -U postgres -f lib1 lib1
口令:

# 将数据库lib1导出才 文本文件 导入数据库 lib3
D:\>psql -U postgres -d lib3 -f lib1
...内容会很多...

方式2:dump文件导出导入 -Fc

D:\>pg_dump -U postgres -Fc lib1 > lib1.dump
口令:

D:\>pg_restore -U postgres -d lib3 lib1.dump > a.txt
口令:

方式3:tar文件导出导入 -Ft

D:\>pg_dump -U postgres -Ft lib1 > lib1.tar
口令:

D:\>
D:\>pg_restore -U postgres -d lib3 lib1.tar > a.txt
口令:

 

注意,

1、方式1执行时,使用 版本10的 psql从 版本13 的 PostgreSQL服务器获取数据时,失败了;

后安装了 PostgreSQL 14,就可以从 低版本13 中导出数据了。

2、方式2、3中,导入时的 信息都被 重定向到了 a.txt 文件。

 

用户&角色管理

todo

 

 

 

参考文档

1、postgresql数据库的数据导出

2、postgresql 查看数据库连接数

3、

 

这篇关于PostgreSQL常用命令(持续更新)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!