pgsql Cheatsheet

One of my recent tasks was the migration of a small spring boot web service to openshift. The webservice works with a postgres which runs in an openshift pod. I had to migrate the database as well.

The psql CLI Tool came in very handy with working with the database. I could get stuff done quickly and work directly on the openshift pods.

Here are my Notes on the psql Tool:

Get help

From the shell:

psql --help

While running the tool:

postgres=# \?

Connect to a database

psql <database name> [--User <username>]

Example:

psql TESTDB --User testuser

Quit psql

postgres=#\q 

list all tables

postgres=#\dt

list all sequences

postgres=#\ds 

list all users on the data base

postgres=#\du

Execute SQL Queries and Scripts

From the command line:

psql <database name> [--User <username> -c <command> 
psql <database name> [--User <Username>] -f <Filename>

From the running psql tool you can just write your sql statements:

postgres=#select * from TESTTABLE; 

Some more command line tools.

Drop a database

Besides the SQL Drop Database Statement postgres offers another cli utility: dropdb

dropdb <connection parameters> <options> <database name>

Create a Data Dump

To backup data the pg_dump tool might come in handy. The pattern for it is the following:

 pg_dump --table=<tablename> --data-only --column-inserts <databasename>