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

Where your question has cat <dump sql file> you can put anything you want that prints out SQL commands on stdout; that includes echo DROP DATABASE ....

Remember that you can use an ordinary PostgreSQL client to interact with a PostgreSQL server, regardless of whether or not it's running in Docker. (This does require you to have published the database port externally, usually with a docker run -p5432:5432 option or similar.) You do not need root-level access on the host, or a root shell on the database server, just to run database commands.

This shell command will drop a named database on a PostgreSQL instance running on the current host; or if it's not on the current host or on the default port, you can set PGHOST and PGPORT environment variables.

export PGHOST=localhost
# export PGUSER=<postgres username>
# export PGDATABASE=<database name>
echo "DROP DATABASE $1" | psql

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>;"