postgreSQL的基本使用 - Cirry

Linux连接数据库

本机连接

root@VM-12-5-debian:~# su - postgres  # 切换用户环境
postgres@VM-12-5-debian:~$ psql  # 连接数据库，默认用户和数据库都是postgres
psql (15.7 (Debian 15.7-0+deb12u1))
Type "help" for help.
postgres=#

连接其他数据库

# psql -h 服务器地址 -p 数据库端口号 -U 用户名
cirry@VM-12-5-debian:~$ psql -h xxxx.xxxx.xxx -p 5432 -U postgres
Password for user postgres:
psql (15.7 (Debian 15.7-0+deb12u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=#

退出连接

postgres=# \q
# 或者 直接输入 ctrl + d

库操作

# 查询数据库
postgres=# \l
                                                 List of databases
    Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges
------------+----------+----------+------------+------------+------------+-----------------+-----------------------
 astro-blog | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
 mydb       | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
# 创建数据库
# create database 数据库名;
postgres=# create database mydb;
CREATE DATABASE
# 切换数据库
# \c 数据库名;
7 collapsed lines
postgres=# \c mydb;
You are now connected to database "mydb" as user "postgres".
# 删除数据库
# drop database 数据库名;
postgres=# drop database mydb;
DROP DATABASE

表操作

Postgres中三类主要数据类型：

数值数据类型
字符串数据类型
时间/日期数据类型

数值类型：

名称	存储长度	描述	范围
smallint	2字节	小范围整数	-32768 ~ +32768
integer	4字节	常用整数	-2147483648 ~ +2147483647
bigint	8字节	大范围整数	-9223372036854775808 ~ +9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前131072位 ~ 小数点后16383位
numeric	可变长	用户指定的精度，精确	小数点前131072位 ~ 小数点后16383位
real	4字节	可变精度，不精确	6位十进制数字精度
double	8字节	可变精度，不精确	15位十进制数字精度

字符串类型：

char(size), character(size): 固定长度字符串，size规定需存储的字符数，由右边的空格补齐；
varchar(size), character varying(size): 可变长度字符串，size规定需存储的字符数；
text: 可变长度字符串。

日期/时间类型：

timestamp: 日期和时间，时间戳；
date: 日期，无时间；
time: 时间。

自增标识字段：

伪类型	存储长度	范围
smallserial	2字节	1 ~ 32768
serial	4字节	1 ~ 2147483647
bigserial	8字节	1 ~ 9223372036854775807

其它的数据类型还有布尔值，货币数额和几何数据等等。

创建表

mydb=# create table test(id serial primary key, name varchar(255));
CREATE TABLE

查询表和表结构

# 查询表
mydb-# \d
             List of relations
 Schema |    Name     |   Type   |  Owner
--------+-------------+----------+----------
 public | test        | table    | postgres
 public | test_id_seq | sequence | postgres
(2 rows)
# 查询指定表结构
mydb=# \d test;
                                    Table "public.test"
 Column |          Type          | Collation | Nullable |             Default
--------+------------------------+-----------+----------+----------------------------------
 id     | integer                |           | not null | nextval('test_id_seq'::regclass)
3 collapsed lines
 name   | character varying(255) |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

插入数据

mydb=# insert into test(name) values('jack');
INSERT 0 1
mydb=# select * from test;
 id | name
----+------
  1 | jack
(1 row)

更新数据

mydb=# update test set name='mayun' where id=1;
UPDATE 1

删除数据

mydb=# delete from test where id = 1;
DELETE 1

schema

mydb=# create schema myschema;
CREATE SCHEMA
mydb=# create table myschema.test(id serial primary key, name varchar(255));
CREATE TABLE

备份数据库

官方文档：pg_dump

备份的格式有几种：

*.bak: 压缩二进制格式
*.sql: 明文格式
*.tar: 压缩格式

# 备份，注意在postgres用户的shell中执行，不是在pgsl中执行
root@VM-12-5-debian:~# su - postgres
# pg_dump -f 备份文件路径  数据库名
postgres@VM-12-5-debian:~$ pg_dump -f /tmp/mydb.sql mydb
postgres@VM-12-5-debian:~$ ls /tmp
mydb.sql

恢复数据库

注意，恢复数据库前，需要提前创建好数据库。

另外，执行恢复命令是在postgres用户的shell中，而不是在psql控制台中。

root@VM-12-5-debian:~# su - postgres  # 切换用户
postgres@VM-12-5-debian:~$ psql # 进入psql控制台
postgres=# create database mydb; # 创建数据库
postgres=# \q # 退出psql控制台
# psql -f 恢复文件路径 数据库名
postgres@VM-12-5-debian:~$ psql -f /tmp/mydb.sql mydb # 执行恢复命令

用户操作

增删改查

postgres=# create user test with password '123456';
CREATE ROLE
# 查询用户
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test      |                                                            | {}
postgres=# alter user test with password '123456789';
ALTER ROLE
# 未赋予角色权限
postgres=# drop user test
DROP ROLE
14 collapsed lines
# 删除已赋予角色权限的用户，需要移除账户数据库所有权限
postgres=# drop user test;
ERROR:  role "test" cannot be dropped because some objects depend on it
DETAIL:  privileges for database mydb
2 objects in database mydb
# 删除库权限
postgres=# revoke all privileges on database mydb from test;
REVOKE
# 删除表权限
postgres=# \c mydb;
mydb=# revoke all privileges on all tables in schema public from test;
REVOKE
mydb=# drop user test;
DROP ROLE

使用其他用户登录

postgres=# \q
postgres@VM-12-5-debian:~$ psql -U test -d mydb
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "test"

提示认证失败，需要修改配置文件。

# vim /etc/postgresql/[数据库版本号]/main/pg_hba.conf
root@VM-12-5-debian:~# vim /etc/postgresql/15/main/pg_hba.conf
# ... 找到下面这一行修改后面的 peer 为 md5
# "local" is for Unix domain socket connections only
local   all             all                                     peer
local   all             all                                     md5
# ...
# 修改完成之后重启postgres
root@VM-12-5-debian:~# systemctl restart postgresql

再使用新创建的用户登录：

postgres@VM-12-5-debian:~$ psql -U test -d mydb;
Password for user test:
psql (15.7 (Debian 15.7-0+deb12u1))
Type "help" for help.
# 查看所有表
mydb=> \d
             List of relations
 Schema |    Name     |   Type   |  Owner
--------+-------------+----------+----------
 public | test        | table    | postgres
 public | test_id_seq | sequence | postgres
(2 rows)
# 查询表内容，提示无权限
3 collapsed lines
mydb=> select * from test;
ERROR:  permission denied for table test
mydb=> \q

用户授权

# postgres用户授权库权限给test
postgres=# grant all privileges on database mydb to test;
GRANT
# 切换到mydb库中
postgres=# \c mydb;
You are now connected to database "mydb" as user "postgres".
# 将mydb的表权限授权给test用户
mydb=# grant all privileges on all tables in schema public to test;
GRANT
mydb=# \q

新用户权限测试

postgres@VM-12-5-debian:~$ psql -U test -d mydb;
Password for user test:
psql (15.7 (Debian 15.7-0+deb12u1))
Type "help" for help.
# 有查询权限了
mydb=> select * from test;
 id | name
----+-------
  2 | jack
  3 | cirry
(2 rows)

角色管理

postgresql里没有区分用户和角色的概念。

postgres=# create role vip;
CREATE ROLE
postgres=# create user cirry;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 cirry     |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 vip       | Cannot login                                               | {}

区别：创建的角色是没有登录功能的。

角色属性

属性	说明
login	只有具有login属性的角色可以登录数据库
superuser	数据库超级用户
createdb	创建数据库权限
createrole	允许其创建或删除其他普通用户角色（超级用户除外）
replication	流复制用到的用户属性，一般单独设定
password	在登录时使用指定密码登录
inherit	用户组对组员的继承标志，成员可以继承用户组的权限特性

创建用户

创建用户外加赋予角色属性示例：

# 查看用户列表
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 cirry     |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 vip       | Cannot login                                               | {}
# 给角色添加登录属性
postgres=# alter role vip login;
ALTER ROLE
# 查看用户列表
postgres=# \du
16 collapsed lines
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 cirry     |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 vip       |                                                            | {}
# 创建一个角色指定登录密码和登录功能
postgres=# create role newuser password '123456' login;
CREATE ROLE
postgres=# \q
# 新角色登录测试
postgres@VM-12-5-debian:~$ psql -U newuser -d mydb;
Password for user newuser:
psql (15.7 (Debian 15.7-0+deb12u1))

其他功能

# 查看用户
postgres=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          |
 cirry    |    68737 | f           | f        | f       | f            | ******** |          |
# 查看角色
postgres=# select * from pg_roles;
          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 pg_database_owner         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  6171
 pg_read_all_data          | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  6181
 pg_write_all_data         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  6182
12 collapsed lines
 pg_monitor                | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3373
 pg_read_all_settings      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3374
 pg_read_all_stats         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3375
 pg_stat_scan_tables       | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3377
 pg_read_server_files      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4569
 pg_write_server_files     | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4570
 pg_execute_server_program | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4571
 pg_signal_backend         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4200
 pg_checkpoint             | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4544
 postgres                  | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |    10
 vip                       | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 68736
 cirry                     | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 68737

常用命令

# 修改当前登录用户密码
postgres=> \password
Enter new password for user "newuser":
Enter it again:
# 退出登录
postgres=> \q
# 查看sql命令
postgres=> \h select
# 列出所有数据库
postgres=> \l
                                                 List of databases
    Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges
------------+----------+----------+------------+------------+------------+-----------------+-----------------------
 astro-blog | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
 gitea      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
31 collapsed lines
 memos      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
# 切换其他数据库
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
# 列出当前数据库的所有表
mydb=# \d
             List of relations
 Schema |    Name     |   Type   |  Owner
--------+-------------+----------+----------
 public | test        | table    | postgres
 public | test_id_seq | sequence | postgres
(2 rows)
# 列出某一张表格结构
mydb=# \d test;
                                    Table "public.test"
 Column |          Type          | Collation | Nullable |             Default
--------+------------------------+-----------+----------+----------------------------------
 id     | integer                |           | not null | nextval('test_id_seq'::regclass)
 name   | character varying(255) |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
# 列出所有用户
mydb=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

推荐订阅源

Cirry's Blog