Tricks for Postgres and Docker that will make your life easier
Tags: docker, database, sql, postgres
Published: 2019-09-02 15:06:27

_Note: This was originally posted at [martinheinz.dev](https://martinheinz.dev/blog/3)_

Nowadays, everybody is trying to run everything in container and I don't blame them, I do the same thing, because running applications,
databases or other tools in Docker container is super nice and we all know why (isolation, easy setup, security...).
However, sometimes debugging, accessing, or in general interacting with containers can be pretty annoying. This includes accessing, modifying or querying databases. So, as I used PostgreSQL extensively and have been running it inside containers for a while now, I - over time - made a list of few commands that can help immensely when doing simple and also not so simple operation with a database server.

### Log Into PSQL

The most basic thing you will always need to do if you want to interact with your database server is to connect to the database itself (using _PSQL_):

```shell
docker exec -it <container_name> psql -U<user_name> -a <db_name>
```

So for Docker container called `db`, default user `postgres` and database name `blog` it would be

```shell
docker exec -it db psql -Upostgres -a blog
```

### Running Command Against Database

It's nice that you can log in and then execute whatever commands you need, but it's often more convenient to do it in one go, especially if you want to run just a single command or query:

```shell
docker exec -it <container_name> psql -U<user_name> -a <db_name> -c '<command/query>'
```

So if we wanted to list all tables in database using same parameters as in previous example:

```shell
docker exec -it db psql -Upostgres -a blog -c '\l'
```

Here, `\l` lists all tables in current database, if you are not familiar with _psql_ "backslash" commands, then I highly recommend this [cheatsheet](https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546).

Apart from `psql` commands you can run any SQL query like so:

```shell
docker exec -it db psql -Upostgres -a blog -c 'SELECT * FROM posts;'
```

### Backing up Your Data
From time to time I need to backup data or whole schema of the database, sometimes just as an _"insurance policy"_ and sometimes so I can make changes recklessly and restore everything afterwards, so here how to do it:

```shell
docker exec -it <container_name> pg_dump -U<user_name> --column-inserts --data-only <db_name> > backup.sql
```

In this example, we are making use of `pg_dump` utility, which allows us to extract PostgreSQL databases. I'm using `--column-inserts` and `--data-only` flags to get only table rows, but quite often all that is needed is schema, for that you can use `-s` flag.

### Execute whole SQL files

Sometimes you need to populate existing database with enough data for testing (please don't do this with production databases) or it's just easier to use data from file then to copy and paste them into command above.

```shell
docker cp ./data.sql <container_name>:/data.sql
docker exec -it <container_name> psql -U<user_name> -a <db_name> -f /data.sql
```

Here we first need to copy the file itself into the running container and then execute it using the `-f` flag.

### Prepopulating Database on the Start

Previous example was good enough if you need to execute it from time to time, but it can become annoying if you have to do it every time you start the database.
So, in case you decide that it's better to just populate the database on the start, then here is solution for that. It just requires little more work:

We will need following files:

- `Dockerfile` - _Dockerfile_ for your Postgres image
- `create_db.sh` - Script that creates database, schema and populates it.
- `schema.sql` - SQL file containing your database schema
- `data.sql` - SQL file containing data used to populate your database
- `.env` - File with environment variables, to make your life easier

First, the `Dockerfile`:

```dockerfile
FROM postgres:11

# Custom initialization scripts
COPY ./create_db.sh /docker-entrypoint-initdb.d/20-create_db.sh
COPY schema.sql /schema.sql
COPY data.sql /data.sql

RUN chmod +x /docker-entrypoint-initdb.d/20-create_db.sh
```

This is very simple _Dockerfile_, all we need to do here is to copy our script and schema/data into the image so they can be on run start-up. You may be asking, _There is no `ENTRYPOINT` or `COMMAND`, how do we run it on start-up?_ - the answer is, that base `postgres` image runs on start any scripts present in `docker-entrypoint-initdb.d` directory, so all we need to do is copy our script to this directory and PostgreSQL takes care of it.

But what is in the script (`create_db.sh`)?

```shell
#!/bin/bash
set -e

POSTGRES="psql --username ${POSTGRES_USER}"

echo "Creating database: ${DB_NAME}"

$POSTGRES <<EOSQL
CREATE DATABASE ${DB_NAME} OWNER ${POSTGRES_USER};
EOSQL

echo "Creating schema..."
psql -d ${DB_NAME} -a -U${POSTGRES_USER} -f /schema.sql

echo "Populating database..."
psql -d ${DB_NAME} -a  -U${POSTGRES_USER} -f /data.sql

```

Start-up script first logs into `psql` with specified username (`POSTGRES_USER`), then it creates your database (`DB_NAME`). Next it create database schema using file we copied into the image and finally it populates the database with data. All the variables here are coming from the `.env` file mentioned before, which makes it very easy to change your database name or username at any time without modifying script itself.

For more complete example please see my repository [here](https://github.com/MartinHeinz/blog-backend/tree/master/postgres)

### What About `docker-compose`?

In my experience, most of the time I end up running database in conjunction with the application that is using it and the simplest way to do it is _docker-compose_. Usually I prefer to refer to the _docker-compose_ service by service name, rather then container name which might or might not be the same thing. In case it isn't
same, you can just following command:

```shell
docker exec -it $(docker-compose ps -q <db_service_name>) psql -U<user_name> -a <db_name>
```

Only real difference here from the previous examples is the `docker-compose` part, which looks up information of the specified service. The `-q` flag make it so, that only container IDs are displayed, which is all we need.


### Conclusion

I hope at least some of these little hacks will make your life easier when dealing with Docker and PostgreSQL or maybe if you were avoiding Docker just because it might be little annoying when working with databases, then I hope you will give it a shot after reading this article. &#x1F642;