OFFICIAL REPOUpdated 2 days, 21 hours ago

postgres

The PostgreSQL object-relational database system provides reliability and data integrity.
0531031569

Supported tags and respective Dockerfile links

For more information about this image and its history, please see the relevant manifest file (library/postgres) in the docker-library/official-images GitHub repo.

What is PostgreSQL?

PostgreSQL, often simply “Postgres”, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data, securely and supporting best practices, and retrieve it later, as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). It can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users. Recent versions also provide replication of the database itself for security and scalability.

PostgreSQL implements the majority of the SQL:2011 standard, is ACID-compliant and transactional (including most DDL statements) avoiding locking issues using multiversion concurrency control (MVCC), provides immunity to dirty reads and full serializability; handles complex SQL queries using many indexing methods that are not available in other databases; has updateable views and materialized views, triggers, foreign keys; supports functions and stored procedures, and other expandability, and has a large number of extensions written by third parties. In addition to the possibility of working with the major proprietary and open source databases, PostgreSQL supports migration from them, by its extensive standard SQL support and available migration tools. And if proprietary extensions had been used, by its extensibility that can emulate many through some built-in and third-party open source compatibility extensions, such as for Oracle.

wikipedia.org/wiki/PostgreSQL

logo

How to use this image

start a postgres instance

docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

This image includes EXPOSE 5432 (the postgres port), so standard container linking will make it automatically available to the linked containers. The default postgres user and database are created in the entrypoint with initdb. > The postgres database is a default database meant for use by users, utilities > and third party applications. > postgresql.org/docs

connect to it from an application

docker run --name some-app --link some-postgres:postgres -d application-that-uses-postgres

… or via psql

docker run -it --link some-postgres:postgres --rm postgres sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'

Environment Variables

The PostgreSQL image uses several environment variables which are easy to miss. While none of the variables are required, they may significantly aid you in using the image.

POSTGRES_PASSWORD

This environment variable is recommend for you to use the PostgreSQL image. This environment variable sets the superuser password for PostgreSQL. The default superuser is defined by the POSTGRES_USER environment variable. In the above example, it is being set to “mysecretpassword”.

POSTGRES_USER

This optional environment variable is used in conjunction with POSTGRES_PASSWORD to set a user and its password. This variable will create the specified user with superuser power and a database with the same name. If it is not specified, then the default user of postgres will be used.

How to extend this image

If you would like to do additional initialization in an image derived from this one, add a *.sh script under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will source any *.sh script found in that directory to do further initialization before starting the service. If you need to execute SQL commands as part of your initialization, the use of Postgres'' single user mode is highly recommended.

You can also extend the image with a simple Dockerfile to set the locale. The folowing example will set the default locale to de_DE.utf8:

FROM postgres:9.4
RUN localedef -i de_DE -c -f UTF-8 -A /usr/share/locale/locale.alias de_DE.UTF-8
ENV LANG de_DE.utf8

Since database initialization only happens on container startup, this allows us to set the language before it is created.

Caveats

If there is no database when postgres starts in a container, then postgres will create the default database for you. While this is the expected behavior of postgres, this means that it will not accept incoming connections during that time. This may cause issues when using automation tools, such as fig, that start several containers simultaneously.

Supported Docker versions

This image is officially supported on Docker version 1.6.0.

Support for older versions (down to 1.0) is provided on a best-effort basis.

User Feedback

Documentation

Documentation for this image is stored in the postgres/ directory of the docker-library/docs GitHub repo. Be sure to familiarize yourself with the repository's REAMDE.md file before attempting a pull request.

Issues

If you have any problems with or questions about this image, please contact us on the mailing list or through a GitHub issue.

You can also reach many of the official image maintainers via the #docker-library IRC channel on Freenode.

Contributing

You are invited to contribute new features, fixes, or updates, large or small; we are always thrilled to receive pull requests, and do our best to process them as fast as we can.

Before you start to code, we recommend discussing your plans on the mailing list or through a GitHub issue, especially for more ambitious contributions. This gives other contributors a chance to point you in the right direction, give you feedback on your design, and help you find out if someone else is working on the same thing.

Comments

1 month ago
profile pictureyellowiscool

@glamm Thank you for your script, it works fine.

This is my dockerfile:

FROM postgres:9.4
ADD init-database.sh /docker-entrypoint-initdb.d/
ADD db.pgdump /tmp/db.pgdump
1 month, 2 weeks ago
profile picturemiranext

Anyone know whats the way to properly stop this container when running docker stop? Just noticed this LOG: database system was interrupted; last known up at 2015-03-07 04:17:41 UTC LOG: database system was not properly shut down; automatic recovery in progress

1 month, 3 weeks ago
profile pictureglamm

For those looking to import a database as part of an init script dropped into /docker-entrypoint-initdb.d, I've found that starting postgres, performing the import via pg_restore, then stopping the database works (please forgive the terrible shell scripting):

#!/bin/bash

: ${DB_USER:=db_user}
: ${DB_PASSWORD:=db_pass}
: ${DB_NAME:=db_name}
: ${DB_ENCODING:=UTF-8}
: ${DB_PG_DUMP_FILE:=/tmp/db.pgdump}

{ gosu postgres postgres --single -jE <<-EOSQL
    CREATE USER "$DB_USER" WITH PASSWORD '$DB_PASSWORD';
EOSQL
} && { gosu postgres postgres --single -jE <<-EOSQL
    CREATE DATABASE "$DB_NAME" WITH OWNER="$DB_USER" TEMPLATE=template0 ENCODING='$DB_ENCODING';
EOSQL
} && { gosu postgres pg_ctl start -w && gosu postgres pg_restore -d "$DB_NAME" "$DB_PG_DUMP_FILE" && gosu postgres pg_ctl stop -w
} && /bin/rm -f ${DB_PG_DUMP_FILE}
2 months, 1 week ago
profile pictureoren432

How can I run sql scripts on it? Opened issue https://github.com/docker/docker/issues/10823.

2 months, 2 weeks ago
profile picturetrevmurray

How do I properly restart the postgres service using this container?

2 months, 3 weeks ago
profile picturedlouwers

Was having trouble importing a postgres dump as part of the build.

Using something like gosu postgres postgres --single < dumpfile.dump will cause all matter of syntax errors. Starting the container detached, execing bash on it and then running psql -U postgres < dumpfile.dump works fine.

Hope someone has run into this issue before and was able to fix it.

2 months, 4 weeks ago
profile pictureufoscout

Could you please help me to configure this container to be accessible from my machine? I started it with: docker run --name some-app --link some-postgres:postgres -d application-that-uses-postgres but when I always get connection refused. I also tried to use “-p 5432:5432” but I cannot connect with telnet to the port.

3 months, 1 week ago
profile picturedebiki

I, and someone else below, was wondering about how to upgrade between major versions. Here's a GitHub issue about that: https://github.com/docker-library/postgres/issues/37

3 months, 2 weeks ago
profile picturedkirrane

Is there any a way to restore a database from a dump file during docker build or run

3 months, 3 weeks ago
profile pictureaventurini

@matthiasblankenhaus Use a separate docker container to extract logs, e.g. progrium/logspout. It automatically attaches to all running containers on the host and routes log outputs to a destination of your choice.

4 months, 1 week ago
profile picturematthiasblankenhaus

Hi !

I am curious about how people handle postgresql logging with this container ? Is there a best practice approach ?

4 months, 2 weeks ago
profile picturegflarity

Could you please describe the best way to import a database? I have a dump file that works fine with psql, but doesn't seem to work with postgres single mode…

4 months, 3 weeks ago
profile picturejoriskoris

What is the method to backup the database? I currently have a data only container with /var/lib/postgresql/data volume, and I mount this container in the main database container using –volumes-from directive. I'd like to be able to dump and pg_restore the database.

4 months, 3 weeks ago
profile pictureaidanlister

Here's a simple extension of this official base image which enables the hstore module during the build: https://registry.hub.docker.com/u/aidanlister/postgres-hstore/

5 months ago
profile picturetransistor1

From @itech's comment:

Simply once you expose the PostgreSQL port, anyone in the world (who can connect to this port on your server) will have FULL access to the database with no password whatsoever!

Someone please correct me if I'm wrong, but I thought that if you use container linking, the port security isn't an issue, because the containers can only see each other, and the PostgreSQL port isn't actually exposed. My understanding is that this is how a postgres backend should be run on Docker (obviously it won't be possible in every scenario).

e.g.:

docker run -d -p 80:80 --link postgres:postgres apache

In which case, the apache server can see the postgres server, but it can't be seen outside of the apache container.

I am not trying to minimize what @itech is saying – it is absolutely true that you will compromise security if you expose port 5432 without setting a password. But it should probably be noted that container linking might be a better solution, if it's possible in a deployment case.

5 months ago
profile picturemmarzantowicz

@magick: There are two possibilities: you can run postgres container without specifying any username/password or you can provide them with -e (–env) option to docker run command.

In first case (no username/password) you shouldn't have been asked any password because by default trust authentication is in action and no password is set for postgres user.

In second case, you can explicitly specify POSTGRES_PASSWORD and POSTGRES_USER and then you must use them because authentication method is set to md5. Below is sample command line with options:

docker run --name some-postgres -d -e "POSTGRES_PASSWORD=somesecret" postgres
5 months ago
profile picturemagick

I'm following the advice from @justfalter on how to “automatically execute SQL commands as part of starting up”, and it seems fine. However when I go to log into the postgres shell, it is asking for a password. Where is this set? How do I know what it is?

Thanks

5 months, 1 week ago
profile picturemmarzantowicz

One more note about “fixing” default authentication methods. One can use sed to change line which allows trust authentication to use md5 instead. Maybe it could be done in more sophisticated way but this one works for me:

sed -ri "s/host all all 0.0.0.0\/0 trust/host all all 0.0.0.0\/0 md5/" "$PGDATA"/pg_hba.conf

As in @justfalter's recipe, create script and COPY it to /docker-entrypoint-initdb.d/ directory. Remember to set some valid user/password before to be able to use your postgres container.

5 months, 2 weeks ago
profile pictureitech

Anyone using this image MUST be very careful because the default configuration is INSECURE.

Simply once you expose the PostgreSQL port, anyone in the world (who can connect to this port on your server) will have FULL access to the database with no password whatsoever!

Related to this issue: https://github.com/docker-library/postgres/issues/31

5 months, 4 weeks ago
profile picturejustfalter

By default, this image disables any requirement for authentication. The '/docker-entrypoint.sh' script automatically adds the following line to /var/lib/postgres/data/pg_hba.conf:

host all all 0.0.0.0/0 trust

Since docker-entrypoint.sh modifies this file when the container is first started, you cannot simply ADD your desired pg_hba.conf from your Dockerfile. Rather, you need to create a script that performs the modifications, and ADD it to '/docker-entrypoint-initdb.d/' directory via your Dockerfile.

host all all 0.0.0.0/0 md5

For example, here's a script, “fix-acl.sh”, that overwrites pg_hba.conf:

#!/bin/bash
cat > /var/lib/postgresql/data/pg_hba.conf <<EOS
# Generated by fix-acl.sh
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

# Allow anyone to connect remotely so long as they have a valid username and 
# password.
host all all 0.0.0.0/0 md5
EOS

Add it to your Dockerfile:

FROM postgres:9.3
ADD fix-acl.sh /docker-entrypoint-initdb.d/

When the image is built, your pg_hba.conf will be set to the desired configuration.

5 months, 4 weeks ago
profile picturejustfalter

For those looking for a concrete example on how to automatically execute SQL commands as part of starting up this docker image, I found the following useful: http://stackoverflow.com/questions/26598738/how-to-create-user-database-in-script-for-docker-postgres.

Create a shell script “setup-my-schema.sh”:

#!/bin/bash
echo "******CREATING DOCKER DATABASE******"
gosu postgres postgres --single <<- EOSQL
   CREATE DATABASE docker;
   CREATE USER docker;
   GRANT ALL PRIVILEGES ON DATABASE docker to docker;
EOSQL
echo ""
echo "******DOCKER DATABASE CREATED******

And a Dockerfile:

FROM postgres:9.3
ADD setup-my-schema.sh /docker-entrypoint-initdb.d/

Build your image

docker build -t pg_with_my_stuff .

And run it:

docker run -d --name my_postgres -p 5432:5432 pg_with_my_stuff

You can then see that the script ran given the output of 'docker logs my_postgres':

******CREATING DOCKER DATABASE******

PostgreSQL stand-alone backend 9.3.5
backend> backend> backend> backend> 
******DOCKER DATABASE CREATED******

Now you can connect to the new database you've created:

$ psql -h 127.0.0.1 -U docker docker
psql (9.3.5)
Type "help" for help.

docker=>
6 months ago
profile picturedperetti

@mekanix … this is a container. It's only accessible from another dockerized app that has been purposely linked to it.

6 months, 1 week ago
profile picturedockerhubby

All the so called “official” images I checked are not ready for productive use - almost all of them have serious problems or totally stupid defaults or some other issue. I come to the conclusion that this docker hub is a place for distributing worst practices and should generally be avoided or could, at best, be a place to learn how not to do it. It is a real shame and does absolutely not fit the “we are cool devops masters”- image that docker guys want to show. Very disapponting. There should be some quality guys doing quality images, not hobbyists publishing every BS.

6 months, 1 week ago
profile picturemekanix

What is the best practice for using this image? As postgres user can be logged in without password, it's not really secure to run it on a host being DB server. How to overcome that?

6 months, 3 weeks ago
profile picturecdancy

Anyone have any recommendations on how to edit the postgresql.conf file? I'm installing pljava and need to add variables to this file. Any help would be appreciated. Thanks

7 months, 2 weeks ago
profile pictureplindelauf

Can someone explain what the de facto standard is for upgrading from one “Dockerized” version of PostgreSQL (e.g. 9.2) to another (e.g. 9.3)?

7 months, 2 weeks ago
profile picturelrhazi

found it: https://github.com/docker-library/postgres

7 months, 2 weeks ago
profile picturelrhazi

Where is the Dockerfile?

7 months, 2 weeks ago
profile picturesalamandra

I agree with jamesstarr: It should be a link to GIT in each repository description. There always someone asking for it on comments.

7 months, 3 weeks ago
profile picturelatkins

Could someone clarify the correct way to increase the log level using this container? If I were running postgres from the command line, I might do something like -d 1.

8 months, 2 weeks ago
profile picturejoshjdevl

Why is this an official container when

1) It uses wheezy which is not LTS 2) It doesn't link to Github providing a Trusted Build

8 months, 3 weeks ago
profile picturehoni

I've posted one solution to fix the “could not create lock file / permission denied” error here.

8 months, 3 weeks ago
profile picturehoni

Thanks @miquella for the locale tip, I've put that in my Dockerfile and it works as expected.

Though I'm having another problem using postgres:9.4 I'm getting this error and postgres won't start. I'm using boot2docker (v1.1.2).

FATAL: could not create lock file “/var/run/postgresql/.s.PGSQL.5432.lock”: Permission denied

Does anybody have any ideas?

8 months, 4 weeks ago
profile picturejamesstarr

The repo is here:https://github.com/docker-library/postgres

I really wish it was in the description, or if hub.docker.com supported some kind of linking back to github.

9 months ago
profile picturebromanko

I am running into the same issue as @kcartmell. Is there any advice on this issue?

9 months, 1 week ago
profile pictureblasthardcheese

@pondidum: As @georged said earlier, the container contains a “contrib” directory. I created a Dockerfile containing the following:

FROM postgres

RUN apt-get update && apt-get install -y libossp-uuid-dev

RUN ./configure --with-ossp-uuid
RUN cd contrib/uuid-ossp && make && make install && cd ../..

After that, CREATE EXTENSION works.

9 months, 1 week ago
profile picturekolypto

Currently, the size of this image is 803.9 MB

9 months, 2 weeks ago
profile picturekcartmell

I've noticed that the initdb invocation in docker-entrypoint.sh doesn't always succeed on the first try when running in virtualbox (boot2docker) or on an Amazon Web Services EC2 instance running ubuntu 14.04. When the issue occurs, a call to gosu whoami returns postgres but a call to id returns root. If I just change the script to keep trying initdb, it seems to always work on the second try. I'm only using the postgres docker image for development instances at this time so I'm not super excited about heading down this rabbit hole, but if someone smarter than me would like to have a conversation or at least receive a proper bug report, I'd be happy to help.

9 months, 2 weeks ago
profile pictureyosifkit

Just a big FYI for anyone looking at the Official Images: you can contribute improvements to make them better. Please, don't go making a different image just because this does not have feature X or needs an update. Pull requests are always welcome. https://github.com/docker-library/postgres

9 months, 3 weeks ago
profile picturebaboon

Is there a way to customize the exposed port?

10 months ago
profile picturebase698

I ran the postgres container interactive and discovered however postgres is setup it wasn't with apt-get (as I guess it wouldn't be). I am trying to install postgis. Anywhere I can get that?

10 months, 1 week ago
profile picturepondidum

Does anyone know what I need to do to make extensions work in postgres wit this?

create extension “uuid-ossp”; gives this error: could not open extension control file “/usr/local/pgsql/share/extension/uuid-ossp.control”:

10 months, 1 week ago
profile picturemiquella

@brainopia: would a docker run --name some-postgres -e LC_ALL=C.UTF-8 -d postgres do what you're looking for?

10 months, 1 week ago
profile picturebrainopia

The encoding of the template database is ASCII, not UTF-8 :(

10 months, 1 week ago
profile picturetianon

jberkus - see https://github.com/docker-library/postgres

10 months, 2 weeks ago
profile picturejberkus

Hey, I'd like to have the PostgreSQL community, meaning probably me, take over maintenance of this Official Image. How do I go about doing that?

10 months, 2 weeks ago
profile picturegeetotes

Where can I see the Dockerfile for this image?

10 months, 2 weeks ago
profile picturecryptix

I'd like to make my own Image based on this one, basically just loading my schema into the db but I don't know how to startup the db without it blocking further execution.

I could write my own docker-entrypoint.sh script but I wanted to see if there is another way, using this as a base, as it is.

my Dockerfile so far: https://gist.github.com/72aa47fba3afa5730b5d

10 months, 2 weeks ago
profile picturegeorged

If anyone is wondering how to install extensions, there is a contrib directory inside the container. To install hstore I made a Dockerfile that ran the following:

RUN cd contrib/hstore && make && make install

Creating the extension with CREATE EXTENSION then succeeds.

10 months, 2 weeks ago
profile picturegeorged

Fantastic, thanks @ubergarm. That works nicely for me.

10 months, 2 weeks ago
profile pictureubergarm

docker run –name my_postgres \ -d \ -v pwd/volumes/data:/var/lib/postgresql/data \ postgres

Note that the postgres container uses uid/gid: postgres:x:999:999

The config file in the container is at: /var/lib/postgresql/data/postgresql.conf

10 months, 2 weeks ago
profile picturenamxam

Yeah, I actually asked myself the exact same thing ;)

10 months, 2 weeks ago
profile picturegeorged

I'm curious as to why the examples don't have any mapped volumes. Is the data intended to be stored inside the container?

Properties


Settings