DEV Community

taijidude
taijidude

Posted on

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
Enter fullscreen mode Exit fullscreen mode

While running the tool:

postgres=# \?
Enter fullscreen mode Exit fullscreen mode

Connect to a database

psql <database name> [--User <username>]
Enter fullscreen mode Exit fullscreen mode

Example:

psql TESTDB --User testuser
Enter fullscreen mode Exit fullscreen mode

Quit psql

postgres=#\q 
Enter fullscreen mode Exit fullscreen mode

list all tables

postgres=#\dt
Enter fullscreen mode Exit fullscreen mode

list all sequences

postgres=#\ds 
Enter fullscreen mode Exit fullscreen mode

list all users on the data base

postgres=#\du
Enter fullscreen mode Exit fullscreen mode

Execute SQL Queries and Scripts

From the command line:

psql <database name> [--User <username> -c <command> 
psql <database name> [--User <Username>] -f <Filename>
Enter fullscreen mode Exit fullscreen mode

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

postgres=#select * from TESTTABLE; 
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

Top comments (0)