Useful PostgreSQL commands

You can use PostgreSQL commands to check the status and contents of the Reports database. Many commands exist; this topic provides a small subset.
    Note:
  • The command examples below assume that you use the default values for the properties on the Database Settings page. If you change any of those values, such as the port number, you might need to use additional arguments on the PostgreSQL commands.

    Type help at the psql command prompt for more information.

  • Only use these commands with the Reports database. Do not run commands on the main RICOH ProcessDirector database unless a Ricoh support representative instructs you to.
Run the commands below at a command prompt on the server that the Reports database is installed on.

Check whether PostgreSQL is running

Type: ps -ef | grep postgres

If PostgresSQL is running, you see multiple lines of output. If it is not running, you see nothing.

Access the Reports database on the primary server

  • /aiw/aiw1/bin/postgresql/Linux/pgsql/bin/psql databasePostgreSQL_user_name

    or

  • /usr/pgsql-14/bin/psql database PostgreSQL_user_name

For example, if you use the default values for Database and User name on the Database settings page, enter:

  • /aiw/aiw1/bin/postgresql/Linux/pgsql/bin/psql history rpdreports

    or

  • /usr/pgsql-14/bin/psql history rpdreports

psql.bin (9.6.4) Type "help" for help.

After accessing the Reports database

Use these commands to retrieve information about the database, database tables, and contents:

\d
List the database tables that exist on the system.
\dt rpt.*
List the databases on the system that are in the rpt schema. Any databases that you migrate from the DB2 database that older versions of the Reports fieature used to save data are in this schema.
\d+ database_table_name
Show the format of the specified table. For example, \d+ job_history shows that format of the job_history database table.
select * from database_table_name;
List all data from the specified table.
select column_name[,column_name2,column_name3,...] from job_history;
List the data from the specified columns in the requested table.
COPY database_table_name TO 'directory_path_and_filename' DELIMITER ',' CSV HEADER;
Write data stored in the database to a CSV file.

For database_table_name, type the name of the PostgreSQL database table that you defined as the value of the Database table name property in the data collector.

For directory_path_and_filename, type the full directory path and name of the CSV that you want to capture the data to.

This example captures data in the job_history table and exports it in CSV format to the test.csv file in the /aiw/aiw1 directory:

COPY job_history TO '/aiw/aiw1/test.csv' DELIMITER ',' CSV HEADER;

\q
Exit PostgreSQL.

Stop the Reports database

Open a command prompt and type this command:

  • docker stop rpd-reports-postgres

    or

  • pg_ctl stop -o "-p portnumber" -U rpdreports

    Where rpdreports is the user name of the PostgreSQL user and portnumber is the port that RICOH ProcessDirector uses to access the Reports database.

    or

  • /usr/pgsql-15/bin/pg_ctl stop -o "-p portnumber" -U rpdreports -P testpassword -D /aiw/aiw1/data/ -l /aiw/aiw1/trace/postrgres.trace

    Where rpdreports and testpassword are the name and password for the PostgreSQL user and portnumber is the port that RICOH ProcessDirector uses to access the Reports database.

Start the Reports database

  • docker start rpd-reports-postgres

    or

  • pg_ctl start -o "-p portnumber" -U rpdreports

    Where rpdreports is the user name of the PostgreSQL user and portnumber is the port that RICOH ProcessDirector uses to access the Reports database.

    or

  • /usr/pgsql-15/bin/pg_ctl start -o "-p portnumber" -U rpdreports -P testpassword -D /aiw/aiw1/data/ -l /aiw/aiw1/trace/postrgres.trace

    Where rpdreports and testpassword are the name and password for the PostgreSQL user and portnumber is the port that RICOH ProcessDirector uses to access the Reports database.