How to delete and recreate a postgres database using a single docker command?

Question

I am writing a microservice application which has a docker container for postgres database. I know that when dumping SQL to database, we use this docker command in terminal:

cat <dump sql file> | docker exec -i <container ID> psql -U <postgres username> <database name>

I was wondering if there is a similar linux terminal docker command that i can run from outside the container to:

1. Create database named

2. Drop database named

Or even:

3. Delete all tables of the to make it completely empty in one command.

Note that i should be able to run the docker command from outside the container through the host OS terminal (linux).

Any suggestions will be appreciated. Thanks in advance.

Answer 1

is the Postgres storage local to the container? If so, then removing the container and recreating it will reset your DB in itself. If your data is mounted from a local or network folder, then reseting it means running commands on psql

You have several options:

• get inside the container with a shell

docker exec -it <container-id> /bin/sh

and then run psql and do whatever you want inside the psql command line.

• run psql directly in docker

docker exec -it <container-id> psql -U <username> -d <database-name>

• have psql installed locally and run it to access the postgres instance in docker

psql -U <username> -h localhost

• run the commands with psql

You cannot DROP and CREATE a database on the same command unfortunately, but you can run 2 separate commands

docker exec -it <container-id> psql -U <username> -d postgres -c "DROP DATABASE <dbname>;"

docker exec -it <container-id> psql -U <username> -d postgres -c "CREATE DATABASE <dbname>;"