PostgreSQL 命令

字符串使用单引号(')包裹

登录

1
2
3
4
5
#psql -h 服务器 -U 用户名  -d 数据库 -p 端口地址  // -U 是大写
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432

$ psql (连接数据库,默认用户和数据库都是postgres)
#相当于系统用户postgres以同名数据库用户的身份,登录数据库,这是不用输入密码的。如果一切正常,系统提示符会变为"postgres=#",表示这时已经进入了数据库控制台。

数据库操作

1
2
3
4
5
CREATE DATABASE mydb;   # 创建数据库
DROP DATABASE <dbname> # 删除数据库

\l # 查看所有数据库
\c mydb # 切换当前数据库

数据库表操作

创建表格时每列都必须使用数据类型。PotgreSQL 中主要有三类数据类型:

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

数值

常见数值类型包括:

名字 存储长度 描述 范围
smallint 2 字节 小范围整数 -32768 到 +32767
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:时间;

其他数据类型类型还有布尔值 boolean (true 或 false),货币数额 money 和 几何数据等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 创建表
CREATE TABLE test(
id int,body varchar(100)
);
# 给表和字段添加注释
COMMENT ON TABLE test IS '这是一个表的注释'; # 给表和字段添加注释
COMMENT ON COLUMN test.id IS '这是一个id的注释'; # 给表和字段添加注释


# 向表中插入数据
insert into test(id,body) values(1,'hello,postgresql');


# 查看当前数据库下所有表
\d


# 查看表结构,相当于 desc
\d test

PostgreSQL 使用序列来标识字段的自增长,数据类型有 smallserial、serial 和 bigserial。这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性。

SMALLSERIAL、SERIAL 和 BIGSERIAL 范围:

伪类型 存储大小 范围
SMALLSERIAL 2字节 1 到 32,767
SERIAL 4字节 1 到 2,147,483,647
BIGSERIAL 8字节 1 到 922,337,2036,854,775,807

示例

1
2
3
4
5
6
7
8
9
10
11
12
# 创建表
CREATE TABLE COMPANY(
ID SERIAL PRIMARY KEY,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

# 插入数据
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ('Paul', 32, 'California', 20000.00);
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ('Allen', 25, 'Texas', 15000.00);

Schema

PostgreSQL 模式(SCHEMA)可以看着是一个表的集合。

一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突,例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势:

  • 允许多个用户使用一个数据库并且不会互相干扰。
  • 将数据库对象组织成逻辑组以便更容易管理。
  • 第三方应用的对象可以放在独立的模式中,这样它们就不会与其他对象的名称发生冲突。

模式类似于操作系统层的目录,但是模式不能嵌套。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 创建schema: 
create schema myschema;

create table myschema.company(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

# 删除schema:
drop schema myschema;

# 删除一个模式以及其中包含的所有对象:
DROP SCHEMA myschema CASCADE;

备份PostgreSQL数据库

如果在生产环境中使用 PostgreSQL,请务必采取预防措施以确保用户的数据不会丢失。

单数据库

PostgreSQL 提供了pg_dump 实用程序来简化备份单个数据库的过程。必须以对要备份的数据库具有读取权限的用户身份运行此命令。

postgres 用户身份登录:

1
sudo su - postgres

通过运行以下命令将数据库的内容转储到文件中。替换dbname为要备份的数据库的名称。

1
pg_dump dbname > dbname.bak

生成的备份文件dbname.bak可以使用scp传输到另一台主机,也可以存储在本地以供以后使用。

要演示恢复丢失的数据,请删除示例数据库并在其位置创建一个空数据库:

使用psql恢复数据库

1
psql test < dbname.bak

备份格式有几种选择:

  • *.bak:压缩二进制格式
  • *.sql:明文转储
  • *.tar:tarball

注意:默认情况下,PostgreSQL将忽略备份过程中发生的任何错误。这可能导致备份不完整。要防止这种情况,可以使用-1选项运行pg_dump命令。 这会将整个备份过程视为单个事务,这将在发生错误时阻止部分备份。

所有数据库

由于 pg_dump 一次只创建一个数据库的备份,因此它不会存储有关数据库角色或其他群集范围配置的信息。 要存储此信息并同时备份所有数据库,可以使用pg_dumpall

创建备份文件:

1
pg_dumpall > pg_backup.bak

从备份还原所有数据库:

1
psql -f pg_backup.bak postgres

示例:

1
2
3
4
5
6
7
8
9
#备份数据库
$ pg_dump -U postgres -f /tmp/postgres.sql postgres (导出postgres数据库保存为postgres.sql)
$ pg_dump -U postgres -f /tmp/postgres.sql -t test postgres (导出postgres数据库中表test的数据)
$ pg_dump -U postgres -F t -f /tmp/postgres.tar postgres (导出postgres数据库以tar形式压缩保存为postgres.tar)

#恢复数据库
$ psql -U postgres -f /tmp/postgres.sql bk01 (恢复postgres.sql数据到bk01数据库)
#pg_restore -- 从pg_dump创建的备份文件中恢复PostgreSQL数据库,用于恢复由pg_dump 转储的任何非纯文本格式中的PostgreSQL数据库。
$ pg_restore -U postgres -d bk01 /tmp/postgres.tar (恢复postgres.tar数据到bk01数据库)

用户操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 创建用户并设置密码
CREATE USER 'username' WITH PASSWORD 'password';
CREATE USER 'test' WITH PASSWORD 'test';


# 修改用户密码
ALTER USER 'username' WITH PASSWORD 'password';


#数据库授权,赋予指定账户指定数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE 'dbname' TO 'username';
#将数据库 mydb 权限授权于 test
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;
#但此时用户还是没有读写权限,需要继续授权表
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xxx;
#注意,该sql语句必须在所要操作的数据库里执行


#移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb from test


#删除用户
drop user test


# 查看用户
\du

注意: pg_hba.conf配置中的第一项设置的意思是:本地用户通过unix socket登陆时,使用peer方式认证。

1
2
# "local" is for Unix domain socket connections only
local all all peer

peer 是用 PostgreSQL 所在的操作系统上的用户登陆。

peer 方式中,client 必须和 PostgreSQL 在同一台机器上。只要当前系统用户和要登陆到 PostgreSQL 的用户名相同,就可以登陆。

在刚部署 PostgreSQL 之后,切换到系统的 postgres 用户后,直接执行 psql 就能进入 PostgreSQL 就是这个原因(当前系统用户为名 postgre,PostgreSQL 中的用户名也是postgre)。

在PostgreSQL中创建一个没有密码的用户:

1
create user local_user1;

在PostgreSQL所在的机上,创建一个同名的用户:

1
useradd local_user1;

切换到 local_user1 用户后,就可以直接通过 unix_socket 登陆 PostgreSQL:

1
2
3
4
5
6
# su - local_user1
[local_user1@10 ~]$ psql postgres
psql (9.2.24)
Type "help" for help.

postgres=>

注意:要指定数据库名,如果不指定默认使用与用户同名的数据库。

peer不是常用的方式!最常用的方式是通过密码远程登陆。

PostgreSQL 角色管理

在 PostgreSQL 里没有区分用户和角色的概念,”CREATE USER” 为 “CREATE ROLE” 的别名,这两个命令几乎是完全相同的,唯一的区别是”CREATE USER” 命令创建的用户默认带有LOGIN属性,而”CREATE ROLE” 命令创建的用户默认不带LOGIN属性

创建 david 角色和 sandy 用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
postgres=# CREATE ROLE david;  //默认不带LOGIN属性
CREATE ROLE
postgres=# CREATE USER sandy;  //默认具有LOGIN属性
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
david | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}

postgres=#
postgres=# SELECT rolname from pg_roles ;
rolname
----------
postgres
david
sandy
(3 rows)

postgres=# SELECT usename from pg_user; //角色david 创建时没有分配login权限,所以没有创建用户
usename
----------
postgres
sandy
(2 rows)

postgres=#

角色属性

属性 说明
login 只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。
superuser 数据库超级用户
createdb 创建数据库权限
createrole 允许其创建或删除其他普通的用户角色(超级用户除外)
replication 做流复制的时候用到的一个用户属性,一般单独设定。
password 在登录时要求指定密码时才会起作用,比如md5或者password模式,跟客户端的连接认证方式有关
inherit 用户组对组员的一个继承标志,成员可以继承用户组的权限特性

创建用户时赋予角色属性

如果要在创建角色时就赋予角色一些属性,可以使用下面的方法。

首先切换到 postgres 用户。

创建角色 bella 并赋予其 CREATEDB 的权限。

1
2
3
4
5
6
7
8
9
10
11
12
postgres=# CREATE ROLE bella CREATEDB ;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB, Cannot login | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}

postgres=#

创建角色renee 并赋予其创建数据库及带有密码登录的属性。

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres=# CREATE ROLE renee CREATEDB PASSWORD 'abc123' LOGIN;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB, Cannot login | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create DB | {}
sandy | | {}

postgres=#

测试renee 角色

1
2
3
4
5
postgres@CS-DEV:~> psql -U renee -d postgres
psql (9.1.0)
Type "help" for help.

postgres=>

给已存在用户赋予各种权限

赋予登录权限

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres=# ALTER ROLE bella WITH LOGIN;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create DB | {}
sandy | | {}

postgres=#

赋予renee 创建角色的权限

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres=# ALTER ROLE renee WITH CREATEROLE;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}

postgres=#

控制台常用命令总结

1
2
3
4
5
6
7
8
9
\password   # 设置密码
\q # 退出
\h # 查看SQL命令的解释,比如\h select。
\? # 查看psql命令列表。
\l # 列出所有数据库。
\c [database_name] # 连接其他数据库。
\d # 列出当前数据库的所有表格。
\d [table_name] # 列出某一张表格的结构。
\du # 列出所有用户。

PostgreSQL 命令
https://flepeng.github.io/041-PostgreSQL-21-命令-PostgreSQL-命令/
作者
Lepeng
发布于
2024年3月6日
许可协议