<img height="1" width="1" style="display:none;" alt="" src="https://px.ads.linkedin.com/collect/?pid=2826169&amp;fmt=gif">
Start  trial

    Start trial

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      I changed a parameter in postgresql.conf, but it did not take effect as I expected. How to reflect the changes?KB2007

      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

      Can I change the configuration parameter value without editing postgresql.conf file?KB2008

      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

      Read our latest blogs

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