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、