Some times there is a situation when you need to make a database dump and use it on the another setup. I my case I want to explore table relationships in metabase database. So my current metabase running in docker container on EC2 instance. This manual with provide some steps on how to create a postgres database dump that metabase is using and load it locally and explore it with a free tool called DBeaver.
- Step. list your running docker containers with docker ps command.
- Locate postgres metabase database
- Log into postgres docker container using postgres as a user name
docker exec -it <your docker container id> psql -U postgres -d postgresto list all available databases use \l command you should see something similar to this
you can display list of all relational tables with \dt command
We can explore some tables for example metric with SELECT COUNT(*) FROM metric; will display number of rows in metric table.
to log in as a root user to the docker container you can use the folllowing comamnd docker exec -it –user root <container id> bin/bash or you can log in as postgres user docker exec -it –user postgres <container id> bin/bash
when you log in as a root you can create folders but can’t execute commands to create a dump database
so the next step is to log in as a root user and create a new file db (cd home and touch db) in home directory and change file permission to 777 (chmod 777 db) and after that you can log in as a postgres user and create db dump with the following two commands 1(cd home) and 2.(pg_dump postgres > db) where postgres is the database name you create a dump from and db is a file name where the dump will be saved, after that you need to exit postgres with \q command and check the file with cat db command.
Now you need to exit this docker container and copy the dump file from the container to your EC2 instance storage. To copy file from docker container to EC2 you can use the following command docker cp <container id>:/home/db ~/new_location/folder/ in my case it was docker cp d544rir839:/home/db ~/new_matabase
The next step would be copy this file from EC2 to your local machine with the following command. scp -i <you pem file> ec2-user@ip:~/path/to/file /path/local/machine/ for example in y case it was the following command scp -i key.pem ec2-user@10.99.**.**:~/new_metabase/db /User/stan/Downloads
The next step is recreate the database locally running this command psql -d metabase -f /path/to/dump this command will create a local database with name metabase (make sure your machine has postgres install I use PostgreSQL12 on my mac). Next step is to install DBeaver and create connection to your local postgres.
Default login and password are postgres. After conencting you should see new database called metabase.
You can create a ER diagram using DBeaver functionality.
Add name and select metabase db and click finish.
You will see ER diagram.
Now you can explore the metabase db and understand relations between tables.
