Knowledge articles
Configuration

The max_connections and max_prepared_transactions settings specified in postgresql.conf affect shared memory usage, which is allocated when the database server starts. The acquired shared memory is used for database connection and prepared transactions, etc.

Therefore, memory is consumed even when database access is not performed.

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, Fujitsu Enterprise Postgres for Kubernetes, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

User changes to be saved in the database cluster require restarting the cluster or reloading the configuration files to take effect.

• To reflect changes in pg_hba.conf, run pg_ctl reload from the command line or execute SELECT pg_reload_conf(); as the superuser.
• To reflect changes in recovery.conf, run pg_ctl restart from then command line on the standby server.
  Note that recovery.conf has been deprecated in version 12. Changes related to recovery on the standby database cluster instance must be specified in postgresql.conf, and might require restarting the database cluster instance.

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

Yes, it is possible to change, but special care needs to be taken care, because it involves database cluster to be stopped and restarted, and it will cause outage to users.

To change the data storage location, follow the steps below:

• Keep your new data storage location ready (e.g., /var/lib/postgres12_backup) with the same permissions as the existing data storage location (/var/lib/postgres12).
• Shut down your current database cluster instance.
  Run pg_ctl stop or use your Fujitsu Enterprise Postgres service.
• Copy files from the current data storage location to new one using rsync -av (-a preserves file and folder permissions at the new location, and –v displays verbose output):
  rsync -av /var/lib/postgres12/* /var/lib/postgres12_backup/
  If rsync is not available on your system, then use the normal copy command.
• To reduce size of the data storage location, consider deleting old unwanted logs.
• Rename the old data storage directory from /var/lib/postgres12 to /var/lib/postgres12_old.
• (optional) Rename the new data folder from /var/lib/postgres12_backup to /var/lib/postgres12 to match the original name.
• Update the data_directory parameter in postgresql.conf if it is still set to the previous data storage location.
• Start the database cluster instance and validate the data.
  pg_ctl start -D /var/lib/postgres12

To change backup data storage location:

• Change the backup_destination parameter in postgresql.conf.
  This parameter specifies the absolute path of the directory where pgx_dmpall will store backup data. It can only be set when specified on starting an instance - it cannot be changed dynamically, while an instance is active.

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

The fatal error message displayed in one of the following:

• FATAL: sorry, too many clients already
• FATAL: remaining connection slots are reserved for non-replication superuser connections
• FATAL: too many connections for database "xxxx"

If the message displayed is FATAL: sorry, too many clients already:

This happened because the number of connections to the database server has exceeded the max_connections parameter in postgresql.conf.

Otherwise:

This happened because the number of connections to the database server has exceeded the value in the formula below - after this value is exceeded, only superusers can connect:

max_connections - superuser_reserved_connections

Both parameters are specified in postgresql.conf. max_connections specifies the maximum number of concurrent connections to the database server (the defaults to 1000), and superuser_reserved_connections specifies the number of superuser connections reserved for database maintenance (the default is 3).

Resolution

Increase the maximum number of concurrent connections specified in max_connections.

Use the formula below to determine the maximum number of simultaneous connections.

max_connections = maximum number of concurrent connections to the instance
　　　　　　　   + superuser_reserved_connections
　　　　　　     + max_wal_senders

max_wal_senders is set in postgresql.conf, and specifies the maximum number of concurrent WAL submission processes to the standby server (the default is 10).

When setting the parameters, keep in mind that increasing the maximum number of simultaneous connections may increase memory usage and affect performance.

Note that formula for the maximum number of simultaneous connections is different when performing database multiplexing operations.

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

In principle, this does not affect Fujitsu Enterprise Postgres functionalities as long as you are not changing the server IP or the user who manages Fujitsu Enterprise Postgres.

However, if your IP changes because of attaching your server to a domain and the old IP was in use (e.g., pg_hba.conf, streaming replication, logical replication etc.), then make sure to update the IP accordingly.

Also, if you change the user who manages (starts or stops) Fujitsu Enterprise Postgres on the local machine to a domain user, then you must adapt this domain user to be the new owner of the database cluster, and hence you need to update the permissions of domain user so that it can manage the Fujitsu Enterprise Postgres database cluster files and directory.

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

The WAL segment size can be changed when creating the instance using initdb. The default size of each WAL segment size is 16MB.

initdb provides the option --wal-segsize to specify the size of WAL segment files when creating the instance.

You cannot change the WAL segment size after initializing the database cluster.

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, Fujitsu Enterprise Postgres for Kubernetes, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

Not all parameters are immediately reflected to database clusters — some require executing pg_ctl command with either reload or restart.
Execute the following command and check the value of “context” column in pg_settings system view.

psql -c "SELECT name, context FROM pg_settings;"

The value of “context” indicates:

• For “postmaster”, restart is required.
• For “sighup”, “superuser-backend”, or “backend”, reload is required.
• For “superuser” or “user”, changes can be applied without reload or restart. These can also be set within a session via SET command.

Applicable to

Product: Fujitsu Enterprise Postgres AE, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

You can change the system configuration parameters across the entire database cluster by using following command.

ALTER SYSTEM SET parameterName = parameterValue

This particular command writes the given parameter value inside the postgresql.auto.conf file. The value set with this command will be effective after the next server configuration reload or server restart.

Applicable to

Product: Fujitsu Enterprise Postgres AE, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux