官网：https://www.postgresql.org/
中文手册：http://www.postgres.cn/docs/15/index.html
1.）安装PgSQL
1.1）源安装
root@pg-1:~# sudo apt install -y postgresql-common
root@pg-1:~# sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
root@pg-1:~# sudo apt install curl ca-certificates
root@pg-1:~# sudo install -d /usr/share/postgresql-common/pgdg
root@pg-1:~# sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
root@pg-1:~# sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
root@pg-1:~# apt update
root@pg-1:~# apt-cache madison postgresql
postgresql | 17+274.pgdg22.04+1 | https://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 Packages
postgresql | 14+238 | https://mirrors.aliyun.com/ubuntu jammy/main amd64 Packages
postgresql-common | 238 | https://mirrors.aliyun.com/ubuntu jammy/main Sources
root@pg-1:~# sudo apt -y install postgresql=14+238
`初始化并启动`
# 方法一
root@pg-1:~# pg_createcluster 14 main --start # 14 指的版本
# 方法二
root@pg-1:~# su - postgres
postgres@pg-1:~$ /usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/data
Success. You can now start the database server using:
/usr/lib/postgresql/14/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start
`启动服务`
postgres@pg-1:~$ /usr/lib/postgresql/14/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start
postgres@pg-1:~$ lsof -i :5432
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
postgres 4653 postgres 5u IPv4 47470 0t0 TCP localhost:postgresql (LISTEN)
`停止服务`
postgres@pg-1:~$ /usr/lib/postgresql/14/bin/pg_ctl -D /var/lib/postgresql/data -l logfile stop
`修改配置`
postgres@pg-1:~$ vim /var/lib/postgresql/data/postgresql.conf
...
listen_addresses = '192.168.139.161'
`进入数据库`
postgres@pg-1:~$ psql
1.2）二进制安装
#
#********************************************************************
#Author: tanghao
#QQ: 2826588067
#Date: 2025-02-25
#FileName: install_postgresql.sh
#URL: https://www.stangj.com
#Description: The test script
#Copyright (C): 2024 All rights reserved
#********************************************************************
POSTGRESQL_VERSION=15.12
INSTALL_DIR=/apps/pgsql
DATA_DIR=/apps/pgsql/data
DB_USER=postgres
CPUS=`lscpu |awk '/^CPU\(s\)/{print $2}'`
. /etc/os-release
color () {
RES_COL=60
MOVE_TO_COL="echo -en \\033[${RES_COL}G"
SETCOLOR_SUCCESS="echo -en \\033[1;32m"
SETCOLOR_FAILURE="echo -en \\033[1;31m"
SETCOLOR_WARNING="echo -en \\033[1;33m"
SETCOLOR_NORMAL="echo -en \E[0m"
echo -n "$1" && $MOVE_TO_COL
echo -n "["
if [ $2 = "success" -o $2 = "0" ] ;then
${SETCOLOR_SUCCESS}
echo -n $" OK "
elif [ $2 = "failure" -o $2 = "1" ] ;then
${SETCOLOR_FAILURE}
echo -n $"FAILED"
else
${SETCOLOR_WARNING}
echo -n $"WARNING"
fi
${SETCOLOR_NORMAL}
echo -n "]"
echo
}
install_postgresql () {
if [ $ID = 'centos' -o $ID = 'rocky' ];then
yum install -y gcc make readline-devel zlib-devel
elif [ $ID = 'ubuntu' ];then
apt update
apt install -y gcc make libreadline-dev zlib1g-dev
else
color "不支持此操作系统，退出!" 1
exit
fi
if [ ! -f postgresql-${POSTGRESQL_VERSION}.tar.gz ] ;then
wget https://ftp.postgresql.org/pub/source/v${POSTGRESQL_VERSION}/postgresql-${POSTGRESQL_VERSION}.tar.gz
fi
tar xf postgresql-${POSTGRESQL_VERSION}.tar.gz
cd postgresql-${POSTGRESQL_VERSION}
./configure --prefix=${INSTALL_DIR}
make -j $CPUS world
make install-world
useradd -s /bin/bash -m -d /home/$DB_USER $DB_USER
mkdir ${DATA_DIR} -pv
chown -R $DB_USER.$DB_USER ${DATA_DIR}/
cat > /etc/profile.d/pgsql.sh <<EOF
export PGHOME=${INSTALL_DIR}
export PATH=${INSTALL_DIR}/bin/:\$PATH
export PGDATA=${DATA_DIR}
export PGUSER=postgres
export MANPATH=${INSTALL_DIR}/share/man:$MANPATH
alias pgstart="pg_ctl -D ${DATA_DIR} start"
alias pgstop="pg_ctl -D ${DATA_DIR} stop"
alias pgrestart="pg_ctl -D ${DATA_DIR} restart"
alias pgrestatus="pg_ctl -D ${DATA_DIR} status"
EOF
su - $DB_USER -c 'initdb'
}
start_service () {
cat > /lib/systemd/system/postgresql.service <<EOF
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
User=postgres
Group=postgres
ExecStart=${INSTALL_DIR}/bin/postmaster -D ${DATA_DIR}
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl enable --now postgresql.service
systemctl is-active postgresql.service
if [ $? -eq 0 ] ;then
color "PostgreSQL 安装成功!" 0
else
color "PostgreSQL 安装失败!" 1
exit 1
fi
}
install_postgresql
start_service
2）基础使用PgSQL
2.1）初始化实例
范例: 创建新的数据库实例数据
# chown postgres:/pgsql/
# su - postgres
$ pg_ctl init -D /pgsq1/data2
2.2) 服务管理
2.2.1）查看服务状态
pg_ctl status -D [datadir]
### 示例
# su - postgres
$ pg_ctl status -D /pgsql/data2
2.2.2）启动服务
pg_ctl start [-w] [-t seconds] [-s] [-D datadir] [-l filename] [-o options] [-p path][-c]
# 参数说明如下。
start #启动数据库实例
-w #等待启动完成
-t #等待启动完成的等待秒数，默认为60秒
-s#只打印错误和警告信息,不打印提示性信息
-D datadir#指定数据库实例的数据目录
-1 #服务器日志输出附加在“filename”文件上，如果该文件不存在则创建它
-o options #声明要直接传递给postgres 的选项，具体可见postgres命令的帮助-p path #指定postgres可执行文件的位置。默认情况下postgres可执行文件来自和pg_ct1相同的目录,不必使用该选项。除非要进行一些不同寻常的操作，或者产生了postqres执行文件找不到的错误-c #提高服务器的软限制(ulimit -c)，尝试允许数据库实例在有异常时产生一个coredump文件,以便于问题定位和故障分析
范例
# su - postgres
$ pg_ctl start -D /pgsql/data2
$ pg_ctl status -D /pgsql/data2
2.2.3）停止服务
# su - postgres
$ pg_ctl stop -D /pgsql/data2
$ pg_ctl status -D /pgsql/data2
2.2.4) 重启服务
# su - postgres
$ pg_ctl restart -D /pgsql/data2
$ pg_ctl status -D /pgsql/data2
2.2.5）加载配置
在配置文件中改变参数后，需要使用上面这条命令使参数生效
#修改配置文件 postgresq1.conf 后，让修改生效的方法有两种
#方法一:在操作系统使用下面命令
pg_ctl reload [-s][-D datadir]
#方法二:在 psql 中使用如下命令
postgres=# select pg_reload_conf();
#注意:加载配置操作只针对一些配置的修改生效,有些配置需要重新启动服务才能生效
范例
# su - postgres
$ pg_ctl reload -D /pgsql/data2
#注意:修改端口不支持reload,只能restart
3）PostgreSQL管理
3.1）配置文件解释
PostgreSQL使用环境变量PGDATA指向的目录做为数据存放的目录。这个目录是在安装时指定的，所以在安装时需要指定一个合适的目录作为数据目录的根目录，而且，每一个PG数据库实例都需要有这样的一个目录。此数据目录的初始化是使用命令initdb来完成的。
初始化完成后，PGDATA数据目录下就会生成三个配置文件
postgresq1.conf #数据库实例的主配置文件，基本上所有的配置参数都在此文件中。
pg_hba.conf #认证配置文件，配置了允许哪些IP的主机访问数据库，认证的方法是什么等信息,
pg_ident.conf #认证方式ident的用户映射文件。
3.2）数据库相关概念
3.2.1）数据库结构组织
在一个PostgreSQL数据库系统中，数据的组织结构可以分为以下五层:
-
实例: 一个PostgreSQL对应一个安装的数据目录$PGDATA,即一个instance实例
-
数据库:一个PostgreSQL数据库服务下可以管理多个数据库，当应用连接到一个数据库时，一般只能访问这个数据库中的数据，而不能访问其他数据库中的内容
-
默认情况下初始实例只有三个数据库:postgres、template0、template1
-
模式:一个数据库可以创建多个不同的名称空间即Schema,用于分隔不同的业务数据表和索引:一个数据库可以有多个
-
表和索引。在PostgreSQL中表的术语称为 Relation，而在其他数据库中通常叫Table
-
行和列:每张表中有很多列和行数据。在 PostgreSQL 中行的术语一般为“Tuple”，而在其他数据库中则叫“Row"。
3.2.2）PostgreSQL中的术语
PostgreSQL有一些术语与其他数据库中不一样，了解了这些术语的意思，就能更好地看懂PostgreSQL中的文档。
与其他数据库不同的术语如下
-
Relation:表示表table或索引index，具体表示的是Table还是 Index需要看具体情况
-
Tuple:表示表中的行，在其他数据库中使用Row来表示
-
Segment:每个表和索引都单独对应一个文件,,即为segment,如果文件大小超过1GB,会创建多个相同名称但后缀不同的文件
-
Page:表示在磁盘中的数据块。在文件中以块为单位存放数据,默认值为8KB,最大可以为32KB
-
Buffer:表示在内存中的数据块。
范例:编译时可以指定片段大小
[root@ubuntu2004 postgresq1-12.9]#./configure --help | grep segsize --with-segsize=SEGSIZE 设置表段大小为 GB[1]
3.2.3）PostgreSQL中的 schema
#创建模式
create schema schema_name;
#删除模式
drop schema schema_name;
#查看模式
\dn
要访问指定模式中的对象，需要先指定一个包含模式名及表名的名字，模式和表之间用一个“点”分开如下:
schema_name.table_name
3.3）psql工具基本用法
psql的历史命令与补全的功能
-
可以使用上下键把以前使用过的命令或SQL语句调出来
-
连续按两个tab键表示把命令补全或给出提示输入
psql -h <hostname or ip> -p <端口> [数据库名称] -U[用户名称]
-h #指定要连接的数据库主机名或IP地址,默认1ocal socket登录(由配置项unix_socket_directories指定)
-p #指定连接的数据库端口
#最后两个参数是数据库名和用户名
#这些连接参数也可以用环境变量指定，比如:
export PGDATABASE=testdb
export PGHOST=10.0.0.200
export PGPORT=5432
export PGUSER=postgres
#然后运行psql即可，其效果与运行 psq1-h 10.0.0.200-p 5432 testdb postgres 相同。
#可通过下面命令査看详细帮助:man /apps/pgsq1/share/man/man1/psql.1
范例:psql本地登录PGSQL
#方法1
[root@rocky8 ~]# psql -d postgres -U postgres
psq1(12.9)Type "help" for help.
postgres=#
#方法2
[root@rocky8 ~]#su - postgres
[postgres@rocky8 ~]$psql
psq7(15.12)Type "help" for help.
postgres=#
范例:远程登录
#注意:默认PostgresQL不支持远程登录,需要修改配置和授权才可以
#如果不指定he11odb数据库,默认连接和用户名同名的数据库
[root@rocky8 ~]# psq1 -h 10.0.0.200 -p 5432 hellodb postgres
Password for user postgres:
psq1(15.12)
Type "help" for help.
hellodb=#
范例:psql命令中直接执行 SQL
[root@rocky8 ~]# psql -U postgres -h 10.0.0.200 -p 5432 -d postgres -c "select current_time'
Password for user postgres:
current_time
04:43:48.508999+00
(1 row)
范例:psql命令中执行文件中的 SQL
[root@rocky8 ~]#cat test.sql
select current_time
\du
#方法一
[root@rocky8 ~]#psql -U postgres -h 10.0.0.200 -p 5432 -d postgres -f test.sql
# 方法二
\i <文件名>
#执行存储在外部文件中的sq]语句或命令
[postgres@rocky8 ~]$psql
postgres=# \i hellodb.sql
3.4）连接管理
3.4.1）访问控制配置文件
在PostgreSQL中，带有一个网络防火墙的功能的文件pg_hba.conf,可以控制允许设置哪些IP的机器访问数据库服务器。
HBA的意思是host-based authentication，也就是基于主机的认证,即实现PostgreSQL防火墙功能
initdb初始化数据目录时，会生成一个默认的pghba.conf文件。
pg_hba.conf文件为pg实例的防火墙配置文件。配置文件格式分为5部分:
TYPEDATABASE USER ADDRESS METHOD
范例:
#如果一台机器只给数据库使用，而没有其他用途，则可以在 pg_hba.conf 中加上下面一行配置:
local all all trust
#该配置表示在这台机器上，任何操作系统的用户都可以使用任何数据库用户(包括数据库超级用户)连接到数据库而不需要任何密码。因为这台主机只供数据库使用，可以把不用的操作系统用户都禁止掉,以保证安全性。
#如果数据库中有一个用户“dba”，操作系统中也有一个用户“dba#在操作系统"dba”用户下连接数据库不需要密码验证的设置方法:
local all dba ident
#如果想在数据库主机上使用密码验证，可以使用下面的配置项:
local all all md5
#如果想让其他主机的连接都使用md5密码验证，则使用如下配置:
host all all 0.0.0.0/0 md5
#允许用户通过10.0.0.0/24的远程主机进行md5验证登录
#TYPE DATABASE USER ADDRESS METHOD
host all all 10.0.0.0/24 md5
#允许用户 tang 通过任意远程主机进行md5验证登录test数据库
#TYPE DATABASE USER ADDRESS METHOD
host all tang 0.0.0.0/0 md5
3.4.2）打开远程连接
默认安装完的PG只监听local。如果要远程连接，需要监听对外提供服务的IP地址。 范例: 实现远程连接
$ psql
psql (15.12)
Type "help" for help.
postgres=# ALTER USER postgres with password '123456';
ALTER ROLE
root@pg-1:~# vim /data/pgsql/postgresql.conf
...
listen_addresses = '0.0.0.0' # what IP address(es) to listen on;
...
root@pg-1:~# vim /data/pgsql/pg_hba.conf
...
# IPv6 local connections:
host all all ::1/128 trust
host all all 0.0.0.0/0 md5 # 允许数据库中所有用户通过任何地址进行密码连接
#重启服务生效
postgres@pg-1:~$ pg_ctl restart -mf # 也可以 pg_ctl restart
范例: 利用.pgpass文件实现免密码连接远程posgresql
[root@rocky8 ~]#vim .pgpass
#hostname:port:database:username:password
10.0.0.200:5432:testdb:postgres:123456
[root@rocky8 ~]#chmod 600 .pgpass
[root@rocky8 ~]#11 .pgpass
-rw-------1 root root 8l Dec 30 10:04 .pgpass
#psq1默认连接本机，需要指定和.pgpass文件内容相匹配信息才可以使用.pgpass文件连接
[root@rocky8 ~]# psq1 -h 10.0.0.200 -U postgres -d testdb -w
..
testdb=# \c
You are now connected to database "testdb" as user "postgres"
testdb=#
3.5）常用操作
3.5.1）查看psql帮助
#列出psq1帮助用法
help
#列出以\开头的命令,即psql的命令
\?
#列出所有SQL命令的帮助，注意:SQL语句必须以
\h
#查看指定SQL的帮助
\h create database
\help create user
3.5.2）数据库的创建和删除
创建数据库可以使用SQL语句 create database 实现,也可以利用 createdb 命令创建数据库
创建数据库
#方法1
[root@ubuntu2204 ~]# createdb -h 10.0.0.200 -p 5432 -U postgres testdb
Password:
#方法2
postgres@ubuntu2204:~$
psq1postgres=# create database testdb;
删除数据库
postgres@ubuntu2204:~$psql
postgres=# drop database testdb;
3.5.3）管理和查看模式
一个数据库包含一个或多个已命名的模式，模式又包含表。模式还可以包含其它对象，包括数据类型、函数、操作符等。同一个对象名可以在不同的模式里使用而不会导致冲突;比如，schema1和schema2都可以包含-一个名为test的表
#创建模式
create schema schema_name;
#删除模式
drop schema schema_name;
#列出所有schema
postgres=# \dn
postgres=# create database testdb;
CREATE DATABASE
postgres=# \c testdb
testdb=# create schema schema_test;
testdb=# create table schema_test.t1(id int);
CREATE TABLE
testdb=# \dn
List of schemas
Name | Owner
-------------+-------------------
public | pg_database_owner
schema_test | postgres
(2 rows)
testdb=# \dt schema_test.*
List of relations
Schema | Name | Type | Owner
-------------+------+-------+----------
schema_test | t1 | table | postgres
(1 row)
3.5.4）查看和连接数据库
#列出所有数据库名,相当于MySQL中的show databases
testdb=# \l
#显示数据库详细信息，比如大小
testdb=# \l+
#查看当前连接信息
testdb=# \c
You are now connected to database "testdb" as user "postgres".
#查看当前连接详细信息
testdb=# \conninfo
You are connected to database "testdb" as user "postgres" via socket in "/tmp" at port "5432".
#连接数据库,相当于use
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
3.5.5）管理表
PostgreSQL 支持多种数据类型实现表结构的创建
testdb=# \c testdb ;
testdb=# create table tb1 (id serial primary key,name text);
testdb=# insert into tb1 (name) select(md5(random()::text)) from generate_series(2,10);
testdb=# select * from tb1;
id | name
----+----------------------------------
1 | 9a34896c9bf93906ef6262ca7f747def
2 | 7e6b597084579ad5b17792e5a3fcd365
3 | 91a8446ff6c28959540bd1ad3d3fd89c
4 | e3d45ace7a951dedf87f3fc1141a8661
5 | 306e49b45f645c094b30606e36375d7c
6 | e7e102aeffadd3978da98e65d720f442
7 | eb3df6c9cbedbcaa783e5edc45827c76
8 | f8dff7f866d7690147a772f73ace5930
9 | e39f839e161bd514c7e60ec683ff8dca
# PostgresQL中插入100万条记录只需要花2s
# 打开时间计算
testdb=# \timing on
testdb=# insert into tb1 (name) select(md5(random()::text)) from generate_series(2,1000000);
INSERT 0 999999
Time: 2351.579 ms (00:02.352)
#复制表结构，不复制数据
testdb=# create table tb2 ( like tb1 );
CREATE TABLE
Time: 2.549 ms
testdb=# \d tb2
Table "public.tb2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
name | text | | |
testdb=# \d tb1
Table "public.tb1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------------------------------
id | integer | | not null | nextval('tb1_id_seq'::regclass)
name | text | | |
Indexes:
"tb1_pkey" PRIMARY KEY, btree (id)
testdb=# select * from tb2 ;
id | name
----+------
(0 rows)
3.5.6）查看表和表信息
#列出所有表,视图，序列
\d
#列出public的schema中所有的表名,相当于show tabledt
\dt
#查看t1的表信息
\dt t1
#支持通配符*和?，以下显示所有t开头的表
\dt t*
#列出myschema模式的表结构
\dt myschema.*
#查看t1的表结构，相当于desc
\d t1
#列出所有表信息,包括大小
testdb=# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+------------+-------------
public | tb1 | table | postgres | permanent | heap | 65 MB |
public | tb2 | table | postgres | permanent | heap | 8192 bytes |
#列出表信息的大小信息
testdb=# \dt+ tb2
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+------------+-------------
public | tb2 | table | postgres | permanent | heap | 8192 bytes |
(1 row)
# 查看所有表
testdb=# select * from pg_tables;
查看表对应的文件径
testdb=# select relid from pg_stat_all_tables where relname='tb2';
relid
-------
16404
(1 row)
Time: 2.637 ms
3.5.7）表的CRUD
SQL的CRUD，即Insert,update,delete,select 四条语句范例:
testdb=# create table tb1 (id serial , name varchar(10));
CREATE TABLE
Time: 0.870 ms
# 查看表结构
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('tang');
INSERT 0 1
Time: 1.127 ms
testdb=# insert into tb1 (name) values('hao');
INSERT 0 1
Time: 0.575 ms
# 查询数据
testdb=# select * from tb1;
id | name
----+------
1 | tang
2 | hao
(2 rows)
Time: 0.171 ms
# 修改数据
testdb=# update tb1 set name='li' where id=2;
UPDATE 1
Time: 0.672 ms
testdb=# select * from tb1;
id | name
----+------
1 | tang
2 | li
(2 rows)
Time: 0.148 ms
# 删除数据
testdb=# delete from tb1 where id=2;
DELETE 1
Time: 0.787 ms
testdb=# select * from tb1;
id | name
----+------
1 | tang
(1 row)
Time: 0.172 ms
# 清空表
testdb=# select * from tb1;
id | name
----+------
(0 rows)
Time: 0.244 ms
范例: 查看表的列信息及大小
testdb=# \d tb1
Table "public.tb1"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------------------------------
id | integer | | not null | nextval('tb1_id_seq'::regclass)
name | character varying(10) | | |
3.5.8）索引管理
创建和删除索引
testdb=# create table tb3(id int,info text,crt_time timestamp);
CREATE TABLE
Time: 2.222 ms
testdb=# insert into tb3 select generate_series(1,100000),(md5(random()::text)),clock_timestamp();
INSERT 0 100000
Time: 90.946 ms
testdb=# select * from tb3 limit 3;
id | info | crt_time
----+----------------------------------+----------------------------
1 | 58d2c3fa4da4e23456f19c8eb1c38e25 | 2025-02-25 21:36:07.874925
2 | 56abe68dc73b6a2e768129dff49223b6 | 2025-02-25 21:36:07.874999
3 | 91388affda276267c5ea770d615bf0d5 | 2025-02-25 21:36:07.875002
(3 rows)
Time: 0.182 ms
#创建索引
testdb=# create index idx_tb3_id on tb3(id);
CREATE INDEX
Time: 125.913 ms
testdb=# \d tb3
Table "public.tb3"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
id | integer | | |
info | text | | |
crt_time | timestamp without time zone | | |
Indexes:
"idx_tb3_id" btree (id)
#删除索引
testdb=# drop index idx_tb3_id ;
DROP INDEX
Time: 1.014 ms
使用索引
testdb=# create index idx_tb3_id on tb3(id);
CREATE INDEX
Time: 28.773 ms
# 使用索引查
testdb=# explain analyze select * from tb3 where id = 9999;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
------
Index Scan using idx_tb3_id on tb3 (cost=0.29..8.31 rows=1 width=45) (actual time=0.008..0.009 rows=1 loo
ps=1)
Index Cond: (id = 9999)
Planning Time: 0.124 ms
Execution Time: 0.019 ms
(4 rows)
Time: 0.344 ms
# 不使用索引查
testdb=# explain analyze select * from tb3 where info = '43f08dd1dff9644b2bfdeba4faaf2d15';
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on tb3 (cost=0.00..2185.00 rows=1 width=45) (actual time=0.064..6.216 rows=1 loops=1)
Filter: (info = '43f08dd1dff9644b2bfdeba4faaf2d15'::text)
Rows Removed by Filter: 99999
Planning Time: 0.034 ms
Execution Time: 6.227 ms
(5 rows)
Time: 6.548 ms
3.5.9）表空间
#列出所有表空间,实际上PostgresQL中的表空间就是对应一个目录，放在这个表空间的表，就是把表的数据文件放到这个表空间下。
testdb=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)
# 创建表空间
postgres@pg-1:~$ mkdir /tmp/tbs1
postgres=# create tablespace tbsl location '/tmp/tbs1';
CREATE TABLESPACE
postgres=# create table tb5(id int) tablespace tbsl;
CREATE TABLE
# 数据就在 /tmp/tbs1/
postgres=# select * from pg_relation_filepath('tb5');
pg_relation_filepath
-----------------------------------------
pg_tblspc/16424/PG_15_202209061/5/16425
postgres@pg-1:~$ tree /tmp/tbs1/
/tmp/tbs1/
└── PG_15_202209061
└── 5
└── 16425
3.5.10）查看系统信息
#查看版本信息
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 15.12 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)
#查看数据库启动时间
postgres=# select pg_postmaster_start_time();
pg_postmaster_start_time
-------------------------------
2025-02-25 20:37:44.521253+08
(1 row)
#查看加载配置文件时间
postgres=# select pg_conf_load_time();
pg_conf_load_time
-------------------------------
2025-02-25 20:37:44.232002+08
(1 row)
#查看时区和时间
postgres=# show timezone;
TimeZone
----------
PRC
(1 row)
#临时修改
postgres=# set timezone='Asia/shanghai';
SET
#永久修改时间
postgres@pg-1:~$ vim /data/pgsql/postgresql.conf
timezone = 'Asia/shanghai'
postgres@pg-1:~$ pg_ctl reload
server signaled
postgres=# select now();
now
-------------------------------
2025-02-25 22:03:13.988801+08
(1 row)
#查看当前指定配置
postgres=# show max_connections;
max_connections
-----------------
100
(1 row)
3.5.11）查看用户和权限
#查看所有用户\du或\dg
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
#查看当前用户
postgres=# select user;
user
----------
postgres
(1 row)
#显示表,视图,序列的权限分配情况
postgres=# \z
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | tb5 | table | | |
(1 row)
3.6）用户和角色
3.6.1）创建用户和角色
在PostgreSQL中，用户与角色是没有区别的, 用户和角色可以用来实现以下功能:
-
用来登录数据库实例、
-
管理数据库对象
创建用户与角色的语法如下:
CREATE USER name [[WITH] option [...]
CREATE ROLE name [[WITH] option [...]
#上面两个命令都可以创建用户,
# 不同的是CREATE USER 创建的用户默认可以登录,而 CREATE ROLE不可以登录
#除了CREATE USER 默认创建出来的用户有LOGIN 的权限，而 CREATE ROLE 创建出来的用户没有“LOGIN"的权限之外，CREATE RULE 与 CREATE USER没有其他任何的区别。
3.6.2）用户管理案例
#查看帮助
\h create user
\h alter user
\h drop user
\h create role
\h alter role
\h drop role
#以下两个命令用法相似
create user #创建的用户默认可以连接
create role #创建的用户默认无法连接
#修改用户
alter user
#删除用户
drop user
#显出所有用户和角色
#\du和\dg命令等价。原因是在PostgresQL数据库中用户和角色不分的。
范例
#创建可以登录的用户和密码
postgres=# CREATE USER tang WITH PASSWORD '123456';
#创建不可登录用户
postgres=# CREATE ROLE hao WITH PASSWORD '123456';
#创建管理员
postgres=# CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD '123456';
#创建复制用户
postgres=# CREATE USER rePI REPLICATION LOGIN ENCRYPTED PASSWORD '123456';
#修改密码
postgres=# ALTER USER admin with password '654321';
#修改权限和密码
postgres=# alter user hao with nologin password '258369';
#删除用户
postgres=# DROP USER hao;
#查看所有用户
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
repi | Replication | {}
tang | | {}
# 查看指定用户
postgres=# \du admin
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
admin | Superuser | {}
3.6.3）权限管理
一个用户的权限分为两类，一类是在创建用户时就指定的权限，这些权限如下:
-
超级用户的权限
-
创建数据库的权限
-
是否允许LOGIN的权限
以上这些权限是创建用户时指定的，后续可使用ALTER ROLE命令来修改。
还有一类权限，是由命令GRANT和 REVOKE来管理的，这些权限如下:
-
在数据库中创建模式(SCHEMA)
-
允许在指定的数据库中创建临时表连接某个数据库
-
在模式中创建数据库对象，如创建表、视图函数等
-
在一些表中做SELECT、UPDATE、INSERRDELETE等操作等
#GRANT命令有两个作用
#1.让某个用户成为某个角色的成员，从而使其拥有角色的权限
#2.把某些数据库逻辑结构对象的操作权限赋予某个用户(或角色)
PostgresQL中的权限是按以下几个层次进行管理的:
-
cluster权限:实例权限通过pg_hba.conf配置
-
管理赋在用户特殊属性上的权限，如超级用户的权限、创建数据库的权限、创建用户的权限Login权限等
-
在数据库中创建模式的权限
-
表空间权限: 通过grant/revoke控制权限操作表,物化视图,索引等
-
在模式中创建数据库对象的权限，如创建表、创建索引，等等
-
查询表、往表中插入数据、更新表、删除表中数据的权限
-
操作表中某些字段的权限
3.6.4）权限案例
#授权创建新数据库
postgres=# alter user tang with CREATEDB;
#database权限设置 (授权 testdb 数据库)
postgres=# GRANT create ON DATABASE testdb To tang;
#schema权限
ALTER SCHEMA tang OWNER to tang;
postgres=# \dn
List of schemas
Name | Owner
-------------+-------------------
public | pg_database_owner
schema_name | postgres
(2 rows)
postgres=# ALTER SCHEMA schema_name OWNER to tang;
#创建 test的schema指定所有者为tang
postgres=# CREATE SCHEMA IF NOT EXISTS teSt AUTHORIZATION tang;
#object权限(授权schema testdb.tb1 有 CRUD 权限)
GRANT select,insert,update,delete ON testdb.tb1 To tang;
#创建数据库并指定所有者的用户
create user wangs with password '123456';
CREATE DATABASE zabbix OWNER wangs;
范例: 创建业务用户和授权
postgres=# create database pinxixi;
postgres=# \c pinxixi
pinxixi=# create user xiaoming with password '123456';
# 方法一
pinxixi=# create schema xiaoming ;
pinxixi=# ALTER SCHEMA xiaoming OWNER to xiaoming;
# 方法二
pinxixi=# CREATE SCHEMA AUTHORIZATION xiaoming;
# 方法三
pinxixi=# GRANT select,insert,update,delete ON ALL TABLES IN SCHEMA xiaoming to xiaoming
范例：创建只读用户
# 创建独用户
CREATE USER readonly with password '123456';
# 把在 public 的 schema 下现有的所有表的 SELECT 权限赋给用户readonly
GRANT SELECT ON ALL TABLES IN SCHEMA public To readonly;
文章评论