squeakywheel/postgres

By squeakywheel

Updated over 4 years ago

Experimental postgresql images for Ubuntu, based on https://github.com/docker-library/postgres

Image

1.1K

Postgres | Ubuntu

About Postgres

PostgreSQL is a powerful, open source object-relational database system. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation. Read more on the PostgreSQL website.

Tags

  • 12.4-focal, 12.4, 12-focal, 12, beta - /!\ this is a beta release
Architectures supported
  • amd64, arm64, s390x

Usage

Docker CLI
$ docker run -d --name postgres-container -p 30432:5432 -e TZ=UTC -e POSTGRES_PASSWORD=My:s3Cr3t/ squeakywheel/postgres:edge

Access your Postgres server at localhost:30432.

Parameters

ParameterDescription
-e TZ=UTCTimezone.
-e POSTGRES_PASSWORD=secretSet the password for the superuser which is postgres by default. Bear in mind that to connect to the database in the same host the password is not needed but to access it via an external host (for instance another container) the password is needed. This option is mandatory and must not be empty.
-e POSTGRES_USER=johnCreate a new user with superuser privileges. This is used in conjunction with POSTGRES_PASSWORD
-e POSTGRES_DB=db_testSet the name of the default database.
-e POSTGRES_INITDB_ARGS="--data-checksums"Pass arguments to the postgres initdb call.
-e POSTGRES_INITDB_WALDIR=/path/to/locationSet the location of the Postgres transaction log. By default it is stored in a subdirectory of the main Postgres data folder (PGDATA).
-e POSTGRES_HOST_AUTH_METHOD=trustSet the auth-method for host connections for all databases, all users, and all addresses. The following will be added to the pg_hba.conf if this option is passed: host all all all $POSTGRES_HOST_AUTH_METHOD.
-e PGDATA=/path/to/locationset the location of the database files. The default is /var/lib/postgresql/data.
-p 30432:5432Expose Postgresql server on localhost:6032.
-v /path/to/postgresql.conf:/etc/postgresql/postgresql.confPass your own postgresql.conf configuration file (download this example file).
-v /path/to/persisted/data:/var/lib/postgresql/dataPersist data instead of initializing a new database every time you launch a new container.

Initialization Scripts

One can also add initialization scripts to their containers. This includes *.sql, .sql.gz, and *.sh scripts, and you just need to put them inside the /docker-entrypoint-initdb.d directory inside the container. After Postgres initialization is done and the default database and user are created, the scripts are executed in the following order:

  • Run any *.sql files in alphabetically order. It will be executed with POSTGRES_USER.
  • Run any executable *.sh scripts in alphabetically order.
  • Source any non-executable *.sh scripts in alphabetically order.

All of this is done before the Postgres service is started. Keep in mind if your PGDATA directory is not empty (contains pre-existing database) they will be left untouched.

Testing/Debugging

In case you need to debug what it is happening with the container you can run docker logs <name_of_the_container>. To get access to an interactive shell run:

$ docker exec -it <name_of_the_container> /bin/bash

With this same image, you can launch an interactive container as a client to connect to your postgresql server running in the first container.

$ docker network create postgres-net
$ docker network connect postgres-net postgres-container
$ docker run -it --rm --network postgres-net squeakywheel/postgres:edge psql -h postgres-container -U postgres

The password will be asked and you can enter My:s3Cr3t/. Now, you are logged in and can enjoy your new instance.

Deploy with Kubernetes

You can use your favorite Kubernetes distribution; if you don't have one, consider installing MicroK8s.

With microk8s running, enable the dns and storage add-ons:

$ microk8s enable dns storage

Create a configmap for the configuration files (download a self-documented postgresql.conf file here):

$ microk8s kubectl create configmap postgres-config --from-file=main-config=config/postgresql.conf

Use the sample deployment yaml provided here.

Apply the `postgres-deployment.yml` (click to expand)
# postgres-deployment.yml
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-volume-claim
spec:
  accessModes:
    - ReadWriteOnce
  storageClassName: microk8s-hostpath
  resources:
    requests:
      storage: 500M
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
      - name: postgres
        image: squeakywheel/postgres:edge
        env:
        - name: POSTGRES_PASSWORD
          value: "My:s3Cr3t/"
        volumeMounts:
        - name: postgres-config-volume
          mountPath: /etc/postgresql/postgresql.conf
          subPath: postgresql.conf
        - name: postgres-data
          mountPath: /var/lib/postgresql/data
        ports:
        - containerPort: 5432
          name: postgres
          protocol: TCP
      volumes:
        - name: postgres-config-volume
          configMap:
            name: postgres-config
            items:
            - key: main-config
              path: postgresql.conf
        - name: postgres-data
          persistentVolumeClaim:
            claimName: postgres-volume-claim
---
apiVersion: v1
kind: Service
metadata:
  name: postgres-service
spec:
  type: NodePort
  selector:
    app: postgres
  ports:
  - protocol: TCP
    port: 5432
    targetPort: 5432
    nodePort: 30432
    name: postgres
$ microk8s kubectl apply -f postgres-deployment.yml

You will now be able to connect to the Postgres server on localhost:30432.

Bugs and Features request

If you find a bug in our image or want to request a specific feature file a bug here:

https://bugs.launchpad.net/ubuntu-docker-images/+filebug

In the title of the bug add postgres: <reason>.

Make sure to include:

  • The digest of the image you are using, you can find it using this command replacing <tag> with the one you used to run the image:
$ docker images --no-trunc --quiet squeakywheel/postgres:<tag>
  • Reproduction steps for the deployment
  • If it is a feature request, please provide as much detail as possible

Docker Pull Command

docker pull squeakywheel/postgres