postgresql学习笔记--基础篇

1. 客户端程序和服务器端程序

1.1 客户端程序

Command	Example	Describe
clusterdb	clusterdb -h pghost1 -p 1921 -d mydb	ClusterDB是SQL Cluster的封装，Postgersql是堆表存储的， clusterdb通过索引对数据库中基于堆表的物理文件重新排序， 它在一定场景下可以节省磁盘访问，加速查询速度。
reindexdb	reindexdb -e -h pghost1 -p 1921 -d mydb	reindexdb是SQL REINDX命令的一个封装。 在索引物理文件发生损坏或者索引膨胀等情况发生时，可以使用reindexdb命令对指定的表或者数据库重建索引并删除旧的索引。
vacuumdb	vacuumdb -h pghost1 -p 1921 mydb	vacuumdb是POSTGRES数据库独有的VACUUM/VACUUM FREEZE和VACUUM FULL，VACUUM ANALYZE这几个SQL命令的封装。 VACUUM系列命令的主要职责是对物理文件等的垃圾回收。
vacuumlo	vacuumlo -h pghost1 -p 1921 mydb	vacuumlo用来清理数据库中未引用的大对象。
createdb dropdb	createdb -h pghost1 -p 1921 newdb “New Database.” dropdb -h pghost1 -p 1921 newdb	create db drop db
createuser dropuser	创建普通用户，只有一个连接，没有创库/创用户的权限，并设置密码 >createuser -h pghost1 -p 1921 -c 1 -g pg_monitor -D -R－Ｓ-P -e newuser >Enter password for new role: >Enter it again: ＞CREATE ROLE newuser PASSWORD 'md518b2c3ec6f3dxxxxxx' NOSUPERUSER NOCREATEDB NOCREATEDROLE INHERIT LOGIN CONNECTION LIMIT 1 IN ROLE pg_monitor; 通过interactive参数交互创建用户 >createuser -h pghost1 -p 1921 -c 1 -g pg_monitor --interactive -e -P newuser >Enter password for new role: >Enter it again: >Shall the new role be a superuser ? (y/n) n >Shall the new role be allowed to create databases? (y/n) n >Shall the new role be allowed to create more new roles ? (y/n)n >CREATE ROLE newuser PASSWORD 'md518b2c3ec6f3dxxxxxx' NOSUPERUSER NOCREATEDB NOCREATEDROLE INHERIT LOGIN CONNECTION LIMIT 1 IN ROLE pg_monitor; 删除用户 dropuser -h pghost1 -p 1921 newuser	createuser dropuser
pg_basebackup		取得一个正在运行中的Postgres实例的基础备份
pg_dump pg_dumpall		以数据库转储方式进行备份
pg_restore		用来从pg_dump创建的非文本格式的备份中恢复数据

其他客户端程序：

ecpg是用于C程序的Postgres嵌入式SQL预处理器。它将SQL调用替换为特殊函数调用，把带有嵌入式SQL语句的C程序转化为普通C代码。

• oid2name：解析一个Postgresql数据目录中的OID和文件节点。
• pgbench：是运行基准测试的工具，平常可以模拟简单的压力测试
• pg_config：获取当前安装的Postgres的配置参数
• pg_receivexlog：可以从一个运行中的实例获取事务日志的流。
• pg_recvlogical:控制逻辑解码复制槽以及来自这种复制槽的流数据。

1.2 服务器程序

• initdb：用来创建行的数据库目录
• pg_archivecleanup：清理Postgresql　WAL归档文件的工具
• pg_controldata：显示数据库服务的控制信息，例如目录版本／预写日志和检查点的信息。
• pg_ctl：初始化/启动/停止/控制数据库服务器的工具。
• pg_resetwal：可以清除预写日志并且有选择地重置存储在pg_control文件中的一些控制信息。 当服务器由于控制文件损坏，pg_resetwal可以作为最后的手段。
• pg_rewind：是在master/slave角色发生切换时，将原master通过同步模式恢复，避免重做基础备份的工具。
• pg_test_fsync:可以通过一个快速的测试，了解系统使用哪一种预写日志的同步方法（wal_sync_method）最快，还可以在发生I/O问题时提供诊断信息。
• pg_test_timing:是一种度量系统统计时开销以及确认系统时间绝不会回退的工具。
• pg_upgrade：是postgres的升级工具。
• pg_waldump：用来将预写日志解析为可读格式。

1.3 初始化数据库目录

创建目录: mkdir -p /pgdata/10/{data,backups,scripts,archive_wals}

postgres@nancloud-onprem-06:/usr/lib/postgresql/9.5/bin$ ./initdb --help

initdb initializes a PostgreSQL database cluster.

Usage:

initdb [OPTION]... [DATADIR]

Options:

-A, --auth=METHOD default authentication method for local connections                      //为本地用户指定pg_hba.conf文件中的认证方法，可以为md5/trust/password等，默认是trust，代表信任服务器上所有用户

--auth-host=METHOD default authentication method for local TCP/IP connections       //指定通过TCP/IP连接的本地用户在pg_hba.conf中使用的认证方法

--auth-local=METHOD default authentication method for local-socket connections        //指定通过UNIX Socket连接的本地用户在pg_hba.conf文件中的认证方法

[-D, --pgdata=]DATADIR location for this database cluster                                              //将要初始化的数据目录；必须选项

-E, --encoding=ENCODING set default encoding for new databases                             //设置数据库默认编码，实际它是设置了template1的编码，因为其他新创建的数据库都是以template1为模板克隆的。

--locale=LOCALE set default locale for new databases　　　　　　　　　　　　　　//设置区域

--lc-collate=, --lc-ctype=, --lc-messages=LOCALE

--lc-monetary=, --lc-numeric=, --lc-time=LOCALE

set default locale in the respective category for

new databases (default taken from environment)

--no-locale equivalent to --locale=C

--pwfile=FILE read password for the new superuser from file　　　　　　　　　　　　//从一个文件读取第一行作为数据库超级用户口令

-T, --text-search-config=CFG　　　　　　　　　　　　　　　　　　　　　　　　　  //设置默认的文本搜索配置

default text search configuration

-U, --username=NAME database superuser name　　　　　　　　　　　　　　　　//设置数据库超级用户的用户名，默认是postgres

-W, --pwprompt prompt for a password for the new superuser　　　　　　　　　　　//在initdb的过程中为数据库超级用户设置一个密码。

-X, --xlogdir=XLOGDIR location for the transaction log directory　　　　　　　　　　//指定预写日志（WAL）的存储目录

Less commonly used options:

-d, --debug generate lots of debugging output

-k, --data-checksums use data page checksums

-L DIRECTORY where to find the input files

-n, --noclean do not clean up after errors

-N, --nosync do not wait for changes to be written safely to disk

-s, --show show internal settings

-S, --sync-only only sync data directory

Other options:

-V, --version output version information, then exit

-?, --help show this help, then exit

　　

Example:

-bash-4.1$ initdb -D ~/test -W

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-.

The default database encoding has accordingly been set to UTF8.

The default text search configuration will be set to "english".

fixing permissions on existing directory /var/lib/pgsql/test ... ok

creating subdirectories ... ok

selecting default max_connections ...

selecting default shared_buffers ... 32MB

creating configuration files ... ok

creating template1 database in /var/lib/pgsql/test/base/ ... ok

initializing pg_authid ... ok

Enter new superuser password:

Enter it again:

setting password ... ok

initializing dependencies ... ok

creating system views ... ok

loading system objects' descriptions ... ok

creating conversions ... ok

creating dictionaries ... ok

setting privileges on built-in objects ... ok

creating information schema ... ok

vacuuming database template1 ... ok

copying template1 to template0 ... ok

copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the -A option the

next time you run initdb.

Success. You can now start the database server using:

postgres -D /var/lib/pgsql/test

or

pg_ctl -D /var/lib/pgsql/test -l logfile start

通过上面样例，可以看出，在initdb的输出中可以看到系统自动创建了template1数据库和postgres数据库，template1是生产其他数据库的模板，postgres是一个默认数据库，需要注意的是，不要在将要初始化的数据库目录中手动创建任何文件，如果数据库目录中已经有文件，会有如下错误，防止误覆盖已有数据库：

-bash-4.1$ initdb -D ~/test -W

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-.

The default database encoding has accordingly been set to UTF8.

The default text search configuration will be set to "english".

initdb: directory "/var/lib/pgsql/test" exists but is not empty

If you want to create a new database system, either remove or empty

the directory "/var/lib/pgsql/test" or run initdb

with an argument other than "/var/lib/pgsql/test".

除了使用initdb来初始化数据库目录，还可以通过pg_ctl工具进行数据库目录初始化：

pg_ctl init -D /pgdata//data -o "-W"

1.3 使用pg_ctl进行管理

• 启动数据库
  • pg_ctl -D /pgdata/10/data start
• 查看数据库运行状态
  • pg_ctl -D /pgdata/10/data status
  • 还可以使用pg_isready工具来检测数据库服务器是否已经允许接收连接： pg_isready -p 1921
• 停止数据库
  • pg_ctl stop [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
  • “-s”参数开启和关闭屏幕上的消息输出：“-t SECS”参数设置超时时间，超过SECS值设置的超时时间自动退出。其中“-m” 参数控制数据库用什么模式停止。Postgres支持3中模式：smart，fast,immediate,默认是fast模式。
    • smar 模式：会等待活动的事务提交结束，并等待客户端主动断开连接后关闭数据库
    • fast模式：会回滚所有活动的事务，并强制断开客户端的连接之后关闭数据库。
    • immediate模式：立即终止所有服务器进程，当下一次数据库启动时会首先进入恢复状态，一般不推荐使用。
    • 在写命令的时候可以使用简写 “-ms” ，“-mf” ，“-mi”

2. 数据库基础配置

2.1 pg_hba.conf

pg_hba.conf 是数据库实例的防火墙，文件格式如下：

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only

local   all         all                               trust

# IPv4 local connections:

host    all         all         127.0.0.1/32          trust

# IPv6 local connections:

host    all         all         ::1/128               trust

• 连接方式： TYPE列标识允许的连接方式，可用的值有：local，host，hostssl，hostnossl
  • local：匹配使用Unix域套接字的连接。如果没有TYPE为local的条目则不允许通过Unix域Socket连接
  • host：匹配使用TCP/IP建立的连接，通过匹配SSL和非SSL连接。默认只监听本地localhost地址，修改后需要同步修改postgresql.conf中listen_address参数。
  • hostssl：匹配必须使用SSL的TCP/IP连接。配置hostssl有3个前提条件：
    • 客户端和服务器端都安装openssl
    • 编译Postgresql的时候指定configure参数--with-openssl打开SSL支持
    • 在postgrsql.conf中配置ssl=on
  • hostnossl：只匹配非ssl的TCP/IP连接
• 目标数据库：DATABASE列标识该行设置对哪个数据库生效
• 目标用户：USER标识该行设置对哪个数据库用户生效
• 访问来源：ADDRESS列标识该行设置对哪个IP地址或IP段生效
• 认证方法
  • Method列表是客户端的认证方法，常见的认证方法有trust,reject,md5 and password
  • reject认证方法主要应用在这样的场景：允许某一网段的大多数主机访问数据库，但拒绝少数机器
  • md5和password认证方式的区别在于md5认证方式为双重md5加密，password指明文密码，所以不要在非信任网络使用password认证方式
  • scram-sha-256是PG10中新增的基于SASL的认证方式，是PG目前提供的最安全的认证方式。不支持旧版本客户端

2.2 postgres.conf

postgres.conf配置文件的文件结构很简单，有多个configparameter=value形式组成，#开头的为注释。value支持的数据类型有Bool，int，float，string，enum。value还支持各种单位，如MB，GB和ms，min、d等。还支持include和include_if_exists指令，并且允许嵌套。

2.2.1 全局配置的修改方法

• 修改postgres.conf配置文件
• 通过ALTER SYSTEM命令修改全局配置，如：

mydb# ALTER SYSTEM SET listen_address='*';

通过ALTER SYSTEM SQL命令修改的全局配置参数，会自动编辑postgresql.auto.conf文件，在数据库启动时会加载postgresql.auto.conf文件，并用它的配置覆盖postgresql.conf中已有的配置。这个文件不要手工修改它。

• 启动数据库时进行设置：postgres -D /pgdata/10/data -c port=1922

2.2.2 非全局配置的修改方法

• 设置和重置Database级别的配置，如：

  ALTER DATABASE name SET configparameter {TO|=}{value|DEFAULT}
  
  ALTER DATABASE name RESET configuration ;

• 设置和重置Session级别的配置
• 通过SET命令设置当前Session的配置。例如：

  SET configparameter {TO|=} {value|'value'|DEFAULT}
  
  SET configparameter TO DEFAULT;

• 更新pg_setttings视图，例如

  UPDATE 'pg_settings' SET setting=new_value where name='configparameter';
  
  UPDATE 'pg_settings' set setting=reset_val where name='configparameter';

• 使用set_config函数更新会话配置

  select set_conf('configparameter',new_value,false);

• 设置和重置Role级别的配置

  ALTER ROLE name in DATABASE database_name SET configparameter {TO|=} {value|DEFAULT}
  
  ALTER ROLE name in DATABASE database_name RESET configparameter;

2.2.3 如何查看配置

• 查询pg_settings系统表，例如

  SELECT name,setting FROM pg_settings where name ~ 'xxx';
  
  SELECT current_setting(name);

• 通过show（show all）命令查看

2.2.4 使配置生效的方法

如果是不需要重启的参数，reload一次就可以生效。命令如下：

SELECT pg_reload_conf();

也可以使用pg_ctl命令reload配置：

pg_ctl -D /pgdata//data reload