Tool Reference

clusterdb [connection-option…​] [ --verbose | -v ] [ --table | -t table ] …​ [dbname]

clusterdb accepts the following command-line arguments:

Cluster all databases.

Specifies the name of the database to be clustered, when -a / --all is not used. If this is not specified, the database name is read from the environment variable PGDATABASE. If that is not set, the user name specified for the connection is used. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options.

Echo the commands that clusterdb generates and sends to the server.

Do not display progress messages.

Cluster table only. Multiple tables can be clustered by writing multiple -t switches.

Print detailed information during processing.

Print the clusterdb version and exit.

Show help about clusterdb command line arguments, and exit.clusterdb also accepts the following command-line arguments for connection parameters:

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.

User name to connect as.

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.

Force clusterdb to prompt for a password before connecting to a database.This option is never essential, since clusterdb will automatically prompt for a password if the server demands password authentication. However, clusterdb 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.

Specifies the name of the database to connect to to discover which databases should be clustered, when -a / --all is used. If not specified, the postgres database will be used, or if that does not exist, template1 will be used. This can be a connection string. If so, connection string parameters will override any conflicting command line options. Also, connection string parameters other than the database name itself will be re-used when connecting to other databases.

Default connection parameters

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

This utility, like most other IvorySQL utilities, also uses the environment variables supported by libpq

In case of difficulty, see CLUSTER and psql for discussions of potential problems and error messages. The database server must be running at the targeted host. Also, any default connection settings and environment variables used by the libpq front-end library will apply.

To cluster the database test:

To cluster a single table foo in a database named xyzzy:

createdb [connection-option…​] [option…​] [dbname [description]]

createdb accepts the following command-line arguments:

Specifies the name of the database to be created. The name must be unique among all IvorySQL databases in this cluster. The default is to create a database with the same name as the current system user.

Specifies a comment to be associated with the newly created database.

Specifies the default tablespace for the database. (This name is processed as a double-quoted identifier.)

Echo the commands that createdb generates and sends to the server.

Specifies the character encoding scheme to be used in this database.

Specifies the locale to be used in this database. This is equivalent to specifying both --lc-collate and --lc-ctype.

Specifies the LC_COLLATE setting to be used in this database.

Specifies the LC_CTYPE setting to be used in this database.

Specifies the ICU locale ID to be used in this database, if the ICU locale provider is selected.

Specifies the locale provider for the database’s default collation.

Specifies the database user who will own the new database. (This name is processed as a double-quoted identifier.)

Specifies the database creation strategy. See CREATE DATABASE STRATEGY for more details.

Specifies the template database from which to build this database. (This name is processed as a double-quoted identifier.)

Print the createdb version and exit.

Show help about createdb command line arguments, and exit.

The options -D, -l, -E, -O, and -T correspond to options of the underlying SQL command CREATE DATABASE; see there for more information about them.

createdb also accepts the following command-line arguments for connection parameters:

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

Specifies the TCP port or the local Unix domain socket file extension on which the server is listening for connections.

User name to connect as.

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.

Force createdb to prompt for a password before connecting to a database.This option is never essential, since createdb will automatically prompt for a password if the server demands password authentication. However, createdb 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.

Specifies the name of the database to connect to when creating the new database. If not specified, the postgres database will be used; if that does not exist (or if it is the name of the new database being created), template1 will be used. This can be a connection string. If so, connection string parameters will override any conflicting command line options.

If set, the name of the database to create, unless overridden on the command line.

Default connection parameters. PGUSER also determines the name of the database to create, if it is not specified on the command line or by PGDATABASE.

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

This utility, like most other IvorySQL utilities, also uses the environment variables supported by libpq

In case of difficulty, see CREATE DATABASE and psql for discussions of potential problems and error messages. The database server must be running at the targeted host. Also, any default connection settings and environment variables used by the libpq front-end library will apply.

To create the database demo using the default database server:

To create the database demo using the server on host eden, port 5000, using the template0 template database, here is the command-line command and the underlying SQL command:

createuser [connection-option…​] [option…​] [username]

Description

createuser creates a new IvorySQL user (or more precisely, a role). Only superusers and users with CREATEROLE privilege can create new users, so createuser must be invoked by someone who can connect as a superuser or a user with CREATEROLE privilege.

If you wish to create a role with the SUPERUSER, REPLICATION, or BYPASSRLS privilege, you must connect as a superuser, not merely with CREATEROLE privilege. Being a superuser implies the ability to bypass all access permission checks within the database, so superuser access should not be granted lightly. CREATEROLE also conveys very extensive privileges.

createuser is a wrapper around the SQL command CREATE ROLE. There is no effective difference between creating users via this utility and via other methods for accessing the server.

createuser accepts the following command-line arguments:

Specifies the name of the IvorySQL user to be created.

Set a maximum number of connections for the new user. The default is to set no limit.

The new user will be allowed to create databases.

The new user will not be allowed to create databases. This is the default.

Echo the commands that createuser generates and sends to the server.

This option is obsolete but still accepted for backward compatibility.

Indicates role to which this role will be added immediately as a new member. Multiple roles to which this role will be added as a member can be specified by writing multiple -g switches.

The new role will automatically inherit privileges of roles it is a member of. This is the default.

The new role will not automatically inherit privileges of roles it is a member of.

Prompt for the user name if none is specified on the command line, and also prompt for whichever of the options -d / -D, -r / -R, -s / -S is not specified on the command line.

The new user will be allowed to log in (that is, the user name can be used as the initial session user identifier). This is the default.

The new user will not be allowed to log in. (A role without login privilege is still useful as a means of managing database permissions.)

If given, createuser will issue a prompt for the password of the new user. This is not necessary if you do not plan on using password authentication.

The new user will be allowed to create, alter, drop, comment on, change the security label for, and grant or revoke membership in other roles; that is, this user will have CREATEROLE privilege. See role creation for more details about what capabilities are conferred by this privilege.

The new user will not be allowed to create new roles. This is the default.

The new user will be a superuser.

The new user will not be a superuser. This is the default.

Print the createuser version and exit.

The new user will have the REPLICATION privilege, which is described more fully in the documentation for CREATE ROLE.

The new user will not have the REPLICATION privilege, which is described more fully in the documentation for CREATE ROLE.

Show help about createuser command line arguments, and exit.

createuser also accepts the following command-line arguments for connection parameters:

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.

User name to connect as (not the user name to create).

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.

Force createuser to prompt for a password (for connecting to the server, not for the password of the new user).This option is never essential, since createuser will automatically prompt for a password if the server demands password authentication. However, createuser 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.

Default connection parameters

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

This utility, like most other IvorySQL utilities, also uses the environment variables supported by libpq

In case of difficulty, see CREATE ROLE and psql for discussions of potential problems and error messages. The database server must be running at the targeted host. Also, any default connection settings and environment variables used by the libpq front-end library will apply.

To create a user joe on the default database server:

To create a user joe on the default database server with prompting for some additional attributes:

To create the same user joe using the server on host eden, port 5000, with attributes explicitly specified, taking a look at the underlying command:

To create the user joe as a superuser, and assign a password immediately:

In the above example, the new password isn’t actually echoed when typed, but we show what was typed for clarity. As you see, the password is encrypted before it is sent to the client.

dropdb [connection-option…​] [option…​] dbname

dropdb accepts the following command-line arguments:

Specifies the name of the database to be removed.

Echo the commands that dropdb generates and sends to the server.

Attempt to terminate all existing connections to the target database before dropping it. See DROP DATABASE for more information on this option.

Issues a verification prompt before doing anything destructive.

Print the dropdb version and exit.

Do not throw an error if the database does not exist. A notice is issued in this case.

Show help about dropdb command line arguments, and exit.

dropdb also accepts the following command-line arguments for connection parameters:

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.

User name to connect as.

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.

Force dropdb to prompt for a password before connecting to a database.This option is never essential, since dropdb will automatically prompt for a password if the server demands password authentication. However, dropdb 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.

Specifies the name of the database to connect to in order to drop the target database. If not specified, the postgres database will be used; if that does not exist (or is the database being dropped), template1 will be used. This can be a connection string. If so, connection string parameters will override any conflicting command line options.

Default connection parameters

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

This utility, like most other IvorySQL utilities, also uses the environment variables supported by libpq .

In case of difficulty, see DROP DATABASE and psql for discussions of potential problems and error messages. The database server must be running at the targeted host. Also, any default connection settings and environment variables used by the libpq front-end library will apply.

To destroy the database demo on the default database server:

To destroy the database demo using the server on host eden, port 5000, with verification and a peek at the underlying command:

dropuser [connection-option…​] [option…​] [username]

dropuser accepts the following command-line arguments:

Specifies the name of the IvorySQL user to be removed. You will be prompted for a name if none is specified on the command line and the -i / --interactive option is used.

Echo the commands that dropuser generates and sends to the server.

Prompt for confirmation before actually removing the user, and prompt for the user name if none is specified on the command line.

Print the dropuser version and exit.

Do not throw an error if the user does not exist. A notice is issued in this case.

Show help about dropuser command line arguments, and exit.

dropuser also accepts the following command-line arguments for connection parameters:

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.

User name to connect as (not the user name to drop).

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.

Force dropuser to prompt for a password before connecting to a database.This option is never essential, since dropuser will automatically prompt for a password if the server demands password authentication. However, dropuser 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.

Default connection parameters

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

This utility, like most other IvorySQL utilities, also uses the environment variables supported by libpq

In case of difficulty, see DROP ROLE and psql for discussions of potential problems and error messages. The database server must be running at the targeted host. Also, any default connection settings and environment variables used by the libpq front-end library will apply.

To remove user joe from the default database server:

To remove user joe using the server on host eden, port 5000, with verification and a peek at the underlying command:

ecpg [option…​] file…​

ecpg accepts the following command-line arguments:

Automatically generate certain C code from SQL code. Currently, this works for EXEC SQL TYPE.

Set a compatibility mode. mode can be INFORMIX, INFORMIX_SE, or ORACLE.

Define a C preprocessor symbol.

Process header files. When this option is specified, the output file extension becomes .h not .c, and the default input file extension is .pgh not .pgc. Also, the -c option is forced on.

Parse system include files as well.

Specify an additional include path, used to find files included via EXEC SQL INCLUDE. Defaults are . (current directory), /usr/local/include, the IvorySQL include directory which is defined at compile time (default: /usr/local/pgsql/include), and /usr/include, in that order.

Specifies that ecpg should write all its output to the given filename. Write -o - to send all output to standard output.

Selects run-time behavior. Option can be one of the following:`no_indicator`Do not use indicators but instead use special values to represent null values. Historically there have been databases using this approach.prepare`Prepare all statements before using them. Libecpg will keep a cache of prepared statements and reuse a statement if it gets executed again. If the cache runs full, libecpg will free the least used statement.`questionmarks Allow question mark as placeholder for compatibility reasons. This used to be the default long ago.

Turn on autocommit of transactions. In this mode, each SQL command is automatically committed unless it is inside an explicit transaction block. In the default mode, commands are committed only when EXEC SQL COMMIT is issued.

Print additional information including the version and the "include" path.

Print the ecpg version and exit.

Show help about ecpg command line arguments, and exit.

When compiling the preprocessed C code files, the compiler needs to be able to find the ECPG header files in the IvorySQL include directory. Therefore, you might have to use the -I option when invoking the compiler (e.g., -I/usr/local/pgsql/include).

Programs using C code with embedded SQL have to be linked against the libecpg library, for example using the linker options -L/usr/local/pgsql/lib -lecpg.

The value of either of these directories that is appropriate for the installation can be found out using pg_config.

If you have an embedded SQL C source file named prog1.pgc, you can create an executable program using the following sequence of commands:

pg_amcheck [option…​] [dbname]

The following command-line options control what is checked:

Check all databases, except for any excluded via --exclude-database.

Check databases matching the specified pattern, except for any excluded by --exclude-database. This option can be specified more than once.

Exclude databases matching the given pattern. This option can be specified more than once.

Check indexes matching the specified pattern, unless they are otherwise excluded. This option can be specified more than once.This is similar to the --relation option, except that it applies only to indexes, not to other relation types.

Exclude indexes matching the specified pattern. This option can be specified more than once.This is similar to the --exclude-relation option, except that it applies only to indexes, not other relation types.

Check relations matching the specified pattern, unless they are otherwise excluded. This option can be specified more than once.Patterns may be unqualified, e.g. myrel*, or they may be schema-qualified, e.g. myschema*.myrel* or database-qualified and schema-qualified, e.g. mydb*.myscheam*.myrel*. A database-qualified pattern will add matching databases to the list of databases to be checked.

Exclude relations matching the specified pattern. This option can be specified more than once.As with --relation, the pattern may be unqualified, schema-qualified, or database- and schema-qualified.

Check tables and indexes in schemas matching the specified pattern, unless they are otherwise excluded. This option can be specified more than once.To select only tables in schemas matching a particular pattern, consider using something like --table=SCHEMAPAT.* --no-dependent-indexes. To select only indexes, consider using something like --index=SCHEMAPAT..A schema pattern may be database-qualified. For example, you may write --schema=mydb.myschema* to select schemas matching myschema* in databases matching mydb*.

Exclude tables and indexes in schemas matching the specified pattern. This option can be specified more than once.As with --schema, the pattern may be database-qualified.

Check tables matching the specified pattern, unless they are otherwise excluded. This option can be specified more than once.This is similar to the --relation option, except that it applies only to tables, materialized views, and sequences, not to indexes.

Exclude tables matching the specified pattern. This option can be specified more than once.This is similar to the --exclude-relation option, except that it applies only to tables, materialized views, and sequences, not to indexes.

By default, if a table is checked, any btree indexes of that table will also be checked, even if they are not explicitly selected by an option such as --index or --relation. This option suppresses that behavior.

By default, if a table is checked, its toast table, if any, will also be checked, even if it is not explicitly selected by an option such as --table or --relation. This option suppresses that behavior.

By default, if an argument to --database, --table, --index, or --relation matches no objects, it is a fatal error. This option downgrades that error to a warning.

The following command-line options control checking of tables:

By default, whenever a toast pointer is encountered in a table, a lookup is performed to ensure that it references apparently-valid entries in the toast table. These checks can be quite slow, and this option can be used to skip them.

After reporting all corruptions on the first page of a table where corruption is found, stop processing that table relation and move on to the next table or index.Note that index checking always stops after the first corrupt page. This option only has meaning relative to table relations.

If all-frozen is given, table corruption checks will skip over pages in all tables that are marked as all frozen.If all-visible is given, table corruption checks will skip over pages in all tables that are marked as all visible.By default, no pages are skipped. This can be specified as none, but since this is the default, it need not be mentioned.

Start checking at the specified block number. An error will occur if the table relation being checked has fewer than this number of blocks. This option does not apply to indexes, and is probably only useful when checking a single table relation. See --endblock for further caveats.

End checking at the specified block number. An error will occur if the table relation being checked has fewer than this number of blocks. This option does not apply to indexes, and is probably only useful when checking a single table relation. If both a regular table and a toast table are checked, this option will apply to both, but higher-numbered toast blocks may still be accessed while validating toast pointers, unless that is suppressed using --exclude-toast-pointers.

The following command-line options control checking of B-tree indexes:

For each index checked, verify the presence of all heap tuples as index tuples in the index using amcheck's heapallindexed option.

For each btree index checked, use amcheck's bt_index_parent_check function, which performs additional checks of parent/child relationships during index checking.The default is to use amcheck’s bt_index_check function, but note that use of the --rootdescend option implicitly selects bt_index_parent_check.

For each index checked, re-find tuples on the leaf level by performing a new search from the root page for each tuple using amcheck's rootdescend option.Use of this option implicitly also selects the --parent-check option.This form of verification was originally written to help in the development of btree index features. It may be of limited use or even of no use in helping detect the kinds of corruption that occur in practice. It may also cause corruption checking to take considerably longer and consume considerably more resources on the server.

The extra checks performed against B-tree indexes when the --parent-check option or the --rootdescend option is specified require relatively strong relation-level locks. These checks are the only checks that will block concurrent data modification from INSERT, UPDATE, and DELETE commands.

The following command-line options control the connection to the server:

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.

User name to connect as.

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.

Force pg_amcheck to prompt for a password before connecting to a database.This option is never essential, since pg_amcheck will automatically prompt for a password if the server demands password authentication. However, pg_amcheck 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.

Specifies a database or connection string to be used to discover the list of databases to be checked. If neither --all nor any option including a database pattern is used, no such connection is required and this option does nothing. Otherwise, any connection string parameters other than the database name which are included in the value for this option will also be used when connecting to the databases being checked. If this option is omitted, the default is postgres or, if that fails, template1.

Other options are also available:

Echo to stdout all SQL sent to the server.

Use num concurrent connections to the server, or one per object to be checked, whichever is less.The default is to use a single connection.

Show progress information. Progress information includes the number of relations for which checking has been completed, and the total size of those relations. It also includes the total number of relations that will eventually be checked, and the estimated size of those relations.

Print more messages. In particular, this will print a message for each relation being checked, and will increase the level of detail shown for server errors.

Print the pg_amcheck version and exit.

Install any missing extensions that are required to check the database(s). If not yet installed, each extension’s objects will be installed into the given schema, or if not specified into schema pg_catalog.At present, the only required extension is amcheck.

Show help about pg_amcheck command line arguments, and exit.

pg_basebackup [option…​]

The following command-line options control the location and format of the output:

Sets the target directory to write the output to. pg_basebackup will create this directory (and any missing parent directories) if it does not exist. If it already exists, it must be empty.When the backup is in tar format, the target directory may be specified as - (dash), causing the tar file to be written to stdout.This option is required.

Selects the format for the output. format can be one of the following: p plain Write the output as plain files, with the same layout as the source server’s data directory and tablespaces. When the cluster has no additional tablespaces, the whole database will be placed in the target directory. If the cluster contains additional tablespaces, the main data directory will be placed in the target directory, but all other tablespaces will be placed in the same absolute path as they have on the source server. (See --tablespace-mapping to change that.)This is the default format. t tar Write the output as tar files in the target directory. The main data directory’s contents will be written to a file named base.tar, and each other tablespace will be written to a separate tar file named after that tablespace’s OID.If the target directory is specified as - (dash), the tar contents will be written to standard output, suitable for piping to (for example) gzip. This is only allowed if the cluster has no additional tablespaces and WAL streaming is not used.

Creates a standby.signal file and appends connection settings to the IvorySQL.auto.conf file in the target directory (or within the base archive file when using tar format). This eases setting up a standby server using the results of the backup.The IvorySQL.auto.conf file will record the connection settings and, if specified, the replication slot that pg_basebackup is using, so that streaming replication will use the same settings later on.

Instructs the server where to place the base backup. The default target is client, which specifies that the backup should be sent to the machine where pg_basebackup is running. If the target is instead set to server:/some/path, the backup will be stored on the machine where the server is running in the /some/path directory. Storing a backup on the server requires superuser privileges or having privileges of the pg_write_server_files role. If the target is set to blackhole, the contents are discarded and not stored anywhere. This should only be used for testing purposes, as you will not end up with an actual backup.Since WAL streaming is implemented by pg_basebackup rather than by the server, this option cannot be used together with -Xstream. Since that is the default, when this option is specified, you must also specify either -Xfetch or -Xnone.

Relocates the tablespace in directory olddir to newdir during the backup. To be effective, olddir must exactly match the path specification of the tablespace as it is defined on the source server. (But it is not an error if there is no tablespace in olddir on the source server.) Meanwhile newdir is a directory in the receiving host’s filesystem. As with the main target directory, newdir need not exist already, but if it does exist it must be empty. Both olddir and newdir must be absolute paths. If either path needs to contain an equal sign (=), precede that with a backslash. This option can be specified multiple times for multiple tablespaces.If a tablespace is relocated in this way, the symbolic links inside the main data directory are updated to point to the new location. So the new data directory is ready to be used for a new server instance with all tablespaces in the updated locations.Currently, this option only works with plain output format; it is ignored if tar format is selected.

Sets the directory to write WAL (write-ahead log) files to. By default WAL files will be placed in the pg_wal subdirectory of the target directory, but this option can be used to place them elsewhere. waldir must be an absolute path. As with the main target directory, waldir need not exist already, but if it does exist it must be empty. This option can only be specified when the backup is in plain format.

Includes the required WAL (write-ahead log) files in the backup. This will include all write-ahead logs generated during the backup. Unless the method none is specified, it is possible to start a postmaster in the target directory without the need to consult the log archive, thus making the output a completely standalone backup.The following methods for collecting the write-ahead logs are supported: n none Don’t include write-ahead logs in the backup. f fetch`The write-ahead log files are collected at the end of the backup. Therefore, it is necessary for the source server’s wal_keep_size parameter to be set high enough that the required log data is not removed before the end of the backup. If the required log data has been recycled before it’s time to transfer it, the backup will fail and be unusable.When tar format is used, the write-ahead log files will be included in the `base.tar file.s stream`Stream write-ahead log data while the backup is being taken. This method will open a second connection to the server and start streaming the write-ahead log in parallel while running the backup. Therefore, it will require two replication connections not just one. As long as the client can keep up with the write-ahead log data, using this method requires no extra write-ahead logs to be saved on the source server.When tar format is used, the write-ahead log files will be written to a separate file named `pg_wal.tar (if the server is a version earlier than 10, the file will be named pg_xlog.tar).This value is the default.

Enables gzip compression of tar file output, with the default compression level. Compression is only available when using the tar format, and the suffix .gz will automatically be added to all tar filenames.

Requests compression of the backup. If client or server is included, it specifies where the compression is to be performed. Compressing on the server will reduce transfer bandwidth but will increase server CPU consumption. The default is client except when --target is used. In that case, the backup is not being sent to the client, so only server compression is sensible. When -Xstream, which is the default, is used, server-side compression will not be applied to the WAL. To compress the WAL, use client-side compression, or specify -Xfetch.The compression method can be set to gzip, lz4, zstd, or none for no compression. A compression detail string can optionally be specified. If the detail string is an integer, it specifies the compression level. Otherwise, it should be a comma-separated list of items, each of the form keyword or keyword=value. Currently, the supported keywords are level and workers.If no compression level is specified, the default compression level will be used. If only a level is specified without mentioning an algorithm, gzip compression will be used if the level is greater than 0, and no compression will be used if the level is 0.When the tar format is used with gzip, lz4, or zstd, the suffix .gz, .lz4, or .zst, respectively, will be automatically added to all tar filenames. When the plain format is used, client-side compression may not be specified, but it is still possible to request server-side compression. If this is done, the server will compress the backup for transmission, and the client will decompress and extract it.When this option is used in combination with -Xstream, pg_wal.tar will be compressed using gzip if client-side gzip compression is selected, but will not be compressed if any other compression algorithm is selected, or if server-side compression is selected.

The following command-line options control the generation of the backup and the invocation of the program:

Sets checkpoint mode to fast (immediate) or spread (the default) .

Specifies that the replication slot named by the --slot option should be created before starting the backup. An error is raised if the slot already exists.

Sets the label for the backup. If none is specified, a default value of “pg_basebackup base backup” will be used.

By default, when pg_basebackup aborts with an error, it removes any directories it might have created before discovering that it cannot finish the job (for example, the target directory and write-ahead log directory). This option inhibits tidying-up and is thus useful for debugging.Note that tablespace directories are not cleaned up either way.

By default, pg_basebackup will wait for all files to be written safely to disk. This option causes pg_basebackup to return without waiting, which is faster, but means that a subsequent operating system crash can leave the base backup corrupt. Generally, this option is useful for testing but should not be used when creating a production installation.

Enables progress reporting. Turning this on will deliver an approximate progress report during the backup. Since the database may change during the backup, this is only an approximation and may not end at exactly 100%. In particular, when WAL log is included in the backup, the total amount of data cannot be estimated in advance, and in this case the estimated target size will increase once it passes the total estimate without WAL.

Sets the maximum transfer rate at which data is collected from the source server. This can be useful to limit the impact of pg_basebackup on the server. Values are in kilobytes per second. Use a suffix of M to indicate megabytes per second. A suffix of k is also accepted, and has no effect. Valid values are between 32 kilobytes per second and 1024 megabytes per second.This option always affects transfer of the data directory. Transfer of WAL files is only affected if the collection method is fetch.

This option can only be used together with -X stream. It causes WAL streaming to use the specified replication slot. If the base backup is intended to be used as a streaming-replication standby using a replication slot, the standby should then use the same replication slot name as primary_slot_name. This ensures that the primary server does not remove any necessary WAL data in the time between the end of the base backup and the start of streaming replication on the new standby.The specified replication slot has to exist unless the option -C is also used.If this option is not specified and the server supports temporary replication slots (version 10 and later), then a temporary replication slot is automatically used for WAL streaming.

Enables verbose mode. Will output some extra steps during startup and shutdown, as well as show the exact file name that is currently being processed if progress reporting is also enabled.

Specifies the checksum algorithm that should be applied to each file included in the backup manifest. Currently, the available algorithms are NONE, CRC32C, SHA224, SHA256, SHA384, and SHA512. The default is CRC32C.If NONE is selected, the backup manifest will not contain any checksums. Otherwise, it will contain a checksum of each file in the backup using the specified algorithm. In addition, the manifest will always contain a SHA256 checksum of its own contents. The SHA algorithms are significantly more CPU-intensive than CRC32C, so selecting one of them may increase the time required to complete the backup.Using a SHA hash function provides a cryptographically secure digest of each file for users who wish to verify that the backup has not been tampered with, while the CRC32C algorithm provides a checksum that is much faster to calculate; it is good at catching errors due to accidental changes but is not resistant to malicious modifications. Note that, to be useful against an adversary who has access to the backup, the backup manifest would need to be stored securely elsewhere or otherwise verified not to have been modified since the backup was taken. pg_verifybackup can be used to check the integrity of a backup against the backup manifest.

Forces all filenames in the backup manifest to be hex-encoded. If this option is not specified, only non-UTF8 filenames are hex-encoded. This option is mostly intended to test that tools which read a backup manifest file properly handle this case.

Prevents the server from estimating the total amount of backup data that will be streamed, resulting in the backup_total column in the pg_stat_progress_basebackup view always being NULL.Without this option, the backup will start by enumerating the size of the entire database, and then go back and send the actual contents. This may make the backup take slightly longer, and in particular it will take longer before the first data is sent. This option is useful to avoid such estimation time if it’s too long.This option is not allowed when using --progress.

Disables generation of a backup manifest. If this option is not specified, the server will generate and send a backup manifest which can be verified using pg_verifybackup. The manifest is a list of every file present in the backup with the exception of any WAL files that may be included. It also stores the size, last modification time, and an optional checksum for each file.

Prevents the creation of a temporary replication slot for the backup.By default, if log streaming is selected but no slot name is given with the -S option, then a temporary replication slot is created (if supported by the source server).The main purpose of this option is to allow taking a base backup when the server has no free replication slots. Using a replication slot is almost always preferred, because it prevents needed WAL from being removed by the server during the backup.

Disables verification of checksums, if they are enabled on the server the base backup is taken from.By default, checksums are verified and checksum failures will result in a non-zero exit status. However, the base backup will not be removed in such a case, as if the --no-clean option had been used. Checksum verification failures will also be reported in the pg_stat_database view.

The following command-line options control the connection to the source server:

Specifies parameters used to connect to the server, as a connection string; these will override any conflicting command line options.The option is called --dbname for consistency with other client applications, but because pg_basebackup doesn’t connect to any particular database in the cluster, any database name in the connection string will be ignored.

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for a Unix domain socket. The default is taken from the PGHOST environment variable, if set, else a Unix domain socket connection is attempted.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the PGPORT environment variable, if set, or a compiled-in default.

Specifies the number of seconds between status packets sent back to the source server. Smaller values allow more accurate monitoring of backup progress from the server. A value of zero disables periodic status updates completely, although an update will still be sent when requested by the server, to avoid timeout-based disconnects. The default value is 10 seconds.

Specifies the user name to connect as.

Prevents issuing 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.

Forces pg_basebackup to prompt for a password before connecting to the source server.This option is never essential, since pg_basebackup will automatically prompt for a password if the server demands password authentication. However, pg_basebackup 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.

Other options are also available:

Prints the pg_basebackup version and exits.

Shows help about pg_basebackup command line arguments, and exits.

This utility, like most other IvorySQL utilities, uses the environment variables supported by libpq .

The environment variable PG_COLOR specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

At the beginning of the backup, a checkpoint needs to be performed on the source server. This can take some time (especially if the option --checkpoint=fast is not used), during which pg_basebackup will appear to be idle.

The backup will include all files in the data directory and tablespaces, including the configuration files and any additional files placed in the directory by third parties, except certain temporary files managed by IvorySQL. But only regular files and directories are copied, except that symbolic links used for tablespaces are preserved. Symbolic links pointing to certain directories known to IvorySQL are copied as empty directories. Other symbolic links and special device files are skipped.

In plain format, tablespaces will be backed up to the same path they have on the source server, unless the option --tablespace-mapping is used. Without this option, running a plain format base backup on the same host as the server will not work if tablespaces are in use, because the backup would have to be written to the same directory locations as the original tablespaces.

When tar format is used, it is the user’s responsibility to unpack each tar file before starting a IvorySQL server that uses the data. If there are additional tablespaces, the tar files for them need to be unpacked in the correct locations. In this case the symbolic links for those tablespaces will be created by the server according to the contents of the tablespace_map file that is included in the base.tar file.

pg_basebackup works with servers of the same or an older major version.

pg_basebackup will preserve group permissions for data files if group permissions are enabled on the source cluster.

To create a base backup of the server at mydbserver and store it in the local directory /usr/local/pgsql/data:

To create a backup of the local server with one compressed tar file for each tablespace, and store it in the directory backup, showing a progress report while running:

To create a backup of a single-tablespace local database and compress this with bzip2:

(This command will fail if there are multiple tablespaces in the database.)

To create a backup of a local database where the tablespace in /opt/ts is relocated to ./backup/ts:

To create a backup of a local server with one tar file for each tablespace compressed with gzip at level 9, stored in the directory backup:

pgbench -i [option…​] [dbname]

pgbench [option…​] [dbname]

pgbench -i creates four tables pgbench_accounts, pgbench_branches, pgbench_history, and pgbench_tellers, destroying any existing tables of these names. Be very careful to use another database if you have tables having these names!

At the default “scale factor” of 1, the tables initially contain this many rows:

You can (and, for most purposes, probably should) increase the number of rows by using the -s (scale factor) option. The -F (fillfactor) option might also be used at this point.

Once you have done the necessary setup, you can run your benchmark with a command that doesn’t include -i, that is

In nearly all cases, you’ll need some options to make a useful test. The most important options are -c (number of clients), -t (number of transactions), -T (time limit), and -f (specify a custom script file). See below for a full list.

The following is divided into three subsections. Different options are used during database initialization and while running benchmarks, but some options are useful in both cases.

pgbench accepts the following command-line initialization arguments:

Specifies the name of the database to test in. If this is not specified, the environment variable PGDATABASE is used. If that is not set, the user name specified for the connection is used.

Required to invoke initialization mode.

Perform just a selected set of the normal initialization steps. init_steps specifies the initialization steps to be performed, using one character per step. Each step is invoked in the specified order. The default is dtgvp. The available steps are:`d` (Drop)Drop any existing pgbench tables.t (create Tables)Create the tables used by the standard pgbench scenario, namely pgbench_accounts, pgbench_branches, pgbench_history, and pgbench_tellers.g or G (Generate data, client-side or server-side)Generate data and load it into the standard tables, replacing any data already present.With g (client-side data generation), data is generated in pgbench client and then sent to the server. This uses the client/server bandwidth extensively through a COPY. pgbench uses the FREEZE option with version 14 or later of IvorySQL to speed up subsequent VACUUM, unless partitions are enabled. Using g causes logging to print one message every 100,000 rows while generating data for the pgbench_accounts table.With G (server-side data generation), only small queries are sent from the pgbench client and then data is actually generated in the server. No significant bandwidth is required for this variant, but the server will do more work. Using G causes logging not to print any progress message while generating data.The default initialization behavior uses client-side data generation (equivalent to g).v (Vacuum)Invoke VACUUM on the standard tables.p (create Primary keys)Create primary key indexes on the standard tables.f (create Foreign keys)Create foreign key constraints between the standard tables. (Note that this step is not performed by default.)

Create the pgbench_accounts, pgbench_tellers and pgbench_branches tables with the given fillfactor. Default is 100.

Perform no vacuuming during initialization. (This option suppresses the v initialization step, even if it was specified in -I.)

Switch logging to quiet mode, producing only one progress message per 5 seconds. The default logging prints one message each 100,000 rows, which often outputs many lines per second (especially on good hardware).This setting has no effect if G is specified in -I.

Multiply the number of rows generated by the scale factor. For example, -s 100 will create 10,000,000 rows in the pgbench_accounts table. Default is 1. When the scale is 20,000 or larger, the columns used to hold account identifiers (aid columns) will switch to using larger integers (bigint), in order to be big enough to hold the range of account identifiers.

Create foreign key constraints between the standard tables. (This option adds the f step to the initialization step sequence, if it is not already present.)

Create indexes in the specified tablespace, rather than the default tablespace.

Create a partitioned pgbench_accounts table with NAME method. Expected values are range or hash. This option requires that --partitions is set to non-zero. If unspecified, default is range.

Create a partitioned pgbench_accounts table with NUM partitions of nearly equal size for the scaled number of accounts. Default is 0, meaning no partitioning.

Create tables in the specified tablespace, rather than the default tablespace.

Create all tables as unlogged tables, rather than permanent tables.

pgbench accepts the following command-line benchmarking arguments:

Add the specified built-in script to the list of scripts to be executed. Available built-in scripts are: tpcb-like, simple-update and select-only. Unambiguous prefixes of built-in names are accepted. With the special name list, show the list of built-in scripts and exit immediately.Optionally, write an integer weight after @ to adjust the probability of selecting this script versus other ones. The default weight is 1. See below for details.

Number of clients simulated, that is, number of concurrent database sessions. Default is 1.

Establish a new connection for each transaction, rather than doing it just once per client session. This is useful to measure the connection overhead.

Print debugging output.

Define a variable for use by a custom script (see below). Multiple -D options are allowed.

Add a transaction script read from filename to the list of scripts to be executed.Optionally, write an integer weight after @ to adjust the probability of selecting this script versus other ones. The default weight is 1. (To use a script file name that includes an @ character, append a weight so that there is no ambiguity, for example filen@me@1.) See below for details.

Number of worker threads within pgbench. Using more than one thread can be helpful on multi-CPU machines. Clients are distributed as evenly as possible among available threads. Default is 1.

Write information about each transaction to a log file. See below for details.

Transactions that last more than limit milliseconds are counted and reported separately, as late.When throttling is used (--rate=…​), transactions that lag behind schedule by more than limit ms, and thus have no hope of meeting the latency limit, are not sent to the server at all. They are counted and reported separately as skipped.When the --max-tries option is used, a transaction which fails due to a serialization anomaly or from a deadlock will not be retried if the total time of all its tries is greater than limit ms. To limit only the time of tries and not their number, use --max-tries=0. By default, the option --max-tries is set to 1 and transactions with serialization/deadlock errors are not retried.

Protocol to use for submitting queries to the server:`simple`: use simple query protocol. extended : use extended query protocol. prepared : use extended query protocol with prepared statements.In the prepared mode, pgbench reuses the parse analysis result starting from the second query iteration, so pgbench runs faster than in other modes.The default is simple query protocol.

Perform no vacuuming before running the test. This option is necessary if you are running a custom test scenario that does not include the standard tables pgbench_accounts, pgbench_branches, pgbench_history, and pgbench_tellers.

Run built-in simple-update script. Shorthand for -b simple-update.

Show progress report every sec seconds. The report includes the time since the beginning of the run, the TPS since the last report, and the transaction latency average, standard deviation, and the number of failed transactions since the last report. Under throttling (-R), the latency is computed with respect to the transaction scheduled start time, not the actual transaction beginning time, thus it also includes the average schedule lag time. When --max-tries is used to enable transaction retries after serialization/deadlock errors, the report includes the number of retried transactions and the sum of all retries.

Report the following statistics for each command after the benchmark finishes: the average per-statement latency (execution time from the perspective of the client), the number of failures, and the number of retries after serialization or deadlock errors in this command. The report displays retry statistics only if the --max-tries option is not equal to 1.

Execute transactions targeting the specified rate instead of running as fast as possible (the default). The rate is given in transactions per second. If the targeted rate is above the maximum possible rate, the rate limit won’t impact the results.The rate is targeted by starting transactions along a Poisson-distributed schedule time line. The expected start time schedule moves forward based on when the client first started, not when the previous transaction ended. That approach means that when transactions go past their original scheduled end time, it is possible for later ones to catch up again.When throttling is active, the transaction latency reported at the end of the run is calculated from the scheduled start times, so it includes the time each transaction had to wait for the previous transaction to finish. The wait time is called the schedule lag time, and its average and maximum are also reported separately. The transaction latency with respect to the actual transaction start time, i.e., the time spent executing the transaction in the database, can be computed by subtracting the schedule lag time from the reported latency.If --latency-limit is used together with --rate, a transaction can lag behind so much that it is already over the latency limit when the previous transaction ends, because the latency is calculated from the scheduled start time. Such transactions are not sent to the server, but are skipped altogether and counted separately.A high schedule lag time is an indication that the system cannot process transactions at the specified rate, with the chosen number of clients and threads. When the average transaction execution time is longer than the scheduled interval between each transaction, each successive transaction will fall further behind, and the schedule lag time will keep increasing the longer the test run is. When that happens, you will have to reduce the specified transaction rate.

Report the specified scale factor in pgbench’s output. With the built-in tests, this is not necessary; the correct scale factor will be detected by counting the number of rows in the pgbench_branches table. However, when testing only custom benchmarks (-f option), the scale factor will be reported as 1 unless this option is used.

Run built-in select-only script. Shorthand for -b select-only.

Number of transactions each client runs. Default is 10.

Run the test for this many seconds, rather than a fixed number of transactions per client. -t and -T are mutually exclusive.

Vacuum all four standard tables before running the test. With neither -n nor -v, pgbench will vacuum the pgbench_tellers and pgbench_branches tables, and will truncate pgbench_history.

Length of aggregation interval (in seconds). May be used only with -l option. With this option, the log contains per-interval summary data, as described below.

Report failures in per-transaction and aggregation logs, as well as in the main and per-script reports, grouped by the following types:serialization failures;deadlock failures.

Set the filename prefix for the log files created by --log. The default is pgbench_log.

Enable retries for transactions with serialization/deadlock errors and set the maximum number of these tries. This option can be combined with the --latency-limit option which limits the total time of all transaction tries; moreover, you cannot use an unlimited number of tries (--max-tries=0) without --latency-limit or --time. The default value is 1 and transactions with serialization/deadlock errors are not retried.

When showing progress (option -P), use a timestamp (Unix epoch) instead of the number of seconds since the beginning of the run. The unit is in seconds, with millisecond precision after the dot. This helps compare logs generated by various tools.

Set random generator seed. Seeds the system random number generator, which then produces a sequence of initial generator states, one for each thread. Values for seed may be: time (the default, the seed is based on the current time), rand (use a strong random source, failing if none is available), or an unsigned decimal integer value. The random generator is invoked explicitly from a pgbench script (random…​ functions) or implicitly (for instance option --rate uses it to schedule transactions). When explicitly set, the value used for seeding is shown on the terminal. Any value allowed for seed may also be provided through the environment variable PGBENCH_RANDOM_SEED. To ensure that the provided seed impacts all possible uses, put this option first or use the environment variable.Setting the seed explicitly allows to reproduce a pgbench run exactly, as far as random numbers are concerned. As the random state is managed per thread, this means the exact same pgbench run for an identical invocation if there is one client per thread and there are no external or data dependencies. From a statistical viewpoint reproducing runs exactly is a bad idea because it can hide the performance variability or improve performance unduly, e.g., by hitting the same pages as a previous run. However, it may also be of great help for debugging, for instance re-running a tricky case which leads to an error. Use wisely.

Sampling rate, used when writing data into the log, to reduce the amount of log generated. If this option is given, only the specified fraction of transactions are logged. 1.0 means all transactions will be logged, 0.05 means only 5% of the transactions will be logged.Remember to take the sampling rate into account when processing the log file. For example, when computing TPS values, you need to multiply the numbers accordingly (e.g., with 0.01 sample rate, you’ll only get 1/100 of the actual TPS).

Show the actual code of builtin script scriptname on stderr, and exit immediately.

Print messages about all errors and failures (errors without retrying) including which limit for retries was exceeded and how far it was exceeded for the serialization/deadlock failures. (Note that in this case the output can be significantly increased.).

pgbench also accepts the following common command-line arguments for connection parameters:

The database server’s host name

The database server’s port number

The user name to connect as

Print the pgbench version and exit.

Show help about pgbench command line arguments, and exit.

A successful run will exit with status 0. Exit status 1 indicates static problems such as invalid command-line options or internal errors which are supposed to never occur. Early errors that occur when starting benchmark such as initial connection failures also exit with status 1. Errors during the run such as database errors or problems in the script will result in exit status 2. In the latter case, pgbench will print partial results.

Default connection parameters.

This utility, like most other IvorySQL utilities, uses the environment variables supported by libpq .

The environment variable PG_COLOR specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

pg_config [option…​]

To use pg_config, supply one or more of the following options:

Print the location of user executables. Use this, for example, to find the psql program. This is normally also the location where the pg_config program resides.

Print the location of documentation files.

Print the location of HTML documentation files.

Print the location of C header files of the client interfaces.

Print the location of other C header files.

Print the location of C header files for server programming.

Print the location of object code libraries.

Print the location of dynamically loadable modules, or where the server would search for them. (Other architecture-dependent data files might also be installed in this directory.)

Print the location of locale support files. (This will be an empty string if locale support was not configured when IvorySQL was built.)

Print the location of manual pages.

Print the location of architecture-independent support files.

Print the location of system-wide configuration files.

Print the location of extension makefiles.

Print the options that were given to the configure script when IvorySQL was configured for building. This can be used to reproduce the identical configuration, or to find out with what options a binary package was built. (Note however that binary packages often contain vendor-specific custom patches.) See also the examples below.

Print the value of the CC variable that was used for building IvorySQL. This shows the C compiler used.

Print the value of the CPPFLAGS variable that was used for building IvorySQL. This shows C compiler switches needed at preprocessing time (typically, -I switches).

Print the value of the CFLAGS variable that was used for building IvorySQL. This shows C compiler switches.

Print the value of the CFLAGS_SL variable that was used for building IvorySQL. This shows extra C compiler switches used for building shared libraries.

Print the value of the LDFLAGS variable that was used for building IvorySQL. This shows linker switches.

Print the value of the LDFLAGS_EX variable that was used for building IvorySQL. This shows linker switches used for building executables only.

Print the value of the LDFLAGS_SL variable that was used for building IvorySQL. This shows linker switches used for building shared libraries only.

Print the value of the LIBS variable that was used for building IvorySQL. This normally contains -l switches for external libraries linked into IvorySQL.

Print the version of IvorySQL.

Show help about pg_config command line arguments, and exit.

If more than one option is given, the information is printed in that order, one item per line. If no options are given, all available information is printed, with labels.

To reproduce the build configuration of the current IvorySQL installation, run the following command:

The output of pg_config --configure contains shell quotation marks so arguments with spaces are represented correctly. Therefore, using eval is required for proper results.

pg_dump [connection-option…​] [option…​] [dbname]

The following command-line options control the content and format of the output.

The following command-line options control the database connection parameters.

This utility, like most other IvorySQL utilities, also uses the environment variables supported by libpq.

pg_dump internally executes SELECT statements. If you have problems running pg_dump, make sure you are able to select information from the database using, for example, [psql](https://www.IvorySQL.org/docs/current/app-psql.html). Also, any default connection settings and environment variables used by the libpq front-end library will apply.

The database activity of pg_dump is normally collected by the cumulative statistics system. If this is undesirable, you can set parameter track_counts to false via PGOPTIONS or the ALTER USER command.

If your database cluster has any local additions to the template1 database, be careful to restore the output of pg_dump into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects. To make an empty database without any local additions, copy from template0 not template1, for example:

When a data-only dump is chosen and the option --disable-triggers is used, pg_dump emits commands to disable triggers on user tables before inserting the data, and then commands to re-enable them after the data has been inserted. If the restore is stopped in the middle, the system catalogs might be left in the wrong state.

The dump file produced by pg_dump does not contain the statistics used by the optimizer to make query planning decisions. Therefore, it is wise to run ANALYZE after restoring from a dump file to ensure optimal performance.

When dumping logical replication subscriptions, pg_dump will generate CREATE SUBSCRIPTION commands that use the connect = false option, so that restoring the subscription does not make remote connections for creating a replication slot or for initial table copy. That way, the dump can be restored without requiring network access to the remote servers. It is then up to the user to reactivate the subscriptions in a suitable way. If the involved hosts have changed, the connection information might have to be changed. It might also be appropriate to truncate the target tables before initiating a new full table copy. If users intend to copy initial data during refresh they must create the slot with two_phase = false. After the initial sync, the two_phase option will be automatically enabled by the subscriber if the subscription had been originally created with two_phase = true option.

To dump a database called mydb into an SQL-script file:

To reload such a script into a (freshly created) database named newdb:

To dump a database into a custom-format archive file:

To dump a database into a directory-format archive:

To dump a database into a directory-format archive in parallel with 5 worker jobs:

To reload an archive file into a (freshly created) database named newdb:

To reload an archive file into the same database it was dumped from, discarding the current contents of that database:

To dump a single table named mytab:

To dump all tables whose names start with emp in the detroit schema, except for the table named employee_log:

To dump all schemas whose names start with east or west and end in gsm, excluding any schemas whose names contain the word test:

The same, using regular expression notation to consolidate the switches:

To dump all database objects except for tables whose names begin with ts_:

To specify an upper-case or mixed-case name in -t and related switches, you need to double-quote the name; else it will be folded to lower case.But double quotes are special to the shell, so in turn they must be quoted. Thus, to dump a single table with a mixed-case name, you need something like

pg_dumpall [connection-option…​] [option…​]

The following command-line options control the content and format of the output.

Dump only the data, not the schema (data definitions).

Include SQL commands to clean (drop) databases before recreating them. DROP commands for roles and tablespaces are added as well.

Create the dump in the specified character set encoding. By default, the dump is created in the database encoding. (Another way to get the same result is to set the PGCLIENTENCODING environment variable to the desired dump encoding.)

Send output to the specified file. If this is omitted, the standard output is used.

Dump only global objects (roles and tablespaces), no databases.

Do not output commands to set ownership of objects to match the original database. By default, pg_dumpall issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created schema elements. These statements will fail when the script is run unless it is started by a superuser (or the same user that owns all of the objects in the script). To make a script that can be restored by any user, but will give that user ownership of all the objects, specify -O.

Dump only roles, no databases or tablespaces.

Dump only the object definitions (schema), not data.

Specify the superuser user name to use when disabling triggers. This is relevant only if --disable-triggers is used. (Usually, it’s better to leave this out, and instead start the resulting script as superuser.)

Dump only tablespaces, no databases or roles.

Specifies verbose mode. This will cause pg_dumpall to output start/stop times to the dump file, and progress messages to standard error. Repeating the option causes additional debug-level messages to appear on standard error. The option is also passed down to pg_dump.

Print the pg_dumpall version and exit.

Prevent dumping of access privileges (grant/revoke commands).

This option is for use by in-place upgrade utilities. Its use for other purposes is not recommended or supported. The behavior of the option may change in future releases without notice.

Dump data as INSERT commands with explicit column names (INSERT INTO table (column, …​) VALUES …​). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-IvorySQL databases.

This option disables the use of dollar quoting for function bodies, and forces them to be quoted using SQL standard string syntax.

This option is relevant only when creating a data-only dump. It instructs pg_dumpall to include commands to temporarily disable triggers on the target tables while the data is restored. Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data restore.Presently, the commands emitted for --disable-triggers must be done as superuser. So, you should also specify a superuser name with -S, or preferably be careful to start the resulting script as a superuser.

Do not dump databases whose name matches pattern. Multiple patterns can be excluded by writing multiple --exclude-database switches. The pattern parameter is interpreted as a pattern according to the same rules used by psql’s \d commands, so multiple databases can also be excluded by writing wildcard characters in the pattern. When using wildcards, be careful to quote the pattern if needed to prevent shell wildcard expansion.

Use the specified value of extra_float_digits when dumping floating-point data, instead of the maximum available precision. Routine dumps made for backup purposes should not use this option.

Use conditional commands (i.e., add an IF EXISTS clause) to drop databases and other objects. This option is not valid unless --clean is also specified.

Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-IvorySQL databases. Note that the restore might fail altogether if you have rearranged column order. The --column-inserts option is safer, though even slower.

When dumping data for a table partition, make the COPY or INSERT statements target the root of the partitioning hierarchy that contains it, rather than the partition itself. This causes the appropriate partition to be re-determined for each row when the data is loaded. This may be useful when restoring data on a server where rows do not always fall into the same partitions as they did on the original server. That could happen, for example, if the partitioning column is of type text and the two systems have different definitions of the collation used to sort the partitioning column.

Do not wait forever to acquire shared table locks at the beginning of the dump. Instead, fail if unable to lock a table within the specified timeout. The timeout may be specified in any of the formats accepted by SET statement_timeout.

Do not dump comments.

Do not dump publications.

Do not dump passwords for roles. When restored, roles will have a null password, and password authentication will always fail until the password is set. Since password values aren’t needed when this option is specified, the role information is read from the catalog view pg_roles instead of pg_authid. Therefore, this option also helps if access to pg_authid is restricted by some security policy.

Do not dump security labels.

Do not dump subscriptions.

By default, pg_dumpall will wait for all files to be written safely to disk. This option causes pg_dumpall to return without waiting, which is faster, but means that a subsequent operating system crash can leave the dump corrupt. Generally, this option is useful for testing but should not be used when dumping data from production installation.

Do not output commands to select table access methods. With this option, all objects will be created with whichever table access method is the default during restore.

Do not output commands to create tablespaces nor select tablespaces for objects. With this option, all objects will be created in whichever tablespace is the default during restore.

Do not output commands to set TOAST compression methods. With this option, all columns will be restored with the default compression setting.

Do not dump the contents of unlogged tables. This option has no effect on whether or not the table definitions (schema) are dumped; it only suppresses dumping the table data.

Add ON CONFLICT DO NOTHING to INSERT commands. This option is not valid unless --inserts or --column-inserts is also specified.

Force quoting of all identifiers. This option is recommended when dumping a database from a server whose IvorySQL major version is different from pg_dumpall’s, or when the output is intended to be loaded into a server of a different major version. By default, pg_dumpall quotes only identifiers that are reserved words in its own major version. This sometimes results in compatibility issues when dealing with servers of other versions that may have slightly different sets of reserved words. Using --quote-all-identifiers prevents such issues, at the price of a harder-to-read dump script.

Dump data as INSERT commands (rather than COPY). Controls the maximum number of rows per INSERT command. The value specified must be a number greater than zero. Any error during restoring will cause only rows that are part of the problematic INSERT to be lost, rather than the entire table contents.

Output SQL-standard SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to determine object ownership. This makes the dump more standards compatible, but depending on the history of the objects in the dump, might not restore properly.

Show help about pg_dumpall command line arguments, and exit.

The following command-line options control the database connection parameters.

Specifies parameters used to connect to the server, as a connection string; these will override any conflicting command line options.The option is called --dbname for consistency with other client applications, but because pg_dumpall needs to connect to many databases, the database name in the connection string will be ignored. Use the -l option to specify the name of the database used for the initial connection, which will dump global objects and discover what other databases should be dumped.

Specifies the host name of the machine on which the database server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. The default is taken from the PGHOST environment variable, if set, else a Unix domain socket connection is attempted.

Specifies the name of the database to connect to for dumping global objects and discovering what other databases should be dumped. If not specified, the postgres database will be used, and if that does not exist, template1 will be used.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the PGPORT environment variable, if set, or a compiled-in default.

User name to connect as.

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.

Force pg_dumpall to prompt for a password before connecting to a database.This option is never essential, since pg_dumpall will automatically prompt for a password if the server demands password authentication. However, pg_dumpall 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.Note that the password prompt will occur again for each database to be dumped. Usually, it’s better to set up a ~/.pgpass file than to rely on manual password entry.

Specifies a role name to be used to create the dump. This option causes pg_dumpall to issue a SET ROLE rolename command after connecting to the database. It is useful when the authenticated user (specified by -U) lacks privileges needed by pg_dumpall, but can switch to a role with the required rights. Some installations have a policy against logging in directly as a superuser, and use of this option allows dumps to be made without violating the policy.

Default connection parameters

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

This utility, like most other IvorySQL utilities, also uses the environment variables supported by libpq

Since pg_dumpall calls pg_dump internally, some diagnostic messages will refer to pg_dump.

The --clean option can be useful even when your intention is to restore the dump script into a fresh cluster. Use of --clean authorizes the script to drop and re-create the built-in postgres and template1 databases, ensuring that those databases will retain the same properties (for instance, locale and encoding) that they had in the source cluster. Without the option, those databases will retain their existing database-level properties, as well as any pre-existing contents.

Once restored, it is wise to run ANALYZE on each database so the optimizer has useful statistics. You can also run vacuumdb -a -z to analyze all databases.

The dump script should not be expected to run completely without errors. In particular, because the script will issue CREATE ROLE for every role existing in the source cluster, it is certain to get a “role already exists” error for the bootstrap superuser, unless the destination cluster was initialized with a different bootstrap superuser name. This error is harmless and should be ignored. Use of the --clean option is likely to produce additional harmless error messages about non-existent objects, although you can minimize those by adding --if-exists.

pg_dumpall requires all needed tablespace directories to exist before the restore; otherwise, database creation will fail for databases in non-default locations.

To dump all databases:

To restore database(s) from this file, you can use:

It is not important to which database you connect here since the script file created by pg_dumpall will contain the appropriate commands to create and connect to the saved databases. An exception is that if you specified --clean, you must connect to the postgres database initially; the script will attempt to drop other databases immediately, and that will fail for the database you are connected to.

pg_isready [connection-option…​] [option…​]

Specifies the name of the database to connect to. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options.

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix-domain socket.

Specifies the TCP port or the local Unix-domain socket file extension on which the server is listening for connections. Defaults to the value of the PGPORT environment variable or, if not set, to the port specified at compile time, usually 5432.

Do not display status message. This is useful when scripting.

The maximum number of seconds to wait when attempting connection before returning that the server is not responding. Setting to 0 disables. The default is 3 seconds.

Connect to the database as the user username instead of the default.

Print the pg_isready version and exit.

Show help about pg_isready command line arguments, and exit.

pg_isready returns 0 to the shell if the server is accepting connections normally, 1 if the server is rejecting connections (for example during startup), 2 if there was no response to the connection attempt, and 3 if no attempt was made (for example due to invalid parameters).

pg_isready, like most other IvorySQL utilities, also uses the environment variables supported by libpq .

The environment variable PG_COLOR specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

It is not necessary to supply correct user name, password, or database name values to obtain the server status; however, if incorrect values are provided, the server will log a failed connection attempt.

Standard Usage:

Running with connection parameters to a IvorySQL cluster in startup:

Running with connection parameters to a non-responsive IvorySQL cluster:

pg_receivewal [option…​]

Directory to write the output to.This parameter is required.

Automatically stop replication and exit with normal exit status 0 when receiving reaches the specified LSN.If there is a record with LSN exactly equal to lsn, the record will be processed.

Do not error out when --create-slot is specified and a slot with the specified name already exists.

Don’t loop on connection errors. Instead, exit right away with an error.

This option causes pg_receivewal to not force WAL data to be flushed to disk. This is faster, but means that a subsequent operating system crash can leave the WAL segments corrupt. Generally, this option is useful for testing but should not be used when doing WAL archiving on a production deployment.This option is incompatible with --synchronous.

Specifies the number of seconds between status packets sent back to the server. This allows for easier monitoring of the progress from server. A value of zero disables the periodic status updates completely, although an update will still be sent when requested by the server, to avoid timeout disconnect. The default value is 10 seconds.

Require pg_receivewal to use an existing replication slot, When this option is used, pg_receivewal will report a flush position to the server, indicating when each segment has been synchronized to disk so that the server can remove that segment if it is not otherwise needed.When the replication client of pg_receivewal is configured on the server as a synchronous standby, then using a replication slot will report the flush position to the server, but only when a WAL file is closed. Therefore, that configuration will cause transactions on the primary to wait for a long time and effectively not work satisfactorily. The option --synchronous (see below) must be specified in addition to make this work correctly.

Flush the WAL data to disk immediately after it has been received. Also send a status packet back to the server immediately after flushing, regardless of --status-interval.This option should be specified if the replication client of pg_receivewal is configured on the server as a synchronous standby, to ensure that timely feedback is sent to the server.

Enables verbose mode.

Enables compression of write-ahead logs.The compression method can be set to gzip, lz4 (if IvorySQL was compiled with --with-lz4) or none for no compression. A compression detail string can optionally be specified. If the detail string is an integer, it specifies the compression level. Otherwise, it should be a comma-separated list of items, each of the form keyword or keyword=value. Currently, the only supported keyword is level.If no compression level is specified, the default compression level will be used. If only a level is specified without mentioning an algorithm, gzip compression will be used if the level is greater than 0, and no compression will be used if the level is 0.The suffix .gz will automatically be added to all filenames when using gzip, and the suffix .lz4 is added when using lz4.

The following command-line options control the database connection parameters.

Specifies parameters used to connect to the server, as a connection string; these will override any conflicting command line options.The option is called --dbname for consistency with other client applications, but because pg_receivewal doesn’t connect to any particular database in the cluster, database name in the connection string will be ignored.

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. The default is taken from the PGHOST environment variable, if set, else a Unix domain socket connection is attempted.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the PGPORT environment variable, if set, or a compiled-in default.

User name to connect as.

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.

Force pg_receivewal to prompt for a password before connecting to a database.This option is never essential, since pg_receivewal will automatically prompt for a password if the server demands password authentication. However, pg_receivewal 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.

pg_receivewal can perform one of the two following actions in order to control physical replication slots:

Create a new physical replication slot with the name specified in --slot, then exit.

Drop the replication slot with the name specified in --slot, then exit.

Other options are also available:

Print the pg_receivewal version and exit.

Show help about pg_receivewal command line arguments, and exit.

pg_receivewal will exit with status 0 when terminated by the SIGINT signal. (That is the normal way to end it. Hence it is not an error.) For fatal errors or other signals, the exit status will be nonzero.

This utility, like most other IvorySQL utilities, uses the environment variables supported by libpq

The environment variable PG_COLOR specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

When using pg_receivewal instead of archive_command or archive_library as the main WAL backup method, it is strongly recommended to use replication slots. Otherwise, the server is free to recycle or remove write-ahead log files before they are backed up, because it does not have any information, either from archive_command or archive_library or the replication slots, about how far the WAL stream has been archived. Note, however, that a replication slot will fill up the server’s disk space if the receiver does not keep up with fetching the WAL data.

pg_receivewal will preserve group permissions on the received WAL files if group permissions are enabled on the source cluster.

To stream the write-ahead log from the server at mydbserver and store it in the local directory /usr/local/pgsql/archive:

pg_recvlogical [option…​]

At least one of the following options must be specified to select an action:

Create a new logical replication slot with the name specified by --slot, using the output plugin specified by --plugin, for the database specified by --dbname.The --two-phase can be specified with --create-slot to enable decoding of prepared transactions.

Drop the replication slot with the name specified by --slot, then exit.

Begin streaming changes from the logical replication slot specified by --slot, continuing until terminated by a signal. If the server side change stream ends with a server shutdown or disconnect, retry in a loop unless --no-loop is specified.The stream format is determined by the output plugin specified when the slot was created.The connection must be to the same database used to create the slot.

--create-slot and --start can be specified together. --drop-slot cannot be combined with another action.

The following command-line options control the location and format of the output and other replication behavior:

In --start mode, automatically stop replication and exit with normal exit status 0 when receiving reaches the specified LSN. If specified when not in --start mode, an error is raised.If there’s a record with LSN exactly equal to lsn, the record will be output.The --endpos option is not aware of transaction boundaries and may truncate output partway through a transaction. Any partially output transaction will not be consumed and will be replayed again when the slot is next read from. Individual messages are never truncated.

Write received and decoded transaction data into this file. Use - for stdout.

Specifies how often pg_recvlogical should issue fsync() calls to ensure the output file is safely flushed to disk.The server will occasionally request the client to perform a flush and report the flush position to the server. This setting is in addition to that, to perform flushes more frequently.Specifying an interval of 0 disables issuing fsync() calls altogether, while still reporting progress to the server. In this case, data could be lost in the event of a crash.

In --start mode, start replication from the given LSN. For details on the effect of this.

Do not error out when --create-slot is specified and a slot with the specified name already exists.

When the connection to the server is lost, do not retry in a loop, just exit.

Pass the option name to the output plugin with, if specified, the option value value. Which options exist and their effects depends on the used output plugin.

When creating a slot, use the specified logical decoding output plugin. This option has no effect if the slot already exists.

This option has the same effect as the option of the same name in pg_receivewal. See the description there.

In --start mode, use the existing logical replication slot named slot_name. In --create-slot mode, create the slot with this name. In --drop-slot mode, delete the slot with this name.

Enables decoding of prepared transactions. This option may only be specified with --create-slot

Enables verbose mode.

The following command-line options control the database connection parameters.

The database to connect to. See the description of the actions for what this means in detail. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options. Defaults to the user name.

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. The default is taken from the PGHOST environment variable, if set, else a Unix domain socket connection is attempted.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the PGPORT environment variable, if set, or a compiled-in default.

User name to connect as. Defaults to current operating system user name.

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.

Force pg_recvlogical to prompt for a password before connecting to a database.This option is never essential, since pg_recvlogical will automatically prompt for a password if the server demands password authentication. However, pg_recvlogical 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.

The following additional options are available:

Print the pg_recvlogical version and exit.

Show help about pg_recvlogical command line arguments, and exit.

This utility, like most other IvorySQL utilities, uses the environment variables supported by libpq .

The environment variable PG_COLOR specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

pg_recvlogical will preserve group permissions on the received WAL files if group permissions are enabled on the source cluster.

pg_restore [connection-option…​] [option…​] [filename]

pg_restore accepts the following command line arguments.

Specifies the location of the archive file (or directory, for a directory-format archive) to be restored. If not specified, the standard input is used.

Restore only the data, not the schema (data definitions). Table data, large objects, and sequence values are restored, if present in the archive.This option is similar to, but for historical reasons not identical to, specifying --section=data.

Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)

Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.With --create, pg_restore also restores the database’s comment if any, and any configuration variable settings that are specific to this database, that is, any ALTER DATABASE …​ SET …​ and ALTER ROLE …​ IN DATABASE …​ SET …​ commands that mention this database. Access privileges for the database itself are also restored, unless --no-acl is specified.When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

Connect to database dbname and restore directly into the database. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options.

Exit if an error is encountered while sending SQL commands to the database. The default is to continue and to display a count of errors at the end of the restoration.

Specify output file for generated script, or for the listing when used with -l. Use - for stdout.

Specify format of the archive. It is not necessary to specify the format, since pg_restore will determine the format automatically. If specified, it can be one of the following:`c` custom`The archive is in the custom format of pg_dump.`d directory`The archive is a directory archive.`t tar`The archive is a `tar archive.

Restore definition of named index only. Multiple indexes may be specified with multiple -I switches.

Run the most time-consuming steps of pg_restore — those that load data, create indexes, or create constraints — concurrently, using up to number-of-jobs concurrent sessions. This option can dramatically reduce the time to restore a large database to a server running on a multiprocessor machine. This option is ignored when emitting a script rather than connecting directly to a database server.Each job is one process or one thread, depending on the operating system, and uses a separate connection to the server.The optimal value for this option depends on the hardware setup of the server, of the client, and of the network. Factors include the number of CPU cores and the disk setup. A good place to start is the number of CPU cores on the server, but values larger than that can also lead to faster restore times in many cases. Of course, values that are too high will lead to decreased performance because of thrashing.Only the custom and directory archive formats are supported with this option. The input must be a regular file or directory (not, for example, a pipe or standard input). Also, multiple jobs cannot be used together with the option --single-transaction.

List the table of contents of the archive. The output of this operation can be used as input to the -L option. Note that if filtering switches such as -n or -t are used with -l, they will restrict the items listed.

Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored.list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line. See below for examples.

Restore only objects that are in the named schema. Multiple schemas may be specified with multiple -n switches. This can be combined with the -t option to restore just a specific table.

Do not restore objects that are in the named schema. Multiple schemas to be excluded may be specified with multiple -N switches.When both -n and -N are given for the same schema name, the -N switch wins and the schema is excluded.

Do not output commands to set ownership of objects to match the original database. By default, pg_restore issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created schema elements. These statements will fail unless the initial connection to the database is made by a superuser (or the same user that owns all of the objects in the script). With -O, any user name can be used for the initial connection, and this user will own all the created objects.

Restore the named function only. Be careful to spell the function name and arguments exactly as they appear in the dump file’s table of contents. Multiple functions may be specified with multiple -P switches.

This option is obsolete but still accepted for backwards compatibility.

Restore only the schema (data definitions), not data, to the extent that schema entries are present in the archive.This option is the inverse of --data-only. It is similar to, but for historical reasons not identical to, specifying --section=pre-data --section=post-data.(Do not confuse this with the --schema option, which uses the word “schema” in a different meaning.)

Specify the superuser user name to use when disabling triggers. This is relevant only if --disable-triggers is used.

Restore definition and/or data of only the named table. For this purpose, “table” includes views, materialized views, sequences, and foreign tables. Multiple tables can be selected by writing multiple -t switches. This option can be combined with the -n option to specify table(s) in a particular schema.NoteWhen -t is specified, pg_restore makes no attempt to restore any other database objects that the selected table(s) might depend upon. Therefore, there is no guarantee that a specific-table restore into a clean database will succeed.NoteThis flag does not behave identically to the -t flag of pg_dump. There is not currently any provision for wild-card matching in pg_restore, nor can you include a schema name within its -t. And, while pg_dump’s -t flag will also dump subsidiary objects (such as indexes) of the selected table(s), pg_restore’s -t flag does not include such subsidiary objects.

Restore named trigger only. Multiple triggers may be specified with multiple -T switches.

Specifies verbose mode. This will cause pg_restore to output detailed object comments and start/stop times to the output file, and progress messages to standard error. Repeating the option causes additional debug-level messages to appear on standard error.

Print the pg_restore version and exit.

Prevent restoration of access privileges (grant/revoke commands).

Execute the restore as a single transaction (that is, wrap the emitted commands in BEGIN / COMMIT). This ensures that either all the commands complete successfully, or no changes are applied. This option implies --exit-on-error.

This option is relevant only when performing a data-only restore. It instructs pg_restore to execute commands to temporarily disable triggers on the target tables while the data is restored. Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data restore.Presently, the commands emitted for --disable-triggers must be done as superuser. So you should also specify a superuser name with -S or, preferably, run pg_restore as a IvorySQL superuser.

This option is relevant only when restoring the contents of a table which has row security. By default, pg_restore will set row_security to off, to ensure that all data is restored in to the table. If the user does not have sufficient privileges to bypass row security, then an error is thrown. This parameter instructs pg_restore to set row_security to on instead, allowing the user to attempt to restore the contents of the table with row security enabled. This might still fail if the user does not have the right to insert the rows from the dump into the table.Note that this option currently also requires the dump be in INSERT format, as COPY FROM does not support row security.

Use conditional commands (i.e., add an IF EXISTS clause) to drop database objects. This option is not valid unless --clean is also specified.

Do not output commands to restore comments, even if the archive contains them.

By default, table data is restored even if the creation command for the table failed (e.g., because it already exists). With this option, data for such a table is skipped. This behavior is useful if the target database already contains the desired table contents. For example, auxiliary tables for IvorySQL extensions such as PostGIS might already be loaded in the target database; specifying this option prevents duplicate or obsolete data from being loaded into them.This option is effective only when restoring directly into a database, not when producing SQL script output.

Do not output commands to restore publications, even if the archive contains them.

Do not output commands to restore security labels, even if the archive contains them.

Do not output commands to restore subscriptions, even if the archive contains them.

Do not output commands to select table access methods. With this option, all objects will be created with whichever access method is the default during restore.

Do not output commands to select tablespaces. With this option, all objects will be created in whichever tablespace is the default during restore.

Only restore the named section. The section name can be pre-data, data, or post-data. This option can be specified more than once to select multiple sections. The default is to restore all sections.The data section contains actual table data as well as large-object definitions. Post-data items consist of definitions of indexes, triggers, rules and constraints other than validated check constraints. Pre-data items consist of all other data definition items.

Require that each schema (-n/--schema) and table (-t/--table) qualifier match at least one schema/table in the backup file.

Output SQL-standard SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to determine object ownership. This makes the dump more standards-compatible, but depending on the history of the objects in the dump, might not restore properly.

Show help about pg_restore command line arguments, and exit.

pg_restore also accepts the following command line arguments for connection parameters:

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. The default is taken from the PGHOST environment variable, if set, else a Unix domain socket connection is attempted.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults to the PGPORT environment variable, if set, or a compiled-in default.

User name to connect as.

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.

Force pg_restore to prompt for a password before connecting to a database.This option is never essential, since pg_restore will automatically prompt for a password if the server demands password authentication. However, pg_restore 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.

Specifies a role name to be used to perform the restore. This option causes pg_restore to issue a SET ROLE rolename command after connecting to the database. It is useful when the authenticated user (specified by -U) lacks privileges needed by pg_restore, but can switch to a role with the required rights. Some installations have a policy against logging in directly as a superuser, and use of this option allows restores to be performed without violating the policy.

Default connection parameters

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

This utility, like most other IvorySQL utilities, also uses the environment variables supported by libpq. However, it does not read PGDATABASE when a database name is not supplied.

When a direct database connection is specified using the -d option, pg_restore internally executes SQL statements. If you have problems running pg_restore, make sure you are able to select information from the database using, for example, psql. Also, any default connection settings and environment variables used by the libpq front-end library will apply.

If your installation has any local additions to the template1 database, be careful to load the output of pg_restore into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects. To make an empty database without any local additions, copy from template0 not template1, for example:

The limitations of pg_restore are detailed below.

See also the pg_dump documentation for details on limitations of pg_dump.

Assume we have dumped a database called mydb into a custom-format dump file:

To drop the database and recreate it from the dump:

The database named in the -d switch can be any database existing in the cluster; pg_restore only uses it to issue the CREATE DATABASE command for mydb. With -C, data is always restored into the database name that appears in the dump file.

To restore the dump into a new database called newdb:

Notice we don’t use -C, and instead connect directly to the database to be restored into. Also note that we clone the new database from template0 not template1, to ensure it is initially empty.

To reorder database items, it is first necessary to dump the table of contents of the archive:

The listing file consists of a header and one line for each item, e.g.:

Semicolons start a comment, and the numbers at the start of lines refer to the internal archive ID assigned to each item.

Lines in the file can be commented out, deleted, and reordered. For example:

could be used as input to pg_restore and would only restore items 10 and 6, in that order:

pg_verifybackup [option…​]

pg_verifybackup accepts the following command-line arguments:

Exit as soon as a problem with the backup is detected. If this option is not specified, pg_verifybackup will continue checking the backup even after a problem has been detected, and will report all problems detected as errors.

Ignore the specified file or directory, which should be expressed as a relative path name, when comparing the list of data files actually present in the backup to those listed in the backup_manifest file. If a directory is specified, this option affects the entire subtree rooted at that location. Complaints about extra files, missing files, file size differences, or checksum mismatches will be suppressed if the relative path name matches the specified path name. This option can be specified multiple times.

Use the manifest file at the specified path, rather than one located in the root of the backup directory.

Don’t attempt to parse write-ahead log data that will be needed to recover from this backup.

Don’t print anything when a backup is successfully verified.

Do not verify data file checksums. The presence or absence of files and the sizes of those files will still be checked. This is much faster, because the files themselves do not need to be read.

Try to parse WAL files stored in the specified directory, rather than in pg_wal. This may be useful if the backup is stored in a separate location from the WAL archive.

Other options are also available:

Print the pg_verifybackup version and exit.

Show help about pg_verifybackup command line arguments, and exit.

To create a base backup of the server at mydbserver and verify the integrity of the backup:

To create a base backup of the server at mydbserver, move the manifest somewhere outside the backup directory, and verify the backup:

To verify a backup while ignoring a file that was added manually to the backup directory, and also skipping checksum verification:

psql [option…​] [dbname [username]]

Print all nonempty input lines to standard output as they are read. (This does not apply to lines read interactively.) This is equivalent to setting the variable ECHO to all.

Switches to unaligned output mode. (The default output mode is aligned.) This is equivalent to \pset format unaligned.

Print failed SQL commands to standard error output. This is equivalent to setting the variable ECHO to errors.

Specifies that psql is to execute the given command string, command. This option can be repeated and combined in any order with the -f option. When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence.command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands within a -c option. To achieve that, you could use repeated -c options or pipe the string into psql, for example:`psql -c '\x' -c 'SELECT * FROM foo;' or`echo '\x \\ SELECT * FROM foo;' | psql `(\\` is the separator meta-command.)Each SQL command string passed to -c is sent to the server as a single request. Because of this, the server executes it as a single transaction even if the string contains multiple SQL commands, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions.If having several commands executed in one transaction is not desired, use repeated -c commands or feed multiple commands to psql’s standard input, either using echo as illustrated above, or via a shell here-document, for example:`psql <<EOF \x SELECT * FROM foo; EOF `

Switches to CSV (Comma-Separated Values) output mode. This is equivalent to \pset format csv.

Specifies the name of the database to connect to. This is equivalent to specifying dbname as the first non-option argument on the command line. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options.

Copy all SQL commands sent to the server to standard output as well. This is equivalent to setting the variable ECHO to queries.

Echo the actual queries generated by \d and other backslash commands. You can use this to study psql’s internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on.

Read commands from the file filename, rather than standard input. This option can be repeated and combined in any order with the -c option. When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence. Except for that, this option is largely equivalent to the meta-command \i.If filename is - (hyphen), then standard input is read until an EOF indication or \q meta-command. This can be used to intersperse interactive input with input from files. Note however that Readline is not used in this case (much as if -n had been specified).Using this option is subtly different from writing psql < `filename. In general, both will do what you expect, but using `-f enables some nice features such as error messages with line numbers. There is also a slight chance that using this option will reduce the start-up overhead. On the other hand, the variant using the shell’s input redirection is (in theory) guaranteed to yield exactly the same output you would have received had you entered everything by hand.

Use separator as the field separator for unaligned output. This is equivalent to \pset fieldsep or \f.

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix-domain socket.

Switches to HTML output mode. This is equivalent to \pset format html or the \H command.

List all available databases, then exit. Other non-connection options are ignored. This is similar to the meta-command \list.When this option is used, psql will connect to the database postgres, unless a different database is named on the command line (option -d or non-option argument, possibly via a service entry, but not via an environment variable).

Write all query output into file filename, in addition to the normal output destination.

Do not use Readline for line editing and do not use the command history

Put all query output into file filename. This is equivalent to the command \o.

Specifies the TCP port or the local Unix-domain socket file extension on which the server is listening for connections. Defaults to the value of the PGPORT environment variable or, if not set, to the port specified at compile time, usually 5432.

Specifies printing options, in the style of \pset. Note that here you have to separate name and value with an equal sign instead of a space. For example, to set the output format to LaTeX, you could write -P format=latex.

Specifies that psql should do its work quietly. By default, it prints welcome messages and various informational output. If this option is used, none of this happens. This is useful with the -c option. This is equivalent to setting the variable QUIET to on.

Use separator as the record separator for unaligned output. This is equivalent to \pset recordsep.

Run in single-step mode. That means the user is prompted before each command is sent to the server, with the option to cancel execution as well. Use this to debug scripts.

Runs in single-line mode where a newline terminates an SQL command, as a semicolon does.NoteThis mode is provided for those who insist on it, but you are not necessarily encouraged to use it. In particular, if you mix SQL and meta-commands on a line the order of execution might not always be clear to the inexperienced user.

Turn off printing of column names and result row count footers, etc. This is equivalent to \t or \pset tuples_only.

Specifies options to be placed within the HTML table tag. See \pset tableattr for details.

Connect to the database as the user username instead of the default. (You must have permission to do so, of course.)

Perform a variable assignment, like the \set meta-command. Note that you must separate name and value, if any, by an equal sign on the command line. To unset a variable, leave off the equal sign. To set a variable with an empty value, use the equal sign but leave off the value. These assignments are done during command line processing, so variables that reflect connection state will get overwritten later.

Print the psql version and exit.

Never issue a password prompt. If the server requires password authentication and a password is not available from other sources 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.Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial connection attempt.

Force psql to prompt for a password before connecting to a database, even if the password will not be used.If the server requires password authentication and a password is not available from other sources such as a .pgpass file, psql will prompt for a password in any case. However, psql 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.Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial connection attempt.

Turn on the expanded table formatting mode. This is equivalent to \x or \pset expanded.

Do not read the start-up file (neither the system-wide psqlrc file nor the user’s ~/.psqlrc file).

Set the field separator for unaligned output to a zero byte. This is equivalent to \pset fieldsep_zero.

Set the record separator for unaligned output to a zero byte. This is useful for interfacing, for example, with xargs -0. This is equivalent to \pset recordsep_zero.

This option can only be used in combination with one or more -c and/or -f options. It causes psql to issue a BEGIN command before the first such option and a COMMIT command after the last one, thereby wrapping all the commands into a single transaction. If any of the commands fails and the variable ON_ERROR_STOP was set, a ROLLBACK command is sent instead. This ensures that either all the commands complete successfully, or no changes are applied.If the commands themselves contain BEGIN, COMMIT, or ROLLBACK, this option will not have the desired effects. Also, if an individual command cannot be executed inside a transaction block, specifying this option will cause the whole transaction to fail.

Show help about psql and exit. The optional topic parameter (defaulting to options) selects which part of psql is explained: commands describes psql’s backslash commands; options describes the command-line options that can be passed to psql; and variables shows help about psql configuration variables.

psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g., out of memory, file not found), 2 if the connection to the server went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.

reindexdb [connection-option…​] [option…​] [ -S | --schema schema ] …​ [ -t | --table table ] …​ [ -i | --index index ] …​ [dbname]

reindexdb [connection-option…​] [option…​] -s | --system [dbname]

reindexdb accepts the following command-line arguments:

Reindex all databases.

Use the CONCURRENTLY option. See REINDEX, where all the caveats of this option are explained in detail.

Specifies the name of the database to be reindexed, when -a/--all is not used. If this is not specified, the database name is read from the environment variable PGDATABASE. If that is not set, the user name specified for the connection is used. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options.

Echo the commands that reindexdb generates and sends to the server.

Recreate index only. Multiple indexes can be recreated by writing multiple -i switches.

Execute the reindex commands in parallel by running njobs commands simultaneously. This option may reduce the processing time but it also increases the load on the database server.reindexdb will open njobs connections to the database, so make sure your max_connections setting is high enough to accommodate all connections.Note that this option is incompatible with the --index and --system options.

Do not display progress messages.

Reindex database’s system catalogs only.

Reindex schema only. Multiple schemas can be reindexed by writing multiple -S switches.

Reindex table only. Multiple tables can be reindexed by writing multiple -t switches.

Specifies the tablespace where indexes are rebuilt. (This name is processed as a double-quoted identifier.)

Print detailed information during processing.

Print the reindexdb version and exit.

Show help about reindexdb command line arguments, and exit.

reindexdb also accepts the following command-line arguments for connection parameters:

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.

User name to connect as.

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.

Force reindexdb to prompt for a password before connecting to a database.This option is never essential, since reindexdb will automatically prompt for a password if the server demands password authentication. However, reindexdb 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.

Specifies the name of the database to connect to to discover which databases should be reindexed, when -a / --all is used. If not specified, the postgres database will be used, or if that does not exist, template1 will be used. This can be a connection string. If so, connection string parameters will override any conflicting command line options. Also, connection string parameters other than the database name itself will be re-used when connecting to other databases.

Default connection parameters

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

This utility, like most other IvorySQL utilities, also uses the environment variables supported by libpq .

In case of difficulty, see REINDEX and psql for discussions of potential problems and error messages. The database server must be running at the targeted host. Also, any default connection settings and environment variables used by the libpq front-end library will apply.

reindexdb might need to connect several times to the IvorySQL server, asking for a password each time. It is convenient to have a ~/.pgpass file in such cases.

To reindex the database test:

To reindex the table foo and the index bar in a database named abcd:

vacuumdb [connection-option…​] [option…​] [ -t | --table table [( column [,…​] )] ] …​ [dbname]

vacuumdb accepts the following command-line arguments:

Vacuum all databases.

Specifies the name of the database to be cleaned or analyzed, when -a/--all is not used. If this is not specified, the database name is read from the environment variable PGDATABASE. If that is not set, the user name specified for the connection is used. The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options.

Disable skipping pages based on the contents of the visibility map.

Echo the commands that vacuumdb generates and sends to the server.

Perform “full” vacuuming.

Aggressively “freeze” tuples.

Always remove index entries pointing to dead tuples.

Execute the vacuum or analyze commands in parallel by running njobs commands simultaneously. This option may reduce the processing time but it also increases the load on the database server.vacuumdb will open njobs connections to the database, so make sure your max_connections setting is high enough to accommodate all connections.Note that using this mode together with the -f (FULL) option might cause deadlock failures if certain system catalogs are processed in parallel.

Only execute the vacuum or analyze commands on tables with a multixact ID age of at least mxid_age. This setting is useful for prioritizing tables to process to prevent multixact ID wraparound .For the purposes of this option, the multixact ID age of a relation is the greatest of the ages of the main relation and its associated TOAST table, if one exists. Since the commands issued by vacuumdb will also process the TOAST table for the relation if necessary, it does not need to be considered separately.

Only execute the vacuum or analyze commands on tables with a transaction ID age of at least xid_age. This setting is useful for prioritizing tables to process to prevent transaction ID wraparound.For the purposes of this option, the transaction ID age of a relation is the greatest of the ages of the main relation and its associated TOAST table, if one exists. Since the commands issued by vacuumdb will also process the TOAST table for the relation if necessary, it does not need to be considered separately.

Do not remove index entries pointing to dead tuples.

Skip the TOAST table associated with the table to vacuum, if any.

Do not truncate empty pages at the end of the table.

Specify the number of parallel workers for parallel vacuum. This allows the vacuum to leverage multiple CPUs to process indexes. See VACUUM.

Do not display progress messages.

Skip relations that cannot be immediately locked for processing

Clean or analyze table only. Column names can be specified only in conjunction with the --analyze or --analyze-only options. Multiple tables can be vacuumed by writing multiple -t switches.TipIf you specify columns, you probably have to escape the parentheses from the shell. (See examples below.)

Print detailed information during processing.

Print the vacuumdb version and exit.

Also calculate statistics for use by the optimizer.

Only calculate statistics for use by the optimizer (no vacuum).

Only calculate statistics for use by the optimizer (no vacuum), like --analyze-only. Run three stages of analyze; the first stage uses the lowest possible statistics target and subsequent stages build the full statistics.This option is only useful to analyze a database that currently has no statistics or has wholly incorrect ones, such as if it is newly populated from a restored dump or by pg_upgrade. Be aware that running with this option in a database with existing statistics may cause the query optimizer choices to become transiently worse due to the low statistics targets of the early stages.

Show help about vacuumdb command line arguments, and exit.

vacuumdb also accepts the following command-line arguments for connection parameters:

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.

User name to connect as.

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.

Force vacuumdb to prompt for a password before connecting to a database.This option is never essential, since vacuumdb will automatically prompt for a password if the server demands password authentication. However, vacuumdb 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.

Specifies the name of the database to connect to to discover which databases should be vacuumed, when -a / --all is used. If not specified, the postgres database will be used, or if that does not exist, template1 will be used. This can be a connection string. If so, connection string parameters will override any conflicting command line options. Also, connection string parameters other than the database name itself will be re-used when connecting to other databases.

Default connection parameters

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

This utility, like most other IvorySQL utilities, also uses the environment variables supported by libpq.

In case of difficulty, see VACUUM and psql for discussions of potential problems and error messages. The database server must be running at the targeted host. Also, any default connection settings and environment variables used by the libpq front-end library will apply.

vacuumdb might need to connect several times to the IvorySQL server, asking for a password each time. It is convenient to have a ~/.pgpass file in such cases.

To clean the database test:

To clean and analyze for the optimizer a database named bigdb:

To clean a single table foo in a database named xyzzy, and analyze a single column bar of the table for the optimizer:

initdb [option…​] [ --pgdata | -D ] directory

This option specifies the default authentication method for local users used in pg_hba.conf (host and local lines). Do not use trust unless you trust all local users on your system. trust is the default for ease of installation.

This option specifies the authentication method for local users via TCP/IP connections used in pg_hba.conf (host lines).

This option specifies the authentication method for local users via Unix-domain socket connections used in pg_hba.conf (local lines).

This option specifies the directory where the database cluster should be stored. This is the only information required by initdb, but you can avoid writing it by setting the PGDATA environment variable, which can be convenient since the database server (postgres) can find the database directory later by the same variable.

Selects the encoding of the template databases. This will also be the default encoding of any database you create later, unless you override it then. The default is derived from the locale, if the libc locale provider is used, or UTF8 if the ICU locale provider is used.

Allows users in the same group as the cluster owner to read all cluster files created by initdb. This option is ignored on Windows as it does not support POSIX-style group permissions.

Specifies the ICU locale ID, if the ICU locale provider is used.

Use checksums on data pages to help detect corruption by the I/O system that would otherwise be silent. Enabling checksums may incur a noticeable performance penalty. If set, checksums are calculated for all objects, in all databases. All checksum failures will be reported in the pg_stat_database view.

Sets the default locale for the database cluster. If this option is not specified, the locale is inherited from the environment that initdb runs in.

Like --locale, but only sets the locale in the specified category.

Equivalent to --locale=C.

This option sets the locale provider for databases created in the new cluster. It can be overridden in the CREATE DATABASE command when new databases are subsequently created. The default is libc.

By default, initdb will wait for all files to be written safely to disk. This option causes initdb to return without waiting, which is faster, but means that a subsequent operating system crash can leave the data directory corrupt. Generally, this option is useful for testing, but should not be used when creating a production installation.

By default, initdb will write instructions for how to start the cluster at the end of its output. This option causes those instructions to be left out. This is primarily intended for use by tools that wrap initdb in platform-specific behavior, where those instructions are likely to be incorrect.

Makes initdb read the database superuser’s password from a file. The first line of the file is taken as the password.

Safely write all database files to disk and exit. This does not perform any of the normal initdb operations. Generally, this option is useful for ensuring reliable recovery after changing fsync from off to on.

Sets the default text search configuration.

Selects the user name of the database superuser. This defaults to the name of the effective user running initdb. It is really not important what the superuser’s name is, but one might choose to keep the customary name postgres, even if the operating system user’s name is different.

Makes initdb prompt for a password to give the database superuser. If you don’t plan on using password authentication, this is not important. Otherwise you won’t be able to use password authentication until you have a password set up.

This option specifies the directory where the write-ahead log should be stored.

Set the WAL segment size, in megabytes. This is the size of each individual file in the WAL log. The default size is 16 megabytes. The value must be a power of 2 between 1 and 1024 (megabytes). This option can only be set during initialization, and cannot be changed later.It may be useful to adjust this size to control the granularity of WAL log shipping or archiving. Also, in databases with a high volume of WAL, the sheer number of WAL files per directory can become a performance and management problem. Increasing the WAL file size will reduce the number of WAL files.

Other, less commonly used, options are also available:

Print debugging output from the bootstrap backend and a few other messages of lesser interest for the general public. The bootstrap backend is the program initdb uses to create the catalog tables. This option generates a tremendous amount of extremely boring output.

Run the bootstrap backend with the debug_discard_caches=1 option. This takes a very long time and is only of use for deep debugging.

Specifies where initdb should find its input files to initialize the database cluster. This is normally not necessary. You will be told if you need to specify their location explicitly.

By default, when initdb determines that an error prevented it from completely creating the database cluster, it removes any files it might have created before discovering that it cannot finish the job. This option inhibits tidying-up and is thus useful for debugging.

Other options:

Print the initdb version and exit.

Show help about initdb command line arguments, and exit.

Specifies the directory where the database cluster is to be stored; can be overridden using the -D option.

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

Specifies the default time zone of the created database cluster. The value should be a full time zone name

This utility, like most other IvorySQL utilities, also uses the environment variables supported by libpq

initdb can also be invoked via pg_ctl initdb.

pg_archivecleanup [option…​] archivelocation oldestkeptwalfile

To configure a standby server to use pg_archivecleanup, put this into its IvorySQL.conf configuration file:

where archivelocation is the directory from which WAL segment files should be removed.

When used within archive_cleanup_command, all WAL files logically preceding the value of the %r argument 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, or when multiple standby servers are recovering from the same archive location.

When used as a standalone program all WAL files logically preceding the oldestkeptwalfile will be removed from archivelocation. In this mode, if you specify a .partial or .backup file name, then only the file prefix will be used as the oldestkeptwalfile. This treatment of .backup file name allows you to remove all WAL files archived prior to a specific base backup without error. For example, the following example will remove all files older than WAL file name 000000010000003700000010:

pg_archivecleanup assumes that archivelocation is a directory readable and writable by the server-owning user.

pg_archivecleanup accepts the following command-line arguments:

Print lots of debug logging output on stderr.

Print the names of the files that would have been removed on stdout (performs a dry run).

Print the pg_archivecleanup version and exit.

Provide an extension that will be stripped from all file names before deciding if they should be deleted. This is typically useful for cleaning up archives that have been compressed during storage, and therefore have had an extension added by the compression program. For example: -x .gz.

Show help about pg_archivecleanup command line arguments, and exit.

The environment variable PG_COLOR specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

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. This will:

pg_checksums [option…​] [[ -D | --pgdata ] datadir]

The following command-line options are available:

Specifies the directory where the database cluster is stored.

Checks checksums. This is the default mode if nothing else is specified.

Disables checksums.

Enables checksums.

Only validate checksums in the relation with filenode filenode.

By default, pg_checksums will wait for all files to be written safely to disk. This option causes pg_checksums to return without waiting, which is faster, but means that a subsequent operating system crash can leave the updated data directory corrupt. Generally, this option is useful for testing but should not be used on a production installation. This option has no effect when using --check.

Enable progress reporting. Turning this on will deliver a progress report while checking or enabling checksums.

Enable verbose output. Lists all checked files.

Print the pg_checksums version and exit.

Show help about pg_checksums command line arguments, and exit.

Specifies the directory where the database cluster is stored; can be overridden using the -D option.

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

Enabling checksums in a large cluster can potentially take a long time. During this operation, the cluster or other programs that write to the data directory must not be started or else data loss may occur.

When using a replication setup with tools which perform direct copies of relation file blocks (for example pg_rewind), enabling or disabling checksums can lead to page corruptions in the shape of incorrect checksums if the operation is not done consistently across all nodes. When enabling or disabling checksums in a replication setup, it is thus recommended to stop all the clusters before switching them all consistently. Destroying all standbys, performing the operation on the primary and finally recreating the standbys from scratch is also safe.

If pg_checksums is aborted or killed while enabling or disabling checksums, the cluster’s data checksum configuration remains unchanged, and pg_checksums can be re-run to perform the same operation.

pg_controldata [option] [[ -D | --pgdata ] datadir]

Default data directory location

Specifies whether to use color in diagnostic messages. Possible values are always, auto and never.

pg_ctl init[db] [-D datadir] [-s] [-o initdb-options]

pg_ctl start [-D datadir] [-l filename] [-W] [-t seconds] [-s] [-o options] [-p path] [-c]

pg_ctl stop [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ] [-W] [-t seconds] [-s]

pg_ctl restart [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ] [-W] [-t seconds] [-s] [-o options] [-c]

pg_ctl reload [-D datadir] [-s]

pg_ctl status [-D datadir]

pg_ctl promote [-D datadir] [-W] [-t seconds] [-s]

pg_ctl logrotate [-D datadir] [-s]

pg_ctl kill signal_name process_id

On Microsoft Windows, also:

pg_ctl register [-D datadir] [-N servicename] [-U username] [-P password] [-S a[uto] | d[emand] ] [-e source] [-W] [-t seconds] [-s] [-o options]