DEV Community

taijidude
taijidude

Posted on

3

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

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more