Fujitsu Enterprise Postgres is a reliable and robust relational database based on (and fully compatible with) the world-renowned open source database system PostgreSQL.

It contains a range of enhanced features for the enterprise environment including Mirroring Controller, Global Meta Cache, Data Masking.

Click here for more information

PostgreSQL is the world’s most advanced open source relational database system, and the 4^th most used database globally. Its proven architecture has earned it a strong reputation for reliability, stability, data integrity, performance, extensibility, and standards-compliance.

Click here for more information

Fujitsu Enterprise Postgres provides all features of PostgreSQL, and extends them with the following for an enterprise solution with better performance, greater security, and enhanced reliability:

Feature	PostgreSQL	Fujitsu Enterprise Postgres
Generative AI for enterprise enables secure storage and protection of vector data for AI applications.
RAG application development simplifies LLM application development through LangChain integration.
In-database inference runs AI models directly inside the database while keeping data secure.
MCP server integration simplifies connectivity between AI applications and business data.
Transparent Data Encryption protects sensitive data at rest with built-in encryption.
FIPS compliance provides FIPS 140-2 compliant cryptography for trusted data protection.
Mirroring Controller delivers automated failover for high availability and business continuity.
Connection Manager maintains database availability through intelligent connection management.
Global Meta Cache accelerates query performance by reducing metadata access overhead.

Click here for a full comparison of PostgreSQL and Fujitsu Enterprise Postgres

• Warranted community source code: Fujitsu warrants and provides support on community source code.

• Reduced TCO: Licenses are available on a subscription model, with a flat rate based on the total number of cores. Flexible support plans are also available.

• No vendor lock-in: Unlike proprietary database providers, Fujitsu does not attempt to lock its customers into restrictive contracts. Fujitsu customers can easily opt out and convert to an open source system because our database is based on PostgreSQL. There are no penalties or restrictive obligations.

Click here for a full comparison of PostgreSQL and Fujitsu Enterprise Postgres

Start by viewing our key features or contact our experts.

Yes, a trial of the full version (no feature differences) is available free of charge for 90 days.

Download the trial version of Fujitsu Enterprise Postgres

• Transparent Data Encryption protects using Advanced Encryption Standard (AES), 256-bit transparent data encryption, PCI DSS-compliant. It provides data encryption, log encryption, and internal communication channel encryption.
• Data Masking methods include character shuffling, nulling or deletion, encryption, masking, and word substitution, to protect sensitive data such as credit card numbers, bank account details, and other sensitive personal information.
• Dedicated Audit Log extends PostgreSQL audit logging to improve accountability, traceability, and compliance. PCI DSS compliant with faster audit log analysis.
• FIPS compliance secures data in transit and at rest with FIPS 140-2 compliant encryption .
• Confidentiality management smplifies Role-Based Access Control (RBAC) configuration and auditing for efficient, error-free security management
• Policy-based login security enforces password policies and account lockouts to prevent unauthorized access.
• Cloud-based key management integrates with cloud key management services for simplified, secure encryption key management.

Click here for more information

• Generative AI for enterprise secures vector data for enterprise AI applications.
• Knowledge Data Management enables semantic, vector, and graph-based knowledge search.
• RAG application development simplifies LLM application development through LangChain integration.
• Hybrid search combines full-text and vector search for more accurate results.
• In-database inference runs AI models directly within the database.
• MCP server integration connects AI applications with business data through MCP.

Click here for more information

Yes. It enables continuous job processing with minimum downtime with features such a Mirroring Controller, Connection Manager, and Multi-Master Replication.

Click here for more information

Yes. We offer a range of PostgreSQL consulting services, including upgrade planning, migration advisory, security and compliance readiness, cloud adoption, monitoring solutions, AI integration, and proof-of-concept guidance to help you maximize the value of PostgreSQL.

Click here for more information

Yes. We provide professional services including implementation, migration, performance tuning, architecture reviews, health checks, training, and expert consulting.

Click here for more information

Our team of experts can help you deliver a seamless migration from most popular database platforms to PostgreSQL or Fujitsu Enterprise Postgres. We perform a Database Migration Assessment, which provides a cost estimate and roadmap to ensure a smooth deployment process.

Click for more information regarding the migration assessment service

For the full list of supported environments, check our Fujitsu Enterprise Postgres datasheet.

Yes, they are — we support the same operating systems in both virtual and physical environments.

For the full list of supported environments, check our Fujitsu Enterprise Postgres datasheet.

Fujitsu Enterprise Postgres is used across all industries, such as finance, governments, healthcare, and logistics.

Contact our experts for more information.

Fujitsu Enterprise Postgres can be installed on an unlimited number of machines.

No, but you can install the client feature on either a 32-bit or 64-bit version of Windows.

The annual subscription license includes 24/7 support, and is licensed per CPU core (physical or virtual).

For example, if a computer has 2 CPUs, each with 4 cores, then 8 licenses are required.

Number of licenses = total number of cores

Software installed and used on non-production environments purely for development, test, or user acceptance purposes costs 25% of the total number of cores on each computer, rounded up to the nearest core.

Number of licenses = total number of cores x 0.25 (rounded up after the decimal point)

You will need to purchase licenses below.

For private cloud environment, purchase whichever number is lower:

• Maximum number of virtual cores on the virtual machine that is available to Fujitsu Enterprise Postgres
• Maximum number of physical cores on the server that is available to Fujitsu Enterprise Postgres

For public cloud environment,

• Maximum number of virtual cores on the virtual machine that is available to Fujitsu Enterprise Postgres (regardless of the number of physical cores or threads)

We provide 24x7x365 support.

For more information, check our Support page, or contact our experts.

The period is usually 7 years. However, this may be extended on a case-by-case basis.

For more information, check our Support page, or the Support Guidebook.

Yes. Fujitsu offers both Instructor-led Training and On-Demand Training for Fujitsu Enterprise Postgres.

Instructor-led Training courses provide expert guidance with hands-on learning, while On-Demand Training delivers self-paced online courses that can be accessed anytime, anywhere to help DBAs, architects, and developers build practical Fujitsu Enterprise Postgres skills.

Click here for more information

Fujitsu Enterprise Postgres supports SQL embedded in COBOL and C programs.

Click here for more information

Both table and index files have a fixed block size of 8 KB.

Platform: Windows, Linux  |  Version: All versions

Fujitsu Enterprise Postgres provides client drivers used for enabling client to connect to database. They are based on versions of open-source software available at the time of release. Please note that the version will be in the state of purchase and will be updated to the new version by applying subsequent patches.

For Fujitsu Enterprise Postgres 18 SP2, we provide .NET Data Provider (Npgsql 10.0.2, 8.0.9) , JDBC (PostgreSQL JDBC driver 42.7.10), and ODBC (psqlODBC 18.00.0001).

For details, refer to Fujitsu Enterprise Postgres General Description > Appendix B - OSS supported by Fujitsu Enterprise Postgres

Platform: Windows, Linux  |  Version: All versions

Execute the shell script FEP_CheckLicense.sh which is located in the server installed directory (the default directory path is /opt/fsepvversionserver64/LIC/.

For full versions of Fujitsu Enterprise Postgres, the script displays Expiration (Indefinite), and for trial versions, it displays Expiration (XX days). The trial period is 90 days from the date of installation. XX days shows the number of days left until the expiration date.

Platform: Linux  |  Version: All versions

This error occurs when the core files cannot be found in/var/tmp(the default location for Fujitsu Enterprise Postgres core files set up by WebAdmin). This can happen because, by default, Linux deletes from/var/tmp any content that has not been accessed for 30 days or more.

The directory to output core files is specified by thecore_directory parameter in postgresql.conf. When you create an instance in WebAdmin, /var/tmp/xxxxx/yyyyy/core is set by default. To solve this issue, do one of the following:

• Specify a different directory to output core files, then restart Fujitsu Enterprise Postgres.
• Modify the Linux default behavior so that contents of /var/tmp are not automatically deleted after 30 days. This can be done by either removing the tmpwatch package (which is responsible for deleting content from /tmp and /var/tmp) or by disabling the cron entry.
  Please note that in some cases, you may not be able to remove the tmpwatch package, because of its dependencies, so you may want to disable the cron entry instead.

For details on creating or changing the core file directory, refer to the Fujitsu Enterprise Postgres Installation and Setup Guide for Server > Chapter 4 - Setup > 4.2 - Preparation for Setup > 4.2.2 - Preparing resource allocation directories.

Platform: Linux  |  Version: All versions

The port specified by the port parameter in postgresql.conf is already in use. Verify whether another database server instance or application is using the same port. If so, configure one of them to use a different port and then restart Fujitsu Enterprise Postgres.

Platform: Linux  |  Version: All versions

Assuming that the OS is RHEL7, instance port number is 27500, and the client IP address is 192.168.93.10, follow the steps below:

1. In postgresql.conf, add the entries listen_addresses = * (* means listen on all adapters for a connection), and port = 27500.
2. Register the port that you want to connect to with firewall-cmd-zone = public-add-port = 27500/tcp-permanent, then reload the port with firewall-cmd-reload.
3. Create an entry in pg_hba.conf (host-based configuration file) that matches the client machine that you want to connect from, for example: host all all 192.168.93.0/24 md5 (in this example, all machines on the 192.168.93.x subnet will match and use the md5 authentication method).
4. Restart PostgreSQL for the setting to take effect with $pg_ctl restart -D instance_destination_directory.
5. If the user's password is entered correctly, other hosts can connect.

For details, refer to Fujitsu Enterprise Postgres Installation and Setup Guide for Server > Chapter 4 - Setup > 4.4 - Configuring remote connections > 4.4.2 - When an instance was created with the initdb command.

Platform: Linux  |  Version: All versions

The pg_stat_statements library is pre-installed (along with other modules) in the contrib folder of the installation folder, so no additional libraries are required to be installed.

Follow the steps below to configure Fujitsu Enterprise Postgres to use pg_stat_statements:

1. Open postgresql.conf, and edit the shared_preload_libraries setting so that pg_stat_statements is the first entry in the list (if other libraries are specified, separate them with commas)

   shared_preload_libraries = 'pg_stat_statements'

2. Restart the instance.

   pg_ctl restart -D instance_destination_directory

3. Connect to the database and execute the CREATE EXTENSION command, specifying pg_stat_statements as the parameter - this will capture metrics for that database which can be viewed by querying the pg_stat_statements view.

   psql -d postgres -c "CREATE EXTENSION pg_stat_statements"

Platform: Linux  |  Version: All versions

This error message is displayed when the Postgres server has not been stopped properly, and fails to clean up the lock file postmaster.pid. This lock file is created when Postgres is started to prevent double booting; normally the file is removed when the database is shut down.

To fix this issue, simply delete the postmaster.pid file, and then start the database agian.

The postmaster.pid file is located in the data directory. Its location is determied by the -D option of the pg_ctl command, or by the PG_DATA environment variable if the command/option was not specified.

Platform: Linux  |  Version: All versions

This error occurs when the port specified in the port setting of postgresql.conf could not be used by the database, which happened because it is already in use either by a running instance of the database or by some other software.

To solve this issue, do one of the following:

• Change the port number to be used by the instance by modifying the port setting of postgresql.conf - make sure that the setting is not commented.
• Identify the software that is using the port and check if it is running on the correct port - if it is not, then take the appropriate action. Use netstat and grep to check the software using the port, for example: netstat -ltnp | grep -w ':27500'.
  You may need to install the net-tools package if it is not already installed.

Platform: Linux  |  Version: All versions

Often, this error message is displayed when the correct path settings cannot be acquired using the PATH and LD_LIBRARY_PATH environment variables.

This may happen either because the environment variables are not set to the Fujitsu Enterprise Postgres installation directory, or because although they are set to the Fujitsu Enterprise Postgres installation directory, the installation directory for the open-source PostgreSQL is listed first.

For details, refer to Fujitsu Enterprise Postgres Installation and Setup Guide for Server > 4.3 - Creating instances > 4.3.2 - Using the initdb command > 4.3.2.1 - Creating an instance

Platform: Windows, Linux  |  Version: All versions

The likely cause of this scenario is that the database is recovering from a crash or non-standard shutdown (such as immediate shutdown mode). You can verify whether it is in crash recovery mode by checking if the log file contains the message database system was not properly shut down; automatic recovery in progress.

If a crash recovery is in progress, you will need to wait until it is complete, at which point the database will start up normally.

You can reduce the time taken for crash recovery by increasing the frequency of checkpoints (reduce the values of max_wal_size and checkpoint_timeout in postgresql.conf to achieve that). But keep in mind that the higher the frequency of checkpoints, the higher the I/O load, so set an appropriate value for operational considerations.

For details on stopping Fujitsu Enterprise Postgres, refer to PostgreSQL documentation > Part III - Server administration > 18 - Server setup and operation > 18.5 - Shutting down the server. For details on max_wal_size and checkpoint_timeout, refer to PostgreSQL documentation > Part III - Server administration > 19 - Server configuration > 19.5 - Write ahead log > 19.5.2 - Checkpoints

Platform: Windows, Linux  |  Version: All versions

Platform: Windows  |  Version: All versions

Follow the steps below to set up and start the database server using server commands, follow the instructions below.

Step 1: Prerequisites

1. Create an instance administrator.
2. Create the data storage destination (required) and the transaction log storage destination (optional)
   

For details, refer to Fujitsu Enterprise Postgres Installation and Setup Guide for Server > Chapter 4 - Setup > 4.2 - Preparations for setup

Step 2: Create an instance using initdb

1. Open the command prompt as the instance administrator created earlier.
2. Update the PATH environment variable - add the bin and lib directories located under the server installation directory.

   SET PATH=C:\Program Files\Fujitsu\fsepv13server64\bin;C:\Program Files\Fujitsu\fsepvl3server64\lib;%PATH%

3. Create a database cluster using initdb and specifying the data storage destination.

   initdb -D C:\work\database\instl --waldir.C:\work\transaction\ins it --Ic-collate--C- --1c-ctype--C- --encodingUTF8

4. If successful, the message Success. You can now start the database server using: is displayed.

For details, refer to Fujitsu Enterprise Postgres Installation and Setup Guide for Server > Chapter 4 - Setup > 4.3 - Creating instances > 4.3.2 - Using the initdb command > 4.3.2.1 - Creating an instance

Step 3: Start the database server

1. Register an instance in the Windows service.

   pg_ctl register -N "inst1" -U fepuser -P password -D C:\work\database\inst1

2. Start an instance either from Administrative Tools or using the pg_ctl start command.

   > pg_ctl start -D C:\work\database\instl

For details, refer to Fujitsu Enterprise Postgres Installation and Setup Guide for Server > Chapter 4 - Setup > 4.3 - Creating instances > 4.3.2 - Using the initdb command > 4.3.2.1 - Creating an instance

Step 4: Connect to the database using psql.

> psql -d postgres

Platform: Windows  |  Version: All versions

Refer to Setting up Grafana on IBM LinuxONE™.

Platform: Linux  |  Version: All versions

Refer to Installing the Prometheus adapter.

Platform: Linux  |  Version: All versions

Refer to Installiing PostGIS.

Platform: Linux  |  Version: All versions

Table and index files in Fujitsu Enterprise Postgres use a fixed block size of 8 KB. This value is not configurable.

Platform: Windows, Solaris, Linux  |  Version: All versions

Fujitsu Enterprise Postgres bundles the standard PostgreSQL client drivers: JDBC, ODBC, the .NET Data Provider (Npgsql), libpq (C), and ECPG. The exact version of each driver depends on your Fujitsu Enterprise Postgres version and the maintenance level.

For the driver versions shipped with a specific release, refer to the Installation and Setup Guide for Server or the Release Notes. Please note that the driver versions listed in the manuals reflect the initial product release and may be updated by later patches and service packs.

Platform: Windows, Solaris, Linux  |  Version: All versions

In Fujitsu Enterprise Postgres, it is recommended to take base backups at least once a day. Failure to take regular base backups can lead to increased disk space consumption and longer recovery times.

Increased disk space consumption may occur because all WAL files generated since the most recent base backup must be retained to ensure recoverability. As the interval between base backups increases, so does the volume of retained WAL data. Recovery times may also increase because recovery requires restoring the most recent base backup and replaying all subsequent WAL files. If a long period has elapsed since the last base backup, a larger volume of WAL data must be replayed, which can increase recovery time.

Background: PostgreSQL recovers a database by restoring a base backup and then applying WAL files up to the target recovery point. Consequently, all WAL files generated since the most recent base backup must be retained and replayed during recovery. Regular base backups help reduce both WAL retention requirements and recovery time. For details on base backup operations, refer to the Fujitsu Enterprise Postgres Operation Guide > Chapter 3 - Database Backup.

Platform: Windows, Linux  |  Version: All versions

The most likely cause is the accumulation of a large number of archive logs in the archived_wal directory because database backups have not been performed for an extended period.

To resolve this issue, perform regular database backups using WebAdmin or the pgx_dumpall command. Regular backups allow obsolete archive logs to be deleted, preventing excessive growth of the archived_wal directory. Although the appropriate backup frequency depends on business and operational requirements, we recommend performing backups at least once per day. For details, refer to the Fujitsu Enterprise Postgres Operation Guide > Chapter 3 - Backing up the database and Fujitsu Enterprise Postgres Reference Guide > Chapter 3 - Server Commands > 3.2 - pgx_dmpall.

Platform: Windows, Linux  |  Version: All versions

This issue may occur because the Oracle data contains null characters (0x00), which are not supported by Fujitsu Enterprise Postgres.

To solve the issue, either replace the null characters (0x00) in Oracle with other valid data (for example, half-width spaces), or create an Oracle view that replaces null characters with valid data using a function such as TRANSLATE and then access the view from Fujitsu Enterprise Postgres through oracle_fdw instead of accessing the table directly.

Platform: Windows Linux  |  Version: 10 and later

The cause may be incorrect configuration. Add oracle to search_path in postgresql.conf or qualify the substr function with oracle (i.e., oracle.substr).

For details, refer to the Fujitsu Enterprise Postgres Application Development Guide > Chapter 9 - Oracle Database Compatibility > 9.2 -Notes on using Oracle database compatibility features > 9.2.1 - Notes on SUBSTR.

Platform: Windows, Linux  |  Version: All versions

Check if the schema containing the table in the FROM clause is specified in the schema search path. If it is not, then either add the target schema to the schema search path or qualify the table name in the FROM clause with the schema name (i.e.,schema name.table name). If it is specified, ensure that the table name is correct and that it actually exists.

For details on the schema search path, refer to the PostgreSQL Documentation > Part II - The SQL Language > Chapter 5 - Data Definition > 5.10 - Schemas > 5.10.3 - The Schema Search Path.

Platform: Windows, Linux  |  Version: All versions

The cause may be differences in execution plans. The execution plan is created by Fujitsu Enterprise Postgres based on the contents of the SELECT command and statistical information.

Statistical information is collected through the execution of ANALYZE and VACUUM ANALYZE commands, and automatic vacuuming. Differences in the statistical information at the time of SELECT command execution, due to the presence or absence of these commands or features, may have led to different execution plans being created, resulting in differences in processing time.

Use the EXPLAIN command to determine whether the execution plans are the same. If they differ, evaluate which execution plan is preferable and then make the execution plans consistent. This can be achieved by executing ANALYZE and fixing the statistical information with pg_dbms_stats, or by specifying the desired execution plan using pg_hint_plan.

For details, refer to PostgreSQL documentation > Part II - The SQL Language > Chapter 14 - Performance Tips.

For other product versions/levels, please refer to the corresponding manual sections.

Platform: Windows, Linux  |  Version: All versions

During the first data retrieval, table and index data (data on disk) is read from the storage medium into shared buffers (memory), and subsequent data retrievals use cached data in shared buffers (memory).

To mitigate this issue, consider executing queries against the tables used by the application or running the pg_prewarm function on those tables after restarting the database server and before starting normal operations, or using a database storage device with higher I/O performance, such as an SSD.

For details on pg_prewarm, refer to PostgreSQL documentation > Part VIII - Appendixes > Appendix F - Additional Supplied Modules >F.30 - pg_prewarm.

Platform: Windows, Linux  |  Version: All versions

The connection service file was not recognized in your environment. Check the settings of the connection service file pg_service.conf specified by the environment variable PGSERVICEFILE.

The connection service file referenced is either ~/.pg_service.conf for the current user or the file specified by the PGSERVICEFILE environment variable. Additionally, the system-wide pg_service.conf file is read from either the directory returned by pg_config --sysconfdir or the directory specified by the PGSYSCONFDIR environment variable.

If service definitions with the same name exist in both user-specific and system-wide files, the user-specific one takes precedence. When specifying a service with the psql command, specify it after psql command options such as the -q option, for example: psql -q -f zzz.sql -v startDate=%start_date% -v endDate=%end_date% service=teikyo.

For details on the connection service file, refer to PostgreSQL documentation > Part IV - Client Interfaces >Chapter 32 - libpq - C Library > 32.17 - The Connection Service File.

Platform: Windows, Linux  |  Version: All versions

Each failover requires Fujitsu Enterprise Postgres to perform crash recovery when the standby server is promoted. If failovers occur repeatedly, the amount of WAL (Write-Ahead Log) data that must be processed during recovery can increase significantly. As a result, the startup takes longer and may exceed the PRIMECLUSTER timeout period.

To prevent startup timeouts during failover, consider increasing the PRIMECLUSTER timeout value or reducing the max_wal_size setting.

The advantages of increasing the PRIMECLUSTER timeout value is that it allows Fujitsu Enterprise Postgres more time to complete crash recovery, and can prevent unnecessary failover failures caused by timeout detection. The disadvantages are that failure detection will take longer and service recovery may be delayed when an actual fault occurs.

The advantages of reducing the max_wal_size setting are reduction in the amount of WAL data processed during recovery and potential shorter failover and startup times. The disadvantages are more frequent checkpoints and potential impact of database performance during normal operation.

For details on failover switchover times, refer to the Fujitsu Enterprise Postgres Cluster Operation Guide > PRIMECLUSTER > Chapter 2 - Setting up Failover Operation > 2.8 - Registering resource information for Fujitsu Enterprise Postgres database cluster. For details on max_wal_size, refer to PostgreSQL documentation > Part II - The SQL Language > Chapter 14 - Performance Tips > 14.4 - Populating a Database > 14.4.6 - Increase max_wal_size.

Platform: Linux  |  Version: All versions

A single transaction accessed a large number of tables and acquired more locks than the shared lock table could handle. When the maximum number of locks is reached, Fujitsu Enterprise Postgres reports ERROR: out of shared memory.

Increase the value of the max_locks_per_transaction and restart the database server. Note that a server restart is required for the change to take effect and increasing this value allows more locks to be held within a transaction.

For details on max_locks_per_transaction, refer to PostgreSQL documentation > Part III - Server Administration > Chapter 19 - Server Configuration > 19.12 - Lock Management.

Platform: Windows, Linux  |  Version: All versions

[Cause]

The cause is that data with the same key as the data being re-created already exists in the replication target.

[Resolution]

If data with the same key already exists in the replication target, logical replication will result in a duplicate key error. Before re-creating the subscription, delete the target data on the replication target, then re-create the logical replication.

For details, refer to the manual: Fujitsu Enterprise Postgres 13 SP1 > PostgreSQL 13.3 Documentation > Part III. Server Administration > Chapter 30. Logical Replication > 30.3. Conflicts

For other product versions/levels, please refer to the corresponding manual sections.

Platform: Windows Linux  |  Version: All versions

When a database contains many objects, snapshot sizes can increase and consume significant repository database space. You can check snapshot sizes with pg_statsinfo -l command.

To prevent excessive growth of the repository database, regularly delete old snapshots using statsinfo.maintenance(timestampz) and configure automatic maintenance by applying the following settings in postgresql.conf.

pg_statsinfo.enable_maintenance = 'snapshot'
pg_statsinfo.maintenance_time = ''
pg_statsinfo.repository_keepday = ''

Platform: Windows, Linux  |  Version: All versions

The time required to establish the connection exceeded the value specified in Timeout of the connection string, or the time required to execute the SQL command exceeded the value specified in CommandTimeout of the connection string or the CommandTimeout property of the NpgsqlCommand object.

If the error occurred while establishing the connection, review the value specified for Timeout. If the error occurred while executing a SQL command, review the value specified for CommandTimeout. For details, refer to Fujitsu Enterprise Postgres Application Development Guide > Chapter 4 - .NET Data Provider > 4.3 - Connecting to the Database > 4.3.4 - Connection String.

If the values specified for Timeout and CommandTimeout are appropriate, the timeout may be caused by high system load delaying SQL execution, lock contention with other SQL commands, or network issues. Check operating system performance metrics, database activity, and network traces to determine whether any of these conditions are occurring.

For details, refer to Fujitsu Enterprise Postgres Operation Guide > Chapter 7 - Routine Operations > 7.6 - Monitoring Database Activity.

Platform: Windows, Linux  |  Version: All versions

The error may have occurred because the SQL command contains an identifier with uppercase letters that is not enclosed in double quotes. In Fujitsu Enterprise Postgres, unquoted identifiers are automatically converted to lowercase.

If you create a role with CREATE ROLE "Joe", then grant privileges with GRANT ALL ON myschema.products to Joe (i.e., with Joe not enclosed by double quotes), the role name Joe will be interpreted as joe, resulting in error.

To preserve uppercase letters in an identifier, enclose the identifier in double quotes: GRANT ALL ON myschema.products to "Joe".

For details, refer to PostgreSQL Documentation > Part II - The SQL Language > Chapter 4 - SQL Syntax > 4.1 - Lexical Structure > 4.1.1. -Identifiers and Key Words.

Platform: Windows, Linux  |  Version: All versions

The application may have a problem with its transaction handling logic. If an SQL command fails within a transaction block and the transaction is not rolled back, subsequent SQL commands may continue to be issued but will be ignored. After an error occurs, no further SQL commands in the transaction block are executed until the transaction is rolled back.

If an SQL command fails within a transaction block, ensure that the application rolls back the transaction before executing any further SQL commands. For details on transactions, refer to PostgreSQL documentation > Part I - Tutorial > Chapter 3 - Advanced Features > 3.4 - Transactions.

Platform: Windows, Linux  |  Version: All versions

The pg_wal/xxx.history file could not be obtained when attempting to start the standby server instance. If the file was manually deleted from the primary server (for example, before pg_basebackup was executed), this error will be output when the standby server instance starts.

Perform database recovery as follows: 1 Stop the primary server instance and the standby server instance, 2 Specify recovery_target_timeline = 'latest' in the primary server's postgresql.conf file, 3 Start the primary server instance and perform recovery, 4 Delete the standby server instance and re-create the standby server using pg_basebackup.

For details on recovery_target_timeline, refer to PostgreSQL documentation > Part III - Server Administration > 19 - Server Configuration > 19.5 - Write Ahead Log > 19.5.5 - Recovery Target. For details on the pg_basebackup command, refer to PostgreSQL documentation > VI - Reference > II - PostgreSQL Client Applications > pg_basebackup. For details on recovery.conf, refer to PostgreSQL documentation > VIII - Appendixes > O - Obsolete or Renamed Features > O.1 - recovery.conf file merged into postgresql.conf.

Platform: Windows, Linux  |  Version: All versions

The environment is configured for synchronous replication, but the script specified in Pgpool-II's failover_command does not switch the database to asynchronous replication during failover.

When detaching or promoting a database instance in the failover_command script, switch the new primary instance to asynchronous replication by removing or commenting out the synchronous_standby_names setting in postgresql.conf, and then executing pg_ctl reload. After failover, the new primary instance operates in asynchronous replication mode.

If a detached database instance is later re-attached during failback, re-enable synchronous replication settings as required.

For details on the parameters, refer to PostgreSQL documentation > III - Server Administration > 19 -Server Configuration > 19.6 - Replication > 19.6.2 - Primary Server > synchronous_standby_names.

Platform: Linux  |  Version: All versions

The COBOL source code encoding may not match the encoding specified when the ecobpg command is executed. When analyzing source code, ecobpg interprets multibyte characters according to the locale or encoding specified at execution time.

For example, if the source code is encoded in SJIS (Shift-JIS) but ecobpg is executed with a UTF-8 locale or the -E UTF8 option, SJIS multibyte characters may be interpreted as UTF-8. This can result in parsing errors or abnormal termination, such as a segmentation fault.

When executing ecobpg on source code that contains multibyte characters, ensure that the source code encoding matches the locale or the encoding specified with the -E option. For details on the ecobpg command, refer to Fujitsu Enterprise Postgres Application Development Guide > Appendix D - ECOBPG - Embedded SQL using COBOL language > D.12 - PostgreSQL client applications > D.12.1 - ecobpg.

Platform: Windows, Linux  |  Version: All versions

You can connect to a database via an application using the DriverManager, PGConnectionPoolDataSource, and PGXADataSource JDBC driver classes.

The following is an example of using the PGConnectionPoolDataSource class to connect to a database:

import java.sql. *;
import org.postgresql.ds.PGConnectionPoolDataSource;
...
PGConnectionPoolDataSource source = new PGConnectionPoolDataSource ();
source.setServerName ("sv1");
source.setPortNumber (27500);
source.setDatabaseName ("mydb");
source.setUser ("myuser");
source.setPassword ("myuser01");
source.setLoginTimeout (20);
source.setSocketTimeout (20);
...
Connection con = source.getConnection ();

Platform: Windows, Linux  |  Version: All versions

The query can be cancelled by using the pg_cancel_backend function, and connections can be forcibly broken using either the pg_terminate_backend function or pgAdmin.

In the pg_cancel_backend and pg_terminate_backend functions, the process ID specifies the query to cancel or connection to break. The process ID can be found in the pg_stat_activity view or with the OS ps command.

For details, refer to the Fujitsu Enterprise Postgres Operation Guide > Chapter 17 - Actions when an error occurs > 17.4 - Actions in response to an application error occurs andPostgreSQL documentation > Part II - The SQL language > Chapter 9 - Functions and operators > 9.27 - System administration functions > 9.28.2 - Server signaling functions.

Platform: Windows, Linux  |  Version: All versions

This may have happened because the application was trying to work on a Connection object that had already been closed. Review your application's handling of Connection objects as follows:

• Are you executing a method on a Connection object that has already been closed?
• Did you execute the getConnection method multiple times on the same instance of the PGPooledConnection or PGXAConnection class, and not on the Connection object returned by the last execution?
• When you execute the getConnection method on a PGPooledConnection or PGXAConnection class, the relationship between these classes and the getConnection method to be executed is one-to-one. If the getConnection method is executed more than once using an instance of the same class, the previously created Connection object is closed.

Platform: Windows, Linux  |  Version: All versions

Possible causes include ExecuteReader() running on a table with binary column data types or other large data structures, or the connection string to the database specifying PreloadReader = True. These cause the data for the entire result set to be held in memory, which would consume a large amount of memory and may have exhausted memory.

To solve the issue, consider changing from ExecuteReader() to ExecuteReader(CommandBehavior.SequentialAccess), or changing PreloadReader = True to PreloadReader = False.

For details on ExecuteReader(CommandBehavior.SequentialAccess), refer to SqlCommand.ExecuteReader Method in the.NET documentation portion in Microsoft Docs. For details on PreloadReader, refer to Fujitsu Enterprise Postgres Application Development Guide > Chapter 4 - .NET Data Provider > 4.3 - Connecting to the database > 4.3.3 - Connection string.

Platform: Windows, Linux  |  Version: All versions

It is possible that the application is not disconnecting from the database, leaving unnecessary connections and exceeding the maximum number of connection pools. If a connection is requested beyond the maximum number of connection pools, it will wait until an available connection is found. If no available connection is found and the connection timeout period is exceeded, the connection request will timeout.

Check the connection that has been waiting for a long time, and close any unnecessary connection. Also, ensure that the application is disconnecting from the database. If it already is, please review the maximum number of connection pools specified in the connection string - this is specified by the Maximum Pool Size keyword, and the default is 100.

For details, refer to Fujitsu Enterprise Postgres Operation Guide > Chapter 7 - Periodic operation > 7.4 - Monitoring the connection state of an application, Fujitsu Enterprise Postgres Operation Guide > Chapter 17 - Actions when an error occurs > 17.4 - Actions in response to an application error, and Fujitsu Enterprise Postgres Application Development Guide > Chapter 4 - .NET Data Provider > 4.3 - Connecting to the database > 4.3.3 - Connection string.

Platform: Windows, Linux  |  Version: All versions

This happened because PREPARE TRANSACTION was performed with the max_prepared_transactions parameter set to 0 in postgresql.conf. Set max_prepared_transactions to at least 1 in postgresql.conf.

For details, refer to PostgreSQL documentation > Part III - Server administration > Chapter 19 - Server configuration > 19.4 - Resource consumption > 19.4.1 - Memory.

Platform: Windows, Linux  |  Version: All versions

ODBC drivers cannot be installed separately, they are installed with the Fujitsu Enterprise Postgres client.

For details on the versions of the ODBC drive installed with the the Fujitsu Enterprise Postgres client, refer to Fujitsu Enterprise Postgres Installation and Setup Guide for Client > Chapter 2 - Installation and uninstallation of the Windows client > 2.1- Operating environment > 2.1.8 - Versions of open-source software used as the base for Fujitsu Enterprise Postgres drivers.

Platform: Windows, Linux  |  Version: All versions

The jit or jit_provider parameter in postgresql.conf is configured incorrectly, or the required LLVM package is not installed. To use JIT compilation, install the required LLVM package and configure jit_provider to use the appropriate LLVM version. If JIT compilation is not required, set jit to off.

For details, refer to PostgreSQL Documentation > Part III - Server Administration > Chapter 30 - Just-in-Time Compilation (JIT), and Fujitsu Enterprise Postgres Installation and Setup Guide for Server > Chapter 2 - Operating Environment > 2.1 - Required Operating System

Platform: Windows, Solaris, Linux  |  Version: All versions

No. Modules included in the PostgreSQL contrib directory, such as pg_stat_statements, are installed automatically with Fujitsu Enterprise Postgres, so no additional package installation is required.

To enable and use pg_stat_statements, add it to the shared_preload_libraries parameter in postgresql.conf (shared_preload_libraries = 'pg_stat_statements'), restart the instance (pg_ctl restart -D instance_directory), then connect to the database and create the extension (SELECT * FROM pg_stat_statements). Finally, verify that the pg_stat_statements view is accessible.

Platform: Windows, Solaris, Linux  |  Version: All versions

You can refer to the pg_stat_all_tables view to check when the latest VACUUM ran for a database object. Monitoring should be enabled to capture the details and check for how long the VACUUM process ran.

You can capture the details using any of the following methods:

• Enable logging and update both log_statement and log_duration parameters in postgresql.conf.
  For details, refer to PostgreSQL documentation > Part III - Server administration > Chapter 19 - Server Configuration > 19.8 - Error Reporting and Logging.
• Enable the pg_stat_statement extension.
  For details, refer to PostgreSQL documentation > Part VIII - Appendixes > Appendix F - Additional Supplied Modules > F.32 - pg_stat_statements.
• Use open source or commercial monitoring tools

You can also run VACUUM manually to see how much time the VACUUM process takes.

Platform: Windows, Linux  |  Version: All versions

In most installations, it is sufficient to run AUTOVACCUM. If you are experiencing fragmentation, you might have to adjust the autovacuuming parameters to obtain the best results. For details, refer to PostgreSQL documentation > Part III - Server administration > Chapter 24 - Routine database maintenance tasks > 24.1 - Routine vacuuming.

Because long-term operations can degrade database access performance, consider periodically running the REINDEX command to reorganize indexes. There is no formula available to estimate the execution time for REINDEX, so please refer to the actual measurement in your environment. For details, refer to Fujitsu Enterprise Postgres Operation Guide > Chapter 7 - Periodic Operations > 7.5 - Reorganizing Indexes.

Platform: Linux  |  Version: All versions

You should obtain database statistics. Information about server process, transactions, and locks can be very important to analyze performance issues or delay. However, these may be wiped out when the database is restarted or after a significant amount of time ellpases. Hence, such information should be collected as soon as possible.

Collect the information below to investigate the issue:

• Server process information - Statistics about client connections to server processes:

  SELECT * FROM pg_stat_activity;

• Transaction information - Information about duration of the transaction (query)

  SELECT pid, wait_event_type, wait_event, state, 
        (current_timestamp - xact_start): interval (3) AS duration, query 
  FROM pg_stat_activity WHERE pid <> pg_backend_pid ();

• Lock information - List of tables waiting for a lock

  SELECT l.locktype, c.relname, l.pid, l.mode, a.query,
        (current_timestamp - xact_start) AS duration
  FROM pg_locks l
       LEFT OUTER JOIN pg_stat_activity a
         ON l.pid = a.pid
       LEFT OUTER JOIN pg_class c
         ON l.relation = c.oid
  WHERE NOT l.granted ORDER BY l.pid;

For details, refer to Fujitsu Enterprise Postgres Operation Guide > Chapter 7 - Periodic Operations > 7.6 - Monitoring Database Activity.

Platform: Windows, Linux  |  Version: All versions

This may be happening because postgres_fdw is configured to retrieve a small number of rows in a single fetch, causing it to communicate with the remote server more often, and thus taking longer.

It is possible to reduce the amount of communication with the remote server by increasing the value of the fetch_size option of postgres_fdw.

For details, refer to PostgreSQL documentation > Part VIII - Appendixes > Appendix F - Additional supplied modules > F.38 - postgres_fdw > F.38.1 - FDW options of postgres_fdw > F.38.1.4 - Remote execution options

For an example on how to use postgres_fdw, refer to our PostgreSQL Insider article Linking to foreign data using foreign data wrappers.

Platform: Windows, Linux  |  Version: All versions

Database performance can degraged when it has a large amount of old version of updated and deleted data. PostgreSQL uses a write-once architecture for writing table data, to minimize lock contention. Therefore, when you execute UPDATE and DELETE statements, the table keeps the old version of the updated or deleted data.

To solve this issue, enable AUTOVACUUM to collect the old version of updated and deleted data, or run the VACUUM and ANALYZE commands manually at regular intervals. But keep in mind that the VACUUM command may result in heavy I/O traffic and exclusive table lock, which can degrade the performance of other running sessions. When running the VACUUM command, set the relevant parameters to reduce the performance impact.

For details, refer to PostgreSQL documentation > Part III - Server administration > Chapter 24 - Routine database maintenance tasks > 24.1 - Routine vacuuming

Platform: Windows, Linux  |  Version: All versions

Using the VACCUM command

Before changing any configuration parameters, check if there is a need to clean unused tuples (tables) from hard disk. If so, run VACUUM FULL tablename

Increasing the value of work_mem

You can also speed up index lookups by tuning the work_mem parameter, which sets the amount of memory for sorting data in memory and hash table operations. Increasing this parameter reduces disk swapping and speeds up index lookups. The parameter setting can also be changed on a per-session basis by editing postgresql.conf or by using the SET statement. However, be aware of large memory consumption because the sort and hash operations are performed at the same time, and the work_mem value is applied to each operation.

For details, refer to PostgreSQL documentation > Part III - Server administration > Chapter 19 - Server configuration > 19.4 - Resource consumption > 19.4.1 - Memory

Adjusting the OS readahead setting (for Linux systems)

You can also speed up extensive searches in Linux system by adjusting the amount of disk readahead in the OS, so that the OS will understand that PostgreSQL is doing a sequential read, and consequently load the page into the cache first, which speeds up index lookups.

To check the current readahead setting: # blockdev -getra dev-name-for-db-area

To change the current readahead setting: # blockdev -setra val-in-512-byte-sectorsdev-name-for-db-area

However, be aware that the effect of the setting will be small if the value exceeds 16 MB (32768 in 512-byte sectors).

Platform: Windows, Linux  |  Version: All versions

You can log SQL statements that take longer to execute than a threshold value (SQL statement execution time) by setting the log_min_duration_statement parameter in postgresql.conf. For details, refer to PostgreSQL documentation > Part III - Server administration > Chapter 19 - Server configuration > 19.8 - Error reporting and logging > 19.8.2 - When to log > log_min_duration_statement

If you want to log execution plans in addition to SQL statements, use the contrib module auto_explain. By setting a threshold value (SQL statement execution time) for the auto_explain.log_min_duration parameter, execution plans for SQL statements that took longer than the threshold value can be output to the log. For details, refer to PostgreSQL documentation > Part VIII - Appendix > Appendix F - Additional supplied modules > F.3 - auto_explain

Platform: Windows, Linux  |  Version: All versions

Indexes are created using memory set aside by the maintenance_work_mem parameter, so this value needs to be big enough for the table. If index creation is taking too long in your environment, then you need to increase its value, to around 3 times as much as required during tests.
It is also recommended to create or rebuild the indexes during the maintenance window or off-peak hours to avoid any transaction blocking.

Platform: Windows, Linux  |  Version: All versions

When you use the streaming replication feature, you can use the pg_is_in_recovery() function to check the server status. It returns t if the server is a standby, or f if the server is a primary.

In Fujitsu Enterprise Postgres, when you use Mirroring Controller to comprise the cluster system, you can use the Mirroring Controller command mc_ctl to determine which is primary and which is standby server as follows:

mc_ctl status -M /mcdir/inst1

The output will look like this:

mirroring status
-----------------
switchable
 server_id  host_role  host           host_status  db_proc_status  disk_status 
------------------------------------------------------------------------------
 inst1p     standby    XXX.XX.XX.156  normal       normal          normal
 inst1s     primary    XXX.XX.XX.161  normal       normal          normal

For details, refer to Fujitsu Enterprise Postgres Cluster Operation Guide - Database Multiplexing > Chapter 3 - Operations in database multiplexing mode > 3.3 - Checking the database multiplexing mode status .

Platform: Windows, Linux  |  Version: All versions

If you promote the standby server while the source (master) is still active, the standby server will become an updatable instance (client connections besides those of a replication type can update the database cluster).

This situation where both database instances of a cluster are updatable is referred to as a split-brain scenario. It means that some clients can make changes to the original master but not to the original standby, while other clients can make changes to the original standby but not to the master, eventuating in two separate database clusters containing different sets of data. At this point, no matter which database cluster you connect to, some data will be missing (as it will exist on the other database instance).

Recovering from a split-brain scenario is difficult because you need to identify the differences and ensure that missing data on one server is recovered from the other server. A new standby will need to be created from the newly recovered master before a high availability architecture can again be achieved.

Before promoting a standby server to master, it is vital that the master database (source) is fenced, i.e., that it is made inaccessible to any clients. For more details on this topic, look up STONITH (Shoot The Other Node In The Head).

For details on how to avoid split brains when using pgpool-II, refer to our PostgreSQL Insider article PostgreSQL High Availability using pgpool-II.

Platform: Linux  |  Version: All versions

No, the recommend approach is to use a binary dump such as that performed by using pg_basebackup. This ensures that the necessary information is written to the database data files and WAL files in order to begin playing the WAL files from the correct position.

The risk of using a logical dump is that data written since the start of the backup is not included in that backup, and that a restore is not able to identify the correct place in the WAL to start synchronising from.

A restore of a binary backup by itself may not produce a consistent database (specifically when the database is being used while the backup is being executed), so the WAL files must be used to bring the database into a consistent state during restore.

Platform: Windows, Solaris, Linux  |  Version: All versions

There is no "DB updates are allowed but updates are not replicated" timing. The standby will re-establish connectivity to the master and replication of all updates will occur.

Platform: Windows, Linux  |  Version: All versions

Follow the steps below:

1. Stop the fenced database instance.
2. Take a backup of the current primary database using pg_basebackup, and use this to replace the fenced database instance data directory.
3. Configure the fenced database instance by setting standby_mode to "on", and setting the appropriate connection information for the database instance to connect to the primary instance.
4. Unfence the instance using the appropriate method for how the database has been fenced.
5. Start the database instance - it will connect to the master and synchronize.

Note that the settings mentioned above are specified differently depending on the database version - versions up to 12 use the recovery.conf file; versions from 12 onwards use the postgresql.conf file and the signal file recovery.signal.

Platform: Windows, Linux  |  Version: All versions

The safest course of action in this situation is to rebuild the standby database from the primary one.

1. Take a backup of the current primary database using pg_basebackup, and use this to replace the data directory of the standby instance giving the error.
2. Configure the standby instance appropriately, including setting the appropriate connection information for the database instance to connect to the primary instance. If the configuration file in the database directory being replaced is suitable, this can be backed up and replace the one from the backup.
3. Start the database instance - it will connect to the master and synchronize.

Platform: Windows, Linux  |  Version: All versions

pg_basebackup is a utility that provides a less error-prone method of building a streaming replication environment, by performing the below activities automatically:

• Checkpoint to flush dirty pages to disk.
• Forcing full page writes to occur.
• Marking the backup starting point in the transaction logs for synchronizing recovery later.

While other options are available, such as taking down the database or using pg_start_backup/pg_stop_backup and then manually copying the data directory, pg_basebackup offers the simplest and safest way of doing it.

Platform: Windows, Linux  |  Version: All versions

The configuration settings that you need to be aware of for streaming replication, and their meanings, are:

Sending servers (including standby servers in cascading replication setups)

• max_wal_senders (integer): Maximum number of concurrent connections from standby servers or streaming base backup clients - i.e., the maximum number of simultaneously running WAL sender processes (default: 10).
• max_replication_slots (integer): Maximum number of replication slots that the server can support (default: 10).
• wal_keep_size (integer): Minimum size of past log file segments kept in the pg_wal directory, in case a standby server needs to fetch them for streaming replication.
• max_slot_wal_keep_size (integer): Maximum size of WAL files that replication slots are allowed to retain in the pg_wal directory at checkpoint time.
• wal_sender_timeout (integer): Amount of time of inactivity from replication connections before they are terminated.
• track_commit_timestamp (boolean): Whether to record commit time of transactions (can only be set in postgresql.conf or on the server command line; default: off).

Master servers

• synchronous_standby_names (string): List of standby servers that can support synchronous replication.
• vacuum_defer_cleanup_age (integer): Number of transactions by which VACUUM and HOT updates will defer cleanup of dead row versions (default: 0, meaning that dead row versions can be removed as soon as possible).

Standby servers

• primary_conninfo (string): Connection string to be used for the standby server to connect with a sending server.
• primary_slot_name (string): Name of existing replication slot to be used when connecting to the sending server via streaming replication to control resource removal on the upstream node.
• promote_trigger_file (string): Trigger file whose presence ends recovery in the standby.
• hot_standby (boolean): Whether users can connect and run queries during recovery.
• max_standby_archive_delay (integer): How long the standby server should wait before canceling standby queries that conflict with about-to-be-applied WAL entries (when Hot Standby is active).
• max_standby_streaming_delay (integer): How long the standby server should wait before canceling standby queries that conflict with about-to-be-applied WAL entries (when Hot Standby is active).
• wal_receiver_create_temp_slot (boolean): Whether the WAL receiver process should create a temporary replication slot on the remote instance when no permanent replication slot to use has been configured (default: off).
• wal_receiver_status_interval (integer): Minimum frequency for the WAL receiver process on the standby to send information about replication progress to the primary or upstream standby.
• hot_standby_feedback (boolean): Whether a hot standby should send feedback to the primary or upstream standby about queries currently executing on the standby.
• wal_receiver_timeout (integer): Amount of time of inactivity from replication connections before they are terminated.
• wal_retrieve_retry_interval (integer): How long the standby server should wait when WAL data is not available from any sources.
• recovery_min_apply_delay (integer): By default, a standby server restores WAL records from the sending server as soon as possible. It may be useful to have a time-delayed copy of the data, offering opportunities to correct data loss errors.

Platform: Windows, Linux  |  Version: All versions

Standby servers connect to primary servers in order to receive data updates that are streamed from the transaction log. This means that the primary instance needs to be configured to allow connection from the standby instance. To do this, appropriate settings should be configured in the pg_hba.conf file (credentials and host that the standby will connect with).

The standby instance also needs to be configured with the connection information for the master instance. This is slightly different depending on which version of the database you are using. For Fujitsu Enterprise Postgres/PostgreSQL versions 11 and earlier, specify the connection information in the recovery.conf file using the primary_conninfo parameter. For later versions, specify the connection information in the postgresql.conf file using the primary_conninfo parameter.

For details on primary_conninfo, refer to PostgreSQL documentation > Part III - Server administration > Chapter 19 - Server configuration > 19.6 - Replication > 19.6.3 - Standby servers

The user executing the pg_dump command does not have access permissions to the resource to be backed up. Use GRANT to set access permissions for the resource to be backed up for the user executing the pg_dump command , or execute the pg_dump command by specifying a superuser for the connecting user. 

For details on the pg_dump command, refer to PostgreSQL Documentation > Part VI. Reference > PostgreSQL Client Applications > pg_dump.

Platform: Windows, Linux  |  Version: All versions

This happened because the user did not add the -w option or the --no-password option to pgx_dmpall. By using these options, the user won't be prompted for a password. For details on the max_connections parameter, refer to Fujitsu Enterprise Postgres Reference Guide > Chapter 3 - Server commands > 3.2 -pgx_dmpall.

To solve this issue, run pgx_dmpall with the -w option or the --no-password option. If either is used, one of the following configuration must also be in place.

• Using the pgpass.conf: Write the password in the %APPDATA%\postgresql\pgpass.conf file (where %APPDATA% refers to the Application Data subdirectory in the user's profile). For details, refer to PostgreSQL documentation > PartIV - Client interfaces > Chapter 32 - libpq-C library > 32.16 - The password file.
• Using the password file pg_hba.conf: Specify "trust" in the "auth-method" field of the pg_hba.conf file in the data directory for the database cluster. For details, refer to PostgreSQL documentation > Part III - Server administration > Chapter 20 - Client authentication > 20.1 The pg_hba.conf file.

These methods allow batch processing without password input.

Platform: Windows, Linux  |  Version: All versions

Fujitsu Enterprise Postgres databases can be backed up and recovered without stopping the database, either using WebAdmin or using pgx_dmpall for backup and pgx_rcvall for recovery.

For details on backup, refer to Fujitsu Enterprise Postgres Operation Guide > Chapter 3 - Backing up the database. For details on recovery, refer to Fujitsu Enterprise Postgres Operation Guide > Chapter 15 - Actions when an error occurs.

It is also possible to back up the database without stopping it using PostgreSQL pg_dump. For details, refer to PostgreSQL documentation > Part III - Server administration > Chapter 25 - Backup and restore.

Platform: Windows, Linux  |  Version: All versions

pgx_dmpall saves backup files to the directory set in backup_destination in postgresql.conf, so when saving the contents of the backup directory to another location, make sure you save all content of this backup directory. When restoring, restore the content of the backup directory to the same state as the source. For details, refer toFujitsu Enterprise Postgres Reference Guide > Chapter 3 - Server commands > 3.2 - pgx_dmpall.

Platform: Windows, Linux  |  Version: All versions

In postgresql.conf, enable WAL archiving by setting wal_level to replica (or logical when using logical replication), archive_mode to on, and archive_command to 'cp %p <archive directory path>/%f'.

Platform: Windows, Linux  |  Version: All versions

You will need at least twice the amount of space used by the instance.

Platform: Windows, Linux  |  Version: All versions

This may be happening because the password to connect to the database server has not been preset, so you are being prompted to enter it and the psql command is waiting. To resolve this, preconfigure the password to connect to the database server by doing one of the following:

• Specify the password in the PGPASSWORD environment variable. For details, refer toPostgreSQL documentation > Part IV - Client interfaces > Chapter 32 - libpq-C library > 32.16 - The password file.
• Specify the password file. In Linux/Solaris, create the .pgpass file in the home directory of the executing user and specify the password. In Windows, create the %APPDATA%\postgresql\pgpass.conf file and specify the password (where % APPDATA% refers to the Application Data directory in the user's profile). For details, refer to PostgreSQL documentation > Part IV - Client interfaces > Chapter 32 - libpq-C library > 32.16 - The password file, PostgreSQL documentation > Part III - Server administration > Chapter 20 - Client authentication, and PostgreSQL documentation > Part VI. Reference > PostgreSQL client applications > psql.

Platform: Windows, Linux  |  Version: All versions

This may be happending because you applied an emergency fix for the WebAdmin feature (GUI features) and have not set up WebAdmin afterwards. Refer to the emergency fix information file [Notes] to run the WebAdmin setup.

For details on WebAdmin setup, refer to Fujitsu Enterprise Postgres Installation and Setup Guide for Server > Appendix B - Setting up and removing WebAdmin > B.1 - Setting up WebAdmin > B.1.1 Setting up WebAdmin.

Platform: Windows, Linux  |  Version: All versions

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.

Platform: Windows, Linux  |  Version: All versions

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.

Platform: Windows, Linux  |  Version: All versions

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:

1. 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).
2. Shut down your current database cluster instance.
3. Run pg_ctl stop or use your Fujitsu Enterprise Postgres service.
4. 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). If rsync is not available on your system, then use the normal copy command.
   rsync -av /var/lib/postgres12/* /var/lib/postgres12_backup/ 
5. To reduce size of the data storage location, consider deleting old unwanted logs.
6. Rename the old data storage directory from /var/lib/postgres12 to /var/lib/postgres12_old.
7. (optional) Rename the new data folder from /var/lib/postgres12_backup to /var/lib/postgres12 to match the original name.
8. Update the data_directory parameter in postgresql.conf if it is still set to the previous data storage location.
9. Start the database cluster instance and validate the data.
   pg_ctl start -D /var/lib/postgres12

To change backup data storage location:

1. 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.

Platform: Windows, Linux  |  Version: All versions

The fatal error message displayed may be one of the following: FATAL: sorry, too many clients already, FATAL: remaining connection slots are reserved for non-replication superuser connections, or 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 max_connections - superuser_reserved_connections - after this value is exceeded, only superusers can connect:

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

The maximum number of simultaneous connections is calculated as max_connections = max num of concurrent connections to 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. For details, refer to PostgreSQL documentation > Part III - Server administration > Chapter 19 - Server configuration > 19.3 - Connections and authentication > 19.3.1 - Connection settings and 19.6 - Replication > 19.6.1. Sending servers.

Note that formula for the maximum number of simultaneous connections is different when performing database multiplexing operations. For details, refer to Fujitsu Enterprise Postgres Cluster Operation Guide (Database Multiplexing) > Chapter 2 - Setting up database multiplexing mode > 2.4 - Setting up the primary server > 2.4.2 - Creating, setting, and registering the primary server instance

Platform: Windows, Linux  |  Version: All versions

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.

Platform: Windows, Linux  |  Version: All versions

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.

Platform: Linux  |  Version: All versions

Not all parameters are immediately reflected to database clusters — some require executing pg_ctl command with either reload or restart. Execute psql -c "SELECT name, context FROM pg_settings;" and check the value of context column in pg_settings system view.

The value of context indicates: for postmaster, restart is required, for sighup, superuser-backend, or backend, reload is required, and for superuser or user, changes can be applied without reload or restart. These can also be set within a session via SET command.

Platform: Windows, Linux  |  Version: All versions

You can change the system configuration parameters across the entire database cluster with ALTER SYSTEM SET parm-name = parm-value.

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

Platform: Windows, Linux  |  Version: All versions

The archive_mode in the standby server's postgresql.conf may be set to on. Set it to always in the standby server's postgresql.conf.

For details, refer to the PostgreSQL documentation >Part III - Server Administration >Chapter 19 - Server Configuration >19.5 - Write Ahead Log >19.5.3 - Archiving.

Platform: Windows, Linux  |  Version: All versions

Conflicts may occur when max_standby_archive_delay and max_standby_streaming_delay are configured with values that are too low. Increase these settings in the standby server's postgresql.conf so that they exceed the SQL execution time.

For details, refer to the PostgreSQL documentation > Part III - Server Administration > Chapter 19 - Server Configuration > 19.6 - Replication > 19.6.3 - Standby Servers and Part III - Server Administration > Chapter 26 - High Availability, Load Balancing, and Replication > 26.4 - Hot Standby > 26.4.2 - Handling Query Conflicts.

For other product versions/levels, please refer to the corresponding manual sections.

Platform: Windows, Linux  |  Version: All versions

You can change the tablespace for tables and indexes using ALTER statements after creating a new table space. It is not necessary to delete the original data.

CREATE TABLESPACE new_tablespace LOCATION 'new_tablespace_directory';
ALTER TABLE table_name SET TABLESPACE new_tablespace;
ALTER INDEX index_name SET TABLESPACE new_tablespace;

For details, refer to PostgreSQL documentation > Part VI - Reference > SQL Commands > ALTER INDEX, ALTER TABLE, and CREATE TABLESPACE.

Platform: Windows, Linux  |  Version: All versions

A standby server is in read-only mode. It cannot generate new transaction IDs or write WAL (Write-Ahead Log) records. On a standby server, you can execute read-only SQL commands such as SELECT. Commands that modify data, such as INSERT, UPDATE, DELETE, CREATE, ALTER, and DROP, cannot be executed.

For details on SQL commands that can and cannot be executed on a standby server, refer to PostgreSQL Documentation > Part III - Server Administration > Chapter 26 - High Availability, Load Balancing, and Replication > 26.4 - Hot Standby > 26.4.1 - User's Overview and 26.4.3. Administrator's Overview.

Platform: Linux  |  Version: All versions

Fujitsu Enterprise Postgres creates a file named postmaster.pid when it starts, and removes it when it shuts down normally. If the database stops unexpectedly or is not shut down properly, this file may remain in the data directory. When Fujitsu Enterprise Postgres starts again, it detects the existing file and reports the error.

To solve this issue, ensure that no Fujitsu Enterprise Postgres process is running, then delete the postmaster.pid file from the data directory and start Fujitsu Enterprise Postgres again.

Note that the data directory is specified by the -D option of pg_ctl, or the PGDATA environment variable.

Platform: Windows, Solaris, Linux  |  Version: All versions

The psql command is waiting for a database password to be entered. This usually happens when a password has not been configured in advance and the batch job cannot respond to the password prompt.

Configure the database password before running the batch job using one of the following methods:

• Option 1: Use a password file - in Linux/Solaris, create the .pgpass file in the user’s home directory, and in Windows, create the pgpass.conf file in %APPDATA%\postgresql\.
• Option 2: Set the database password in the PGPASSWORD environment variable before running the batch job.

For details on password files and the PGPASSWORD environment variable, refer to the PostgreSQL Documentation > Part IV - Client Interfaces > Chapter 32 - libpq - C Library > 32.15 - Environment Variables > PGPASSWORD and 32.16 - The Password File. For details on client authentication and the psql command, refer to PostgreSQL Documentation > Part III - Server Administration > Chapter 20 - Client Authentication and Part VI - Reference > PostgreSQL Client Applications > psql.

Platform: Windwows, Solaris, Linux  |  Version: All versions

Fujitsu Enterprise Postgres provides two types of statistics information: PostgreSQL statistics and Fujitsu Enterprise Postgres statistics.

PostgreSQL statistics help monitor database activity, including table access information, index access information, buffer hit counts, block read counts, function execution counts, function execution times. This information can be used to check buffer cache efficiency, identify frequently accessed tables, and find slow-running functions.

Fujitsu Enterprise Postgres statistics include wait and lock information, which can help to identify performance bottlenecks.

For details on the statistics information that can be obtained, refer to the Fujitsu Enterprise Postgres Operation Guide > Chapter 7 - Routine Operations > 7.6 - Monitoring Database Activity and PostgreSQL Documentation > Part III - Server Administration > Chapter 27 - Monitoring Database Activity > 27.2 - The Cumulative Statistics System.

Platform: Windwows, Solaris, Linux  |  Version: All versions

Log slow SQL statements by setting the log_min_duration_statement parameter in postgresql.conf. Any SQL statement whose execution time exceeds the configured threshold will be written to the log.

To log execution plans that exceed the specified execution time, use the auto_explain module and set the auto_explain.log_min_duration parameter. This helps identify slow SQL statements, the execution plans used by the optimizer, and potential performance bottlenecks.

For details on how to use auto_explain, refer to PostgreSQL Documentation > Part III - Server Administration > Chapter 19 - Server Configuration > 19.8 - Error Reporting and Logging > 19.8.2 - When to Log > log_min_duration_statement, and Part VIII - Appendixes > Appendix F - Additional Supplied Modules and Extensions> F.3 - auto_explain.

Platform: Windwows, Solaris, Linux  |  Version: All versions

When investigating a performance issue, collect database statistics and activity information as soon as possible. Some information, such as active sessions, transactions, and locks, may be lost after a database restart or when processing completes. The following information is recommended:

• Server process information - Shows active client connections and database sessions.

  SELECT * FROM pg_stat_activity;

• Transaction information - Shows currently running transactions and their duration.

  SELECT pid,
         wait_event_type,
         wait_event,
         state,
         (current_timestamp - xact_start)::interval(3) AS duration,
         query
  FROM pg_stat_activity
  WHERE pid <> pg_backend_pid();

• Lock information - Shows sessions waiting for locks and the affected tables.

  SELECT l.locktype,
         c.relname,
         l.pid,
         l.mode,
         a.query,
         (current_timestamp - xact_start) AS duration
  FROM pg_locks l
  LEFT JOIN pg_stat_activity a ON l.pid = a.pid
  LEFT JOIN pg_class c ON l.relation = c.oid
  WHERE NOT l.granted
  ORDER BY l.pid;

Collecting this information can help to identify long-running queries, blocking sessions, lock contention, and resource bottlenecks.

Platform: Windows, Solaris, Linux  |  Version: All versions

This may be because the CSV format file uses 0x00 as the NULL value. The COPY FROM command treats unquoted empty characters as NULL, rather than 0x00.

If the CSV format file uses 0x00 as the NULL value, replace it with an empty string. If you want to distinguish NULL values from empty characters, you can also specify the string representing them in the NULL option of the COPY FROM command, so replace 0x00 with the specified string. For details, refer to PostgreSQL documentation > Part VI - Reference > SQL commands > COPY

Platform: Windows, Linux  |  Version: All versions

Yes. By leveraging tablespaces, tables and indexes can be placed in separate areas.

For details, refer to the PostgreSQL documentation > Part III - Server administration > Chapter 22 - Managing databases > 22.6 - Tablespaces, Part VI - Reference > SQL commands > CREATE TABLESPACE, CREATE TABLE, and CREATE INDEX

Platform: Windows, Linux  |  Version: All versions

Due to the I/O latency in the WAL archive storage area, the disk containing WAL is full and WAL cannot be written because the copy speed to the archive area is slower than the rate at which WAL data is generated. Consider expanding the capacity of the disk that store WAL files, reducing the frequency of database update, or using faster disks for WAL archiving.

• Check the error message in postgresql.log. It will contain an error message stating that the server has stopped because the disk was full and that the system could not write WAL, for example: PANIC: could not write to file "pg_wal/waltemp.4920": No space left on device
• Compare the WAL generation rate and backup rate. Check the WAL generation rate with timestamp intervals of each WAL file under the pg_wal directory, check the backup rate with timestamp intervals of each file under the archived_wal directory, and confirm that the WAL generation rate is larger than backup rate.

As long as your environment can keep up with the average speed of the WAL generation of the server, the processing speed of the command for archiving is not important. Normal operations continue even if the archive process is slightly delayed, but note that a significantly slower archive process increases the amount of data lost in the event of a disaster. This also means that many segment files waiting to be archived will be stored in $PGDATA/pg_wal, which may cause the disk to be full. It is recommended that you monitor the archive process to ensure that it is working as intended.

For details, refer to PostgreSQL documentation > Part III - Server administration > Chapter 25 - Backup and restore > 25.3.1 - Setting up WAL archiving. You can also refer to our blog post How to solve the problem if pg_wal is full.

Platform: Windows, Linux  |  Version: All versions

Before clearing space in the backup data directory, one thing to note is the scenario where archiving log is enabled and archive logs are stored in the backup data directory. Once this destination runs out of space, the actual data directory will be filled with WAL logs. If that data directory runs out of space, the database may be unavailable. So, this must be urgently taken care of.

If you run out of space in the backup data directory, the first step is to delete unnecessary files on it. If that does not solve the problem, temporarily save backup data to another location with sufficient space, or replace the disk containing the backup data directory with a disk with more capacity.

For details, refer to Fujitsu Enterprise Postgres Operation Guide > Chapter 15 - Actions when an error occurs > 15.7 - Actions in response to insufficient space on the backup data storage destination .

Platform: Linux  |  Version: All versions

You can import data into the database by using the COPY statement with the -c option of the psql command, for example: $ psql -d db-name -c "COPY table1 FROM 'import_data.csv' DELIMITER ','". For details, refer to PostgreSQL documentation > Part VI - Reference > SQL Commands > COPY

Platform: Windows, Linux  |  Version: All versions

Step 1: Identify whether the installed PostgreSQL is Fujitsu Enterprise Postgres or open-source software

Option 1: Check the default installation directory of Fujitsu Enterprise Postgres. List the content of the default installation directory of Fujitsu Enterprise Postgres (/opt/fsepvversionserver64). If the directory exists, and its sub-directories are not empty, then Fujitsu Enterprise Postgres is installed.

Option 2: Execute the pg_ctl command. Set the appropriate environment variables required to execute the Fujitsu Enterprise Postgres command pg_ctl, and then either check the server status with $ pg_ctl status -D data-directoryor start the database server with $ pg_ctl start -D data-directory. If you can execute the command successfully, then it means that Fujitsu Enterprise Postgres is installed.

Option 3: Check the RPM packages. List the RPM packages related to Fujitsu Enterprise Postgres with $ rpm -qa | grep FJSVfsep. If packages with name starting with FJSVfsep are listed, then it means that Fujitsu Enterprise Postgres is installed. Note that RPM packages may differ depending on the components installed by the customer.

Step 2: Verify the details of the installed product.

Option 1: List the Fujitsu middleware products installed on the machine. As the root user, execute # /opt/FJSVcir/cimanager.sh, then verify the product names and versions with /opt/FJSvVcir/cimanager.sh.

Option 2: Check the RPM package details. Display information about the installed package with $ rpm -qi FJSVfsep-SV-version, and verify details such as installation date/time and installation directory.

Step 3: Verify if the Fujitsu Enterprise Postgres process is running

List the PostgreSQL-related processes currently running with $ ps -ef | grep postgres. If the postgres process of Fujitsu Enterprise Postgres installation directory /opt/fsepvversionserver64/bin/postgres is listed, then it means that Fujitsu Enterprise Postgres process has started.

Step 4: Verify the patches applied to the Fujitsu Enterprise Postgres server

If you have applied patches with the downloaded RPM, list details of the applied patches with $ rpm -qa | grep prefix of product patches and rpm -qi <prefix of product patches>, and Verify that the latest patches have been applied to the database server.

Platform: Linux  |  Version: All versions

You must use the foreign data wrapper postgres_fdw, which is built-in to Fujitsu Enterprise Postgres and provides read/write support. For details, refer to our PostgreSQL Insider article Linking to foreign data using foreign data wrappers.

Platform: Windows, Linux  |  Version: All versions

Yes. All identifiers, including column names, are converted to lowercase in Fujitsu Enterprise Postgres, unless enclosed by double quotes. Identifiers created with double-quotes retain their original capitalization.

Platform: Windows, Linux  |  Version: All versions

In psql, use the meta-commands \dt, \di, \l, and \du, respectively. This information can also be obtained from the system views pg_tables, pg_indexes, pg_database, and pg_user, respectively.

Platform: Linux  |  Version: All versions

You can check with SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema='schema-name' AND table_name='table-name'); or with the meta-command \dt schema-name table-name.

Platform: Linux  |  Version: All versions

You can verify which tablespaces are encrypted and their encryption algorithm using the pgx_tablespaces system view: SELECT spcname, spcencalgo FROM pg_tablespace ts, pgx_tablespaces tsx WHERE ts.oid = tsx.spctablespace;.

Platform: Linux  |  Version: All versions

Encryption using Transparent Data Encryption is applied at the tablespace level. This means that data such as tables and indexes created in the specified tablespace, the WAL, backup files, and archive logs will be automatically encrypted.

The data and index in the encrypted tablespace along with the associated WAL files can be backed up by taking a physical backup using the pgx_dmpall or pg_basebackup command. It is important to back up the keystore.ks file so that encrypted data can be restored with a keystore and passphrase. If there is any tablespace which is not encrypted, then it is backed up as unencrypted.

Note that a logical backup taken by pg_dump, pg_dumpall, or COPY command is not encrypted. This is because a logical backup is taken through SQL interface (like a client executing any other select statement), so encrypted data are decrypted before writing to a backup file.

Platform: Linux  |  Version: All versions

If you want to mask the first 12 digits of a credit card number, you can apply partial masking. There are 3 different types of Data Masking supported that can be applied using masking policies, which include:

• Full masking, where a whole column value can be obfuscated with alternate values. For example, values in a numeric type of column are replaced with ‘0’ and values in a character type of column are replaced with a space.
• Partial masking, which allows masking part of a string - for example, the first 12 digits of a credit card number can be replaced with ‘*’.
• Regular expression masking is flexible and allows you to define masking by using regular expressions, which is useful for unstructured types like XML or JSON (ability to mask a single element). For example, for strings with variable length such as email address, characters preceding ‘@’ can be replaced with ‘*’.

You can specify whether to apply a masking policy using a function - if it returns is true, then masking will be applied. This approach also gives the flexibility to selectively mask data to specific users.

Platform: Linux  |  Version: All versions