Use docker-compose to run PostgreSQL

Use docker to run PostgreSQL is handy.

First, prepare docker-compose.yml file:

Notice that any file put into docker-entrypoint-initdb.d folder, will be executed automatically while database is initialized.

The init.sql will create a simple table:

\c mydb;
CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(128) NOT NULL
);

And data of the database will be persisted in data folder under current directory, since we set an environment variable and a volume like:

environment:      
  PGDATA: /var/lib/postgresql/data/pgdata    
volumes:
  - "./data:/var/lib/postgresql/data"

To start, just run:

docker-compose up
# or add -d flag:
# docker-compose up -d

You can see the logs indicate that both database and table has been created:

Now, let’s try to enter the container and run some psql commands.

docker exec -it psqldb bash
psql -U admin -d mydb

Show tables and describe the table created by init.sql.

\dt

\d products

Seems that everything is good.

If you prefer using UI to manipulate the database, pgadmin maybe one of your choice.

First, you need to create server:

Give it a name whatever you like:

Enter the hostname, username and password. The username and password is defined in the docker-compose.yml file.

After save, you should be able to see your database and table in the left penal:

Finally, if you would like to stop the database, run:

docker-compose down

If you need to remove the database and all data stored in the database, run commands below and clean the pgdata directory which is mounted as a volume.

docker-compose rm

Related code could be found in Github.