Run PostgreSQL as a Docker container : fast/quick play approach

Run a PostgreSQL container from the latest tag and with the default configuration and no volume mounted  (no volume means no data persisted after container removal!) :
docker run --name my-postgres -d postgres

Execute interactive commands on that container with the psql command :
docker exec -ti my-postgres psql -U postgres
Here is the psql official documentation for the version 12.

Run PostgreSQL as a Docker container : specific version, custom postgre configuration and data persistence outside the container

Set the PostgreSQL server configuration of the container

First step : retrieve and store on the host the sample PostgreSQL conf of the image :
docker run -i postgres:15.2 cat /usr/share/postgresql/postgresql.conf.sample > postgresql.conf.sample

Second step : modify the configuration file and run the image with that configuration enabled (bind mount + use the postgres cmd with the configuration file as property) :
docker run -d --name my-postgres -v "$PWD/my-postgresql.conf":/etc/postgresql/postgresql.conf postgres -c 'config_file=/etc/postgresql/postgresql.conf'

Another way to set the database configuration is setting values in the docker command of the started container :
Example :
docker run -d --name flask-postgres -e POSTGRES_DB=flask_db -e POSTGRES_PASSWORD=secret postgres:15.2 -c 'shared_buffers=256MB' -c 'max_connections=200'
Note: POSTGRES_DB is optional, it allows to specify the name of the database to create.

About database, user and password

The default postgres user and database are created in the entrypoint (initdb).
The postgres database is a default database meant for use by users, utilities and third party applications.
That’s why the only mandatory env variable is : POSTGRES_PASSWORD.
While optiona, if we pass the env envariable POSTGRES_USER, its value is used to create the user and the db associated to. Otherwise the default postgres user will be used.

Run the PostgreSQL container by specifying the source of the data volume

Run a PostgreSQL container from a specific tag and with my-postgres-data, the source volume mounted for postgresql data :
docker run --name my-postgres -v my-postgres-data:/var/lib/postgresql/data -d postgres:12.1

Declare a service PostgreSQL in docker-compose

Here we define a specific PostgreSQL version,  a named volume for data (to be able to reuse it outside the current container),  the password, the user (that will be also the db name).
Fix : the password has to be hidden.

  database:
    image: postgres:12.1
    volumes:
      - my-postgres-data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: pass
      POSTGRES_USER: david

Run PostgreSQL as a Docker container managed by the linux services system on the host

Maybe when I would need that…

pg_dump execution form docker container

In some cases, we don’t have access to the server where is installed postgres or we don’t have the exact a client version of server pg_dump.
Docker container may help here.
Without docker we do that to dump a database:
pg_dump -h foo.com --format custom -p 4369 db_name > db_name.dump
To dump a specific schema we do that:
pg_dump -n bar_schema -h foo.com --format custom -p 4369 db_name > db_name.dump

It is a way we do with docker:

export PGUSER="foo_user"
export PGPASSWORD="foo_password"
export PGHOST=foo.com
export PPORT=1777
# All
docker run -it  -e PGPASSWORD=$PGPASSWORD -e PGUSER=$PGUSER -e PGHOST=$PGHOST bitnami/postgresql:15.6.0 pg_dump --format p db_name > airflow_db.dump
# Specific schema
docker run -it  -e PGPASSWORD=$PGPASSWORD -e PGUSER=$PGUSER -e PGHOST=$PGHOST bitnami/postgresql:15.6.0 pg_dump -n airflow --format p db_name > airflow_db.dump

Import dump:

docker rm -f postgres_dump; docker run -d -ti --entrypoint bash --name=postgres_dump bitnami/postgresql:15.6.0 
docker cp airflow_db.dump postgres_dump:/tmp
docker exec -e PGPASSWORD=$PGPASSWORD -e PGUSER=$PGUSER -e PGHOST=$PGHOST -e PGPORT=$PGPORT -it postgres_dump  psql db_name -f "/tmp/airflow_db.dump"