9.1参考3

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(pg_ctl)
(pg_resetxlog)
Line 303: Line 303:
  
 
== pg_resetxlog ==
 
== pg_resetxlog ==
-- reset the write-ahead log and other control information of a PostgreSQL database cluster
+
 
 +
'''Name'''
 +
 
 +
pg_resetxlog -- reset the write-ahead log and other control information of a PostgreSQL database cluster
 +
 
 +
'''Synopsis'''
 +
 
 +
<pre>pg_resetxlog [-f] [-n] [-ooid ] [-x xid ] [-e xid_epoch ] [-m mxid ] [-O mxoff ] [-l timelineid,fileid,seg ] datadir</pre>
 +
 
 +
'''Description'''
 +
 
 +
pg_resetxlog clears the write-ahead log (WAL) and optionally resets some other control information stored in the pg_control file. This function is sometimes needed if these files have become corrupted. It should be used only as a last resort, when the server will not start due to such corruption.
 +
 
 +
After running this command, it should be possible to start the server, but bear in mind that the database might contain inconsistent data due to partially-committed transactions. You should immediately dump your data, run initdb, and reload. After reload, check for inconsistencies and repair as needed.
 +
 
 +
This utility can only be run by the user who installed the server, because it requires read/write access to the data directory. For safety reasons, you must specify the data directory on the command line. pg_resetxlog does not use the environment variable PGDATA.
 +
 
 +
If pg_resetxlog complains that it cannot determine valid data for pg_control, you can force it to proceed anyway by specifying the -f (force) switch. In this case plausible values will be substituted for the missing data. Most of the fields can be expected to match, but manual assistance might be needed for the next OID, next transaction ID and epoch, next multitransaction ID and offset, and WAL starting address fields. These fields can be set using the switches discussed below. If you are not able to determine correct values for all these fields, -f can still be used, but the recovered database must be treated with even more suspicion than usual: an immediate dump and reload is imperative. Do not execute any data-modifying operations in the database before you dump, as any such action is likely to make the corruption worse.
 +
 
 +
The -o, -x, -e, -m, -O, and -l switches allow the next OID, next transaction ID, next transaction ID's epoch, next multitransaction ID, next multitransaction offset, and WAL starting address values to be set manually. These are only needed when pg_resetxlog is unable to determine appropriate values by reading pg_control. Safe values can be determined as follows:
 +
 
 +
:A safe value for the next transaction ID (-x) can be determined by looking for the numerically largest file name in the directory pg_clog under the data directory, adding one, and then multiplying by 1048576. Note that the file names are in hexadecimal. It is usually easiest to specify the switch value in hexadecimal too. For example, if 0011 is the largest entry in pg_clog, -x 0x1200000 will work (five trailing zeroes provide the proper multiplier).
 +
 
 +
:A safe value for the next multitransaction ID (-m) can be determined by looking for the numerically largest file name in the directory pg_multixact/offsets under the data directory, adding one, and then multiplying by 65536. As above, the file names are in hexadecimal, so the easiest way to do this is to specify the switch value in hexadecimal and add four zeroes.
 +
 
 +
:A safe value for the next multitransaction offset (-O) can be determined by looking for the numerically largest file name in the directory pg_multixact/members under the data directory, adding one, and then multiplying by 65536. As above, the file names are in hexadecimal, so the easiest way to do this is to specify the switch value in hexadecimal and add four zeroes.
 +
 
 +
:The WAL starting address (-l) should be larger than any WAL segment file name currently existing in the directory pg_xlog under the data directory. These names are also in hexadecimal and have three parts. The first part is the "timeline ID" and should usually be kept the same. Do not choose a value larger than 255 (0xFF) for the third part; instead increment the second part and reset the third part to 0. For example, if 00000001000000320000004A is the largest entry in pg_xlog, -l 0x1,0x32,0x4B will work; but if the largest entry is 000000010000003A000000FF, choose -l 0x1,0x3B,0x0 or more.
 +
 
 +
<pre>
 +
Note: pg_resetxlog itself looks at the files in pg_xlog and chooses a default -l setting beyond the last existing file name. Therefore, manual adjustment of -l should only be needed if you are aware of WAL segment files that are not currently present in pg_xlog, such as entries in an offline archive; or if the contents of pg_xlog have been lost entirely.
 +
</pre>
 +
 
 +
:There is no comparably easy way to determine a next OID that's beyond the largest one in the database, but fortunately it is not critical to get the next-OID setting right.
 +
 
 +
:The transaction ID epoch is not actually stored anywhere in the database except in the field that is set by pg_resetxlog, so any value will work so far as the database itself is concerned. You might need to adjust this value to ensure that replication systems such as Slony-I work correctly — if so, an appropriate value should be obtainable from the state of the downstream replicated database.
 +
 
 +
The -n (no operation) switch instructs pg_resetxlog to print the values reconstructed from pg_control and then exit without modifying anything. This is mainly a debugging tool, but can be useful as a sanity check before allowing pg_resetxlog to proceed for real.
 +
 
 +
The -V and --version options print the pg_resetxlog version and exit. The options -? and --help show supported arguments, and exit.
 +
 
 +
'''Notes'''
 +
 
 +
This command must not be used when the server is running. pg_resetxlog will refuse to start up if it finds a server lock file in the data directory. If the server crashed then a lock file might have been left behind; in that case you can remove the lock file to allow pg_resetxlog to run. But before you do so, make doubly certain that there is no server process still alive.
 +
 
 
== postgres ==
 
== postgres ==
 
-- PostgreSQL database server
 
-- PostgreSQL database server
 
== postmaster ==
 
== postmaster ==
 
-- PostgreSQL database server
 
-- PostgreSQL database server

Revision as of 07:06, 20 September 2013

参考3 服务器应用

这部分包括 PostgreSQL 服务器应用和支持工具的参考信息。这些命令只能用于在数据库服务器所在的主机上运行。其它工具程序在 Reference II,客户端应用 中列出。

Contents

initdb

Name

initdb -- 创建一个新的 PostgreSQL数据库集群

Synopsis

initdb [option...] --pgdata | -D directory

Description

initdb 创建一个新的 PostgreSQL 数据库集群。 一个数据库集群是由单个服务器实例管理的数据库集合。

创建数据库集群包括创建数据库数据的宿主目录,生成共享的系统表(不属于任何特定数据库的表)和创建 template1 和 postgres 数据库。当你以后再创建一个新数据库时, template1 数据库里所有内容都会拷贝过来。(因此,任何在 template1 里面安装的东西都自动拷贝到之后创建的数据库中。) postgres 数据库是一个缺省数据库,用于给用户、工具或者第三方应用提供缺省数据库。

尽管initdb会尝试创建相应的数据目录, 但经常会发生它没有权限做这些事情的情况。因为所需要的目录的父目录通常是 root 所有的目录。 要初始化这种设置,用 root 创建一个空数据目录, 然后用 chown 把该目录的所有权交给数据库用户帐号, 然后 su 成数据库用户,最后以数据库用户身份运行 initdb。

initdb 必须以服务器进程所有者身份运行,因为服务器需要initdb所创建文件和目录的访问权限。因为服务器不能以root身份运行,你也不能以root身份运行initdb。(事实上会被拒绝。)

initdb 初始化该数据库集群的缺省区域和字符集编码。字符编码排序(LC_COLLATE)和字符集表(LC_CTYPE, 也就是,大写,小写,数字等)可在数据库创建时单独设置。initdb 决定 template1 数据库的编码,而该编码将成为所有其它数据库的缺省。

要修改缺省编码排序或者字符集表,使用--lc-collate和--lc-ctype选项。使用 C 或 POSIX 之外的字符编码排序还会有性能影响。因此在运行initdb时选取正确的设置非常重要。

其余的设置可以在服务器启动后改变。也可以使用--locale设置缺省值,包括编码排序和字符集表。服务器的设置可通过SHOW ALL显示。更多细节可以参考Section 22.1。

若要改变缺省编码,使用--encoding。更多细节可以参考Section 22.3。

Options

-A authmethod --auth=authmethod

这个选项声明本地用户在 pg_hba.conf 里面使用的认证方法。 除非你相信所有你的系统上的本地用户,否则不要使用 trust。 Trust 是所有安装的缺省。

-D directory --pgdata=directory

这个选项声明数据库集群应该存放在哪个目录。 这是initdb需要的唯一信息,但是你可以通过设置 PGDATA 环境变量来避免键入, 这样做可能方便一些,因为稍后数据库服务器(postmaster)可以通过同一个变量找到数据库目录。

-E encoding --encoding=encoding

选择模板数据库的编码方式。这将是你以后创建的数据库的缺省编码方式, 除非你创建数据库时覆盖了它。缺省是从区域设置中获得的,如果没有区域设置,就是 SQL_ASCII。 PostgreSQL 服务器支持的字符集在 Section 22.3.1 里描述。

--locale=locale

为数据库集群设置缺省的区域。如果没有声明这个选项,那么区域 是从 initdb 运行的环境中继承过来的。 区域设置在 Section 22.1 里描述。

--lc-collate=locale --lc-ctype=locale --lc-messages=locale --lc-monetary=locale --lc-numeric=locale --lc-time=locale

类似 --locale,但是只设置特殊范畴的区域。

--no-locale

等价于--locale=C。

--pwfile=filename

使initdb从文件中读取超级用户的密码。密码位于该文件中第一行。

--text-search-config=CFG

设置缺省文本搜索。参看default_text_search_config获得更多信息。

-U username --username=username

选择数据库超级用户的用户名。缺省是运行 initdb 的用户的有效用户。 超级用户的名字是什么并不重要, 但是我们可以选择习惯的名字 postgres,即使操作系统的用户名字不一样也无所谓。

-W --pwprompt

令 initdb 提示输入数据库超级用户的口令。 如果你不准备使用口令认证,这个东西并不重要。 否则你将不能使用口令认证直到你设置了口令。

-X directory --xlogdir=directory

该选项指定事务日志存放的路径。

其他的,不常用的参数同样可用:

-d --debug

从初始化后端打印调试输出以及一些其它的一些普通用户不太感兴趣的信息。 初始化后端是 initdb 用于创建系统表的程序。 这个选项生成大量非常烦人的输出。

-L directory

告诉 initdb 到哪里找初始化数据库所需要的输入文件。 通常是不必要的。如果需要你明确声明的话,程序会提示你输入。

-n --noclean

缺省时,当initdb 发现一些错误妨碍它完成创建数据库集群的工作时, 它将在检测到不能结束工作之前将其创建的所有文件删除。 这个选项禁止任何清理动作,因而对调试很有用。

-V --version

打印initdb版本后退出。

-? --help

显示initdb命令行参数的帮助信息后退出。

Environment

PGDATA

声明数据库集群存储的目录;可以用 -D 选项覆盖。

这个工具,和其他PostgreSQL工具一样都使用libpq支持的环境变量(参照Section31.13)。

Notes

initdb 也可以被pg_ctl initdb调用。

See Also

pg_ctl, postgres

pg_controldata

Name

pg_controldata -- 显示PostgreSQL数据库集群的控制信息

Synopsis

pg_controldata [option] [datadir]

Description

pg_controldata 打印initdb初始化过的信息,比如系统表的版本。它也可以显示预写日志和检查点处理的信息。这个信息是集群层面的,不针对任何特定的数据库。

该工具仅可以被初始化该集群的用户执行,因为它需要对数据库数据目录的访问权限。你可以通过命令行指定数据目录也可以通过设置环境变量 PGDATA。该工具还支持-V和--version选项,该选项打印pg_controldata版本并退出。它还支持选项-?和--help,输出命令行帮助信息。

Environment

PGDATA

缺省数据目录

pg_ctl

Name

pg_ctl -- 初始化、启动、停止和重起 PostgreSQL

Synopsis

pg_ctl init[db] [-s] [-D datadir] [-o initdb-options]
pg_ctl start [-w] [-t seconds] [-s] [-D datadir] [-l filename] [-o options] [-p path] [-c]
pg_ctl stop [-W] [-t seconds] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ]
pg_ctl restart [-w] [-t seconds] [-s] [-D datadir] [-c] [-m s[mart] | f[ast] | i[mmediate] ] [-o options]
pg_ctl reload [-s] [-D datadir]
pg_ctl status [-D datadir]
pg_ctl promote [-s] [-D datadir]
pg_ctl kill signal_name process_id
pg_ctl register [-N servicename] [-U username] [-P password] [-D datadir] [-S a[uto] | d[emand] ] [-w] [-t seconds] [-s] [-o options]
pg_ctl unregister [-N servicename]

Description

pg_ctl 是一个用于初始化,启动,停止, 或者重起 PostgreSQL 后端服务器(postgres), 或者显示一个运行着的服务器的状态的工具, 尽管我们可以手动启动服务器,但是 pg_ctl 封装了重新定向日志输出,与终端和进程组合理分离,以及另外提供了方便的选项用于有控制的关闭。

在Init或initdb模式下创建一个新的PostgreSQL数据库集群。一个数据库集群是由一个服务进程管理的多个数据库集合。该模式调用initdb命令。详情请参考initdb。

在 start 模式里会启动一个新的服务器。服务器是在后台启动的,标准输入被附着到了 /dev/null(在Windows下是nul) 上。在类UNIX系统中,缺省情况下服务器的标准输出和标准错误输出都被发送到pg_ctl's的标准输出(不是标准错误)。pg_ctl的标准输出被重定向到一个文件或者通过管理输送给另一个进行,比如日志处理程序rotatelogs。否则postgres将把它的输出写到控制终端(从后台)并且不会脱离shell进程组。在Windows中,缺省情况下服务器的标准输出和标准错误被发送到终端。这些缺省行为可以通过-l选项来指定log重定向到某个文件。推荐使用-l或者输出重定向。

在 stop 模式下,那个正在特定数据目录运行的服务器被关闭。你可以用 -m 选项选择三种不同的关闭模式:"Smart" 模式(也是缺省模式)等待所有客户端中断联接且正在进行的备份将被终止。如果服务器不是hot standby,恢复和流复制也将被在客户端连接中断后退出。"Fast" 模式并不等待客户端中断联接。 所有活跃事务都被回滚并且客户端都强制断开。 "Immediate" 模式将在没有干净关闭的情况下退出。这么做将导致在重新启动的时候的恢复。

restart 实际上是先执行一个停止,然后紧跟一个启动。它允许变换postgres命令行的选项。

reload 模式简单地给postmaster发送一个 SIGHUP 信号,导致它重新读取她的配置文件 (postgresql.conf,pg_hba.conf 等等) 这样就允许修改配置文件选项而不用完全重启系统来使之生效。

status 模式检查一个服务器是否在指定的数据目录运行, 如果是,那么显示其 PID 和调用它的命令行选项。

生产模式,运行在某个数据目录的standby服务器将退出恢复并进开始进行读写操作。

kill 模式允许你给一个指定的进程发送信号。这个功能对 Microsoft Windows 特别有用,因为那里没有 kill 命令。 使用 --help 查看支持的信号的名字的列表。

register 模式允许你在 Microsoft Windows 上注册一个系统服务。-S选项用于设定启运类型,或者“自动”(系统启动时自动启动)或者“后台”(后台运行)。

unregister 模式允许你在 Microsoft Windows 上删除这个系统服务, 这个系统服务是前面用 register 命令注册的。

Options

-c

允许服务器崩溃时产生core文件,只要其所在的平台允许,所有的信息将会放进core文件中。异常进程产生的堆栈信息对于调试或问题诊断非常有用。

-D datadir

声明该数据库文件的文件系统位置。 如果忽略这个选项,使用环境变量 PGDATA。

-l filename

把服务器日志输出附加在 filename 文件上。 如果该文件不存在,那么创建它。umask设置为 077, 因此缺省时是不允许从其它用户向日志文件访问的。

-m mode

声明关闭模式。mode 可以是smart, fast, 或者 immediate,或者是这三个之一的第一个字母。若不指定,缺省是smart模式。

-o options

声明要直接传递给 postgres 的选项。
参数通常都用单或者双引号包围以保证它们作为一个整体传递。

-o initdb-options

指定要直接传递给initdb命令行的选项。
参数通常都用单或者双引号包围以保证它们作为一个整体传递。

-p path

声明 postgres 可执行文件的位置。缺省时postgres是从和pg_ctl相同的目录取出,如果不是, 那么就是写死的安装目录。除非你想干点什么特别的事情,并且得到类似没有找到postgres这样的错误,否则没有必要使用这个选项。
在init模式,该选项相同的用来指定initdb可执行文件的位置。

-s

只打印错误,而不打印提示性信息。

-t

等待启动或关闭完成的最大秒数。缺省是60秒。

-w

等待启动或者关闭的完成。 这个参数是关闭时的缺省值,但不是启动时的缺省值。等待启动时,pg_ctl不断地尝试连接服务器。等待关闭时,pg_ctl等待服务器删除其PID文件。pg_ctl基于启动或关闭成功与否返回退出代码。

-W

不等待启动或者停止的完成。这是启动和重起的缺省。

Options for Windows

-N servicename

要注册的系统服务的名字。这个名字将用于服务名和显示名。

-P password

用户启动服务的口令。

-S start-type

注册系统服务的启动类型。启动类型可以是auto或demand,或者二者的首字母。如果不指定,缺省值是auto.

-U username

启动服务的用户名。对于domain用户,格式为DOMAIN\username.

Environment

PGDATA

缺省数据目录位置

pg_ctl 像其他PostgreSQL实用工具一样,同样使用libpq支持的环境变量(参照Section 31.13)。其他的变量,参照postgres.

Files

postmaster.pid

该文件是否存在用于帮助pg_ctl判断服务器是否正在运行。

postmaster.opts

如果该文件存在于数据目录,pg_ctl(在启动模式下)将以该文件的内容为参数传递给postgres,除非被-o选项覆盖。该文件内容也会在状态模式下显示。

Examples

Starting the Server

启动服务器:

$ pg_ctl start

启动服务器,等待至服务器接受连接:

$ pg_ctl -w start

使用端口5433启动服务器,同时禁用fsync:

$ pg_ctl -o "-F -p 5433" start

Stopping the Server

关闭服务器:

$ pg_ctl stop

选项-m用来控制服务器关闭方式:

$ pg_ctl stop -m fast

Restarting the Server

服务器重启几乎等价于关闭后再启动,只是pg_ctl保存并重用了传递给之前进程的命令行选项。最简单的重启:

$ pg_ctl restart

重启服务器并等待其关闭并重启:

$ pg_ctl -w restart

使用端口5433重启,重启时禁用fsync:

$ pg_ctl -o "-F -p 5433" restart

Showing the Server Status

下面是简单的pg_ctl状态输出例子:

$ pg_ctl status
pg_ctl: server is running (PID: 13718)
/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data" "-p" "5433" "-B" "128"

This is the command line that would be invoked in restart mode.

See Also

initdb, postgres

pg_resetxlog

Name

pg_resetxlog -- reset the write-ahead log and other control information of a PostgreSQL database cluster

Synopsis

pg_resetxlog [-f] [-n] [-ooid ] [-x xid ] [-e xid_epoch ] [-m mxid ] [-O mxoff ] [-l timelineid,fileid,seg ] datadir

Description

pg_resetxlog clears the write-ahead log (WAL) and optionally resets some other control information stored in the pg_control file. This function is sometimes needed if these files have become corrupted. It should be used only as a last resort, when the server will not start due to such corruption.

After running this command, it should be possible to start the server, but bear in mind that the database might contain inconsistent data due to partially-committed transactions. You should immediately dump your data, run initdb, and reload. After reload, check for inconsistencies and repair as needed.

This utility can only be run by the user who installed the server, because it requires read/write access to the data directory. For safety reasons, you must specify the data directory on the command line. pg_resetxlog does not use the environment variable PGDATA.

If pg_resetxlog complains that it cannot determine valid data for pg_control, you can force it to proceed anyway by specifying the -f (force) switch. In this case plausible values will be substituted for the missing data. Most of the fields can be expected to match, but manual assistance might be needed for the next OID, next transaction ID and epoch, next multitransaction ID and offset, and WAL starting address fields. These fields can be set using the switches discussed below. If you are not able to determine correct values for all these fields, -f can still be used, but the recovered database must be treated with even more suspicion than usual: an immediate dump and reload is imperative. Do not execute any data-modifying operations in the database before you dump, as any such action is likely to make the corruption worse.

The -o, -x, -e, -m, -O, and -l switches allow the next OID, next transaction ID, next transaction ID's epoch, next multitransaction ID, next multitransaction offset, and WAL starting address values to be set manually. These are only needed when pg_resetxlog is unable to determine appropriate values by reading pg_control. Safe values can be determined as follows:

A safe value for the next transaction ID (-x) can be determined by looking for the numerically largest file name in the directory pg_clog under the data directory, adding one, and then multiplying by 1048576. Note that the file names are in hexadecimal. It is usually easiest to specify the switch value in hexadecimal too. For example, if 0011 is the largest entry in pg_clog, -x 0x1200000 will work (five trailing zeroes provide the proper multiplier).
A safe value for the next multitransaction ID (-m) can be determined by looking for the numerically largest file name in the directory pg_multixact/offsets under the data directory, adding one, and then multiplying by 65536. As above, the file names are in hexadecimal, so the easiest way to do this is to specify the switch value in hexadecimal and add four zeroes.
A safe value for the next multitransaction offset (-O) can be determined by looking for the numerically largest file name in the directory pg_multixact/members under the data directory, adding one, and then multiplying by 65536. As above, the file names are in hexadecimal, so the easiest way to do this is to specify the switch value in hexadecimal and add four zeroes.
The WAL starting address (-l) should be larger than any WAL segment file name currently existing in the directory pg_xlog under the data directory. These names are also in hexadecimal and have three parts. The first part is the "timeline ID" and should usually be kept the same. Do not choose a value larger than 255 (0xFF) for the third part; instead increment the second part and reset the third part to 0. For example, if 00000001000000320000004A is the largest entry in pg_xlog, -l 0x1,0x32,0x4B will work; but if the largest entry is 000000010000003A000000FF, choose -l 0x1,0x3B,0x0 or more.
Note: pg_resetxlog itself looks at the files in pg_xlog and chooses a default -l setting beyond the last existing file name. Therefore, manual adjustment of -l should only be needed if you are aware of WAL segment files that are not currently present in pg_xlog, such as entries in an offline archive; or if the contents of pg_xlog have been lost entirely.
There is no comparably easy way to determine a next OID that's beyond the largest one in the database, but fortunately it is not critical to get the next-OID setting right.
The transaction ID epoch is not actually stored anywhere in the database except in the field that is set by pg_resetxlog, so any value will work so far as the database itself is concerned. You might need to adjust this value to ensure that replication systems such as Slony-I work correctly — if so, an appropriate value should be obtainable from the state of the downstream replicated database.

The -n (no operation) switch instructs pg_resetxlog to print the values reconstructed from pg_control and then exit without modifying anything. This is mainly a debugging tool, but can be useful as a sanity check before allowing pg_resetxlog to proceed for real.

The -V and --version options print the pg_resetxlog version and exit. The options -? and --help show supported arguments, and exit.

Notes

This command must not be used when the server is running. pg_resetxlog will refuse to start up if it finds a server lock file in the data directory. If the server crashed then a lock file might have been left behind; in that case you can remove the lock file to allow pg_resetxlog to run. But before you do so, make doubly certain that there is no server process still alive.

postgres

-- PostgreSQL database server

postmaster

-- PostgreSQL database server

Personal tools