How to use Postgres PSQL Command

Products

• CA Application Performance Management (APM / Wily / Introscope)

Issue/Introduction

Here are some instructions to get started using simple commands with the PSQL command in Postgres.

PSQL is a command provided by Postgres database which allows running SQL command to retrieve information and data from Postgres

Environment

Postgres Database

Resolution

To find the Postgres PSQL, you will need to locate where you have installed the Postgres DB

Windows:  <PostgreSQL>\bin\psql.exe

Linux:  <PostgreSQL>/bin/./psql

Change your path to the correct folder.

To get started, you have to run the command with switches telling Postgres what you are trying to do:

psql -U username -D database

example for APM - psql -U postgres -d cemdb press enter or (linux) ./psql -U postgres -d cemdb

You will be asked to enter the password that you assigned when installing the Postgres Database.  (APM does not store any record of this password.)

sample commands:

List Databases

\list

List all the table in the current database

SELECT table_name FROM information_schema.tables WHERE table_schema='public' ;

List Table sizes

SELECT relname,relfilenode,relpages,(relpages*8/(1024*1024)) as disk_space_in_GB FROM pg_class ORDER BY relpages DESC;

List Database sizes

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database; 

Quit out of the psql command

\q 

You can also run the Select, Update, Insert and Delete commands for table manipulations but finish the command line with a ;

 

 

Additional Information

Here are some external links that can help you do more with this command

17 Practical psql Commands That You Don’t Want To Miss

psql command line tutorial and cheat sheet