Start  trial

    Start trial

      Knowledge articles - Operation/Maintenance

      The message ERROR: input value length is x; too long for type <data-type> (y) is displayed when copying data from a CSV file to a table using the COPY FROM command.KB3001

      This may be because the CSV format file uses 0x00 as the NULL value.

      Resolution

      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 more information, refer to PostgreSQL documentation > Part VI - Reference > SQL commands > COPY

      Applicable to

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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      Can tables and indexes be stored in separate areas?KB3002

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

      For details, refer to the sections below in PostgreSQL documentation:

      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

      The database server's WAL space disk utilization is 100% and the database process has stopped. How do I solve it?KB3003

      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.

      Resolution

      Consider the following:

      • Expand the capacity of the disk that store WAL files
      • Reduce the frequency of database update
      • Use faster disks for WAL archiving

      How to investigate

      • Check the error message in postgresql.log
        The log will contain an error message similar to the one below, stating that the server has stopped because the disk was full and that the system could not write WAL:
        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.
        • 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 more 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.

      Applicable to

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

      Versions: from 10

      Architecture: X86, S390x

      Operating System: Windows, Linux

      What should I do if the database runs out of space to store backup data?KB3004

      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, do one of the following:

      • Temporarily save backup data to another location with sufficient space.
      • Replace the disk containing the backup data directory with a disk with more capacity.

      For more information, 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 [Linux version] [Windows version]

      Applicable to

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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      How to import data from a CSV file?KB3005

      You can import data into the database by using the COPY statement with the -c option of the psql command.

      Example:

      $ psql -d <db-name> -c "COPY table1 FROM 'import_data.csv' DELIMITER ','"

      For more information, refer to PostgreSQL documentation > Part VI - Reference > SQL Commands > COPY

      Applicable to

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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      How to verify if the PostgreSQL installed on my machine is Fujitsu Enterprise Postgres or open-source software?KB3006

      Follow the steps to verify the status of products installed in the test or production environment.

      This article shows examples using Fujitsu Enterprise Postgres 13 Advanced Edition.

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

      Follow any of the procedures below.

      Option 1: Check the default installation directory of Fujitsu Enterprise Postgres

      Execute the ls command in the default installation directory of Fujitsu Enterprise Postgres (/opt/fsepv13server64) - if the directory below exists, and these directories are not empty, then Fujitsu Enterprise Postgres is installed.

      [fsepuser hostname/]$ ls /opt/fsepv13server64/
      bin  doc  etc  FJQSS  include  lib  LIC  mc  OSS  PKG  setup  share

      Option 2: Execute the pg_ctl command

      Set the appropriate environment variables required to execute the Fujitsu Enterprise Postgres command pg_ctl, and then do one of the following:

      • Check the server status: $ pg_ctl status -D <data directory>
      • Start the database server: $ pg_ctl start -D <data directory>

      If you can execute the command successfully, then it means that Fujitsu Enterprise Postgres is installed.

      [fsepuser hostname/]$ pg_ctl status -D /database/instl
      pg_ctl: no server running
      [fsepuser hostname/]$ pg_ctl start -D /databaSe/instl
      waiting for server to start 2021-12-15 01:43:46.788 UTC [8821] LOG: redirecting log output to logging coIlector process
      2021-12-15 01:43:46.788 UTC [8821] HINT: Future log output will appear in directory "log".
       done
      server started

      Option 3: Check the RPM packages

      List the RPM packages related to Fujitsu Enterprise Postgres.

      $ rpm -qa | grep FJSVfsep

      If packages named 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.

      [fsepuser hostname/]$ rpm -qa lgrep FJSVfsep
      FJSVfsep-AE-PR-13-1300-0.e17.x86_64
      FJSVfsep-ULOG-13-1300-0.e17.x86_64
      FJSVfsep-ODBC-13-1300-0.e17.x86_64
      FJSVfsep-SV-13-1300-0.e17.x86_64
      FJSVfsep-SV-CM-13-1300-0.e17 .x86_64
      FJSVfsep-JDBC-13-1300-0.e17.x86_64
      FJSVFsep-CL-CM-13-1300-0.e17. x86_64
      FJSVfsep-POOL2-13-1300-0.e17.x86_64
      FJSVfsep-AUDIT-13-1300-0.e17.x86_64
      FJSVfsep-CL-13-1300-0.e17.x86_64
      FJSVfsep-WAD-13-1300-0.e17.x86_64

      Step 2: Verify the details of the installed product.

      Follow any of the procedures below.

      Option 1: List the Fujitsu middleware products installed on the machine.

      Execute the following command as the root user.

      # /opt/FJSVcir/cimanager.sh

      Verify the product names and versions

      [root hostname/]# /opt/FJSvVcir/cimanager.sh
      Loading Uninstaller...
      
      Currently installed products
      1. FUJITSU Enterprise Postgres WebAdmin 13
      2. FUJITSU Enterprise Postgres Pgpool-II 13
      3. FUJITSU Enterprise Postgres Client(64bit) 13
      4. FUJITSU Enterprise Postgres Advanced Edition(64bit) 13

      Option 2: Check the RPM package details.

      Display information about the installed package FJSVfsep-SV-13.

      $ rpm -qi FJSVfsep-SV-13

      Verify details such as installation date/time and installation directory.

      [fsepuser hostname/]$ rpm -qi FJSVfsep-SV-13
      Name        : FJSVfsep-SV-13
      Version     : 1300
      Release     : 0.e17
      Architecture: x86_64
      Install Date: Fri 28 May 2021 05:14:51 AM UTC
      Group       : Applications/Databases
      Size        : 288097973
      License     : FUJITSU LIMITED
      Signature   : (none)
      Source RPM  : FJSVfsep-SV-13-1300-0.e17.src.rpm
      Build Date  : Tue 09 Mar 2022 05:50:15 AM UTC
      Build Host  : VM036799.localdomain
      Relocations : /opt/fsepvl3server64
      Packager    : FUJITSU LIMITED
      Vendor      : FUJITSU LIMITED
      URL         : https://www.fujitsu.com
      Summary     : FUJITSU Enterprise Postgres Server
      Description :
      FUJITSU Enterprise Postgres Server

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

      List the PostgreSQL-related processes currently running.

      $ ps -ef | grep postgres

      If the postgres process of Fujitsu Enterprise Postgres installation directory (/opt/fsepv13server64/bin/postgres) is listed, then it means that Fujitsu Enterprise Postgres process has started.

      [fsepuser hostname/]$ ps -ef | grep postgres
      fsepuser 8821    1 0 01:43 7 00:00:00 /opt/fsepvl3server64/bin/postgres -D /database/instl
      fsepuser 8822 8821 0 01:43 7 00:00:00 postgres: logger
      fsepuser 8824 8821 0 01:43 7 00:00:00 postgres: checkpointer
      fsepuser 8825 8821 0 01:43 ? 00:00:00 postgres: background writer
      fsepuser 8826 8821 0 01:43 7 00:00:00 postgres: walwriter
      fsepuser 8827 8821 0 01:43 7 00:00:00 postgres: autovacuum launcher
      fsepuser 8828 8821 0 01:43 7 00:00:00 postgres: archiver last was 000000010000000000000020
      fsepuser 8829 8821 0 01:43 ? 00:00:00 postgres: stats collector
      fsepuser 8830 8821 0 01:43 7 00:00:00 postgres: logical replication launcher
      fsepuser 9050 8684 0 02:01 pts/0 00:00:00 grep --color=auto postgres

      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.

      $ rpm -qa | grep <prefix of product patches>
      $ rpm -qi <prefix of product patches>

      Verify that the latest patches have been applied to the database server.

      [fsepuser hostname/]$ rpm -qa | grep FJSVfsep-SV-13
      FJSVfsep-SV-13-1300-0.el7.x86_64
      [fsepuser hostname/]$ rpm -qi FJSVfsep-SV-13
      Name        : FJSVfsep-SV-13
      Version     : 1300
      Release     : 0.e17
      Architecture: x86_64
      Install Date: Fri 28 May 2021 05:14:51 AM UTC
      Group       : Applications/Databases
      Size        : 288097973
      License     : FUJITSU LIMITED
      Signature   : (none)
      Source RPM  : FJSVfsep-SV-13-1300-0.e17.src.rpm
      Build Date  : Tue 09 Mar 2022 05:50:15 AM UTC
      Build Host  : VM036799.localdomain
      Relocations : /opt/fsepvl3server64
      Packager    : FUJITSU LIMITED
      Vendor      : FUJITSU LIMITED
      URL         : https://www.fujitsu.com
      Summary     : FUJITSU Enterprise Postgres Server
      Description :
      FUJITSU Enterprise Postgres Server

      Applicable to

      Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE

      Versions: from 9.5

      Architecture: X86

      Operating System: Linux

      How can I perform a query that involves more than 1 database server?KB3007

      You must use the foreign data wrapper postgres_fdw, which is built-in to Fujitsu Enterprise Postgres and provides read/write support. For further details, refer to the below link.
      https://www.fast.fujitsu.com/postgresql-insider-fdw-ove

      Applicable to

      Product: Fujitsu Enterprise Postgres AE, PostgreSQL

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      Are column names case-sensitive?KB3008

      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.

      Applicable to

      Product: Fujitsu Enterprise Postgres AE, PostgreSQL

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      How do I find out what tables, indexes, databases, and user are defined?KB3009

      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.

      Applicable to

      Product: Fujitsu Enterprise Postgres AE, PostgreSQL

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      How do I check if a table exists in a specific schema?KB3010

      You can check with the query below:

      SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE
      table_schema='schemaName' AND table_name='tableName');

      It can also be checked with meta-command:

      \dt schemaName.tableName

      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.