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

Managing the PostgreSQL database on Windows

Use binaries and utility scripts to manage the PostgreSQL database.
Important: To manage the PostgreSQL database, you must log in as a user who does not have system administrator rights. A system administrator will be unable to run PostrgreSQL binaries and utility scripts.
Informatica has created some utility scripts 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 of the following sections contain sample commands that use these default values:

Backing up the PostgreSQL database on Windows

Use the script db_backup.bat 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.bat <dbusername> <dbpassword> <path to backup file along with name of backup file with a ".dump" extension> <dbport>
For example, the Secure Agent creates the backup file 'BackupFile1.dump' in the location C:\postgre\backup if you run the following command:
db_backup.bat bpeluser bpel "C:\postgre\backup\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 Windows

Use the command db_restore.bat 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.bat <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.bat bpeluser bpel "C:\postgre\backup\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 Windows

Shut down the PostgreSQL database and then use the command db_reset.bat 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 command:
    3. server_stop.bat
  3. 3To reset the PostgreSQL database, run the following command:
    4. db_reset.bat

Starting the PostgreSQL server on Windows

To start the PostgreSQL server on Windows, 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.bat
    Note: If you do not use the default 5432 port, you must pass the port number as an argument as follows:
    server_start.bat <port_number>
    For example, server_start.bat 6789

Stopping the PostgreSQL server on Windows

To stop the PostgreSQL server on Windows, 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.bat.

Getting the PostgreSQL server status on Windows

To get the status of the PostgreSQL server on Windows, 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.bat
    Note: If you do not use the default 5432 port, you must pass the port number as an argument as follows:
    server_status.bat <port_number>
    For example, server_status.bat 6789

Vacuuming the PostgreSQL database on Windows

Vacuum the PostgreSQL database to delete obsolete tuples and gain space. You use the script db_maintenance.bat to vacuum the PostgreSQL database.
By default, the PostgreSQL database autovacuums. 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.bat <dbusername> <dbpassword> <dbport> vacuum
  3. 3To vacuum a single table, run the following command:
    4. db_maintenance.bat <dbusername> <dbpassword> <dbport> vacuum <tablename>
For example, you vacuum the 'aeprocesslogdata' table if you run the following command:
db_maintenance.bat bpeluser bpel 5432 vacuum aeprocesslogdata
Note: The dbport argument is required even if you use the default port, 5432.

Reindexing the PostgreSQL database on Windows

Use the reindexing option to clean the index and free up space after you vacuum data on PostgreSQL. You use the script db_maintenance.bat 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.bat <dbusername> <dbpassword> <dbport> reindex
  3. 3To reindex a single table, run the following command:
    4. db_maintenance.bat <dbusername> <dbpassword> <dbport> reindex <tablename>
For example, you reindex the 'aeprocesslogdata' table if you run the following command:
db_maintenance.bat bpeluser bpel 5432 reindex aeprocesslogdata
Note: The dbport argument is required even if you use the default port, 5432.

Resetting transaction logs on Windows

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