Set the following PostgreSQL parameters to be able to extract the userlog:
These must be configured on all instances.
Set logical or higher. However, it is not necessary to create a logical replication slot.
Set always. This is to extract userlogs even on the standby.
Set up appropriate archiving commands according to the PostgreSQL documentation. However, the following two conditions must be met at this time.
Run pg_ctl stop -m immediate if archiving (e.g., cp) fails.
The destination file name must be "% f".
Add userlog. This allows the extraction daemon process to run. When adding userlog, the control file for userlog extraction must be initialized using the pgx_userlog_control command before the next instance restart. This procedure is described in the next step.
The extraction daemon makes an internal connection to PostgreSQL using the Server Programming Interface (SPI) to obtain various internal aspects of PostgreSQL. This consumes one entry managed by max_connections. Add 1 to the estimate for this parameter.
Set the following parameters for userlog extraction:
Of these parameters, only userlog.standby_names can be affected by reload. All others require an instance restart.
The type of each parameter is shown in parentheses. The semantics of this type are the same as for PostgreSQL.
Specify the output directory of the extracted file.
Specify a directory different from userlog.archive_directory. If you specify the same directory, an error occurs.
Specify the archive file output directory.
Specify the name of an instance that can be directly downstream of replication from an instance.
The name of the instance is the name specified in application_name in the replication connection string specified in primary_conninfo for each instance. All instances must have different names.
This parameter is referenced when the instance acts as the upstream side of replication. The upstream instance will extract up to the oldest write-synchronized log position reported by all downstream standbys. The effect of this is described in "1.4 Relationship to Replication".
If the replication connection to the downstream standby is broken, the old log location is remembered until it is reconnected and the new log location is advertised. That is, extraction will not proceed until the downstream standby is restarted and reconnected for replication, or until the standby is removed from userlog.standby_names and reloaded. This is useful when you want to temporarily stop the standby.
It excludes itself and any downstream standby that has never had a replication connection to the upstream side. So, although the parameter name says standby, you can also list yourself, all instances that might connect in the future, or names that are completely unrelated.
Note
PostgreSQL allows both physical and logical replication connections from user applications. The application_name used by these applications must not be the same as the application_name of the replication connection between the servers. Also note the environment variables APPNAME and fallback_application_name. PostgreSQL will allow connections with the same name, but the extraction daemon cannot distinguish between them.
If an erroneous connection with the same name is made, wait until WAL writes are completed on all these replication destinations to avoid missing userlogs. However, be aware that this may be unexpected behavior due to the overall design of the system.
The extraction daemon establishes an SQL connection via the Server Program Interface (SPI) to find out where WAL was written in the standby. Because you are connecting as a superuser on the instance, no credentials are required, but you must specify an existing database as the destination database. The default is "postgres". This database is created by default when an instance is created, but if you intentionally drop it, specify the name of another existing database in this parameter.
There are other parameters that control userlog extraction. However, these settings are not required.
This parameter can limit the total size of the userlog that can be emitted by a single transaction.
If you attempt to insert a userlog and the total size, including the userlog, exceeds the value specified for this parameter, you should explicitly execute a ROLLBACK statement to end the transaction, since the insertion function returns SQLSTATE: 54000 and raises an error, causing the transaction to ABORT internally and prevent further SQL execution. 54000 is the SQLSTATE we are defining as program_limit_exceeded. Specifying 0 for this parameter does not limit the total size. The default is 0. The unit (kB, MB, etc.) can be appended like any other PostgreSQL parameter.
Note
For example, if you supply text data as an argument to pgx_emit_userlog(), it is limited by the length after conversion to the database character encoding.
The interval at which the extraction daemon monitors the archive directory. Detects that this monitoring has generated the following archive files and creates the following extracted files: The unit is seconds. The default is 1. The retry interval for the extracted file by userlog.write_retry_num also uses the value of this parameter.
The number of times the extraction daemon can fail to create an extract file. The extraction daemon shuts itself down if it fails more than the specified number of times. A special value of -1 allows unlimited. A value of 0 simply means that no single failure is allowed. The default is 0.
