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

49

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

answered Dec 30, 2018 at 22:59

MrE

21.1k14 gold badges92 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ᴹᴵᴷᴴᴬᴵᴸ ᴹᴼᴿᴳᵁᴺᴼᵛ Over a year ago

@sshussain270 please change accepted answer :) to this ;)

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

5

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

edited Jan 18, 2021 at 12:17

answered Jan 15, 2021 at 12:07

dezz

511 silver badge4 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related