Postgres学习之入门 && 浏览数据库

最新推荐文章于 2024-03-11 07:50:29 发布

文档搬运工

最新推荐文章于 2024-03-11 07:50:29 发布

阅读量494

点赞数

分类专栏： Postgres SQL

本文链接：https://blog.csdn.net/xxzhaobb/article/details/112370320

版权

Postgres SQL 专栏收录该内容

39 篇文章 6 订阅

订阅专栏

本文档详细介绍了PostgreSQL数据库的管理操作，包括查看数据库版本、连接方式、显示数据库信息、用户权限、日志配置、数据存储大小以及表的操作。此外，还涉及到了数据库扩展、对象依赖关系和性能监控等内容。

摘要由CSDN通过智能技术生成

参考文档《PostgreSQL 9 Administration Cookbook》（第二版）中文版

第一章迈出第一步
-- 查看数据库的版本

mydb=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)

mydb=#
-bash-4.2$ psql -V
psql (PostgreSQL) 10.15
-bash-4.2$ psql --version
psql (PostgreSQL) 10.15
-bash-4.2$ 
-bash-4.2$ more PG_VERSION 
10
-bash-4.2$

-- 传统的连接方式

-bash-4.2$ psql -h localhost -p 5432 -d mydb -U postgres
Password for user postgres: 
psql.bin (10.15)
Type "help" for help.

mydb=#

从pg9.2开始，可以通过url方式连接到数据库

psql postgres://myuser:mypassword@myhost:5432/mydb
以上连接，到myhost主机的5432端口的postgresql，数据库名为mydb，用户名myuser，密码mypasswd

-bash-4.2$ psql postgres://postgres:oracle@localhost:5432/mydb
psql.bin (10.15)
Type "help" for help.

mydb=#

-- 显示当前数据库

mydb=# select current_database();
 current_database 
------------------
 mydb
(1 row)

-- 显示当前用户

mydb=# select current_user;
 current_user 
--------------
 postgres
(1 row)

--显示当前连接IP和端口

mydb=# select inet_server_addr(),inet_server_port();
 inet_server_addr | inet_server_port 
------------------+------------------
 ::1              |             5432
(1 row)

mydb=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)

-- 显示连接信息

mydb=# \conninfo
You are connected to database "mydb" as user "postgres" on host "localhost" at port "5432".
mydb=#

-- 简单的psql命令

-bash-4.2$ psql -c "select current_user" -d mydb
Password: 
 current_user 
--------------
 postgres
(1 row)

-bash-4.2$

-- 使用psql执行语句，比如，example.sql里面有两条语句，以分号分开，执行结果如下

[root@asm12c tmp]# cat /tmp/example.sql 
select version();
select current_user;
[root@asm12c tmp]# 

-bash-4.2$ psql -f "/tmp/example.sql" -d mydb
Password: 
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)

 current_user 
--------------
 postgres
(1 row)

-bash-4.2$

psql允许输入的两种类型的命令
psql 元命令(meta-commands) --比如以\开头的命令
SQL语句

\?提供psql元命令的相关帮助
\h 提供特定SQL命令的帮助信息，比如\delete

-- pg密码文件的设置（注意权限是600，否则会报错，或者无法使用密码文件。实际上，不是这个权限，会被忽略掉这个密码文件）
密码文件的格式，密码文件在linux下位于~/.pgpass 。密码文件可以包含多行

host:port:dbname:user:password

-- 不是600权限，设置了400权限，这里报错了，然后忽略了

-bash-4.2$ psql
WARNING: password file "/postgres/10/.pgpass" has group or world access; permissions should be u=rw (0600) or less
Password: 
-bash-4.2$ ls -l

-bash-4.2$ more .pgpass 
*:5432:*:postgres:oracle
-bash-4.2$ ls -l .pgpass 
-rw------- 1 postgres postgres 25 Jan  7 10:13 .pgpass
-bash-4.2$ psql -d mydb
psql.bin (10.15)
Type "help" for help.

mydb=#

-- postgres用户密码的修改

\password

结果相当于 alter user postgres password 'md5234i123ui4ui42';

-- 使用pg_isready小程序来检测数据库是否可以被连接，不需要输入用户名密码之类的，这个工具值强制检查主机名和端口。

-bash-4.2$ pg_isready 
/tmp:5432 - accepting connections
-bash-4.2$ pg_isready -d mydb
/tmp:5432 - accepting connections
-bash-4.2$

-- log_connections 参数，设置改参数为on，则可以记录登录和断开连接的情况，log日志记录的格式，以时间+进程开头。可以通过ps来查看

log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] ' 



2021-01-07 11:04:21.390 CST [57190] LOG:  parameter "log_connections" changed to "on"
2021-01-07 11:04:21.390 CST [57190] LOG:  parameter "log_disconnections" changed to "on"
2021-01-07 11:04:26.991 CST [100461] LOG:  connection received: host=[local]
2021-01-07 11:04:26.992 CST [100461] LOG:  connection authorized: user=postgres database=mydb
2021-01-07 11:04:45.951 CST [100608] LOG:  connection received: host=[local]
2021-01-07 11:04:45.952 CST [100608] LOG:  connection authorized: user=postgres database=postgres
2021-01-07 11:04:52.228 CST [100608] LOG:  disconnection: session time: 0:00:06.276 user=postgres database=postgres host=[local]
2021-01-07 11:05:38.368 CST [101000] LOG:  connection received: host=[local]
2021-01-07 11:05:38.369 CST [101000] LOG:  connection authorized: user=postgres database=postgres
2021-01-07 11:06:26.351 CST [101000] LOG:  disconnection: session time: 0:00:47.983 user=postgres database=postgres host=[local]

第二张浏览数据库

-- 查看postgres数据库版本的支持情况
https://www.postgresql.org/support/versioning/

Releases

Version	Current minor	Supported	First Release	Final Release
13	13.1	Yes	September 24, 2020	November 13, 2025
12	12.5	Yes	October 3, 2019	November 14, 2024
11	11.10	Yes	October 18, 2018	November 9, 2023
10	10.15	Yes	October 5, 2017	November 10, 2022
9.6	9.6.20	Yes	September 29, 2016	November 11, 2021
9.5	9.5.24	Yes	January 7, 2016	February 11, 2021
9.4	9.4.26	No	December 18, 2014	February 13, 2020
9.3	9.3.25	No	September 9, 2013	November 8, 2018
9.2	9.2.24	No	September 10, 2012	November 9, 2017
9.1	9.1.24	No	September 12, 2011	October 27, 2016
9.0	9.0.23	No	September 20, 2010	October 8, 2015
8.4	8.4.22	No	July 1, 2009	July 24, 2014
8.3	8.3.23	No	February 4, 2008	February 7, 2013
8.2	8.2.23	No	December 5, 2006	December 5, 2011
8.1	8.1.23	No	November 8, 2005	November 8, 2010
8.0	8.0.26	No	January 19, 2005	October 1, 2010
7.4	7.4.30	No	November 17, 2003	October 1, 2010
7.3	7.3.21	No	November 27, 2002	November 27, 2007
7.2	7.2.8	No	February 4, 2002	February 4, 2007
7.1	7.1.3	No	April 13, 2001	April 13, 2006
7.0	7.0.3	No	May 8, 2000	May 8, 2005
6.5	6.5.3	No	June 9, 1999	June 9, 2004
6.4	6.4.2	No	October 30, 1998	October 30, 2003
6.3	6.3.2	No	March 1, 1998	March 1, 2003

-- 查看服务启动了多长时间

select pg_postmaster_start_time();
select current_timestamp - pg_postmaster_start_time(); 
select date_trunc('second',current_timestamp - pg_postmaster_start_time()) as uptime ;

mydb=# select date_trunc('second',current_timestamp - pg_postmaster_start_time()) as uptime ;
      uptime      
------------------
 13 days 21:17:48
(1 row)

mydb=#

-- 在/etc/目录下，有个postgres-reg.ini ，记录了pg的一些安装信息

[root@asm12c etc]# more postgres-reg.ini 
[PostgreSQL/10]
InstallationDirectory=/postgres/10
Version=10.15-1
Shortcuts=1
DataDirectory=/postgres/10/data
Port=5432
ServiceID=postgresql-10
Locale=en_US.UTF-8
Superuser=postgres
Serviceaccount=postgres
Description=PostgreSQL 10
Branding=PostgreSQL 10
SB_Version=4.1.0
pgAdmin_Version=4.28
CLT_Version=10.15.1
[root@asm12c etc]#

-- postgres的消息级别，可以通过设置log_min_messages来调整日志文件记录的信息数量，log_error_verbosity来调整日志文件信息里面每条日志展示的信息

-- 定位数据库的系统标识符
通过pg_controldata 查询其中的system identifier 来查看，pg_controldata 主要是显示服务器的控制文件内容。

-bash-4.2$ pg_controldata 
pg_control version number:            1002
Catalog version number:               201707211
Database system identifier:           6908875802519103773
Database cluster state:               in production
pg_control last modified:             Wed 06 Jan 2021 04:59:18 PM CST
Latest checkpoint location:           0/A353EA8
Prior checkpoint location:            0/A339750
Latest checkpoint's REDO location:    0/A353E70
Latest checkpoint's REDO WAL file:    00000001000000000000000A
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:686
Latest checkpoint's NextOID:          24670
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        549
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  686
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Wed 06 Jan 2021 04:59:15 PM CST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              100
max_worker_processes setting:         8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            d8428eff377529161c8b433513e2662f8c0ef6cc0d2ac54b79b67a712ecad5c1
-bash-4.2$ pg_controldata | grep "system identifier"
Database system identifier:           6908875802519103773
-bash-4.2$

-- 查看有多少个数据库

-bash-4.2$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 korean    | postgres | EUC_KR   | ko_KR.euckr | ko_KR.euckr | 
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 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
(5 rows)

-bash-4.2$ 

mydb=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 korean    | postgres | EUC_KR   | ko_KR.euckr | ko_KR.euckr | 
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 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
(5 rows)

mydb=# select datname from pg_database;
  datname  
-----------
 postgres
 mydb
 template1
 template0
 korean
(5 rows)

mydb=#

template1 是可以修改的数据库，可以自定义template1 作为模板
template0存在的目的，是在于修改了template1后，仍然还有一个备份。如果破坏掉了template1，则还可以通过template0重建一个。

-- 使用\x格式化输出结果

mydb=# \x
Expanded display is on.

--查看数据库中有多少张表，也可以通过\d来查看（PG里面，好像把序列、物化视图、视图都当做表了）

select count(*) from information_schema.tables where table_schema not in ('information_schema','pg_catalog');

PG在系统信息表（catalog tables）中存储关于数据库的信息。描述了已被定义的数据库的各方面的内容。主要的系统信息表都存放在pg_catalog、information_schema的模式中。

-- 查看一个数据库占用了多少磁盘空间

select pg_database_size(current_database());  -- 查询一个数据库的大小（当前数据库） 
select sum(pg_database_size(datname)) from pg_database;   -- 查询所有数据库的大小 

mydb=# select pg_database_size(current_database());
 pg_database_size 
------------------
          9288839
(1 row)

mydb=# select sum(pg_database_size(datname)) from pg_database;
   sum    
----------
 41374751
(1 row)

mydb=#

-- 查看一个表占用了多少磁盘空间

select pg_relation_size('table_name')  ;-- 只查看表占用空间
select pg_total_relation_size('table_name')  ; -- 查看获取表的总大小，包括索引和其他一些相关的空间占用
select pg_size_pretty(pg_relation_size('t'));   -- 使用函数pg_size_pretty格式化，查询的结果带单位
select pg_size_pretty(pg_total_relation_size('t'));

\dt+ 查看表大小

mydb=# select count(*) from t;
 count 
-------
 10000
(1 row)

mydb=# select pg_relation_size('t');
 pg_relation_size 
------------------
           368640
(1 row)

mydb=# 
mydb=# select pg_total_relation_size('t');
 pg_total_relation_size 
------------------------
                 393216
(1 row)

mydb=# 
mydb=# select pg_size_pretty(pg_relation_size('t'));
 pg_size_pretty 
----------------
 360 kB
(1 row)

mydb=# select pg_size_pretty(pg_total_relation_size('t'));
 pg_size_pretty 
----------------
 384 kB
(1 row)

mydb=# 

mydb=# \dt+ t
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | t    | table | postgres | 384 kB | 
(1 row)

.vm 和.fsm文件，分别表示visibility map 和free space map .这些文件主要是处于维护目的而产生，通常很小，即使对应的很大的表。

-- 查询最大的10张表，函数pg_relation_size，来计算指定的表或索引占用的实际的磁盘空间

select table_name,pg_relation_size(table_schema ||'.' || table_name) as size from information_schema.tables
where table_schema not in ('information_schema','pg_catalog')
order by size desc 
limit 10;

mydb=# select table_name,pg_relation_size(table_schema ||'.' || table_name) as size from information_schema.tables
mydb-# where table_schema not in ('information_schema','pg_catalog')
mydb-# order by size desc 
mydb-# limit 10;
  table_name  |  size  
--------------+--------
 t            | 368640
 emp          |   8192
 products     |   8192
 tab_big      |   8192
 weather      |   8192
 shoelace_log |   8192
 image        |   8192
 cities       |   8192
 users        |   8192
 foo          |   8192
(10 rows)

mydb=#

-- 粗略地估算表中的数据量，这个是优化器使用的方法，无论检查的表有多大，返回的行数都很快。通过执行时间，可以看到效率很好。

select (case when reltuples > 0 then 
	   pg_relation_size('t') * reltuples/(8192*relpages)
	   else 0
	   end)::bigint as estimated_row_count
from pg_class
where oid='t'::regclass;

mydb=# \timing
Timing is on.
mydb=# select count(*) from t;
 count 
-------
 10000
(1 row)

Time: 1.453 ms
mydb=# select (case when reltuples > 0 then 
mydb(#     pg_relation_size('t') * reltuples/(8192*relpages)
mydb(#     else 0
mydb(#     end)::bigint as estimated_row_count
mydb-# from pg_class
mydb-# where oid='t'::regclass;    -- 将一个对象名，转换成对象标识符编号的快捷方法。等同于调用函数relname2relid() 
 estimated_row_count 
---------------------
               10000
(1 row)

Time: 0.519 ms
mydb=#

pg_relation_size()函数是获取当前表的大小值的。
pg_class系统表里面记录了表的两类信息，数据块数relpages和记录数reltuples 。如果统计信息比较陈旧的话，计算结果是不准确的。
PG使用relpages和reltuples的值来计算每个块上平均有多少行，这个值叫做平均记录密度。
假设平均记录密度一直保持不变，则可以根据这个公式来估算行数：估算行数 = 数据块数 * 每块的行数

-- 快速定位表的文件的位置

select reltablespace,relfilenode from pg_class where oid='t'::regclass; -- 16665   -- 这个是查看表t的文件编号
select oid as databaseid from pg_database where datname=current_database();  -- 16393   -- 这个是查看数据库的编号 

mydb=# select oid as databaseid from pg_database where datname=current_database();
 databaseid 
------------
      16393
(1 row)

Time: 0.676 ms
mydb=# select reltablespace,relfilenode from pg_class where oid='t'::regclass;
 reltablespace | relfilenode 
---------------+-------------
             0 |       16665
(1 row)

Time: 0.517 ms
mydb=#

--具体的文件位置，可以看到表t对应的文件大小，大约368K，和上面SQL查询的结果一样

-bash-4.2$ ll 16665
-rw------- 1 postgres postgres 368640 Jan  6 15:04 16665
-bash-4.2$ pwd
/postgres/10/data/base/16393

-- 创建一个函数，来获取表的行数

CREATE OR REPLACE FUNCTION estimated_row_count(text)
RETURNS bigint
LANGUAGE sql
AS $$
SELECT (CASE WHEN reltuples > 0 THEN
pg_relation_size($1)/(8192*relpages/reltuples)
ELSE 0
END)::bigint
FROM pg_class
WHERE oid = $1::regclass;
$$;

select estimated_row_count('t');

mydb=# select estimated_row_count('t');
 estimated_row_count 
---------------------
               10000
(1 row)

Time: 0.822 ms
mydb=#

-- 获取表的大小而不锁表

CREATE OR REPLACE FUNCTION
pg_relation_size_nolock(tablename regclass)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
classoutput RECORD;
tsid INTEGER;
rid INTEGER;
dbid INTEGER;
filepath TEXT;
filename TEXT;
datadir TEXT;
i INTEGER := 0;
tablesize BIGINT;
BEGIN
--
-- get data directory
--
EXECUTE 'SHOW data_directory' INTO datadir;
--
-- get relfilenode and reltablespace
--
SELECT
reltablespace as tsid
,relfilenode as rid
INTO classoutput
FROM pg_class
WHERE oid = tablename
AND relkind = 'r';
--
-- Throw an error if we can't find the tablename specified
--
IF NOT FOUND THEN
RAISE EXCEPTION 'tablename % not found', tablename;
END IF;
tsid := classoutput.tsid;
rid := classoutput.rid;
--
-- get the database object identifier (oid)
--
SELECT oid INTO dbid
FROM pg_database
WHERE datname = current_database();
--
-- Use some internals knowledge to set the filepath
--
IF tsid = 0 THEN
filepath := datadir || '/base/' || dbid || '/' || rid;
ELSE
filepath := datadir || '/pg_tblspc/'
|| tsid || '/'
|| dbid || '/'
|| rid;
END IF;
--
-- Look for the first file. Report if missing
--
SELECT (pg_stat_file(filepath)).size
INTO tablesize;
--
-- Sum the sizes of additional files, if any
--
WHILE FOUND LOOP
i := i + 1;
filename := filepath || '.' || i;
--
-- pg_stat_file returns ERROR if it cannot see file
-- so we must trap the error and exit loop
--
BEGIN
SELECT tablesize + (pg_stat_file(filename)).size
INTO tablesize;
EXCEPTION
WHEN OTHERS THEN
EXIT;
END;
END LOOP;
RETURN tablesize;
END;
$$;

select pg_relation_size_nolock('t');

mydb=# select pg_relation_size_nolock('t');
 pg_relation_size_nolock 
-------------------------
                  368640
(1 row)

Time: 7.260 ms
mydb=#

-- 查看数据库的扩展

mydb=# \dx
                                    List of installed extensions
   Name   | Version |   Schema   |                            Description                            
----------+---------+------------+-------------------------------------------------------------------
 file_fdw | 1.0     | public     | foreign-data wrapper for flat file access
 pg_trgm  | 1.3     | public     | text similarity measurement and index searching based on trigrams
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

mydb=# select * from pg_extension;
 extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
----------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql  |       10 |           11 | f              | 1.0        |           | 
 file_fdw |       10 |         2200 | t              | 1.0        |           | 
 pg_trgm  |       10 |         2200 | t              | 1.3        |           | 
(3 rows)

Time: 0.637 ms
mydb=#

-- 对象的依赖关系

CREATE TABLE orders (
orderid integer PRIMARY KEY
);

CREATE TABLE orderlines (
orderid integer
,lineid smallint
,PRIMARY KEY (orderid, lineid)
);

ALTER TABLE orderlines ADD FOREIGN KEY (orderid) REFERENCES orders (orderid);
DROP TABLE orders;  -- 报错 

\d+ orders

SELECT * FROM pg_constraint
WHERE confrelid = 'orders'::regclass;

mydb=# \d+ orders
                                   Table "public.orders"
 Column  |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
---------+---------+-----------+----------+---------+---------+--------------+-------------
 orderid | integer |           | not null |         | plain   |              | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (orderid)
Referenced by:
    TABLE "orderlines" CONSTRAINT "orderlines_orderid_fkey" FOREIGN KEY (orderid) REFERENCES orders(orderid)

mydb=#

END