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:
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: