Secure Agent Services > Process Server > Managing the PostgreSQL database on Linux
  

Managing the PostgreSQL database on Linux

Use binaries utility scripts to manage the PostgreSQL database.
Important: To manage the PostgreSQL database, you must log in as a user who does not have root access. A root user will be unable to run PostrgreSQL binaries and utility scripts.
Informatica has created some utility script based on PostgreSQL binaries. These utility scripts make it easier for you to manage the PostgreSQL database.
The following directories contain files for the PostgreSQL database:
For more information about PostgreSQL scripts, see the PostgreSQL help at https://www.postgresql.org/docs/current/static/index.html.
Some sections contain sample commands that use the following default values:

Backing up the PostgreSQL database on Linux

Use the script db_backup.sh to back up the PostgreSQL database.
To back up the PostgreSQL database, perform the following steps:
  1. 1Go to <Secure Agent installation directory>/apps/process-engine/data/db/util.
  2. 2Run the following command:
    3. db_backup.sh <dbusername> <dbpassword> <path to backup file along with name of backup file>.dump <dbport>
For example, the Secure Agent creates the backup file backupfile1.dump in the location /home/data/myfolder/ if you run the following command:
db_backup.sh bpeluser bpel "/home/data/myfolder/backupfile1.dump" 6432
Note: The dbport argument is optional. Specify the dbport argument if you use a port that is different from the default port, 5432.

Restoring the PostgreSQL database on Linux

Use the script db_restore.sh to restore the PostgreSQL database from a backup file.
To restore the PostgreSQL database file from a backup file, perform the following steps:
  1. 1Go to <Secure Agent installation directory>/apps/process-engine/data/db/util.
  2. 2Run the following command:
    3. db_restore.sh <dbusername> <dbpassword> <path to dump file> <dbport>
For example, you use the file backupfile1.dump to restore the PostgreSQL database if you run the following command:
db_restore.sh bpeluser bpel "/home/data/myfolder/backupfile1.dump" 6432
Note: The dbport argument is optional. Specify the dbport argument if you use a port that is different from the default port, 5432.

Resetting the PostgreSQL database on Linux

You first shut down the PostgreSQL database and then use the script db_reset.sh to reset it.
To reset the PostgreSQL database to its original state, perform the following steps:
  1. 1Go to <Secure Agent installation directory>/apps/process-engine/data/db/util
  2. 2To shut down the server, run the following script:
    3. server_stop.sh
  3. 3To reset the PostgreSQL database, run the following script:
    4. db_reset.sh

Starting the PostgreSQL server on Linux

To start the PostgreSQL server on Linux, perform the following steps:
  1. 1Go to <Secure Agent installation directory>/apps/process-engine/data/db/util.
  2. 2Run the following script:
    3. server_start.sh
    Note: If you do not use the default 5432 port, you must pass the port number as an argument as follows:
    server_start.sh <port_number>
    For example, server_start.sh 6789

Stopping the PostgreSQL server on Linux

To stop the PostgreSQL server, perform the following steps:
  1. 1Go to <Secure Agent installation directory>/apps/process-engine/data/db/util.
  2. 2Run the following script:
    3. server_stop.sh.

Getting the PostgreSQL server status on Linux

To get the status of the PostgreSQL server on Linux, perform the following steps:
  1. 1Go to <Secure Agent installation directory>/apps/process-engine/data/db/util.
  2. 2Run the following script:
    3. server_status.sh
    Note: If you do not use the default 5432 port, you must pass the port number as an argument as follows:
    server_status.sh <port_number>
    For example, server_status.sh 6789

Vacuuming the PostgreSQL database on Linux

Vacuum the PostrgeSQL database to delete obsolete tuples and gain space. You use the script db_maintenance.sh to vacuum the PostgreSQL database.
By default, the PostgreSQL database auto vacuums. If you want to manually vacuum the database, perform the following steps:
  1. 1Go to <Secure Agent installation directory>/apps/process-engine/data/db/util.
  2. 2To vacuum the entire database, run the following command:
    3. db_maintenance <dbusername> <dbpassword> <dbport> vacuum
  3. 3To vacuum a single table, run the following command:
    4. db_maintenance.sh <dbusername> <dbpassword> <dbport> vacuum <tablename>
For example, you vacuum the 'aeprocesslogdata' table if you run the following command:
db_maintenance.sh bpeluser bpel 5432 vacuum aeprocesslogdata
Note: The dbport argument is required even if you use the default port, 5432.

Reindexing the PostgreSQL database on Linux

Use the reindexing option to clean the index and free up space after you vacuum data on PostgreSQL. You use the script db_maintenance.sh to reindex the PostgreSQL database.
To reindex the PostgreSQL database, perform the following steps:
  1. 1Go to <Secure Agent installation directory>/apps/process-engine/data/db/util.
  2. 2To reindex the entire database, run the following command:
    3. db_maintenance <dbusername> <dbpassword> <dbport> reindex
  3. 3To reindex a single table, run the following command:
    4. db_maintenance.sh <dbusername> <dbpassword> <dbport> reindex <tablename>
For example, you reindex the 'aeprocesslogdata' table if you run the following command:
db_maintenance.sh bpeluser bpel 5432 reindex aeprocesslogdata
Note: The dbport argument is required even if you use the default port, 5432.

Resetting transaction logs on Linux

If the PostgreSQL server does not start because of corruption to the control information, use the command pg_resetxlog to reset the control information.
To reset the control information of the PostgreSQL database, perform the following steps:
  1. 1Go to <Secure Agent installation directory>/apps/process-engine/data/db/postgresql-linux-x64-binaries/pgsql/bin .
  2. 2Run the following command:
    3. pg_resetxlog -D <path to postgreSQL data directory>
For example, you reset the transactions logs in the Data directory, if you run the following command:
pg_resetxlog -D "home/apps/process engine/data/PostGreSql/Data"