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:

Create database named
Drop database named

Or even:

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.

MrE · Accepted Answer · 2018-12-30 22:59:38Z

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

David Maze · Accepted Answer · 2018-12-30 01:04:53Z

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

dezz · Accepted Answer · 2021-01-18 12:17:34Z

A bit ugly, but one liner is also possible :

Find out id of docker container

docker ps | grep postgres | awk '{print $1}'

Paste it in command from post above

docker exec -it $(docker ps | grep postgres | awk '{print $1}') psql -U postgres -d postgres -с $command

We cannot DROP and CREATE a database on the same command, but we can run two times using for loop in bash )

for command in 'delete dbname' 'create dbname'; do echo $command; done

Finally let's compile it together in one liner:

for command in 'drop database metrics;' 'create database metrics;'; do docker exec -it $(docker ps | grep postgres | awk '{print $1}') psql -U postgres -d postgres -c $command; done

PS 'metrics' - database name just an example and you should use yours :)

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related