Fujitsu Logo
ENQUIRE

    Knowledge articles - Configuration

    Do the settings max_connections and max_prepared_transactions consume memory even when there is no database access? Or do they cause memory to be consumed only when database connection is performed or when using prepared transactions?KB2001

    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    How do I reflect changes to pg_hba.conf and recovery.conf?KB2002

    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    Is it possible to change the data storage location or backup data storage location for an instance already created?KB2003

    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    I cannot connect to the database server and a fatal error message regarding excessive number of existing connections is displayed.KB2004

    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    If I install FUJITSU Enterprise Postgres on a server that does not join in a domain, and then I join the server to a domain, will FUJITSU Enterprise Postgres functionalities be affected?KB2005

    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    How do I change the WAL segment size?KB2006

    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    Read our latest blogs

    Read our most recent articles regarding all aspects of PostgreSQL and FUJITSU Enterprise Postgres.