Useful PostgreSQL commands
- 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.
Check whether PostgreSQL is running
Linux:
ps -ef | grep postgresIf PostgresSQL is running, you see multiple lines of output. If it is not running, you see nothing.
Windows:
Open the Windows Task Manager. Look for PostgreSQL processes running on the server.
Access PostgreSQL on the primary server
Linux:
/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
Windows:
C:\aiw\aiw1\bin\postgresql\Windows\pgsql\bin\psql databasePostgreSQL_user_name or <RPD Install Directory>\PostgreSQL\bin\pg_psql database PostgreSQL_user_name
For example, if you use the default values for Database and User name on the Database settings page, enter:
C:\aiw\aiw1\bin\postgresql\Windows\pgsql\bin\psql history rpdreports or <RPD Install Directory>\PostgreSQL\bin\pg_psql history rpdreports
Expected output:
psql.bin (9.6.4) Type "help" for help.After accessing PostgreSQL
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 thetest.csv
file in the/aiw/aiw1
directory:COPY job_history TO '/aiw/aiw1/test.csv' DELIMITER ',' CSV HEADER;
- \q
- Exit PostgreSQL.
Stop the PostgreSQL database
Open a command prompt and type this command:
- On a Windows server:
- \aiw\aiw1\bin\postgresql\Windows\pgsql\bin\pg_ctl stop -o "-p 5432" -U rpdreports -P testpassword -D \aiw\aiw1\data -l \aiw\aiw1\trace\postgres.trace
or
- <RPD Install Directory>\PostgreSQL\bin\pg_ctl stop -o "-p 5432" -U rpdreports -P testpassword -D \aiw\aiw1\data -l \aiw\aiw1\trace\postgres.trace
- \aiw\aiw1\bin\postgresql\Windows\pgsql\bin\pg_ctl stop -o "-p 5432" -U rpdreports -P testpassword -D \aiw\aiw1\data -l \aiw\aiw1\trace\postgres.trace
- On a Linux server:
- /aiw/aiw1/bin/postgresql/Linux/pgsql/bin/pg_ctl stop -o "-p 5432" -U rpdreports -P testpassword -D /aiw/aiw1/data/ -l /aiw/aiw1/trace/postrgres.trace
or
- /usr/pgsql-14/bin/pg_ctl stop -o "-p 5432" -U rpdreports -P testpassword -D /aiw/aiw1/data/ -l /aiw/aiw1/trace/postrgres.trace
- /aiw/aiw1/bin/postgresql/Linux/pgsql/bin/pg_ctl stop -o "-p 5432" -U rpdreports -P testpassword -D /aiw/aiw1/data/ -l /aiw/aiw1/trace/postrgres.trace
Start the PostgreSQL database
- On a Windows server:
- \aiw\aiw1\bin\postgresql\Windows\pgsql\bin\pg_ctl start -o "-p 5432" -U rpdreports -P testpassword -D \aiw\aiw1\data -l \aiw\aiw1\trace\postgres.trace
or
- <RPD Install Directory>\PostgreSQL\bin\pg_ctl start -o "-p 5432" -U rpdreports -P testpassword -D \aiw\aiw1\data -l \aiw\aiw1\trace\postgres.trace
- \aiw\aiw1\bin\postgresql\Windows\pgsql\bin\pg_ctl start -o "-p 5432" -U rpdreports -P testpassword -D \aiw\aiw1\data -l \aiw\aiw1\trace\postgres.trace
- On a Linux server:
- /aiw/aiw1/bin/postgresql/Linux/pgsql/bin/pg_ctl start -o "-p 5432" -U rpdreports -P testpassword -D /aiw/aiw1/data/ -l /aiw/aiw1/trace/postrgres.trace
or
- /usr/pgsql-14/bin/pg_ctl start -o "-p 5432" -U rpdreports -P testpassword -D /aiw/aiw1/data/ -l /aiw/aiw1/trace/postrgres.trace
- /aiw/aiw1/bin/postgresql/Linux/pgsql/bin/pg_ctl start -o "-p 5432" -U rpdreports -P testpassword -D /aiw/aiw1/data/ -l /aiw/aiw1/trace/postrgres.trace