pgsql常用操作
查看帮助
#列出psql帮助用法
help
#列出以\开头的命令,即psql的命令
\?
#列出所有SQL命令的帮助，注意：SQL语句必须以 ; 结束
\h
#查看指定SQL的帮助
\h create database
\help create user
设置显示信息的格式
#后续查询将坚着显示,类似于MySQL中的\G
\x
#开启命令执行时长提示
\timing on
#显示详细的信息,可以打印出报出问题的源代码位置
\set VERBOSITY verbose
范例
一
postgres=# \x
Expanded display is on
postgres=# \l
List of databases
-[ RECORD 1 ]-----+----------------------
Name | db1
Owner | postgres
Encoding | UTF8
Collate | C.UTF-8
Ctype | C.UTF-8
Access privileges |
-[ RECORD 2 ]-----+----------------------
Name | postgres
Owner | postgres
Encoding | UTF8
Collate | C.UTF-8
Ctype | C.UTF-8
Access privileges |
-[ RECORD 3 ]-----+----------------------
Name | template0
Owner | postgres
Encoding | UTF8
Collate | C.UTF-8
Ctype | C.UTF-8
Access privileges | =c/postgres +
| postgres=CTc/postgres
-[ RECORD 4 ]-----+----------------------
postgres=# select pg_sleep(3);
pg_sleep
----------
(1 row)
二
testdb=# \timing on
Timing is on.
testdb=# select pg_sleep(3);
Time: 3003.928 ms (00:03.004)
testdb=# \timing off
Timing is off.
testdb=# select pg_sleep(3);
三：查看出错对应的源代码位置
[root@ubuntu2004 ~]#su - postgres
postgres@ubuntu2004:~$ psql
psql (12.9)
Type "help" for help.
postgres=# \set VERBOSITY verbose
postgres=# select zhao;
ERROR: 42703: column "zhao" does not exist
LINE 1: select zhao;
^
LOCATION: errorMissingColumn, parse_relation.c:3349
#说明:错误对应的是parse_relation.c文件中的3349行中errorMissingColumn函数
postgres=#
[root@ubuntu2004 ~]#find -name parse_relation.c
./postgresql-12.9/src/backend/parser/parse_relation.c
[root@ubuntu2004 ~]#vim +3349 `find -name parse_relation.c`
errorMissingColumn(ParseState *pstate,
const char *relname, const char *colname, int location)
{
FuzzyAttrMatchState *state;
char *closestfirst = NULL;
/*
* Search the entire rtable looking for possible matches. If we find one,
* emit a hint about it.
*
* TODO: improve this code (and also errorMissingRTE) to mention using
* LATERAL if appropriate.
*/
state = searchRangeTableForCol(pstate, relname, colname, location);
/*
* Extract closest col string for best match, if any.
*
* Infer an exact match referenced despite not being visible from the fact
* that an attribute number was not present in state passed back -- this
* is what is reported when !closestfirst. There might also be an exact
* match that was qualified with an incorrect alias, in which case
* closestfirst will be set (so hint is the same as generic fuzzy case).
*/
if (state->rfirst && AttributeNumberIsValid(state->first))
closestfirst = strVal(list_nth(state->rfirst->eref->colnames,
state->first - 1));
if (!state->rsecond)
{
/*
数据库创建和删除
创建数据库可以使用SQL语句create database 实现,也可以利用createdb 命令创建数据库
createdb 是一个 SQL 命令 CREATE DATABASE 的封装。
createdb 命令语法格式如下：
createdb [option...] [dbname [description]]
参数说明：
options：参数可选项，可以是以下值：
-D tablespace指定数据库默认表空间。
-e 将createdb 生成的命令发送到服务端。
-E encoding指定数据库的编码。
-l locale指定数据库的语言环境。
-T template指定创建此数据库的模板。
--help显示 createdb 命令的帮助信息。
-h host指定服务器的主机名。
-p port指定服务器监听的端口，或者 socket 文件。
-U username连接数据库的用户名。
-w忽略输入密码。
-W连接时强制要求输入密码
dbname：要创建的数据库名。
description：关于新创建的数据库相关的说明。
删除数据库可以使用SQL语句drop database实现
范例
创建数据库
#方法1
[root@ubuntu2004 ~]#createdb -h 10.0.0.200 -p 5432 -U postgres testdb
Password:
#方法2
postgres@ubuntu2004:~$ psql
postgres=# create database testdb;
删除数据库
postgres@ubuntu2004:~$ psql
postgres=# drop database testdb;
查看数据库存放目录的路径
postgres=# select oid,datname from pg_database;
oid | datname
-------+-----------
12673 | postgres
16384 | hellodb
1 | template1
12672 | template0
16408 | testdb
(5 rows)
[root@ubuntu2004 ~]#ls /pgsql/data/base/
1 12672 12673 16384 16408
管理和查看模式
一个数据库包含一个或多个已命名的模式，模式又包含表。模式还可以包含其它对象，包括数据类型、函数、操作符等。同一个对象名可以在不同的模式里使用而不会导致冲突；比如，schema1和schema2都可以包含一个名为test的表
#创建模式
create schema schema_name;
#删除模式
drop schema schema_name;
#列出所有schema
postgres=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
db1=# create table m48_sch.t1(id int);
CREATE TABLE
db1=# create table m47_sch.t1(id int);
CREATE TABLE
db1=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
public | t2 | table | postgres
(2 rows)
db1=# \dt m48_sch.t1
List of relations
Schema | Name | Type | Owner
---------+------+-------+----------
m48_sch | t1 | table | postgres
(1 row)
db1=# \dt m48_sch.*
List of relations
Schema | Name | Type | Owner
---------+------+-------+----------
m48_sch | t1 | table | postgres
(1 row)
db1=# \dt m47_sch.*
List of relations
Schema | Name | Type | Owner
---------+------+-------+----------
m47_sch | t1 | table | postgres
(1 row)
查看和连接数据库
#列出所有数据库名,相当于MySQL中的show databases;
postgres=# \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)
#显示数据库详细信息,比如大小
testdb-# \l+
List of
databases
Name | Owner | Encoding | Collate | Ctype | Access privileges |
Size | Tablespace | Description
-----------+----------+----------+---------+---------+-----------------------+--
-------+------------+---------------------------------
-----------
hellodb | postgres | UTF8 | C.UTF-8 | C.UTF-8 | |
406 MB | pg_default |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | |
7641 kB | pg_default | default administrative connectio
n database
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +|
7497 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres |
| |
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +|
7497 kB | pg_default | default template for new databas
es
| | | | | postgres=CTc/postgres |
| |
testdb | postgres | UTF8 | C.UTF-8 | C.UTF-8 | |
102 MB | pg_default |
(5 rows)
#查看当前连接信息
postgres=# \c
You are now connected to database "postgres" as user "postgres".
#查看当前连接详细信息
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp"
at port "5432".
#连接数据库,相当于use
postgres=# \c hellodb
You are now connected to database "hellodb" as user "postgres".
hellodb=#
管理表
PostgreSQL 支持多种数据类型实现表结构的创建
范例
查看支持数据类型
postgres=# select typname from pg_type;
typname
---------------------------------------
bool
bytea
char
name
int8
int2
int2vector
int4
regproc
text
oid
tid
xid
cid
oidvector
pg_type
pg_attribute
pg_proc
pg_class
json
xml
管理表
postgres=# \c testdb
testdb=# create table tb1 (id serial primary key,name text);
CREATE TABLE
Time: 2.661 ms
testdb=# insert into tb1 (name) select (md5(random()::text)) from
generate_series (2,10);
INSERT 0 9
Time: 0.816 ms
testdb=# select * from tb1;
id | name
----+----------------------------------
1 | b47df9d3454abe38b2e72561e3672aa1
2 | c5fd0088c707b22b838c292348db14aa
3 | 060e504c872a696acbb8001c99aa48ca
4 | 752d0510b0e8bc2d3b437e19b14ad97f
5 | f81883d4948f7103103b3d3f996727bf
6 | 089ff4438b34abfe51c799d6e8907418
7 | a37eece887341cea9401ae1e1c9d81b1
8 | d1f297d969f5fa496c33d9d92cb42ede
9 | 93aa102cccf4d6a275ff53fd7e3cc1bc
(9 rows)
Time: 0.276 ms
#PostgreSQL中插入100万条记录只需要花2s
testdb=# \timing on
Timing is on.
testdb=# insert into tb1 (name) select (md5(random()::text)) from
generate_series (1,1000000);
INSERT 0 1000000
Time: 2111.662 ms (00:02.112)
#复制表结构，不复制数据
testdb=# create table tb2 ( like tb1 );
CREATE TABLE
testdb=# \d tb2
Table "public.tb2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
name | text |
testdb=# select * from tb2;
id | name
----+------
(0 rows)
testdb=# drop table tb2;
查看表和表信息
#列出所有表,视图,序列
\d
#列出public的schema中所有的表名,相当于show tables;
\dt
#查看t1的表信息
\dt t1
#支持通配符*和？，以下显示所有t开头的表
\dt t*
#列出myschema模式的表结构
\dt myschema.*
#查看t1的表结构,相当于desc
\d t1
#列出所有表信息,包括大小
hellodb-# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------+-------+----------+------------+-------------
public | boy | table | postgres | 398 MB |
public | classes | table | postgres | 8192 bytes |
public | coc | table | postgres | 8192 bytes |
public | courses | table | postgres | 8192 bytes |
public | scores | table | postgres | 8192 bytes |
public | students | table | postgres | 8192 bytes |
public | teachers | table | postgres | 8192 bytes |
public | toc | table | postgres | 0 bytes |
(8 rows)
#列出表信息
hellodb=# \dt students
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | students | table | postgres
(1 row)
#列出表信息的大小信息
hellodb=# \dt+ students
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------+-------+----------+------------+-------------
public | students | table | postgres | 8192 bytes |
(1 row)
#查看所有表
postgres=# select * from pg_tables;
schemaname | tablename | tableowner | tablespace |
hasindexes | hasrules | hastriggers | rowsecurity
--------------------+-------------------------+------------+------------+-------
-----+----------+-------------+-------------
public | test1 | postgres | | f
| f | f | f
pg_catalog | pg_statistic | postgres | | t
| f | f | f
pg_catalog | pg_type | postgres | | t
| f | f | f
pg_catalog | pg_foreign_server | postgres | | t
| f | f | f
#查看表大小
testdb=# select pg_total_relation_size('tb1');
-[ RECORD 1 ]----------+--------
pg_total_relation_size | 9953280
testdb=# select pg_total_relation_size('tb1')/1024/1024||'MB';
-[ RECORD 1 ]-
?column? | 9MB
查看表对应的文件路径
db1=# select oid,datname from pg_database where datname = 'db1';
oid | datname
-------+---------
16385 | db1
(1 row)
db1=# select relid from pg_stat_all_tables where relname='tb2';
relid
-------
16413
(1 row)
[root@ubuntu2004 ~]#ll /pgsql/data/base/16385/16413
-rw------- 1 postgres postgres 0 Feb 14 07:21 /pgsql/data/base/16385/16413
查看当前库中的所有表的统计信息
postgres=# select * from pg_stat_all_tables;
-[ RECORD 1 ]-------+------------------------
relid | 3592
schemaname | pg_catalog
relname | pg_shseclabel
seq_scan | 0
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 0
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
-[ RECORD 2 ]-------+------------------------
relid | 3603
schemaname | pg_catalog
......
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
#查看指定表t1的信息
testdb=# select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+-------
relid | 16385
schemaname | public
relname | t1
seq_scan | 0
seq_tup_read | 0
idx_scan |
idx_tup_fetch |
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 0
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
系统表（system catalogs）
官方文档：PostgreSQL: Documentation: 16: Chapter 53. System Catalogs
系统表的定义
系统表也称为系统目录(system catalogs),是关系型数据库存放模式元数据的地方，比如表和列的信息，以及内部统计信息等。PostgreSQL的系统表也就是普通表。虽然可以删除并重建这些表、增加列、插入和更新数值， 但会导致系统损坏。通常情况下，不应该手工修改系统目录，通过相关SQL命令去实现。例如: 当执行CREATE DATABASE 时会向系统表pg_database中插入一行记录 ,并且实际上在磁盘上创建该数据库。
系统表包括存放系统信息的普通表或者视图,系统视图建立在系统表之上
系统表的创建
pg的每一个数据库中都有一套自己的系统表，其中大多数系统表都是在数据库创建时从模板数据库中拷贝过来的
系统表的维护
系统表中的信息由相的SQL命令关联至系统表自动维护
系统表的存储方式
和数据库相关的系统表保存在$PGDATA/base目录下相应数据库的文件夹下,文件夹命名为pg_database里记录的数据库oid( Object identifiers) ,系统表都有一个列名为对象标识符oid,用于标识postgres里各个对象,如表、序列、索引等,以前版本是隐藏的
所有数据库共享的系统表，如pg_database，保存在$PGDATA/global下
范例
查看系统表
#查看系统表
postgres=# \dS
List of relations
Schema | Name | Type | Owner
------------+---------------------------------+-------+----------
pg_catalog | pg_aggregate | table | postgres
pg_catalog | pg_am | table | postgres
pg_catalog | pg_amop | table | postgres
pg_catalog | pg_amproc | table | postgres
.....
postgres=# \dS+
List of relations
Schema | Name | Type | Owner | Size |
Description
------------+---------------------------------+-------+----------+------------+-
------------
pg_catalog | pg_aggregate | table | postgres | 56 kB |
pg_catalog | pg_am | table | postgres | 40 kB |
pg_catalog | pg_amop | table | postgres | 80 kB |
pg_catalog | pg_amproc | table | postgres | 56 kB |
.....
#列出所有pg开头的系统表
postgres=# \dt pg_*
List of relations
Schema | Name | Type | Owner
------------+-------------------------+-------+----------
pg_catalog | pg_aggregate | table | postgres
pg_catalog | pg_am | table | postgres
pg_catalog | pg_amop | table | postgres
pg_catalog | pg_amproc | table | postgres
pg_catalog | pg_attrdef | table | postgres
#列出所有pg开头的系统视图
postgres=# \dv pg_*
List of relations
Schema | Name | Type | Owner
------------+---------------------------------+------+----------
pg_catalog | pg_available_extension_versions | view | postgres
pg_catalog | pg_available_extensions | view | postgres
pg_catalog | pg_config | view | postgres
pg_catalog | pg_cursors | view | postgres
pg_catalog | pg_file_settings | view | postgres
#查看系统表pg_database的结构
postgres=# \d pg_database
Table "pg_catalog.pg_database"
Column | Type | Collation | Nullable | Default
---------------+-----------+-----------+----------+---------
oid | oid | | not null |
datname | name | | not null |
datdba | oid | | not null |
encoding | integer | | not null |
datcollate | name | | not null |
datctype | name | | not null |
datistemplate | boolean | | not null |
datallowconn | boolean | | not null |
datconnlimit | integer | | not null |
datlastsysoid | oid | | not null |
datfrozenxid | xid | | not null |
datminmxid | xid | | not null |
dattablespace | oid | | not null |
datacl | aclitem[] | | |
Indexes:
"pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
"pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"
postgres=# \d pg_tables;
View "pg_catalog.pg_tables"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
schemaname | name | | |
tablename | name | | |
tableowner | name | | |
tablespace | name | | |
hasindexes | boolean | | |
hasrules | boolean | | |
hastriggers | boolean | | |
rowsecurity | boolean | | |
查看指定表对应的文件
testdb=# select * from pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16408/16409
(1 row)
[root@ubuntu2004 ~]#ll /pgsql/data/base/16408/16409
-rw------- 1 postgres postgres 76562432 Jan 16 03:44
/pgsql/data/base/16408/16409
表的CRUD
SQL的CRUD，即 Insert,update,delete,select 四条语句范例:
testdb=# create table tb1 (id serial,name varchar(10));
CREATE TABLE
testdb=# \d tb1;
Table "public.tb1"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+------------------------
---------
id | integer | | not null |
nextval('tb1_id_seq'::regclass)
name | character varying(10) | | |
testdb=# insert into tb1 (name)values('zhao');
INSERT 0 1
testdb=# insert into tb1 (name)values('li');
INSERT 0 1
testdb=# select * from tb1;
id | name
----+-------
1 | zhao
2 | li
(2 rows)
testdb=# update tb1 set name='zhao' where id=2;
UPDATE 1
testdb=# select * from tb1;
id | name
----+------
1 | zhao
2 | zhao
(2 rows)
testdb=# delete from tb1 where id=2;
DELETE 1
testdb=# select * from tb1;
id | name
----+------
1 | zhao
(1 row)
#清空表
testdb=# truncate tb1;
testdb=# truncate table tb1;
TRUNCATE TABLE
testdb=# select * from tb1;
id | name
----+------
(0 rows)
范例
查看表的列信息及大小
hellodb=# \d students;
Table "public.students"
Column | Type | Collation | Nullable | Default
-----------+-----------------------+-----------+----------+---------
stuid | integer | | not null |
name | character varying(50) | | not null |
age | smallint | | not null |
gender | character(1) | | not null |
classid | smallint | | |
teacherid | integer | | |
Indexes:
"students_pkey" PRIMARY KEY, btree (stuid)
hellodb=# select pg_column_size(name),name from students;
pg_column_size | name
----------------+---------------
12 | Shi Zhongyu
11 | Shi Potian
10 | Xie Yanke
10 | Ding Dian
10 | Yu Yutong
9 | Shi Qing
7 | Xi Ren
10 | Lin Daiyu
13 | Ren Yingying
13 | Yue Lingshan
14 | Yuan Chengzhi
13 | Wen Qingqing
13 | Tian Boguang
12 | Lu Wushuang
8 | Duan Yu
7 | Xu Zhu
10 | Lin Chong
9 | Hua Rong
12 | Xue Baochai
10 | Diao Chan
14 | Huang Yueying
10 | Xiao Qiao
8 | Ma Chao
8 | Xu Xian
12 | Sun Dasheng
(25 rows)
索引管理
范例
创建和删除索引
testdb=# create table tb1(id int,info text,crt_time timestamp);
CREATE TABLE
testdb=# insert into tb1 select
generate_series(1,100000),md5(random()::text),clock_timestamp();
INSERT 0 100000
testdb=# select * from tb1 limit 3;
1 | 4d801e211aca0b2787ecbd489eb91460 | 2020-03-18 03:06:55.279125
2 | c798c226fcf884c0d892de5f6bed0355 | 2020-03-18 03:06:55.279367
3 | 268445645ff62f6c7cbbad98a74704b8 | 2020-03-18 03:06:55.279369
#创建索引
testdb=# create index idx_tb1_id on tb1(id);
CREATE INDEX
testdb=# \d tb1
Table "public.tb1"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
id | integer | | |
info | text | | |
crt_time | timestamp without time zone | | |
Indexes:
"idx_tb1_id" btree (id)
#删除索引
testdb=# drop index idx_tb1_id ;
DROP INDEX
Time: 1.329 ms
testdb=# \d tb1
Table "public.tb1"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
id | integer | | |
info | text | | |
crt_time | timestamp without time zone | | |
testdb=#
使用索引
#打开时间
testdb=#\timing on
#查询条件是索引列
testdb=# explain analyze select * from tb1 where id = 99999;
Index Scan using idx_tb1_id on tb1 (cost=0.29..8.31 rows=1 width=45) (actual
time=0.037..0.038 rows=1 loops=1)
Index Cond: (id = 99999)
Planning Time: 0.330 ms
Execution Time: 0.068 ms
#查询条件不是索引列
testdb=# explain analyze select * from tb1 where info =
'268445645ff62f6c7cbbad98a74704b8';
Seq Scan on tb1 (cost=0.00..2185.00 rows=1 width=45) (actual time=0.012..7.000
rows=1 loops=1)
Filter: (info = '268445645ff62f6c7cbbad98a74704b8'::text)
Rows Removed by Filter: 99999
Planning Time: 0.077 ms
Execution Time: 7.017 ms
#关闭索引
testdb=# set enable_indexscan=off;
SET
testdb=# set enable_bitmapscan=off;
SET
#再次查询全表扫描
testdb=# explain analyze select * from tb1 where id = 99999;
Seq Scan on tb1 (cost=0.00..2185.00 rows=1 width=45) (actual time=5.646..5.647
rows=1 loops=1)
Filter: (id = 99999)
Rows Removed by Filter: 99999
Planning Time: 0.113 ms
Execution Time: 5.664 ms
testdb=# explain (analyze,verbose,costs,buffers,timing) select * from tb1 where
id = 99999;
表空间
#列出所有表空间,实际上PostgresQL中的表空间就是对应一个目录，放在这个表空间的表，就是把表的数据文件放到这个表空间下。
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)
#复制表到文件中
testdb=# select * from t1;
id
----
1
2
(2 rows)
testdb=# copy t1 to '/tmp/t1.txt';
COPY 2
[root@ubuntu2004 ~]#cat /tmp/t1.txt
1
2
范例
表空间pg_talspc目录
[root@ubuntu2004 ~]#su - postgres
postgres@ubuntu2004:~$ mkdir ts1
postgres@ubuntu2004:~$ psql testdb
postgres=# create tablespace ts1 location '/home/postgres/ts1/';
CREATE TABLESPACE
Time: 0.936 ms
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+--------------------
pg_default | postgres |
pg_global | postgres |
ts1 | postgres | /home/postgres/ts1
postgres@ubuntu2004:~$ readlink /pgsql/data/pg_tblspc/16442
/home/postgres/ts1
查看表空间对应的文件
[root@ubuntu2004 ~]#ls /pgsql/data/pg_tblspc
[root@ubuntu2004 ~]#su - postgres
postgres@ubuntu2004:~$ mkdir /tmp/tbs1
postgres@ubuntu2004:~$ psql
testdb=# create tablespace tbs1 location '/tmp/tbs1' ;
CREATE TABLESPACE
testdb=# create table tb1(id int) tablespace tbs1;
CREATE TABLE
testdb=# select * from pg_relation_filepath('tb1');
pg_relation_filepath
---------------------------------------------
pg_tblspc/16450/PG_12_201909212/16408/16451
(1 row)
postgres@ubuntu2004:~$ tree /pgsql/data/pg_tblspc/
/pgsql/data/pg_tblspc/
└── 16450 -> /tmp/tbs1
1 directory, 0 files
postgres@ubuntu2004:~$ tree /tmp/tbs1/
/tmp/tbs1/
└── PG_12_201909212
└── 16408
└── 16451
2 directories, 1 file
查看系统信息
可以通过系统函数查看系统信息,也可以通过show/set 查看和修改配置
#查看版本信息
postgres=# select version();
version
---------------------------------------------------------------------------------
--------
PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-
1ubuntu1~20.04) 9.4.0, 64-bit
(1 row)
#查看数据库启动时间
postgres=# select pg_postmaster_start_time();
pg_postmaster_start_time
-------------------------------
2021-10-23 10:47:50.068746+00
(1 row)
#查看加载配置文件时间
postgres@ubuntu2004:~$ pg_ctl reload
postgres=# select pg_conf_load_time();
pg_conf_load_time
------------------------------
2021-10-23 10:47:50.068746+00
(1 row)
#查看时区和时间
postgres=# show timezone;
TimeZone
----------
Etc/UTC
(1 row)
#临时修改
postgres=# set timezone='Asia/Shanghai'
SET
#永久修改时区
postgres@ubuntu2004:~$ vim /pgsql/data/postgresql.conf
timezone = 'Asia/Shanghai'
postgres@ubuntu2004:~$ pg_ctl reload
postgres=# select now();
now
-------------------------------
2021-10-23 08:50:47.108479+00
(1 row)
#查看当前用户
postgres=# select user;
user
----------
postgres
(1 row)
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
postgres=# select session_user;
session_user
--------------
postgres
(1 row)
#查看当前数据库
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# select current_database();
current_database
------------------
testdb
(1 row)
#查看当前session所在的客户端IP和端口
[root@rocky8 ~]#psql -h 10.0.0.200 -U postgres
Password for user postgres:
psql (12.9)
Type "help" for help.
postgres=# select inet_client_addr(),inet_client_port();
inet_client_addr | inet_client_port
------------------+------------------
10.0.0.8 | 47124
(1 row)
#查看当前session所连接的数据库服务器的IP和端口
postgres=# select inet_server_addr(),inet_server_port();
inet_server_addr | inet_server_port
------------------+------------------
10.0.0.200 | 5432
(1 row)
#查询当前session对应的后台服务时程pid
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
56000
(1 row)
#查看当前内置变量
postgres=> \set
#查看当前指定配置
postgres=# show max_connections;
max_connections
-----------------
100
(1 row)
postgres=# select current_setting('max_connections');
current_setting
-----------------
100
(1 row)
postgres=# select current_setting('listen_addresses');
current_setting
-----------------
*
(1 row)
#显示系统函数
postgres=# \dfS+
#查看连接数
postgres=# select count(*) from pg_stat_activity;
-[ RECORD 1 ]
count | 7
#查看当前最大的连接数
postgres=# select setting from pg_settings where name = 'max_connections';
setting
---------
100
(1 row)
#查看所有设置名称
postgres=# select name from pg_settings;
name
----------------------------------------
allow_system_table_mods
application_name
archive_cleanup_command
archive_command
archive_mode
archive_timeout
array_nulls
authentication_timeout
......
#查看当前设置名和值
postgres=# select name,setting from pg_settings;
name | setting
----------------------------------------+---------------------------------------
------
allow_system_table_mods | off
application_name | psql
archive_cleanup_command |
archive_command | test ! -f /archive/%f &&cp %p
/archive/%f
archive_mode | on
archive_timeout | 0
array_nulls | on
authentication_timeout | 60
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 3
autovacuum_multixact_freeze_max_age | 400000000
#查看指定的当前的参数设置
postgres=# show port;
port
------
5432
(1 row)
postgres=# show archive_mode;
archive_mode
--------------
on
(1 row)
show和set查看和修改配置
# 查看参数
SHOW name;
SHOW ALL;
postgres=# show all;
-[ RECORD 1 ]----------------------------------------------------------------------------
name | allow_system_table_mods
setting | off
description | Allows modifications of the structure of system tables.
-[ RECORD 2 ]----------------------------------------------------------------------------
name | application_name
setting | psql
description | Sets the application name to be reported in statistics and logs.
-[ RECORD 3 ]----------------------------------------------------------------------------
name | archive_cleanup_command
setting |
description | Sets the shell command that will be executed at every restart
point.
...
#修改配置
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' |
DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }
postgres=# show maintenance_work_mem ;
maintenance_work_mem
----------------------
64MB
(1 row)
postgres=# set maintenance_work_mem to '128MB';
SET
postgres=# show maintenance_work_mem ;
maintenance_work_mem
----------------------
128MB
(1 row)
#注意:不是所有配置都可以直接修改的
postgres=# set max_connections to '200';
ERROR: parameter "max_connections" cannot be changed without restarting the
server
#查看数据库的大小,pg_size_pretty函数会把数字以MB,GB等易读格式显示
postgres=# select
pg_database_size('hellodb'),pg_size_pretty(pg_database_size('hellodb'));
pg_database_size | pg_size_pretty
------------------+----------------
8029039 | 7841 kB
(1 row)
explain可以查看SQL的执行计划
#explain可以查看SQL的执行计划
hellodb=# explain select * from students;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on students (cost=0.00..1.25 rows=25 width=138)
(1 row)
hellodb=# explain analyze select * from students;
QUERY PLAN
-----------------------------------------------------------------------------------------
Seq Scan on students (cost=0.00..1.25 rows=25 width=138) (actual
time=0.070..0.072 rows=25 loops=1)
Planning Time: 0.035 ms
Execution Time: 0.111 ms
(3 rows)
hellodb=# explain analyze verbose select * from students;
QUERY PLAN
----------------------------------------------------------------------------------------
Seq Scan on public.students (cost=0.00..1.25 rows=25 width=138) (actual
time=0.008..0.010 rows=25 loops=1)
Output: stuid, name, age, gender, classid, teacherid
Planning Time: 0.044 ms
Execution Time: 0.024 ms
(4 rows)
查看用户和权限
#查看所有用户\du或\dg
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------+-----------
dba | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
#查看当前用户
postgres=# select user ;
user
----------
postgres
(1 row)
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
#显示表,视图,序列的权限分配情况
postgres=# \z
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | a1 | table | | |
public | a2 | table | | |
public | t1 | table | | |
(3 rows)
postgres=# \z t1
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | t1 | table | | |
(1 row)
#和\z功能相同
postgres=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | a1 | table | | |
public | a2 | table | | |
public | t1 | table |
事物管理和锁
PGSQL的事务中支持DML,DDL(除了create database,create tablespace),DCL在psql 中事务是自动提交的。和MySQL相同，执行完一条delete或update语句后，事务就自动提交了
如果不想自动提交，方法有两种：
-
方法一:运行begin;命令，然后执行DML,DDL,DCL等语句，最后再执行commit或rollback语句。
-
方法二:直接使用psql中的命令关闭自动提交的功能。\set AUTOCOMMIT off,注意，命令中的AUTOCOMMIT是大写的，不能使用小写，如果使用小写、虽然不会报错，但会导致关闭自动提交的操作不起作用。
#开始事务
BEGIN [ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }]
#提交和取消事务
COMMIT|END
ROLLBACK
#关闭自动提交,可以用rollback取消DML语句
\set AUTOCOMMIT off
\set AUTOCOMMIT on
#查看AUTOCOMMIT状态
\echo :AUTOCOMMIT
#查看事务ID
select txid_current();
范例
postgres@ubuntu2004:~$ psql
psql (12.9)
Type "help" for help.
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# \d
Did not find any relations.
testdb=# begin;
BEGIN
testdb=# create table tb1 (id int);
CREATE TABLE
testdb=# insert into tb1 values (1);
INSERT 0 1
testdb=# select * from tb1;
id
----
1
(1 row)
testdb=# rollback;
ROLLBACK
testdb=# \d
Did not find any relations.
testdb=#
#查看事务ID
postgres=# select txid_current();
txid_current
--------------
543
(1 row)
#事务块中不支持create database
testdb=# begin;
BEGIN
testdb=# create database db1;
2022-01-18 01:54:36.823 UTC [14674] ERROR: CREATE DATABASE cannot run inside a transaction block
2022-01-18 01:54:36.823 UTC [14674] STATEMENT: create database db1;
ERROR: CREATE DATABASE cannot run inside a transaction block
#查看ctid(数据所在的数据块的编号及位移),xmin(插入事务XID),xmax(删除记录的事务XID)
testdb=# select ctid,xmin,xmax,* from tb1;
(0,1) | 538 | 0 | 1 | 0865298f54f626f6b86533dce032b065
(0,2) | 538 | 0 | 2 | 2196366f04ed0b8558807e60d7b3d6da
(0,3) | 538 | 0 | 3 | cdf2d414a117300daf68989a5b29965c
#查看锁信息
testdb=# select relation::regclass,* from pg_locks;
relation | locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid |
mode | granted | fastpath
----------+------------+----------+----------+------+-------+------------+------
---------+---------+-------+----------+--------------------+-------+
-----------------+---------+----------
tb1_pkey | relation | 16408 | 24590 | | | |
| | | | 5/2 | 20413 |
AccessShareLock | t | t
tb1 | relation | 16408 | 24583 | | | |
| | | | 5/2 | 20413 |
AccessShareLock | t | t
| virtualxid | | | | | 5/2 |
| | | | 5/2 | 20413 |
ExclusiveLock | t | t
pg_locks | relation | 16408 | 12143 | | | |
| | | | 4/182 | 19202 |
AccessShareLock | t | t
| virtualxid | | | | | 4/182 |
| | | | 4/182 | 19202 |
ExclusiveLock | t | t
(5 rows)
常用系统函数
#查看当前日志文件lsn位置;
select pg_current_wal_lsn();
select pg_current_xlog_location();
#当前xlog buffer中的insert位置，注意和上面pg_current_xlog_location()的区别:
SELECT pg_current_wal_insert_lsn();
select pg_current_xlog_insert_location();
#查看某个1sn对应的日志名:
select pg_walfile_name(lsn) ;
select pg_xlogfile_name(1sn);
#查看某个1sn在日志中的偏移量:
select pg_walfile_name_offset('lsn号');
select pg_xlogfile_name_offset('lsn号');
#查看两个lsn位置的差距;
select pg_wa1_1sn_diff('lsn号','lsn号');
select pg_xlog_1ocation_diff('lsn号','lsn号');
#查看备库接收到的1sn位置:
select pg_last_wal_receive_lsn();
select pg_last_xlog_receive_location();
#查看备库回放的lsn位置:
select pg_last_xact_replay_timestamp();
select pg_last_xlog_relay_location();
#创建还原点:
select pg_create_restore_point(now()::text);
#查看表的数据文件路径，filenode:
select pg_relation_filenode( '表名');
#查看表students的oid:
select 'students'::regclass::oid;
#查看当前会话pid:
select pg_backend_pid();
#生成序列:
select generate_series (1,8,2);
#生成uuid (pg13新特性):
se1ect gen_random_uuid();
#重载配置文件信息:
select pg_reload_conf();
#查看数据库启动时间:
select pg_postmaster_start_time();