Shared memory and Semaphores

Shared memory and semaphores are collectively referred to as “System V IPC”, which is required to run AgensGraph. In the case where the size requested by AgensGraph exceeds the IPC limit, the server fails to start.

AgensGraph requires several bytes of System V shared memory per server copy (usually 48 bytes for 64-bit platforms). This amount is easily allocated in mordern operating systems. However, when running multiple copies of the server or when other applications also use System V shared memory, it may be necessary to increase SHMMAX (the maximum shared memory size in bytes) or SHMALL (the system-wide System V shared memory). Note that SHMALL is handled on a page-by-page basis rather than a byte-by-byte basis on most systems.

In the case of AgensGraph, the minimum size of the shared memory segment (SHMMIN) is about 32 bytes at most (usually 1) and is less likely to cause a problem. The maximum number of segments system-wide (SHMMNI) or the maximum number per process (SHMSEG) is highly unlikely to cause problems unless the system is set to zero.

AgensGraph uses one semaphore per connection (max_connections) and one semaphore per autovacuum worker process (autovacuum_max_workers) among the 16 sets. Each set has a 17th semaphore with a “magic number” that detects collisions with a semaphore set of other applications. The maximum number of semaphores in the system is set by SEMMNS, and at least “max_connections + autovacuum_max_workers + 1 added to 16 allowed connections + worker” (see Table 1-1 formula). The parameter SEMMNI limits the number of semaphore sets that can exist concurrently on the system. It should be at least ceil ((max_connections + autovacuum_max_workers + 4) / 16). Reducing the number of acceptable connections can be a temporary solution in case of failure, but you may receive an ambiguous message from the semget function like “No space left on device.”

In some cases, it may be necessary to increase SEMMAP so that it can be similar to SEMMNS at least. This parameter defines the size of the semaphore resource map, which contains the entries that adjacent blocks of the semaphore need. A set of released semaphores is added to the entry adjacent to the released block or is registered as a new entry. When the map is full, the released semaphore disappears (until it is rebooted). As the semaphore space is divided, the number of available semaphores becomes smaller.

SEMMSL that determines the number of semaphores to be included in a set should be at least 17 for AgensGraph.

Other settings related to “semaphore undo” such as SEMMNU and SEMUME do not affect AgensGraph.

Note: If you encounter a “Warning: Out of Shared Memory” error while performing some queries, visit Bitnine’s website(https://bitnine.net/contact) and request a technical support; the team will provide you with a guide on the memory settings suitable for your site environment.

Linux

Linux binaries are compressed with tarball. After unzipping them to the desired location, perform the post-installation task to complete the installation.

Windows

Coming soon.

Trust Authentication

If trust authentication is specified, AgensGraph assumes that anyone who can connect to the server using the specified database user name is authenticated for database access (including the superuser name). The limitations of the database and user columns still apply as well. This method should only be used if adequate operating system level protection is provided for server connections.

trust authentication is appropriate for local connections to a single user workstation and is very convenient. It is not appropriate, however, for multiuser machines in general. trust authentication is suitable for TCP/IP connections only when all the users on all machines that are allowed to connect to the server are trusted in pg_hba.conf, which specifies trust. It is unreasonable to use trust for TCP/IP connections except for localhost (127.0.0.1).

Password Authentication

The password-based authentication methods are md5, scram-sha-256, and password. Both methods work similarly except that the password is sent in MD5 hash and plaintext respectively.

While md5 has been widely used in previous versions, scram-sha-256 is the most secure method in this version. Using scram-sha-256 or md5 to guard against password “sniffing” attacks is preferable; normal password should be avoided if possible. However, md5 cannot be used with the db_user_namespace feature. You can safely use a password if the connection is protected by SSL encryption.

The AgensGraph database password is distinguished from the operating system user password. Passwords of each database user are stored in the pg_authid system catalog. Such passwords can be managed by SQL commands CREATE USER and ALTER ROLE (e.g. CREATE USER foo WITH PASSWORD ‘secret’). If the password is not set for a user, the stored password is null and password authentication always fails for the user.

GSSAPI Authentication

GSSAPI is an industry-standard protocol for security authentication as defined in RFC 2743, and provides single sign-on for GSSAPI-supporting systems. Authentication itself is secure, but if you do not use SSL, the data sent over the database connection is transmitted unencrypted.

When you connect to the database, you should check if there is a ticket for the security rule that matches the name of the requested database user. For example, if the database name is fred, the security rule fred@EXAMPLE.COM is connectable.

The following configuration options are supported for GSSAPI.

• include_realm
  If set to 1, the realm name of the authenticated user security rule is included in the system user name transmitted through the user name mapping. This is useful when processing users in multiple realms.

• map
  Allows mapping between the system and database user names.

• krb_realm
  Sets a realm that matches the user security rule name. When this parameter is set, only users in the corresponding realm are allowed. If not set, users in all realms can connect; the realm depends on whether the username mapping is complete or not.

SSPI Authentication

SSPI is a Windows technology for single sign-on (SSO) security authentication. AgensGraph uses SSPI in negotiate mode. It uses Kerberos if available, and in other cases, automatically falls back to NTLM. SSPI authentication works only when both the server and client use Windows; if GSSAPI is available, it works with non-Windows as well.

While using Kerberos authentication, SSPI works the same way as GSSAPI.

The following configuration options are supported for SSPI.

• include_realm
  If set to 1, the realm name of the authenticated user security rule is included in the system user name transmitted through the user name mapping. This is useful when processing users in multiple realms.

• map
  Allows mapping between the system and database user names.

• krb_realm
  Sets a realm that matches the user security rule name. When this parameter is set, only users in the corresponding realm are allowed. If not set, users in all realms can make a connection; the realm depends on whether the username mapping is complete or not.

Ident Authentication

The ident authentication method works by acquiring the client’s operating system user name from the ident server and using it as an allowed database user name (optional user name mapping is applied). This authentication is supported only for TCP/IP connections.

The following configuration option is supported for ident:

• map
  Allows mapping between the system and database user names.

Some ident servers have a nonstandard option that allows the returned user name to be encrypted using a key known only to the administrator of the original machine. As AgensGraph does not have a way to decrypt the returned string to determine the actual user name, you should not use this option if you are using AgensGraph on the ident server.

Peer authentication

The peer authentication method works by acquiring the client’s operating system user name from the kernel and using it as an allowed database user name (optional user name mapping is applied). This authentication is supported only for local connections.

The following configuration option is supported for peer:

• map
  Allows mapping between the system and database user names.

Peer authentication can only be used on operating systems such as Linux that provides getpeereid(), SO_PEERCRED (socket parameter), and/or similar mechanisms.

LDAP Authentication

This authentication works like a password except when you use LDAP as a password verification method. LDAP is used only when verifying username/password pairs. This means the user must exist in the database before applying LDAP for authentication.

LDAP authentication can be performed in two modes. The first mode (simple binding mode) is to bind the server to a unique name consisting of prefix username suffix. Typically, the prefix parameter is used to specify cn= or DOMAIN\ in an active directory environment; suffix is used to specify the rest of the active directory environment.

In the second mode (search+binding mode), the server first binds to the LDAP directory using the specified and fixed username and password via ldapbinddn and ldapbindpasswd, and then searches for users who are trying to log in to the database. If no user and password are set, anonymous binding to the directory is attempted. The search is performed in the subtree of ldapbasedn and what matches ldapsearchattribute exactly are retrieved. If a user is found in this search, the server disconnects the connection and re-binds to the directory as the user using the password specified on the client to verify if the login is correct. This mode is the same as the one used in the LDAP authentication schema of other software, such as Apache mod_authnz_ldap and pam_ldap. While LDAP authentication works more flexibly when the user objects are in the directory, it disconnects the two LDAP servers.

The following configuration options are used in both modes.

• ldapserver
  The name or IP address of the LDAP server to connect to. You may specify multiple servers separated by spaces.

• ldapport
  The port number of the LDAP server to connect to. If no port is specified, the default port setting of the LDAP library will be used.

• ldaptls
  If set to 1, AgensGraph is connected to the LDAP server using TLS encryption. This encrypts only traffic to the LDAP server. The connection to the client will remain unencrypted unless you use SSL.

The following options are used only in the simple binding mode.

• ldapprefix
  A string to append before a user name in the DN binding when performing simple binding authentication.

• ldapsuffix
  A string to append after a user name in the DN binding when performing simple binding authentication.

The following options are used only in the search+bindingmode.

• ldapbasedn
  Root DN at which a user search is initiated when performing search+binding authentication.

• ldapbinddn
  User DN that binds to the directory to perform a search during search+binding authentication.

• ldapbindpasswd
  User’s password that binds to the directory to perform a search during search+binding authentication.

• ldapsearchattribute
  Attribute that matches the user name in the search when performing search+binding authentication. If no attribute is specified, the uid attribute is used.

• ldapurl
  RFC 4516 LDAP URL. This is another way to write some of the other LDAP options in a more compact standard format. The default is:

  ldap://host[:port]/basedn[?[attribute][?[scope]]]

  scope should be specified as base, one, or sub (usually the latter). Only one attribute is used, and other settings for standard LDAP URLs such as filters and extensions are not supported. For non-anonymous bindings, ldapbinddn and ldapbindpasswd should be specified as separate options.

  In order to use an encrypted LDAP connection, the option ldaptls should be used in addition to ldapurl. ldaps URL schema (direct SSL connection) is not supported.

  LDAP URLs are currently supported only by OpenLDAP, not Windows.

It is an error to mix the configuration options of simple bindings with the options of search+bindings.

An example of a simple binding LDAP configuration:

host ... ldap ldapserver=ldap.example.net ldapprefix="cn=" ldapsuffix=", dc=example, dc=net"

If the database user someuser is asked to connect to the database server, AgensGraph tries to bind to the LDAP server using DN cn=someuser and dc=example, dc=net, and the password provided by the client. If the connection is successful, database access is allowed.

An example of a search+binding configuration is as follows:

host ... ldap ldapserver=ldap.example.net ldapbasedn="dc=example, dc=net" ldapsearchattribute=uid

If the database user someuser is asked to connect to the database server, AgensGraph tries to bind to the LDAP server anonymously (because ldapbinddn is not specified) and performs a search for (uid=someuser) under the specified base DN. If an entry is found, it attempts to bind using the discovered information and the password provided by the client. If the secondary connection is successful, database access is allowed.

The same search+binding configuration with URL is as follows:

host ... ldap lapurl="ldap://ldap.example.net/dc=example,dc=net?uid?sub"

As other software supporting authentication for LDAP uses the same URL format, it gets easier to share settings.

RADIUS Authentication

This authentication works like a password except when you use LDAP as a password verification method. RADIUS is used only when verifying username/password pairs. This means the user must exist in the database before applying RADIUS for authentication.

If you are using RADIUS authentication, an Access Request message is sent to the configured RADIUS server. This request is an Authenticate Only type and contains parameters for user name, password (encrypted), and NAS Identifier. The request is encrypted using secret that is shared with the server. The RADIUS server sends back either Access Accept or Access Reject. The RADIUS accounts are not supported.

The following configuration options are supported for RADIUS.

• radiusserver
  The name or IP address of the RADIUS server to connect to. This parameter is required.

• radiussecret
  Shared secret used when communicating with a RADIUS server while maintaining security. Its values on AgensGraph and RADIUS servers should be identical. A string of at least 16 characters is recommended. This parameter is required.

• radiusport
  The port number of the RADIUS server to connect to. If no port is specified, the default port “1812” is used.

• radiusidentifier
  A string is used as a NAS identifier in RADIUS requests. For example, this parameter can be used as the second parameter in the RADIUS server by identifying the database user to be authenticated. If no identifier is specified, the default postgresql is used.

Certificate Authentication

As this method performs authentication using an SSL client certificate, it is available only for SSL connections. When you apply this authentication method, the server requires the client to provide a valid certificate. The password prompt is not sent to the client. The cn (common name) attribute of the certificate is compared to the requested database user name; if they match, the login is allowed. You may use the user name mapping to set cn different from the database user name.

The following configuration option is supported for SSL certificate authentication.

• map
  Allows mapping between the system and database user names.

PAM Authentication

This authentication works like a password except when you use Pluggable Authentication Modules (PAM) as an authentication mechanism. The default PAM service name is postgresql. PAM is used only when verifying username/password pairs. This means the user must exist in the database before applying PAM for authentication.

The following configuration option is supported for PAM.

• pamservice
  PAM service name.

Creating an archive of Write Ahead Logging (WAL)

   Internally, the AgensGraph system constantly creates WAL records in manipulating the database. Those records are divided into WAL segment files so they can be stored in physical disk space. A single WAL file is basically a 16MB file (this size is determined when you compile the server). The filename uses the corresponding number in the WAL order. If you set it not to create a WAL archive file, only a few of these files will be created, and you will need to find and “reuse” any log files that you no longer use. Internally, it finds the status information of WAL records, reuses it by changing the checkpoint operation to a no-longer-in-use state, and recording a new WAL record in its place. 

   If you create a WAL archive file, you archive existing WAL record information elsewhere (same host, NFS mount point, or even tape) before reusing a certain WAL segment file. The archiving method is specified directly by the administrator. Let the administrator decide even in the case where there is already a file with the same name. However, when reusing the WAL segment file, the AgensGraph server only performs operations according to the “method of archiving” set by the administrator. You may simply use the cp command for the archive, use a more complex custom shell script, or use a backup solution command. This part is entirely up to the administrator.  

   To create a WAL archive file, specify archive (or hot_standby) with the value of the configuration parameter wal level. Next, set the environment configuration parameter archive mode to on, and the environment configuration parameter archive command to an appropriate system command. All of these settings are configured in the postgresql.conf file. You can use %p (absolute path to the WAL log file) and %f (the name of the log file to be archived) reserved arguments for the shell command to specify as the archive_command value. If you need to type % literally, type %%. In general, this setting is used in the following format.

# Unix
  archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'   
# Windows
  archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"'  

   The above configuration simply copies the WAL segment file to the /mnt/server/archivedir directory (with the same file name). The above is only an example for Unix and Windows operating systems. Actual settings should be changed to match the operating system. %p %f reserved arguments are changed as follows for the actual execution of the command.  

test ! -f /mnt/server/archivedir/00000001000000A900000065 && 
cp pg_xlog/00000001000000A900000065\ /mnt/server/archivedir/00000001000000A900000065

   This will always make the WAL log file fresh at a certain location. 

   This archive command is executed by the privileges of the system user who ran the AgensGraph server. So, just like processing WAL segment files, you should consider security policies when processing archive files. It should be noted that a fatal security incident can occur if it can be read, overwritten, and deleted by anyone. 

   Another thing to note is that the archive command’s shell execution return value should be 0 (zero) if the command is successful, or a different value if the command is not successful. The server will determine if the log file has been successfully archived or failed with this return value, and if successful, will either erase or reuse the original log file; if unsuccessful, it will retry until it succeeds. 

   You should not overwrite files that already exist with this archive command. Overwriting may cause unintended errors in the restore operation. (This file may already have been created or used by another database.) It is safest for the administrator to manually process the log file because constant errors may occur if it already exists. 

   It is recommended to check if the file already exists before you archive a log file. If it exists, set the command to return a non-zero value. On Unix, a test command is provided for this task. On some Unix platforms, the -i option is provided in the cp command to avoid overwriting files that already exist. You must check the return value when using this command. (The GNU cp command returns “0” if there is a log file, which is not desirable.) 

   When you save a WAL file separately, operational jobs may need to be aborted manually, or saving operation may fail repeatedly because of insufficient storage space. For example, if you want to archive a WAL segment file to a tape storage device and the device does not automatically exchange tapes and there is no free space on the tape; then, the user continues to run the file write operation until an error occurs and may repeat the process. In such a case, the pg_xlog/ directory will not delete or reuse the WAL segment file because the data has not been safely copied to the tape storage, and the new WAL segment files created later will continue to accumulate and eventually stop as there will be no more free space in the pg_xlog/ directory and a PANIC error will occur in the server. 

   You also have to worry about the data recording speed of the WAL file when it is stored separately. Even if the work is properly done, the same problem may occur if the speed at which the WAL file is created is faster than the speed at which the file is kept separately. If the pg_xlog/ directory has enough free space, it will not be a problem, of course. Before using this feature, you should consider this kind of problem in this section sufficiently; the administrator should monitor whether this function works as intended as well. 

   The path to the save file should be up to 64 ASCII characters. The file name must use the reserved word %f (i.e. only directories can be changed). The original WAL segment file must use the reserved word %p. Note that changes to the postgresql.conf, pg_hba.conf, and pg_ident.conf files are not reflected in the database to be restored by this backup method because the WAL file contains only transactional operation information. You should back up these configuration files according to your system’s normal file backup policy. 

   The archive command is executed for files that are all reflected on the server (rollbacked or committed and then checkpointed) among the WAL files. That is, for a database with very small workloads, the interval at which archive commands are executed is very long. If there is a database failure at this interval, data loss occurs. Thus, you must forcefully use the archive command to save this segment file separately after a certain time before all the content of the WAL segment file is processed. If the setting value is too short, the disk space can be wasted (a disadvantage). Typically, the setting in minutes is appropriate. You may also force the user to change the segment file using the pg_switch_xlog function. This function is typically used when bulk data entry, modification, or deletion occurs and immediate backups are required. 

   If you set the wal_level value to minimal, you will not be able to save this WAL log and restore it based on it. This is because, if you use this setting, you do not keep the restore-related information in the WAL file. Therefore, when you change the wal_level setting, you must restart the server. In the case of archive_command setting, however, reloading the configuration files is sufficient. Stopping this operation during operation may be needed. To do that, specify the archive_command setting as an empty string (’’). The archive_command setting will remain in the pg_xlog/ directory until you copy the WAL file again.  

Parameter Interaction via the Configuration File

The most basic way to set these parameters is to edit postgresql.conf, which is usually in the data directory. If the database cluster directory is initialized, the default copy is installed. An example similar to this file is as follows:

# This is a comment
log_connections = yes
log_destination = 'syslog'
search_path = '"$user", public'
shared_buffers = 128MB

A parameter per line is specified. The equals sign between name and value is optional. Blank spaces are not important (except for the quoted parameters) and blank lines are ignored. The hash mark (#) means that the rest of the line is a comment. Simple identifiers or non-numeric parameter values should use single quotes. To include a single quotation mark in the parameter value, you must add one more single quotation mark, or use backslashes and quotation marks.

The parameters configured like this are provided in the cluster by default. The settings visible in the active session are these values unless you override them. The following section describes how an administrator or user overrides these defaults.

Each time the main server process receives a SIGHUP signal, the configuration file is read again. Executing pg_ctl reload on the command line or calling SQL function pg_reload_conf () sends SIGHUP. The main server process also spreads this signal to all server processes that are currently running, allowing the new value to be applied to the existing sessions (applied after the currently-running client command is complete). Alternatively, the user may send signals directly to a single server process. Some parameters can only be configured when the server starts. Changing the entries of the configuration file will be ignored until the server restarts. Likewise, incorrect parameter settings in the configuration file are also ignored during the SIGHUP process (but recorded in the log).

In addition to postgresql.conf, the AgesnGraph data directory ($AGDATA) contains postgresql.auto.conf that has the same format as postgresql.conf; postgresql.auto.conf should not be edited directly. This file contains the settings provided by the ALTER SYSTEM command. Every time postgresql.conf is present, this file is automatically read and the corresponding settings are applied in the same manner. The settings in postgresql.auto.conf take precedence over those in postgresql.conf.

Parameter Interaction via SQL

AgensGraph provides three SQL commands for setting configuration defaults. The ALTER SYSTEM command mentioned above provides a way to change global defaults using a SQL syntax, which is equivalent to editing postgresql.conf in function. In addition, there are two commands that enable default settings for each database or role.

• ALTER DATABASE: Overrides global settings by the database.
• ALTER ROLE: Overrides both global and database-specific settings with custom values.

The values set using ALTER DATABASE and ALTER ROLE shall apply only when starting a new database session. This overrides the value obtained from the configuration file or server command line and applies the default for the rest of the session. As some settings cannot be changed after starting the server, configuration using this command (or one of those listed below) is not possible.

When a client connects to the database, it provides two additional SQL commands (or equivalent functions) that can interact with the AgensGraph session-local configuration settings.

• You can check the current values of all parameters with the SHOW command. The corresponding function is current_setting (setting_name text).
• SET command allows you to modify the current value of the parameter, which can be set locally in the session. It does not affect other sessions. The corresponding function is set_config (setting_name, new_value, is_local).

The system view pg_settings can also be used to check and change session-local values.

• View query is similar to SHOW ALL, but shows more detailed results. It is also more flexible as it enables you to specify filter conditions or join with other relations.
• In this view, using UPDATE to update the setting column is equivalent to executing the SET command.
  For example:

SET configuration_parameter TO DEFAULT;

The above syntax is identical with the one below:

UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';

Managing Configuration File Contents

AgensGraph provides features to subdivide the complicated postgresql.conf into smaller files. Though the configuration methods of the features are not identical, they are useful especially for managing related servers.

In addition to setting individual parameters, postgresql.conf has “include, a directive. It specifies another file to read and process as it as if the file is inserted into the configuration file. This feature physically divides the configuration file. Here is a simple example of include:

include 'filename'

If the filename is not absolute, it is processed as a relative path to the configuration file directory it references. “include” can be nested.

There is also a directive “include_if_exists”, which works the same as the include directive except when the reference file does not exist or the file cannot be read. “include” treats this as an error condition, but include_if_exists simply logs the message and continues to process the referenced configuration file.

postgresql.conf may also include include_dir, which specifies the path to the configuration file to include as follows:

include_dir 'path'

If it is not an absolute path, it is processed as a relative path to the referenced configuration file directory. Files that are not directories within the specified directory are included only when their names end with .conf. As such files may be hidden on some platforms, file names that start with . are also ignored to prevent mistakes. Files in the include directory are processed in the file name order (according to the C locale conventions; for example, numeric-alphabetical order and uppercase-lowercase order).

Rather than using a single postgresql.conf file, “include” files or directories can be used to logically separate database configuration. Imagine a company that runs two database servers with different memory capacities. In the case of logging, there are likely to be configuration elements shared by two databases. However, the two servers are likely to have different/customized memory-related parameters. The way to manage such a situation is to split the changed customized configuration content into three files. Users may include each file by adding the following code to the end of the postgresql.conf file:

include 'shared.conf'
include 'memory.conf'
include 'server.conf'

The shared.conf file on all systems is identical. Each server with a different memory size can share the same memory.conf. Users are able to manage both 8GB RAM and 16GB RAM servers as a single file. Server-specific configuration information is included in server.conf.

You may also create a configuration file directory and add this information into the file. For example, the conf.d directory can be referenced as the last entry in postgresql.conf.

include_dir 'conf.d'

Then, specify the filename of the conf.d directory as follows:

00shared.conf
01memory.conf
02server.conf

This naming convention clarifies the order of file loading. When the server reads the configuration file, only the last parameter setting is applied. In this example, the values are set in conf.d/02server.conf override the values set in conf.d/01memory.conf.

You can use this method for a descriptive naming of files.

00shared.conf
01memory-8GB.conf
02server-foo.conf

This placement order assigns a unique name for each configuration file change. By doing so, reduces ambiguity when several server configurations are stored in a single location (e.g. the version control repository).

Planner Cost Constants

The cost variable described in this section is calculated at an arbitrary scale. As only the relative values are related, the query plan does not change if it is moved up or down with the same coefficient. Basically, these cost variables are based on the cost of fetching sequential pages. Since seq_page_cost is generally set to 1.0, other cost variables are configured based on seq_page_cost. You may use other costs, if you want, such as the number of milliseconds that you actually run on a particular machine.

Note: Unfortunately, there is no suitable way to determine the ideal value for a cost variable. It is best to process this as an average of the total query mixes received by a particular installation. In light of some experience, we should say changing this value can be very dangerous.

• seq_page_cost (floating point)

  • Sets the disk page fetch cost, which is part of the sequential fetch method expected by the planner. The default value is 1.0. This value can override tables and indexes in a particular tablespace by setting the same-named tablespace parameters.

• random_page_cost (floating point)

  • Sets the processing cost of nonsequentially-fetched disk pages expected by the planner. Sets the disk page fetch cost, which is part of the sequential fetch method expected by the planner. The default value is 4.0. This value can override tables and indexes in a particular tablespace by setting the same-named tablespace parameters.

  • Reducing this value in proportion to seq_page_cost causes the system to be biased towards the index scan. Increasing this value makes index scans more expensive; both values can be increased or decreased together to change the importance of disk I/O costs in proportion to the CPU cost. This feature is explained in the following parameters.

  • Random access to disk storage is typically four times more expensive than sequential access. However, as with indexed reads, most of the random access to disk occurs in cache; a small default is used (4.0). Even if random access appears to be 40 times slower than sequential access, we may expect 90% of the actual random reads to be cached.

  • If a 90% cache ratio is a false assumption in the user’s workload, you can increase the random_page_cost to reflect the actual costs of random storage reads. Thus, it may be appropriate to reduce random_page_cost if the database is smaller than the total server memory and the data is fully cached. A storage with a relatively low random reading cost, such as an SSD, may be better modeled with a lower random_page_cost value.

  Tip: Although setting random_page_cost below seq_page_cost is allowed in the system, it is not reasonable to do so in practice. However, if the entire database is cached in RAM, no cost is required in touching out-of-sequence pages and the two parameters may be identically configured. Also, since the cost for fetching pages that are already in RAM in an excessively cached database is much lower than the cost for fetching pages in a normal state, the user must reduce both values in proportion to the CPU parameters.

• cpu_tuple_cost (floating point)

  • Sets the processing cost of each row during a query expected by the planner. The default value is 0.01.

• cpu_index_tuple_cost (floating point)

  • Sets the processing cost of each index entry during the index scan expected by the planner. The default value is 0.005.

• cpu_operator_cost (floating point)

  • Sets the processing cost of each operator or function during a query expected by the planner. The default value is 0.0025.

• effective_cache_size (integer)

  • Sets the effective size of disk cache that can be used for a single query to be estimated by the planner. This parameter is reflected in the cost of using the index. The larger the value, the more likely the index scan will be used. The smaller the value, the more likely a sequential scan will be used. If you set this parameter, you must consider both the shared buffers and disk cache of the kernel used by the AgensGraph data file. The number of concurrent queries that are expected for different tables should be considered as well. This parameter has no effect on the amount of shared memory allocated by AgensGraph, nor does it preserve the kernel disk cache; it is used only for estimation purposes. The system also does not assume that data will remain in the disk cache between queries. The default is 4 gigabytes (4GB).

Options

-a
–echo-all
All input lines are shown as standard outputs as read (not read interactively). This is equivalent to setting the ECHO variable to all.

-A
–no-align
Switches to non-aligned output mode (otherwise default output mode is aligned).

-b
–echo-errors
Shows failed SQL commands as standard error outputs. This is equivalent to setting the ECHO variable to error.

-c command
–command=command
Specifies command to cause agens to execute the specified command string. This option can be repeated and used by combining commands with -f option. If -c or -f is specified, agens processes all -c and -f options without reading the command as standard input and exits.

command must be a command string that can be fully parsed by the server or a single backslash command. Therefore, you cannot mix SQL and agens meta-commands in -c option. You are allowed to use the repeated -c option or use strings with a pipe (‘|’). Examples:

$ agens -c '\x'-c 'SELECT * FROM test;'

or

$ echo '\x \\SELECT * FROM test;' | agens

(\\ is a delimiter metacommand.)

Each SQL command string delivered to -c is sent to the server as a single query. For this reason, if no explicit BEGIN/COMMIT commands that can be split into multiple transactions are contained in a string, the server runs it in a single transaction even with multiple SQL commands in a string. Agens also outputs only the results of the last SQL command to the string. As standard inputs are transmitted separately here, this behavior differs from that when they are sent as agens’ standard input or when the same strings are read from a file.

Placing more than one command in a single -c string often leads to unexpected results because of this behavior. It is better to use the -c command repeatedly, or use echo as in the example above, or use the here-document shell, as in the example below, to enter multiple commands into the agens standard input.

psql <<EOF
\x
SELECT * FROM test;
EOF

-d dbname
–dbname=dbname
Specifies the name of the database to connect to. This is the same as specifying dbname as an argument, not the first option on the command-line.

If this parameter has an equal (=) sign or starts with a valid URI prefix (postgresql:// or postgres://), it is processed as a conninfo string.

-e
–echo-queries
Copy all SQL commands sent to the server to standard output. This is equivalent to setting the variable ECHO in the query.

-E
–echo-hidden
Equivalent to turning the ECHO_HIDDEN variable to on.

-f filename
–file=filename
Read commands from a file with a filename, rather than standard input. This option can be repeated and combined in order using the -c option. When specifying -c or -f, agens does not read commands as standard input. Instead, it processes all the -c and -f options in order and exits. Except for this, this option is generally equivalent to the metacommand \i. 

If the filename is -(hyphen), standard input reads EOF or up to a metacommand \q. This allows interaction between interactive input and input from the file. In this case, however, Readline is not used (as if -n is specified). 

Using this option is slightly different from using agens < filename. In general, both will work as expected, but using -f will allow you to use useful features such as error messages with line numbers. This option may reduce startup overhead. On the other hand, a variant using the shell’s input changes guarantees (theoretically) the same output as the input it receives when everything is entered manually.

-F separator
–field-separator=separator
Use separator as the field delimiter for unaligned output. This is equivalent to \pset fieldsep or \f.

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

-H
–html
Turns on the HTML tabular output. This is equivalent to the \pset format html or \H command.

-l
–list
List all available databases and exit. Other non-connection options are ignored. Similar to meta-command \list.

-L filename
–log-file=filename
Create all query logs in filename

-n
–no-readline
Do not use extended command-line editing.

-o filename
–output=filename
Save all query results to the filename file. It is the same as the \o command.

-p port
–port=port
Specifies the TCP port or local Unix domain socket file extension on which the server listens for connections. The default value is the value of the PGPORT environment variable or, if not set, specified at compile time (default: 5432).

-P assignment
–pset=assignment
Specifies output options in \pset style. The name and value must be separated by an equal sign instead of a space. For example, to set the output format to LaTeX, use -P format=latex.

-q
–quiet
Specifies this for quiet work of agens. It basically outputs the start message and various information. With this option enabled, nothing would occur. This is useful when used with the -c option. Equivalent to turning on the QUIET variable.

-R separator
–record-separator=separator
Use separator as the record delimiter for unaligned output. This is the same as the \pset recordsep command.

-s
–single-step
Run in single-step mode. That is, a message is displayed to the user before each command is sent to the server, and an undo option is also displayed. Used to debug scripts.

-S
–single-line
Like a semicolon, a new line character is executed in single-line mode, which terminates the SQL command.

Notice: This mode is provided for those who want to use it and is not basically recommended for use. In particular, if you mix SQL and meta-commands on a single line, the order of execution may not be obvious to the first-time user.

-t
–tuples-only
Turn off column name and result row output. This is the same as the \t command.

-T table_options
–table-attr=table_options
Specifies the options to place within the HTML table tag. For more information, refer to \pset.

-U username
–username=username
Connect to the database with username instead of the default (you must have necessary permissions).

-v assignment
–set=assignment
–variable=assignment
Performs the same variable assignment as the meta-command \set. If the name and value are present, they must be separated by an equal sign on the command-line. To unset the variable, leave the equal sign. To set a variable to an empty value, use the equal sign and leave the value unchanged. Variables that are allocated/executed in the initial phase of startup but are reserved for internal use can be overwritten later on.

-V
–version
Shows agens version information and exits.

–revision
Shows agens revision information and exits.

-w
–no-password
Do not enter a password. If the server requires password authentication and a password in some other ways, such as .pgpass file, the connection attempt will fail. This option is useful for deployment jobs or scripts where there is no user to enter a password.

As this option is set for the entire session, it affects use of a metacommand \connect as well as the initial connection attempt.

-W
–password
Forcibly sets agens to request a password before connecting to the database.

This option is not needed when the server requires password authentication; agens automatically requires a password as well. However, agens will waste connection attempts to know whether the server wants a password or not. In some cases it is better to type -W to avoid further connection attempts.

As this option is set for the entire session, it affects use of a metacommand \connect as well as the initial connection attempt.

-x
–expanded
Turns on extended expression assignment mode. This is the same as the \w command.

-X
–no-psqlrc
Do not read the startup file (either the system’s psqlrc file or your .psqlrc file).

-z
–field-separator-zero
Sets the field separator for unaligned output to 0 bytes.

-0
–record-separator-zero
Sets the record separator for unaligned output to 0 bytes. For example, it is useful for interfaces like xargs -0.

-1
–single-transaction
This option should be used with one or more -c and / or -f options.

This option wraps all commands in a single transaction by issuing BEGIN first, and COMMIT in the end. This ensures that all commands are completed successfully or that the changes do not take effect.

If you include BEGIN, COMMIT, or ROLLBACK in the command itself, this option cannot have the desired effect. In addition, if you cannot execute individual commands within a transaction block, specifying this option will cause the entire transaction to fail.

-?
–help[ =options ]
Displays help for agens and exits. The optional options parameter (optional default) selects the part of agens described. The command describes agens’ backslash command, and options describes the command-line options that can be passed to agens. Shows help for agens configuration variables.

Exit Status

Agens will return 0 if the shell is done successfully, or 1 if there is a fatal internal error (e.g. memory shortage and file cannot be found). Agens will return 2 if the session is not interactive because of incorrect connection to the server, or return 3 if the script has an error and the variable ON_ERROR_STOP is set.

Usage

$ agens postgres
agens (11.11)
Type "help" for help.
postgres=#

db=# \c mydb myuser bitnine 5555
db=# \c service=mydb
db=# \c "host=localhost port=5555 dbname=mydb connect_timeout=10 sslmode=disable"
db=# \c postgresql://myuser@localhost/mydb?application_name=myapp

db=# \echo `date`
Tue Oct 26 21:40:57 CEST 1999

dbdb=# SELECT format('create index on my_table(%I)', attname)
-# FROM pg_attribute
-# WHERE attrelid = 'my_table'::regclass AND attnum > 0
-# ORDER BY attnum
-# \gexec
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

db=# SELECT 'hello' AS var1, 10 AS var2
db-# \gset
db=# \echo :var1 :var2
hello 10

db=# SELECT 'hello' AS var1, 10 AS var2
db-# \gset result_
db=# \echo :result_var1 :result_var2
hello 10

db=# \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
lo_import 152801

db=# \setenv PAGER less
db=# \setenv LESS -imx4F

Advanced Features

db=# \set foo bar

db=# \echo :foo
bar

\set HISTFILE ~/.psql_history- :DBNAME

db=# \set foo 'my_table'
db=# SELECT * FROM :foo;

db=# \set foo 'my_table'
db=# SELECT * FROM :"foo";

db=# \set content `cat my_file.txt`
db=# INSERT INTO my_table VALUES (:'content');

db=# \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '

$if agens
set disable-completion on
$endif

Environment

COLUMNS
If \pset columns are 0, you need to adjust the wrapped format and width or switch from the extended automatic mode to the vertical format, in order to determine whether the extensive output is required.

PAGER
If the query results do not fit on the screen, they are piped through this command. Typical values are more or less. The default value depends on the platform. You can disable PAGER by setting it to blank or using pager-related options of the \pset command.

PGDATABASE
PGHOST
PGPORT
PGUSER
Basic connection parameters (Reference).

PSQL_EDITOR
EDITOR
VISUAL
An editor is used for \e, \ef, and \ev commands. These variables are checked in the order listed and are used in the first set order.

The built-in default editor is vi on Unix systems and notepad.exe on Windows systems.

PSQL_EDITOR_LINENUMBER_ARG
When using \e, \ef, and \ev with a line number argument, these variables specify the command-line argument used to pass the start line number to the user’s editor. For editors like Emacs or vi, this is a plus (+) sign. If there has to be a space between the option name and the line number, include the trailing space in the variable value. Here is an example:

PSQL_EDITOR_LINENUMBER_ARG='+'
PSQL_EDITOR_LINENUMBER_ARG='--line '

On Unix systems, the default is + (this corresponds to the default editor vi and is useful for many other common editors). However, there is no default on Windows systems.

PSQL_HISTORY
A tilde (~) expansion is performed as an alternative location to the command history file.

PSQLRC
A tilde (~) extension is performed as an alternative location to the user’s .psqlrc file.

SHELL
Commands executed with \!.

TMPDIR
A directory to store temporary files (default is /tmp). This utility uses environment variables supported by libpq.

Files

psqlrc and ~/.psqlrc
Agens reads and executes commands from the system-wide startup file (psqlrc) and your personal startup file (~/.psqlrc) before connecting to the database and accepting normal commands unless you specify the -X option. These files are typically used to set up clients and/or servers using the \set and SET commands.

The system-wide startup file is named psqlrc and can be found in the “System Configuration” directory of the installation. This directory is most reliably identified by running pg_config --sysconfdir. By default, this directory is ../etc/ for the directory containing the AgensGraph executable(s). The name of this directory can be explicitly set via the PGSYSCONFDIR environment variable.

The user’s personal startup file name is .psqlrc and the calling user’s home directory is searched. On Windows where such a concept is not available, the name of the personal startup file is %APPDATA%\postgresql\psqlrc.conf. The location of the user-startup file can be explicitly set via the PSQLRC environment variable.

.psql_history
command-line history is stored in ~/.psql_history or %APPDATA%\postgresql\psql_history file on Windows.

The location of the history file can be explicitly set via the PSQL_HISTORY environment variable.

Notes

Agens works best with major versions (old or new) of the server. The backslash command can fail, especially if the server is newer than agens itself. The generic ability to execute SQL commands and display query results should work on new major versions of the server, but cannot be guaranteed in every case. In the case where you want to use agens and try to connect an agens program to multiple servers of other major versions, you had better use the latest version. Alternatively, you should keep a copy of each major version of agens and use the version that matches the server.

Notes for Windows Users

Agens is built as a “console application”. Be especially cautious when using 8-bit characters in agens because the Windows console window uses a different encoding from the rest of the system. When agens searches for a problematic console code page, a warning message is displayed at startup. To change the console code page, you need two things as follows:

• Enter and set cmd.exe /c chcp 1252 code page (1252 is a code page suitable for German; enter an appropriate code page). If you are using Cygwin, you can put this command in /etc/profile.
  
• Since raster fonts do not work with ANSI code pages, set the console font to Lucida Console.

Examples

The first example (below) shows how to distribute commands over multiple lines of input.

agens=# CREATE TABLE my_table (
agens(#  first integer not null default 0,
agens(#  second text)
agens-# ;
CREATE TABLE

Check the definition of the table.

agens=# \d my_table
             Table "my_table"
 Attribute |  Type   |      Modifier
-----------+---------+--------------------
 first     | integer | not null default 0
 second    | text    |

You can change the prompt as follows:

agens=# \set PROMPT1 '%n@%m %~%R%# '
tester@[local] agens=# 

Populate the table with data and query the table.

tester@[local] agens=#  SELECT * FROM my_table;
 first | second
-------+--------
     1 | one
     2 | two
     3 | three
     4 | four
(4 rows)

You can use the \pset command to display tables in different ways.

tester@[local] agens=# \pset border 2
Border style is 2.
tester@[local] agens=# SELECT * FROM my_table;
+-------+--------+
| first | second |
+-------+--------+
|     1 | one    |
|     2 | two    |
|     3 | three  |
|     4 | four   |
+-------+--------+
(4 rows)

tester@[local] agens=# \pset border 0
Border style is 0.
tester@[local] agens=# SELECT * FROM my_table;
first second
----- ------
    1 one
    2 two
    3 three
    4 four
(4 rows)

tester@[local] agens=# \pset border 1
Border style is 1.
tester@[local] agens=# \pset format unaligned
Output format is unaligned.
tester@[local] agens=# \pset fieldsep ,
Field separator is ",".
tester@[local] agens=# \pset tuples_only
Showing only tuples.
tester@[local] agens=# SELECT second, first FROM my_table;
one,1
two,2
three,3
four,4

Or you can use a short command.

tester@[local] agens=# \a \t \x
Output format is aligned.
Tuples only is off.
Expanded display is on.
tester@[local] agens=# SELECT * FROM my_table;
-[ RECORD 1 ]-
first  | 1
second | one
-[ RECORD 2 ]-
first  | 2
second | two
-[ RECORD 3 ]-
first  | 3
second | three
-[ RECORD 4 ]-
first  | 4
second | four

If appropriate, you can use the \crosstabview command to display the query results in a crosstab representation.

db=# SELECT first, second, first > 2 AS gt2 FROM my_table;
 first | second | gt2 
-------+--------+-----
     1 | one    | f
     2 | two    | f
     3 | three  | t
     4 | four   | t
(4 rows)

db=# \crosstabview first second
 first | one | two | three | four 
-------+-----+-----+-------+------
     1 | f   |     |       | 
     2 |     | f   |       | 
     3 |     |     | t     | 
     4 |     |     |       | t
(4 rows)

This second example shows a multiplication table in which the numeric sequence is sorted in reverse order and the columns are in ascending numerical order.

agens=# SELECT t1.first as "A", t2.first+100 AS "B", t1.first*(t2.first+100) as "AxB",
agens-# row_number() over(order by t2.first) AS ord
agens-# FROM my_table t1 CROSS JOIN my_table t2 ORDER BY 1 DESC
agens-# \crosstabview "A" "B" "AxB" ord
 A | 101 | 102 | 103 | 104 
---+-----+-----+-----+-----
 4 | 404 | 408 | 412 | 416
 3 | 303 | 306 | 309 | 312
 2 | 202 | 204 | 206 | 208
 1 | 101 | 102 | 103 | 104
(4 rows)

Options

dbname
Specifies the name of a database to dump. If not specified, the username specified in the connection will be used.

-a
–data-only
Dumps only data, not schema (data definition).

-b
–blobs
Includes a large object in the dump. This is the default behavior unless --schema, --table, or --schema-only is specified. Therefore, the -b switch is useful only if a particular schema or table adds a large object to the requested dump. The blob is regarded as data, so it is included when --data-only is used, but not included when it is not --schema-only.

-c
–clean
Prints a command to clean up (delete) database objects before issuing a command to create a database object. (If --if-exists is not specified and the object does not exist in the target database, a false error message may be generated.)

This option is meaningful only in plain text format. For archive formats, you can specify options when you call 
pg_restore.

-C
–create
Starts the output with a command that creates a database itself and reconnects to the created database. (If you use this form of script, the database of the target installation you want to connect to is not important before you run the script.) If --clean is also specified, the script deletes the target database and then reconnects.

This option is meaningful only in plain text format. For archive formats, you can specify options when you call 
pg_restore.

-E encoding
–encoding=encoding
Creates a dump with the specified character set encoding. By default, dumps are written in database encodings. (Another way to achieve the same result is to set the PGCLIENTENCODING environment variable to the desired dump encoding.)

-f file
–file=file
Sends the output to the specified file. This parameter can be omitted in a file-based output format where standard output is used. However, it should be provided for a directory output format that specifies a target directory instead of a file. In such a case, a directory (that has not existed) is created by pg_dump.

-F format
–format=format(c|d|t|p)
Selects an output format. the format can be one of the following:

• p
  plain
  Prints plain-text SQL script files (default).

• c
  custom
  Prints user-defined type archives suitable for pg_restore input. Along with the directory output format, this option is the most flexible output format that allows you to manually select the items kept during a restore and change their sequence. This format is compressed by default.

• d
  directory
  Prints user-defined type archives suitable for pg_restore input. This will create each table, a file that will dump blob, and a directory that contains a so-called table of contents file that describes the objects dumped in a machine-readable format that can be read by pg_restore. Directory-type archives can be manipulated with standard Unix tools. For instance, files in uncompressed archives can be compressed with gzip. This format is compressed by default and supports parallel dumps.

• t
  tar
  Prints a tar-format archive suitable for input to pg_restore. The tar format is compatible with the directory format. Extracting a tar format archive creates a valid directory format archive. However, the tar format does not support compression. With the tar format, the relative order of table data items cannot be changed during a restore.

-j njobs
–jobs=njobs
Runs a parallel dump by dumping the njobs tables at the same time. This option reduces the dump time, but increases the load on the database server. As this option is the only output format that allows multiple processes to write data at the same time, it can be used only in the directory output format.

Since pg_dump will open an njobs+1 connection to the database, the max_connections setting should be high enough to accommodate all connections.

If you request an exclusive lock on a database object while executing a parallel dump, the dump may fail. This is because the pg_dump master process requires the worker process to request a shared lock on the object to dump later so that no one deletes it while the dump is running. If another client requests an exclusive lock on the table, the lock is not granted, but it waits until the shared lock of the master process is released. As a result, no other access to the table are granted and they wait after the exclusive lock request. This includes the worker process that tries to dump the table. If there are no precautions, a classical deadlock occurs. To detect this conflict, the pg_dump worker process requests another shared lock using the NOWAIT option. If this shared lock is not granted to the worker process, someone else has to request an exclusive lock; as there is no way to continue the dump, pg_dump will have to stop dumping.

For consistent backup, the database server must support synchronized snapshots. With this feature, database clients can see the same dataset even if they use different connections. pg_dump -j uses multiple database connections. It connects to the database once in the master process and connects to each worker job again. Without the synchronized snapshot feature, inconsistent backups may occur because different worker jobs cannot see the same data on each connection.

-n schema
–schema=schema
Dumps only schema that matches the schema. This selects both the schema itself and all objects contained in the schema. If this option is not specified, all non-system schemas in the target database are dumped. Multiple schemas can be selected by creating multiple -n switches. Schema parameters are also interpreted in the same pattern as the rules used by agens’ \d command, and wildcard characters can be used to select multiple schemas. When using wildcards, use quotation marks to prevent the shell from extending wildcards (refer to Examples).

Notice: If -n is specified, pg_dump will not dump any other database objects that may vary depending on the schema selected. Therefore, there is no guarantee that the result of a particular schema dump can be restored to a normal clean database by itself.

Notice: Non-schema objects such as blob are not dumped if -n is specified. You can add blob back to the dump using the --blobs switch.

-N schema
–exclude-schema=schema
Do not dump schema that matches the schema pattern. Patterns are interpreted according to the same rules as -n. -N can be used more than once to exclude schemas that match one of several patterns.

Given both -n and -N, this operation dumps schemas that match at least one -n switch, except for the -N switch. If -N appears without -n, schemas that match -N are excluded from the normal dump.

-o
–oids
Dumps the object identifier (OID) into part of the data in all tables. Use this option if your application refers to an OID column in some ways (e.g. foreign key constraint). Otherwise, this option is not available.

-O
–no-owner
Do not print commands that set ownership of an object to match the original database. By default, pg_dump issues an ALTER OWNER or SET SESSION AUTHORIZATION statement to set ownership of the created database object. This command will fail when the script is run unless the superuser (or the same user who owns all objects in the script) starts this command. Specify -O to create a script that grants ownership of all objects only to an applicable user, but can be restored by any user.

This option is meaningful only in plain text format. For archive formats, you can specify options when you call 
pg_restore.

-s
–schema-only
Dumps object definitions (schemas) only.

This option is the opposite of --data-only. This is similar to --section=pre-data --section=post-data.

(Do not confuse this with the --schema option, which uses the word “schema” in other ways.)

To exclude table data only for a subset of the tables in the database, see --exclude-table-data.

-S username
–superuser=username
Specifies the name of superuser to use when not using the trigger. This option is relevant only if --disable-triggers is used (generally it is better to skip this step; instead you should start the results with a supercomputer).

-t table
–table=table
Dumps only a table whose name matches the table. To this end, “table” includes views, materialized views, sequences, and foreign tables. You may create multiple -t switches to select multiple tables. As the table parameter is interpreted in the same pattern as the agens \d command, you can select multiple tables using wildcard characters in the pattern. To prevent the shell from extending wildcards, use quotes (refer to Examples).

The -n and -N switches do not work when using -t, because the tables selected with -t are dumped regardless of the switches and non-table objects are not dumped.

Notice: If you specify -t, pg_dump will not attempt to dump other database objects on which the selected table depends. Therefore, there is no guarantee that the results for a particular database can be successfully restored to its own database.

-T table
–exclude-table=table
Do not dump tables that match the table pattern. This pattern is interpreted according to the same rules as -t. -T can be given more than once, except for tables that match one of the various patterns.

When both -t and -T are given, this action dumps tables that match at least one -t switch, except for the -T switch. If -T is used without -t, tables that match -T are excluded from normal dumps.

-v
–verbose
Specifies the verbose mode. This will cause pg_dump to print the detailed object description and start/stop times to a dump file and process the message as a standard error.

-V
–version
Displays the version of pg_dump and exits.

-x
–no-privileges
–no-acl
Prevents dumping of access privileges (grant/revoke command).

-Z 0..9
–compress=0..9
Specifies the compression level to use. “0” means no compression. For the user-defined archive format, this specifies compression of individual table data segments, the default is to compress at a medium level. For plain text output, setting a nonzero compression level compresses the entire output file as supplied by gzip (but, the default is not compressed). The tar archive format currently does not support compression at all.

–binary-upgrade
This option is used by the full upgrade utility. Using this option for other purposes is not recommended or supported. The behavior of this option may change in future releases without notice.

–column-inserts
Dumps data into an INSERT statement with an explicit column name (INSERT INTO table (column, …) VALUES …). This will greatly slow down the restoration. This option is primarily useful for creating dumps that can be loaded into a non-Agens database. This option generates a separate command for each row, so if an error occurs while reloading a row, only that row (not the entire table content) is lost.

–disable-dollar-quoting
This option disables the use of dollar quotes for the function body and allows the use of quotation marks using SQL standard string syntax.

–disable-triggers
This option is relevant only when creating a data-only dump. Instructs pg_dump to include a command to temporarily disable trigger on the target table while data is being reloaded. Use this option in case of referential integrity check in which you do not want a call while reloading data or in the case where there are other triggers on the table.

Currently, the commands generated for --disable-triggers must be run by the superuser. Therefore, be careful when you specify the superuser name with -S or start the resulting script as the superuser.

This option is meaningful only in plain text format. For archive formats, you can specify options when you call 
pg_restore.

–enable-row-security
This option is relevant only when dumping the content of a table with row security. By default, pg_dump sets 
row_security to off so that all data is dumped from the table. An error occurs if the user does not have sufficient privileges to bypass row security. This parameter allows pg_dump to dump some of the user-accessible table content by setting row_security to on.

–exclude-table-data=table
Do not dump data for a table that matches the table pattern. --exclude-table-data can be supplied more than once to exclude tables that match one of several patterns. This option is useful when you need to define a specific table, even if you do not need the data.

To exclude data for all tables in the database, refer to --schema-only.

–if-exists
Use conditional commands (such as the IF EXISTS clause) to clean up database objects. This option is not valid unless --clean is specified.

–inserts
Dumps data with INSERT command instead of COPY. This will greatly slow down the restoration speed. This option is primarily useful for creating dumps that can be loaded into a non-Agens database. This option generates a separate command for each row, so if an error occurs while reloading a row, only that row (not the entire table content) is lost. Rearranging the column order can cause the restore to fail. The --column-insert option is slow, but can be used safely when changing the column order.

–lock-wait-timeout=timeout
Does not wait forever to acquire the shared table lock at the beginning of the dump. It will fail if the table cannot be locked within the specified timeout. The timeout can be specified in the format allowed by the SET statement_timeout (allowed values are integers in milliseconds).

–no-security-labels
Does not dump the security label.

–no-synchronized-snapshots
This option allows you to run pg_dump -j. Refer to the description of the -j parameter for details.

–no-tablespaces
Does not print command to select tablespaces. When this option is used, all objects are created in the table realm, which is the default value during restoration.

This option is meaningful only in plain text format. For archive formats, you can specify options when you call 
pg_restore.

–no-unlogged-table-data
Does not dump the content of unlogged tables. This option does not affect whether the table definition (schema) is dumped or not. Limit only dumping of table data. Data in non-logged tables is always excluded when dumping from the standby server.

–quote-all-identifiers
Forces all identifiers to be quoted. This option is recommended when the main version of AgensGraph dumps the database on a different server from pg_dump, or when you load the output on another major version of the server. By default, pg_dump quotes only identifiers that are reserved words in its major version. This may cause a compatibility problem when processing different versions of the server with different versions of the reserved word set. You can use --quote-all-identifier to avoid this problem instead of using a hard-to-read dump script.

–section=section
Dumps only the named section. A section can be pre-data, data, or post-data. This option can be specified more than once to select multiple sections. The default is to dump all sections.

The data section includes actual table data, large-volume object content, and sequence values. The post-data entry contains the definitions of indexes, triggers, rules, and constraints (not validity checking constraints). The pre-data entry contains all other data definition entries.

–serializable-deferrable
Uses a serializable transaction in dumps to ensure that the used snapshot matches the state of the database at a later time. This option ensures there is no risk of a dump failing or another transaction being rolled back to serialization_failure by waiting for a point in the transaction stream for which no exception is present.

This option is not useful for disaster recovery-only dumps. It can be useful to load a database copy while the database is still being updated or to load a database copy for another read-only load sharing. Without it, the dump can eventually reflect a condition that is inconsistent with any subsequent execution of the committed transaction. For instance, you may use batch processing techniques to mark batches as closed without displaying all the items in the batch.

This option makes no difference if there is no active read/write transaction when pg_dump starts. If a read-write transaction is active (enabled), the start of a dump can be delayed for an indeterminate amount of time. Once executed, performance will be the same regardless of the use of switches.

–snapshot=snapshot
Uses a specified synchronized snapshot when dumping the database.

This option is useful when you need to synchronize a dump with a logical replication slot or a concurrent session.

For a parallel dump, the snapshot name defined by this option is used instead of creating a new snapshot.

–strict-names
Requires that each schema (-n/--schema) and table (-t/--table) qualifiers match at least one schema/table in the database to be dumped. Without a matching schema/table qualifier, pg_dump generates an error without  
--strict-names.

This option does not affect -N/--exclude-schema, -T/--exclude-table, --exclude-table-data. An exclusion pattern that does not match an object is not regarded as an error.

–use-set-session-authorization
Prints the SQL standard SET SESSION AUTHORIZATION command, instead of the ALTER OWNER command, to identify the object ownership. By doing so, the dump can be compliant with the standard, but may not be restored properly depending on the record of the object in the dump. In addition, dumps using SET SESSION AUTHORIZATION require that superuser privileges be restored correctly, but ALTER OWNER requires fewer privileges.

-?
–help
Displays help for pg_dump command-line arguments and exits.

The following command-line options control database connection parameters.

-d dbname
–dbname=dbname
Specifies the name of the database to connect to. This is equivalent to specifying dbnameas an argument, not the first option on the command-line.

If this parameter has an equal (=) sign or starts with a valid URI prefix (postgresql:// or postgres://), it is processed as a conninfo string.

-h host
–host=host
Specifies the hostname of the system on which the server is running. If the value starts with a slash, it is used as the directory for Unix domain sockets. The default value is taken from the PGHOST environment variable (if set). Otherwise, a Unix domain socket connection is attempted.

-p port
–port=port
Specifies the TCP port or local Unix domain socket file extension on which the server listens for connections. The default value is the PGPORT environment variable. It should be set to the (compiled) default value.

-U username
–username=username
The name of the user to connect to.

-w
–no-password
Does not prompt for a password. If the server requires password authentication and cannot use the password in any other way, such as in a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where there is no user who may enter a password.

-W
–password
Before connecting to the database, run pg_dump to enter the password.

As pg_dump automatically requires a password when the server requests password authentication, this option is never needed. However, pg_dump wastes connection attempts to find out if the server wants a password. In some cases, it is better to type -W to avoid extra connection attempts.

–role=rolename
Specifies the role name to be used to create a dump. With this option, pg_dump issues the SET ROLE rolename command after connecting to the database. This is useful when an authenticated user (specified by -U) lacks the privileges required by pg_dump, but has the authority to switch to the required privilege. If you have a policy that prevents you from logging in directly as the superuser, you may use this option to generate a dump without violating the policy.

Examples

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

$ pg_dump mydb > db.sql

To reload a script into a newly created database named newdb:

$ agens -d newdb -f db.sql

To dump a database to a user-defined type archive file:

$ pg_dump -Fc mydb > db.dump

To dump a database to a directory format archive:

$ pg_dump -Fd mydb -f dumpdir

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

$ pg_dump -Fd mydb -j 5 -f dumpdir

To reload an archive file into a newly created database called newdb:

$ pg_restore -d newdb db.dump

To dump a single table called mytab:

$ pg_dump -t mytab mydb > db.sql

To dump all the tables in the detroit schema that start with the name emp except for the table named employee_log:

$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

To dump all schemas whose names start with east or west and end with gsm, and exclude schemas whose names contain test:

$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

or

$ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql

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

$ pg_dump -T 'ts_*' mydb > db.sql

You must enclose the name in double quotes to specify a name that mixes uppercase or mixed-case characters in the -t and related switches. Otherwise, it is converted to lowercase. However, as double quotes apply only to shells, you must enclose them in quotes. Thus, to dump a single table with a mixed case, you should do the following:

$ pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql

Options

pg_restore accepts the following command-line arguments:

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

-a
–data-only
Restore only data that is not the schema (data definition). Table data, large objects, and sequence values are restored if they are in the archive.

This option is similar to specifying --section=data.

-c
–clean
Drops a database object before regenerating it. (If you do not use --if-exists, a harmless error message may be generated when there is no object in the target database.)

-C
–create
Creates a database before restoring. If specified with -clean, drop, and recreate the target database before connection.

With this option, the database named -d is used only to execute the initial DROP DATABASE and CREATE DATABASE commands. All data is restored to the database name that appears in the archive.

-d dbname
–dbname=dbname
Connects to the database dbname and restores it directly to the database.

-e
–exit-on-error
If an error occurs while sending an SQL command to the database, it terminates. The default is to continue and display the number of errors at the end of the restore.

-f filename
–file=filename
Specifies the output file of the generated script or, if used with -l, specifies the file to list. The default is standard output.

-F format
–format=format
Specifies the archive format. Specifying this format is not required as pg_restore automatically determines it. If specified, it can be one of the following:

• c
  custom
  A user-defined format of pg_dump.

• d
  directory
  A directory archive.

• t
  tar
  A tar archive.

-I index
–index=index
Only definitions of the named indexes are restored. Multiple indexes can be specified with multiple -I switches.

-j number-of-jobs
–jobs=number-of-jobs
Executes the most time-consuming part of pg_restore as multiple concurrent tasks are used to load data, create indexes, or create constraints. This option greatly reduces the time to restore a large database to a server running on a multiprocessor system.

Each task is a process or thread, depending on the operating system, and uses a separate connection to the server.

The optimal value for this option varies depending on the hardware settings of the server, client, and network (e.g. the number of CPU cores, disk configuration). If you set a larger value than the number of CPU cores in the server, you can shorten the restore time in most cases. A too high value may cause performance degradation due to memory thrashing.

Only the user-defined and directory archive formats are supported in this option. The input must be a regular file or directory. This option is ignored when exporting scripts without connecting directly to the database server. You cannot use multiple jobs with the --single-transaction option.

-l
–list
Lists the contents of the archive. The output of this operation can be used as input to the -L option. Use a filtering switch such as -n or -t with -l to restrict the listed items.

-L list-file
–use-list=list-file
Restores only the archive elements in list-file in the order they are listed in the file. If you use a filtering switch such as -n or -t with -L, you further restrict what is restored.

list-file is usually created by editing the output of the previous -l operation. Lines can be moved or removed, and annotations can be used with a semicolon (;) at the beginning of the line.

-n namespace
–schema=schema
Restores only the objects in the named schema. Multiple schemas can be specified with a multiple -n switch. You can combine this option with -t to restore only specific tables.

-O
–no-owner
Does not print commands that set ownership of the objects so that they match the original database. By default, pg_restore sets ownership of schema elements created through the ALTER OWNER or SET SESSION AUTHORIZATION statement. This statement fails if the superuser (or the same user who owns all objects in the script) does not make an initial connection to the database. Using -O allows all usernames to be used for initial connections; the user will own all the objects created.

-P function-name(argtype [, …])
–function=function-name(argtype [, …])
Restores only named functions. Be careful with the spelling of the function name and arguments as shown in the table of contents of the dump file. Multiple functions can be specified using a multiple -P switch.

-s
–schema-only
Restores only the schema (data definition) to the extent of schema entry in the archive (not in the data).

This option is the opposite of --data-only. It is similar to specifying --section=pre-data,--section=post-data.

(Do not confuse this with the --schema option, which uses the word “schema” in other ways.)

-S username
–superuser=username
Specifies the superuser name to use when disabling trigger. This is only relevant if --disable-triggers are used.

-t table
–table=table
Restores the definition or data of a 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 a table in a particular schema.

Note: If you specify -t, pg_restore does not attempt to restore any other database objects on which the selected table depends. Therefore, there is no guarantee that the result of a particular schema dump can be restored to a normal clean database by itself.

Note: This flag does not work in the same way as the -t flag of pg_dump. There is currently no provision for wildcard matching in pg_restore, and you cannot include the schema name in -t.

-T trigger
–trigger=trigger
Restores only named triggers. Multiple triggers can be specified as multiple -T switches.

-v
–verbose
Specifies the verbose mode.

-V
–version
Displays the version of pg_restore and exits.

-x
–no-privileges
–no-acl
Prevents restoration of access privileges (grant/revoke command).

-1
–single-transaction
Runs a restore in a single transaction (i.e. wrap the command released by BEGIN/COMMIT). By doing this, all commands are completed successfully or the changes do not take effect. This option means --exit-on-error.

–disable-triggers
This option is relevant only when performing a data-only restore. This instructs pg_restore to temporarily disable trigger on the target table while executing a command to reload the data. Use this option in case of referential integrity check in which you do not want a call while reloading data or in the case where there are other triggers on the table.

Currently the command for --disable-trigger must be run as superuser. Thus, specify the superuser name with -S, or run pg_restore as the AgensGraph superuser.

–enable-row-security
This option is relevant only when restoring the content of a table with row security. By default, pg_restore sets row_security to off so that all data is restored to the table. An error occurs if the user does not have sufficient privileges to bypass row security. This parameter allows pg_restore to restore the content of the table with row security by setting row_security to on. This operation may fail even if the user is not authorized to insert rows from the dump into the table.

As COPY FROM does not support row security, the current dump should be an INSERT type in this option.

–if-exists
Uses conditional commands (such as the IF EXISTS clause) to clean up database objects. This option is not valid unless –clean is specified.

–no-data-for-failed-tables
Basically, table data creation is restored even if the table creation command fails. If you use this option, data in those tables are skipped. This is useful if you already have content in the table you want in the target database. For example, auxiliary tables for PostgreSQL extensions, such as PostGIS, may already be loaded into the target database. Specifying this option prevents duplicate or obsolete data from loading.

This option is valid only when restoring directly to the database, not when generating a SQL script output.

–no-security-labels
The command to restore the security label does not print the command even if it is included in the archive.

–no-tablespaces
Does not print the command to select tablespaces. When this option is enabled, all objects are created in all tablespaces that are the default during a restore.

–section=section
Restores only the named sections. Section names 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. A post-data item consists of the definitions of indexes, triggers, rules, and constraints (not validity check constraints). The Pre-data item consists of all other data definition entries.

–strict-names
Each schema (-n-schema-schema) and table (-T-table) qualifiers need to match one or more schemas/tables with the backup file.

–use-set-session-authorization
Prints the SQL standard SET SESSION AUTHORIZATION command, instead of the ALTER OWNER command, to identify object ownership. By doing this, the dump can be compliant with the standard, but may not be restored properly depending on the record of the object in the dump.

-?
–help
Displays help for pg_restore command-line arguments and exits.

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

-h host
–host=host
Specifies the hostname of the system on which the server is running. If the value starts with a slash, it is used as a directory for Unix domain sockets. The default value is taken from the PGHOST environment variable (if set). Otherwise, a Unix domain socket connection is attempted.

-p port
–port=port
Specifies the TCP port or local Unix domain socket file extension on which the server listens for connections. The default value is the PGPORT environment variable and should be set to the (compiled) default.

-U username
–username=username
The name of the user to connect to.

-w
–no-password
Does not prompt for a password. If the server requires password authentication and cannot use the password in any other way, such as in a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where there is no user who can enter the password.

-W
–password
Before connecting to the database, run pg_restore to enter the password.

As pg_restore automatically requires a password when the server requests password authentication, this option is never needed. However, pg_restore wastes connection attempts to find out if the server wants a password. In some cases, it is better to type -W to avoid extra connection attempts.

–role=rolename
Specifies the role name to be used to create a dump. With this option, pg_restore issues the SET ROLE rolename command after connecting to the database. This is useful when an authenticated user (specified by -U) lacks the privileges required by pg_restore, but has the authority to switch to the required privilege. If you have a policy that prevents you from logging in directly as the superuser, you may use this option to generate a dump without violating the policy.

Examples

Suppose you have dumped a database called mydb into a dump file in a user-defined format.

$ pg_dump -Fc mydb > db.dump

To delete the database and re-create the database in the dump:

$ dropdb mydb
$ pg_restore -C -d postgres db.dump

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

To reload a dump into a new database called newdb:

$ createdb -T template0 newdb
$ pg_restore -d newdb db.dump

Connect directly to the database to be restored without using -C. Replicate the new database from template0, not template1, to make sure it is initially empty.

To rearrange database entries, you must first dump the table of contents of the archive.

$ pg_restore -l db.dump > db.list

The list file consists of a header and a line for each entry as follows (example):

;
; Archive created at Mon Sep 14 13:55:39 2009
;     dbname: DBDEMOS
;     TOC Entries: 81
;     Compression: 9
;     Dump Version: 1.10-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 8.3.5
;     Dumped by pg_dump version: 8.3.8
;
;
; Selected TOC Entries:
;
3; 2615 2200 SCHEMA - public pasha
1861; 0 0 COMMENT - SCHEMA public pasha
1862; 0 0 ACL - public pasha
317; 1247 17715 TYPE public composite pasha
319; 1247 25899 DOMAIN public domain0 pasha

The semicolon marks the beginning of a comment, and the number at the beginning of the line indicates the internal archive ID assigned to each entry.

You can annotate, delete, and resort lines in a file. Here is an example:

10; 145433 TABLE map_resolutions postgres
;2; 145344 TABLE species postgres
;4; 145359 TABLE nt_header postgres
6; 145402 TABLE species_records postgres
;8; 145416 TABLE ss_old postgres

It can be used as input to pg_restore and only entries 10 and 6 are restored in order.

$ pg_restore -L db.list db.dump

Options

-c
–core-file
The server releases the soft resource limit stored in the core file to allow creation of the core files. This is useful for debugging or diagnosing problems by allowing the failed server process to obtain stack traces.

-D datadir
–pgdata datadir
Specifies the file system locations of the database configuration files. If omitted, the environment variable AGDATA will be used.

-l filename
–log filename
Adds the server log output to filename. If the file does not exist, a new file with the filename is created. As umask is set to 077, other users are now allowed to access the log file by default.

-m mode
–mode mode
Specifies the shutdown mode. mode can be the first letter of one of smart, fast, and immediate. If not set, fast will be used.

-o options
Specifies the options to pass to the agens command. Multiple option calls are added.

The options are usually enclosed in single or double quotes so they can be passed in groups.

-o initdb-options
Specifies the options to pass directly to the initdb command.

The options are usually enclosed in single or double quotes so they can be passed in group.

-p path
Specifies the location of the agens executable. By default, the agens executable runs in the same directory as ag_ctl or fails in the hard-wired installation directory. You do not need to use this option unless you use it in general or there is an error that the agens executable cannot be found.

In init mode, use this option to specify the location of the initdb executable.

-s
–silent
Only error messages can be printed.

-t
–timeout
The maximum time (seconds) to wait before startup or shutdown. The default value is the value of the PGCTLTIMEOUT environment variable, or 60 seconds (if not set).

-V
–version
Prints the ag_ctl version and exits.

–revision
Shows the agens revision information and exits.

-w
Waits until startup or shutdown completes. Standby mode is the default option for shutdown, but not for a startup. When waiting for the startup, ag_ctl repeatedly tries to connect to the server. When waiting for shutdown, it waits for the server to remove the PID file. This option allows you to enter an SSL password on startup. ag_ctl returns an exit code depending on the success of the startup or shutdown.

-W
Does not wait for startup or shutdown to complete. This is the default for start and restart modes.

-?
–help
Displays help for ag_ctl command-line arguments and exits.

Options for Windows

-N servicename
The name of the system service to register. The name is used as a service name and a displayed name.

-P password
Password that enables service startup by the user.

-S start-type
Startup type of system service to be registered. The startup type can be auto, demand, or the first character of either. If it is omitted, an auto will be used.

-U username
The name of the user to start the service. For domain users, use the DOMAIN\username format.

Environment

AGDATA Default AGDATA data directory location.

Examples

$ ag_ctl start

$ ag_ctl -w start

$ ag_ctl -o "-F -p 5432" start

$ ag_ctl stop

$ ag_ctl stop -m fast

$ ag_ctl restart

$ ag_ctl -w restart

$ ag_ctl -o "-F -p 5432" restart

$ ag_ctl status
ag_ctl: server is running (PID: 2823)
/path/to/AgensGraph/bin/postgres

Options

pg_upgrade accepts the following command-line arguments:

-b bindir
–old-bindir=bindir
Previous AgensGraph bin directory (environment variable: PGBINOLD)

-B bindir
–new-bindir=bindir
New AgensGraph bin directory (environment variable: PGBINNEW)

-c
–check
Checks the cluster without changing the data.

-d datadir
–old-datadir=datadir
Previous cluster data directory (environment variable: PGDATAOLD)

-D datadir
–new-datadir=datadir
New cluster data directory (environment variable: PGDATANEW)

-j
–jobs
The number of processes or threads that can be used concurrently

-k
–link
Uses hard links instead of copying files to the new cluster.

-o options
–old-options options
Multiple option calls are added as an option passed directly to the previous agens command.

-O options
–new-options options
Multiple option calls are added as an option passed directly to the new agens command.

-p port
–old-port=port
Old cluster port number (environment variable: PGPORTOLD)

-P port
–new-port=port
New cluster port number (environment variable: PGPORTNEW)

-r
–retain
Maintains SQL and log files even after successful completion.

-U username
–username=username
Installation user name for the cluster (environment variable: PGUSER)

-v
–verbose
Enables verbose internal logging

-V
–version
Displays version information and exits.

-?
–help
Displays help and exits.

Usage

Here are the steps to perform an upgrade using pg_upgrade:

1. Back up your existing database cluster.
   

$ mv /user/local/AgensGraph /usr/local/AgensGraph.old

2. Install a new AgensGraph binary.

3. Initialize the new AgensGraph cluster.
   

$ initdb

4. Terminate the services of the existing cluster and the new cluster.

$ ag_ctl -D /opt/AgensGraph/2.12/db_cluster stop
$ ag_ctl -D /opt/AgensGraph/2.13/db_cluster stop

5. Run pg_upgrade.

$ pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin -B newCluster/bin

or

$ export PGDATAOLD=oldCluster/data
$ export PGDATANEW=newCluster/data
$ export PGBINOLD=oldCluster/bin
$ export PGBINNEW=newCluster/bin
$ pg_upgrade

6. Start a new AgensGraph.

$ ag_ctl start

7. After upgrading, perform the following tasks.

   • Statistics
     As optimizer statistics are not sent by pg_upgrade, you should run the command at the end of the upgrade to regenerate that information. You may need to set connection parameters to match the new cluster.

   • Deleting old clusters If you are satisfied with the upgrade, you can delete the old cluster’s data directory by running the script mentioned upon completion of pg_upgrade.