oid2name — resolve OIDs and file nodes in a PostgreSQL data directory
vacuumlo — remove orphaned large objects from a PostgreSQL database
This section covers PostgreSQL client applications in contrib. They can be run from anywhere, independent of where the database server resides. See also PostgreSQL Client Applications for information about client applications that part of the core PostgreSQL distribution.
This appendix and the previous one contain information regarding the modules that can be found in the contrib directory of the PostgreSQL distribution. See for more information about the contrib section in general and server extensions and plug-ins found incontrib specifically.
This appendix covers utility programs found in contrib. Once installed, either from source or a packaging system, they are found in the bin directory of the PostgreSQL installation and can be used like any other program.
oid2name — resolve OIDs and file nodes in a PostgreSQL data directory
oid2name [option...]
oid2name is a utility program that helps administrators to examine the file structure used by PostgreSQL. To make use of it, you need to be familiar with the database file structure, which is described in Chapter 66.
The name “oid2name” is historical, and is actually rather misleading, since most of the time when you use it, you will really be concerned with tables' filenode numbers (which are the file names visible in the database directories). Be sure you understand the difference between table OIDs and table filenodes!
oid2name connects to a target database and extracts OID, filenode, and/or table name information. You can also have it show database OIDs or tablespace OIDs.
oid2name accepts the following command-line arguments:
-f filenode
show info for table with filenode
filenode-i
include indexes and sequences in the listing
-o oid
show info for table with OID
oid-q
omit headers (useful for scripting)
-s
show tablespace OIDs
-S
include system objects (those in information_schema, pg_toast and pg_catalog schemas)
-t tablename_pattern
show info for table(s) matching
tablename_pattern-V
--version
Print the oid2name version and exit.
-x
display more information about each object shown: tablespace name, schema name, and OID
-?
--help
Show help about oid2name command line arguments, and exit.
oid2name also accepts the following command-line arguments for connection parameters:
-d database
database to connect to
-H host
database server's host
-p port
database server's port
-U username
user name to connect as
-P password
password (deprecated — putting this on the command line is a security hazard)
To display specific tables, select which tables to show by using -o, -f and/or -t. -o takes an OID, -f takes a filenode, and -t takes a table name (actually, it's a LIKE pattern, so you can use things like foo%). You can use as many of these options as you like, and the listing will include all objects matched by any of the options. But note that these options can only show objects in the database given by -d.
If you don't give any of -o, -f or -t, but do give -d, it will list all tables in the database named by -d. In this mode, the -S and -i options control what gets listed.
If you don't give -d either, it will show a listing of database OIDs. Alternatively you can give -s to get a tablespace listing.
oid2name requires a running database server with non-corrupt system catalogs. It is therefore of only limited use for recovering from catastrophic database corruption situations.
B. Palmer <bpalmer@crimelabs.net>
vacuumlo — remove orphaned large objects from a PostgreSQL database
vacuumlo [option...] dbname...
vacuumlo is a simple utility program that will remove any “orphaned” large objects from a PostgreSQL database. An orphaned large object (LO) is considered to be any LO whose OID does not appear in any oid or lo data column of the database.
If you use this, you may also be interested in the lo_manage trigger in the lo module. lo_manage is useful to try to avoid creating orphaned LOs in the first place.
All databases named on the command line are processed.
vacuumlo accepts the following command-line arguments:
-l limit
Remove no more than limit large objects per transaction (default 1000). Since the server acquires a lock per LO removed, removing too many LOs in one transaction risks exceeding max_locks_per_transaction. Set the limit to zero if you want all removals done in a single transaction.
-n
Don't remove anything, just show what would be done.
-v
Write a lot of progress messages.
-V
--version
Print the vacuumlo version and exit.
-?
--help
Show help about vacuumlo command line arguments, and exit.
vacuumlo also accepts the following command-line arguments for connection parameters:
-h hostname
Database server's host.
-p port
Database server's port.
-U username
User name to connect as.
-w
--no-password
Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.
-W
Force vacuumlo to prompt for a password before connecting to a database.
This option is never essential, since vacuumlo will automatically prompt for a password if the server demands password authentication. However, vacuumlo will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.
vacuumlo works by the following method: First, vacuumlo builds a temporary table which contains all of the OIDs of the large objects in the selected database. It then scans through all columns in the database that are of type oid or lo, and removes matching entries from the temporary table. (Note: Only types with these names are considered; in particular, domains over them are not considered.) The remaining entries in the temporary table identify orphaned LOs. These are removed.
Peter Mount <peter@retep.org.uk>
This section covers PostgreSQL server-related applications in contrib. They are typically run on the host where the database server resides. See also PostgreSQL Server Applications for information about server applications that part of the core PostgreSQL distribution.
pg_standby — supports the creation of a PostgreSQL warm standby server
pg_standby [option...] archivelocation nextwalfile walfilepath [restartwalfile]
pg_standby supports creation of a “warm standby” database server. It is designed to be a production-ready program, as well as a customizable template should you require specific modifications.
pg_standby is designed to be a waiting restore_command, which is needed to turn a standard archive recovery into a warm standby operation. Other configuration is required as well, all of which is described in the main server manual (see Section 26.2).
To configure a standby server to use pg_standby, put this into its recovery.conf configuration file:
where archiveDir is the directory from which WAL segment files should be restored.
If restartwalfile is specified, normally by using the %r macro, then all WAL files logically preceding this file will be removed from archivelocation. This minimizes the number of files that need to be retained, while preserving crash-restart capability. Use of this parameter is appropriate if the archivelocation is a transient staging area for this particular standby server, but not when the archivelocation is intended as a long-term WAL archive area.
pg_standby assumes that archivelocation is a directory readable by the server-owning user. If restartwalfile (or -k) is specified, the archivelocation directory must be writable too.
There are two ways to fail over to a “warm standby” database server when the master server fails:Smart Failover
In smart failover, the server is brought up after applying all WAL files available in the archive. This results in zero data loss, even if the standby server has fallen behind, but if there is a lot of unapplied WAL it can be a long time before the standby server becomes ready. To trigger a smart failover, create a trigger file containing the word smart, or just create it and leave it empty.Fast Failover
In fast failover, the server is brought up immediately. Any WAL files in the archive that have not yet been applied will be ignored, and all transactions in those files are lost. To trigger a fast failover, create a trigger file and write the word fast into it. pg_standby can also be configured to execute a fast failover automatically if no new WAL file appears within a defined interval.
pg_standby accepts the following command-line arguments:
-c
Use cp or copy command to restore WAL files from archive. This is the only supported behavior so this option is useless.-d
Print lots of debug logging output on stderr.
-k
Remove files from archivelocation so that no more than this many WAL files before the current one are kept in the archive. Zero (the default) means not to remove any files from archivelocation. This parameter will be silently ignored if restartwalfile is specified, since that specification method is more accurate in determining the correct archive cut-off point. Use of this parameter is deprecated as of PostgreSQL 8.3; it is safer and more efficient to specify a restartwalfile parameter. A too small setting could result in removal of files that are still needed for a restart of the standby server, while a too large setting wastes archive space.
-r maxretries
Set the maximum number of times to retry the copy command if it fails (default 3). After each failure, we wait for sleeptime * num_retries so that the wait time increases progressively. So by default, we will wait 5 secs, 10 secs, then 15 secs before reporting the failure back to the standby server. This will be interpreted as end of recovery and the standby will come up fully as a result.
-s sleeptime
Set the number of seconds (up to 60, default 5) to sleep between tests to see if the WAL file to be restored is available in the archive yet. The default setting is not necessarily recommended; consult Section 26.2 for discussion.
-t triggerfile
Specify a trigger file whose presence should cause failover. It is recommended that you use a structured file name to avoid confusion as to which server is being triggered when multiple servers exist on the same system; for example /tmp/pgsql.trigger.5432.
-V
--version
Print the pg_standby version and exit.
-w maxwaittime
Set the maximum number of seconds to wait for the next WAL file, after which a fast failover will be performed. A setting of zero (the default) means wait forever. The default setting is not necessarily recommended; consult Section 26.2 for discussion.
-?
--help
Show help about pg_standby command line arguments, and exit.
pg_standby is designed to work with PostgreSQL 8.2 and later.
PostgreSQL 8.3 provides the %r macro, which is designed to let pg_standby know the last file it needs to keep. With PostgreSQL 8.2, the -k option must be used if archive cleanup is required. This option remains available in 8.3, but its use is deprecated.
PostgreSQL 8.4 provides the recovery_end_command option. Without this option a leftover trigger file can be hazardous.
pg_standby is written in C and has an easy-to-modify source code, with specifically designated sections to modify for your own needs
On Linux or Unix systems, you might use:
where the archive directory is physically located on the standby server, so that the archive_command is accessing it across NFS, but the files are local to the standby (enabling use of ln). This will:
produce debugging output in standby.log
sleep for 2 seconds between checks for next WAL file availability
stop waiting only when a trigger file called /tmp/pgsql.trigger.5442 appears, and perform failover according to its content
remove the trigger file when recovery ends
remove no-longer-needed files from the archive directory
On Windows, you might use:
Note that backslashes need to be doubled in the archive_command, but not in the restore_command or recovery_end_command. This will:
use the copy command to restore WAL files from archive
produce debugging output in standby.log
sleep for 5 seconds between checks for next WAL file availability
stop waiting only when a trigger file called C:\pgsql.trigger.5442 appears, and perform failover according to its content
remove the trigger file when recovery ends
remove no-longer-needed files from the archive directory
The copy command on Windows sets the final file size before the file is completely copied, which would ordinarily confuse pg_standby. Therefore pg_standby waits sleeptime seconds once it sees the proper file size. GNUWin32's cp sets the file size only after the file copy is complete.
Since the Windows example uses copy at both ends, either or both servers might be accessing the archive directory across the network.
Simon Riggs <simon@2ndquadrant.com>