Quering PostgreSQL with Docker

Recently I had to write a bash script to truncate all tables on a test database server. Using pgadmin is a real pain since I have at lot of tables and databases. In addition, I need to do this quite frequently.

And I also didn’t want to install postgres on my machine. The requirement is so small and a full installation is not necessary.

Using PostgreSQL client with Docker

Then I found a docker image that contains only the psql client (here)! You can run almost any commands in psql using this image. Here is an example:

docker run -it --rm jbergknoff/postgresql-client postgresql://user:pass@host:5432/db

For example, if you want to run a select, simply type:

docker run -it --rm jbergknoff/postgresql-client postgresql://user:pass@host:5432/db -c "select * from table"

One drawback of this image is it has paging and I couldn’t reuse the image. I need to use docker run every time I want to query.

For my need, I would like something that I can start a container once and run SQL commands many times.

PostgreSQL client docker image improved

So I created a new docker image based on the above image that you can download here:

https://hub.docker.com/r/codingpuss/postgres-client

Similarly, run

docker run -dit --network=local_net --name=pgclient codingpuss/postgres-client

To create a container. Now, you can reuse it as many time as you like to query from any PostgreSQL database like this:

 docker exec -it pgclient psql postgresql://postgres:1@pgdb:5432/postgres -c 'select * from pg_catalog.pg_tables'

And here is the result:

querying postgres database using client in docker

Leave a comment

Your email address will not be published. Required fields are marked *