Postgres command basics

Products

• Carbon Black EDR

Issue/Introduction

Helpful tips on how to utilize Postgres basics when troubleshooting an EDR server

Environment

• Carbon Black EDR Server: Supported Versions

Resolution

1. Logging into Postgres Command Line

   psql -p 5002 cb

2. Output via Terminal

   psql -p 5002 cb -c "<command>"

Command	Use
\dt	List of Available Tables
\dt+	List of Available Tables and Sizes
\d <tablename>	Columns, Types (ints, timestamp, etc), Nullable and Default entries. Also includes Indexes, References and Triggers
\x	Pretty output. Makes the output much easier to read
\q	Quit out of the Postgres Command Line
\copy	Copy table information to csv format. \copy <table> TO 'path_and_filename.csv' WITH CSV HEADER"
\l	Lists Databases
\df	Lists Functions
\di	Lists Indexes
\dn	Lists Schema's
\dv	Lists Views
\du	Lists Users

 

Advanced Commands	Use
select * from pg_indexes where tablename='<table>' and schemaname='public';	Outputs Table Index Definition
select * from pg_size_pretty(pg_total_relation_size('<table'));	Outputs true table size
select * from pg_size_pretty(pg_database_size('cb'));	Outputs full Postgres database size

Additional Information

• Output in readable format to terminal using -x and -c:

  psql -p 5002 cb -x -c "SELECT id,name,search_query FROM watchlist_entries WHERE id = 6"

   

• How to save a table from Postgres as CSV
• Postgres utilizes SQL equivalent commands. https://www.w3schools.com/sql/