postgreSQL的基本使用

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

6
postgres=#

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

8
postgres=#

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

1
# 查询数据库
2
postgres=# \l
3
                                                 List of databases
4
    Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges
5
------------+----------+----------+------------+------------+------------+-----------------+-----------------------
6
 astro-blog | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
7
 mydb       | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
8

9
# 创建数据库
10
# create database 数据库名;
11
postgres=# create database mydb;
12
CREATE DATABASE
13

14
# 切换数据库
15
# \c 数据库名;
7 collapsed lines
16
postgres=# \c mydb;
17
You are now connected to database "mydb" as user "postgres".
18

19
# 删除数据库
20
# drop database 数据库名;
21
postgres=# drop database mydb;
22
DROP DATABASE

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

1
# 查询表
2
mydb-# \d
3
             List of relations
4
 Schema |    Name     |   Type   |  Owner
5
--------+-------------+----------+----------
6
 public | test        | table    | postgres
7
 public | test_id_seq | sequence | postgres
8
(2 rows)
9

10
# 查询指定表结构
11
mydb=# \d test;
12
                                    Table "public.test"
13
 Column |          Type          | Collation | Nullable |             Default
14
--------+------------------------+-----------+----------+----------------------------------
15
 id     | integer                |           | not null | nextval('test_id_seq'::regclass)
3 collapsed lines
16
 name   | character varying(255) |           |          |
17
Indexes:
18
    "test_pkey" PRIMARY KEY, btree (id)

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

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

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

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

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

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

1
postgres=# create user test with password '123456';
2
CREATE ROLE
3
# 查询用户
4
postgres=# \du
5
                                   List of roles
6
 Role name |                         Attributes                         | Member of
7
-----------+------------------------------------------------------------+-----------
8
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
9
 test      |                                                            | {}
10

11
postgres=# alter user test with password '123456789';
12
ALTER ROLE
13
# 未赋予角色权限
14
postgres=# drop user test
15
DROP ROLE
14 collapsed lines
16
# 删除已赋予角色权限的用户，需要移除账户数据库所有权限
17
postgres=# drop user test;
18
ERROR:  role "test" cannot be dropped because some objects depend on it
19
DETAIL:  privileges for database mydb
20
2 objects in database mydb
21
# 删除库权限
22
postgres=# revoke all privileges on database mydb from test;
23
REVOKE
24
# 删除表权限
25
postgres=# \c mydb;
26
mydb=# revoke all privileges on all tables in schema public from test;
27
REVOKE
28
mydb=# drop user test;
29
DROP ROLE

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

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

1
postgres@VM-12-5-debian:~$ psql -U test -d mydb;
2
Password for user test:
3
psql (15.7 (Debian 15.7-0+deb12u1))
4
Type "help" for help.
5

6
# 查看所有表
7
mydb=> \d
8
             List of relations
9
 Schema |    Name     |   Type   |  Owner
10
--------+-------------+----------+----------
11
 public | test        | table    | postgres
12
 public | test_id_seq | sequence | postgres
13
(2 rows)
14

15
# 查询表内容，提示无权限
3 collapsed lines
16
mydb=> select * from test;
17
ERROR:  permission denied for table test
18
mydb=> \q

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

1
postgres@VM-12-5-debian:~$ psql -U test -d mydb;
2
Password for user test:
3
psql (15.7 (Debian 15.7-0+deb12u1))
4
Type "help" for help.
5

6
# 有查询权限了
7
mydb=> select * from test;
8
 id | name
9
----+-------
10
  2 | jack
11
  3 | cirry
12
(2 rows)

1
postgres=# create role vip;
2
CREATE ROLE
3
postgres=# create user cirry;
4
CREATE ROLE
5
postgres=# \du
6
                                   List of roles
7
 Role name |                         Attributes                         | Member of
8
-----------+------------------------------------------------------------+-----------
9
 cirry     |                                                            | {}
10
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
11
 vip       | Cannot login                                               | {}

1
# 查看用户列表
2
postgres=# \du
3
                                   List of roles
4
 Role name |                         Attributes                         | Member of
5
-----------+------------------------------------------------------------+-----------
6
 cirry     |                                                            | {}
7
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
8
 vip       | Cannot login                                               | {}
9

10
# 给角色添加登录属性
11
postgres=# alter role vip login;
12
ALTER ROLE
13

14
# 查看用户列表
15
postgres=# \du
16 collapsed lines
16
                                   List of roles
17
 Role name |                         Attributes                         | Member of
18
-----------+------------------------------------------------------------+-----------
19
 cirry     |                                                            | {}
20
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
21
 vip       |                                                            | {}
22

23
# 创建一个角色指定登录密码和登录功能
24
postgres=# create role newuser password '123456' login;
25
CREATE ROLE
26
postgres=# \q
27

28
# 新角色登录测试
29
postgres@VM-12-5-debian:~$ psql -U newuser -d mydb;
30
Password for user newuser:
31
psql (15.7 (Debian 15.7-0+deb12u1))

1
# 查看用户
2
postgres=# select * from pg_user;
3
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
4
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
5
 postgres |       10 | t           | t        | t       | t            | ******** |          |
6
 cirry    |    68737 | f           | f        | f       | f            | ******** |          |
7

8

9
# 查看角色
10
postgres=# select * from pg_roles;
11
          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid
12
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
13
 pg_database_owner         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  6171
14
 pg_read_all_data          | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  6181
15
 pg_write_all_data         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  6182
12 collapsed lines
16
 pg_monitor                | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3373
17
 pg_read_all_settings      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3374
18
 pg_read_all_stats         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3375
19
 pg_stat_scan_tables       | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3377
20
 pg_read_server_files      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4569
21
 pg_write_server_files     | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4570
22
 pg_execute_server_program | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4571
23
 pg_signal_backend         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4200
24
 pg_checkpoint             | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4544
25
 postgres                  | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |    10
26
 vip                       | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 68736
27
 cirry                     | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 68737

1
# 修改当前登录用户密码
2
postgres=> \password
3
Enter new password for user "newuser":
4
Enter it again:
5
# 退出登录
6
postgres=> \q
7
# 查看sql命令
8
postgres=> \h select
9
# 列出所有数据库
10
postgres=> \l
11
                                                 List of databases
12
    Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges
13
------------+----------+----------+------------+------------+------------+-----------------+-----------------------
14
 astro-blog | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
15
 gitea      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
31 collapsed lines
16
 memos      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
17

18
# 切换其他数据库
19
postgres=# \c mydb
20
You are now connected to database "mydb" as user "postgres".
21

22
# 列出当前数据库的所有表
23
mydb=# \d
24
             List of relations
25
 Schema |    Name     |   Type   |  Owner
26
--------+-------------+----------+----------
27
 public | test        | table    | postgres
28
 public | test_id_seq | sequence | postgres
29
(2 rows)
30

31
# 列出某一张表格结构
32
mydb=# \d test;
33
                                    Table "public.test"
34
 Column |          Type          | Collation | Nullable |             Default
35
--------+------------------------+-----------+----------+----------------------------------
36
 id     | integer                |           | not null | nextval('test_id_seq'::regclass)
37
 name   | character varying(255) |           |          |
38
Indexes:
39
    "test_pkey" PRIMARY KEY, btree (id)
40

41
# 列出所有用户
42
mydb=# \du
43
                                   List of roles
44
 Role name |                         Attributes                         | Member of
45
-----------+------------------------------------------------------------+-----------
46
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}