PostgreSQL系列【2】PostgreSQL常用命令、和参数说明

PostgreSQL系列【2】PostgreSQL常用命令、和参数说明

一、角色、用户管理

1、创建、删除角色

psql
CREATE ROLE name;
DROP ROLE name;

2、创建、删除用户，修改密码

psql
CREATE USER name WITH
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  INHERIT
  NOREPLICATION
  CONNECTION LIMIT -1
  PASSWORD 'xxxxxx';
  
 DROP USER name;
 \password

二、数据库管理

1、创建数据库、删除数据库

CREATE DATABASE name OWNER username TEMPLATE template0;
DROP DATABASE name;

2、数据库备份

pg_dump -h IP -p PORT  -U USERNAME-F c -b -v -f SAVEPATH  DBNAME

3、数据库恢复

psql -h IP -p PORT -U USERNAME-d DBNAME< '备份文件路径'

4、常用控制台命令

\password           设置密码。
\q                  退出。
\h                  查看SQL命令的解释，比如\h select。
\?                  查看psql命令列表。
\l                  列出所有数据库。
\c [database_name]  连接其他数据库。
\d                  列出当前数据库的所有表格。
\d [table_name]     列出某一张表格的结构。
\du                 列出所有用户。
\e                  打开文本编辑器。
\conninfo           列出当前数据库和连接的信息。

三、数据库参数设置方式

1、通过SQL影响参数

PostgreSQL提供了三个SQL命令来建立配置默认值：

1）ALTER SYSTEM

ALTER SYSTEM被用来在整个数据库集簇范围内更改 服务器配置参数。它比传统的手动编辑postgresql.conf 文件的方法更方便。ALTER SYSTEM会把给出的参数 设置写入到postgresql.auto.conf文件中，该文件会随着 postgresql.conf一起被读入。把一个参数设置为 DEFAULT或者使用RESET变体可以 把该配置项从postgresql.auto.conf文件中移除。使用 RESET ALL可以移除所有这类配置项。

用ALTER SYSTEM设置的值将在下一次重载服务器 配置后生效，那些只能在服务器启动时更改的参数则会在下一次服务器重启后生效。重载服务器配置可以通过以下做法实现：调用 SQL 函数pg_reload_conf()， 运行pg_ctl reload或者向主服务器进程发送一个SIGHUP信号。

只有超级用户能够使用ALTER SYSTEM。还有，由于 这个命令直接作用于文件系统并且不能被回滚，不允许在一个事务块或者函数中使用它。
示例：

ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM RESET wal_level;

2）ALTER DATABASE

ALTER DATABASE 更改一个数据库的属性。

示例：

更改某些针对每个数据库的设置
只有数据库拥有者或者超级用户可以更改这些设置。

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

这里 option 可以是：
    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

重命名
只有数据库拥有者或者超级用户可以重命名一个数据库，非超级用户拥有者还必须拥有CREATEDB特权。当前数据库不能被重命名（如果你需要这样做请连接到一个不同的数据库）。

ALTER DATABASE name RENAME TO new_name

更改数据库的拥有者
要修改拥有者，你必须拥有该数据库并且也是新拥有角色的一个直接或间接成员，并且你必须具有CREATEDB特权（注意超级用户自动拥有所有这些特权）。

ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

更改数据库的默认表空间
只有数据库拥有者或超级用户能够这样做，你还必须对新表空间具有创建特权。这个命令会在物理上移动位于该数据库旧的默认表空间中的任何表或索引到新的表空间中。新的默认表空间对于这个数据库必须是空的，并且不能有人可以连接到该数据库。在非默认表空间中的表和索引不受影响。

ALTER DATABASE name SET TABLESPACE new_tablespace

更改用于一个PostgreSQL数据库的运行时配置变量的会话默认值
接下来只要一个新的会话在该数据库中开始，指定的值就会成为该会话的默认值。数据库相关的默认值会覆盖出现在postgresql.conf中或者从postgres命令行接收到的设置。只有数据库拥有者或超级用户可以更改一个数据库的会话默认值。一些变量不能用这种方式设置或者只能由超级用户更改。

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

3）ALTER ROLE

ALTER ROLE 和ALTER DATABASE差不多，只是作用对象换成了数据库角色
示例：
更改一个 PostgreSQL角色的属性

ALTER ROLE role_specification [ WITH ] option [ ... ]
其中 option 可以是：
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'

更改该角色的名称
数据库超级用户能重命名任何角色。具有 CREATEROLE特权的角色能够重命名任何非超级用户角色。当前的会话 用户不能被重命名（如果需要这样做，请以一个不同的用户连接）。由于 MD5加密的口令使用角色名作为 salt，因此如果一个角色的口令是 MD5加密的，重命名该角色会清空其口令。

ALTER ROLE name RENAME TO new_name

更改一个角色的配置变量的会话默认值
可以为所有数据库设置，或者只为IN DATABASE中指定的数据库设置。如果指定的是 ALL而不是一个角色名，将会为所有角色更改该设置。把 ALL和IN DATABASE一起使用实际上和使用命 令ALTER DATABASE ... SET ...相同。

ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL


其中 role_specification 可以是：

    role_name
  | CURRENT_USER
  | SESSION_USER

2、通过配置文件影响参数

设置这些参数最基本的方法是编辑postgresql.conf文件，postgresql.conf在centos里面默认位置 /var/lib/pgsql/11/data/postgresql.conf。所以直接打开进行编辑。

vi /var/lib/pgsql/11/data/postgresql.conf

3、通过 Shell 影响参数

除了在数据库或者角色层面上设置全局默认值或者进行覆盖，你还可以通过 shell 工具把设置 传递给PostgreSQL。服务器和 libpq 客户端库都能通过 shell 接受参数值。
在服务器启动期间，可以通过-c命令行参数把参数设置传递给 postgres命令。例如：

postgres -c log_connections=yes -c log_destination='syslog'

这种方式提供的设置会覆盖通过postgresql.conf或者 ALTER SYSTEM提供的设置，因此除了重启服务器之外无法从全局上改变它们。
当通过libpq启动一个客户端会话时，可以使用PGOPTIONS 环境变量指定参数设置。这种方式建立的设置构成了会话生存期间的默认值，但是不会影响 其他的会话。由于历史原因，PGOPTIONS的格式和启动 postgres命令时用到的相似，特别是-c标志必须被指定。 例如：

env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql

通过 shell 或者其他方式，其他客户端和库可能提供它们自己的机制，以便允许用户在不直接 使用SQL命令的前提下修改会话设置。

三、数据库常用参数说明

1、listen_addresses
指定服务器在哪些 TCP/IP 地址上监听客户端连接

2、port
服务器监听的 TCP 端口；默认是 5432

3、max_connections
数据库的最大并发连接数。默认值通常是 100 个连接

4、shared_buffers
设置数据库服务器将使用的共享内存缓冲区量。默认通常是 128 兆字节（128MB）
一个合理的shared_buffers开始值是系统内存的 25%。将shared_buffers设置为超过 40% 的RAM更好。为了能把对写大量新的或改变的数据的处理分布在一个较长的时间段内， shared_buffers更大的设置通常要求对max_wal_size也做相应增加

5、max_prepared_transactions
设置可以同时处于“prepared”状态的事务的最大数目。如果你正在使用预备事务，你将希望把max_prepared_transactions至少设置为max_connections一样大，因此每一个会话可以有一个预备事务待处理。

6、checkpoint_timeout
自动 WAL 检查点之间的最长时间，默认是 5 分钟（5min）。

7、checkpoint_completion_target
指定检查点完成的目标，作为检查点之间总时间的一部分。默认是 0.5。

8、checkpoint_flush_after
在执行检查点时，只要有checkpoint_flush_after字节被写入， 就尝试强制 OS 把这些写发送到底层存储。这样做将会限制内核页面高速缓存中的脏数据数量， 降低在检查点末尾发出fsync或者 OS 在后台大批量写回数据时被卡住的可能性。合法的范围在 0（禁用强制写回）和2MB之间。Linux 上的默认值是256kB，其他平台上是0 。

9、max_wal_size
在自动WAL检查点使得WAL增长到最大尺寸。这是软限制；特殊情况下WAL大小可以超过 max_wal_size，如重负载下，错误archive_command，或者 较大wal_keep_segments的设置。缺省是1GB。增加这个参数会延长崩溃恢复所需要的时间。

10、min_wal_size
只要WAL磁盘使用率低于这个设置，旧的WAL文件总数被回收，以供将来检查点使用。而不是删除。这可以用来确保预留足够的WAL空间处理WAL使用中的峰值，比如当运行大批量工作时。缺省是80MB。

11、lc_messages
消息使用的语言Language of messages，可选值 UTF8

12、lc_monetary
货币数量使用的格式，可选值 Chinese (Simplified)_China.936

13、lc_numeric
数字格式 ，可选值 Chinese (Simplified)_China.936

14、lc_time
时间格式 ，可选值 Chinese (Simplified)_China.936