PostgreSQL 9.5 SQL Database Server container image
This container image includes PostgreSQL 9.5 SQL database server for OpenShift and general usage.
Users can choose between RHEL and CentOS based images.
The RHEL image is available in the Red Hat Container Catalog
The CentOS image is then available on Docker Hub
This container image provides a containerized packaging of the PostgreSQL postgres daemon
and client application. The postgres server daemon accepts connections from clients
and provides access to content from PostgreSQL databases on behalf of the clients.
You can find more information on the PostgreSQL project from the project Web site
For this, we will assume that you are using the
If you want to set only the mandatory environment variables and not store the database
in a host directory, execute the following command:
$ docker run -d --name postgresql_database -e POSTGRESQL_USER=user -e POSTGRESQL_PASSWORD=pass -e POSTGRESQL_DATABASE=db -p 5432:5432 rhscl/postgresql-95-rhel7
This will create a container named
postgresql_database running PostgreSQL with
db and user with credentials
user:pass. Port 5432 will be exposed
and mapped to the host. If you want your database to be persistent across container
executions, also add a
-v /host/db/path:/var/lib/pgsql/data argument (see
below). This will be the PostgreSQL database cluster directory.
If the database cluster directory is not initialized, the entrypoint script will
and setup necessary database users and passwords. After the database is initialized,
or if it was already present,
is executed and will run as PID 1. You can stop the detached container by running
docker stop postgresql_database.
Environment variables and volumes
The image recognizes the following environment variables that you can set during
initialization by passing
-e VAR=VALUE to the Docker run command.
User name for PostgreSQL account to be created
Password for the user account
Password for the
postgres admin account (optional)
Alternatively, the following options are related to migration scenario:
Hostname/IP to migrate from
Password for the remote 'postgres' admin user
POSTGRESQL_MIGRATION_IGNORE_ERRORS (optional, default 'no')
Set to 'yes' to ignore sql import errors
The following environment variables influence the PostgreSQL configuration file. They are all optional.
POSTGRESQL_MAX_CONNECTIONS (default: 100)
The maximum number of client connections allowed
POSTGRESQL_MAX_PREPARED_TRANSACTIONS (default: 0)
Sets the maximum number of transactions that can be in the "prepared" state. If you are using prepared transactions, you will probably want this to be at least as large as max_connections
POSTGRESQL_SHARED_BUFFERS (default: 32M)
Sets how much memory is dedicated to PostgreSQL to use for caching data
POSTGRESQL_EFFECTIVE_CACHE_SIZE (default: 128M)
Set to an estimate of how much memory is available for disk caching by the operating system and within the database itself
You can also set the following mount points by passing the
-v /host/dir:/container/dir:Z flag to Docker.
PostgreSQL database cluster directory
Notice: When mouting a directory from the host into the container, ensure that the mounted
directory has the appropriate permissions and that the owner and group of the directory
matches the user UID or name which is running inside the container.
Typically (unless you use
docker run -u option) processes in container
run under UID 26, so -- on GNU/Linux -- you can fix the datadir permissions
for example by:
$ setfacl -m u:26:-wx /your/data/dir $ docker run <...> -v /your/data/dir:/var/lib/pgsql/data:Z <...>
PostgreSQL container supports migration of data from remote PostgreSQL server.
You can run it like:
$ docker run -d --name postgresql_database \ -e POSTGRESQL_MIGRATION_REMOTE_HOST=172.17.0.2 \ -e POSTGRESQL_MIGRATION_ADMIN_PASSWORD=remoteAdminP@ssword \ [ OPTIONAL_CONFIGURATION_VARIABLES ] openshift/postgresql-92-centos7
The migration is done the dump and restore way (running
remote cluster and importing the dump locally by
psql). Because the process
is streamed (unix pipeline), there are no intermediate dump files created during
this process to not waste additional storage space.
If some SQL commands fail during applying, the default behavior
of the migration script is to fail as well to ensure the all or nothing
result of scripted, unattended migration. In most common cases, successful
migration is expected (but not guaranteed!), given you migrate from
a previous version of PostgreSQL server container, that is created using
the same principles as this one (e.g. migration from
Migration from a different kind of PostgreSQL container can likely fail.
If this all or nothing principle is inadequate for you, and you know
what you are doing, there's optional
which does best effort migration (some data might be lost, it is up to user
to review the standard error output and fix the issues manually in
Please keep in mind that the container image provides help for users'
convenience, but fully automatic migration is not guaranteed. Thus, before you
start proceeding with the database migration, get prepared to perform manual
steps in order to get all your data migrated.
Note that you might not use variables like
POSTGRESQL_USER in migration
scenario, all the data (including info about databases, roles or passwords are
copied from old cluster). Ensure that you use the same
OPTIONAL_CONFIGURATION_VARIABLES as you used for initialization of the old
PostgreSQL container. If some non-default configuration is done on remote
cluster, you might need to copy the configuration files manually, too.
Security warning: Note that the IP communication between old and new PostgreSQL
clusters is not encrypted by default, it is up to user to configure SSL on
remote cluster or ensure security via different means.
When the PostgreSQL image is run with the
--memory parameter set and if there
are no values provided for
POSTGRESQL_EFFECTIVE_CACHE_SIZE those values are automatically calculated
based on the value provided in the
The values are calculated based on the
formulas. For the
shared_buffers we use 1/4 of given memory and for the
effective_cache_size we set the value to 1/2 of the given memory.
PostgreSQL admin account
The admin account
postgres has no password set by default, only allowing local
connections. You can set it by setting the
variable when initializing your container. This will allow you to login to the
postgres account remotely. Local connections will still not require a password.
Since passwords are part of the image configuration, the only supported method
to change passwords for the database user (
admin user is by changing the environment variables
Changing database passwords through SQL statements or any way other than through
the environment variables aforementioned will cause a mismatch between the
values stored in the variables and the actual passwords. Whenever a database
container starts it will reset the passwords to the values stored in the
Upgrading database (by switching to newer PostgreSQL image version)
Warning! Please, before you decide to do the data directory upgrade, always
ensure that you've carefully backed up all your data and that you are OK with
potential manual rollback!
This image supports automatic upgrade of data directory created by
the PostgreSQL server version 9.4 (and only this version) - provided by sclorg
image. The upgrade process is designed so that you should be able to just
switch from image A to image B, and set the
appropriately to explicitly request the database data transformation.
The upgrade process is internally implemented via
pg_upgrade binary, and for
that purpose the container needs to contain two versions of PostgreSQL server
(have a look at
man pg_upgrade for more info).
pg_upgrade process - and the new server version, we need to initialize
a brand new data directory. That's data directory is created automatically by
container tooling under /var/lib/pgsql/data, which is usually external
pg_upgrade execution is then similar to dump&restore
approach -- it starts both old and new PostgreSQL servers (within container) and
"dumps" the old datadir while and at the same time it "restores" it into new
datadir. This operation requires a lot of data files copying, so you can decide
what type of upgrade you'll do by setting
The data files are copied from old datadir to new datadir. This option has low risk of data loss in case of some upgrade failure.
Data files are hard-linked from old to the new data directory, which brings performance optimization - but the old directory becomes unusable, even in case of failure.
Note that because we copy data directory, you need to make sure that you have
enough space for the copy; upgrade failure because of not enough space might
lead to data loss.
This image can be extended using
For example to build customized image
with configuration in
$ s2i build ~/image-configuration/ postgresql new-postgresql
The directory passed to
s2i build should contain one or more of the
*.sh files from this directory during early start of the
container. There's no PostgreSQL daemon running on background.
Contained configuration files (
*.conf) will be included at the end of image
Contained shell scripts (
*.sh) are sourced when the database is freshly
initialized (after successful initdb run which made the data directory
non-empty). At the time of sourcing these scripts, the local PostgreSQL
server is running. For re-deployments scenarios with persistent data
directory, the scripts are not sourced (no-op).
Same sematics as
postgresql-init/, except that these scripts are
always sourced (after
postgresql-init/ scripts, if they exist).
s2i build all provided files are copied into
directory in the new image. Only one
file with the same name can be used for customization and user provided files
are preferred over default files in
so it is possible to overwrite them.
At first the postgres daemon writes its logs to the standard output, so these are available in the container log. The log can be examined by running:
docker logs <container>
Then log output is redirected to logging collector process and will appear in directory "pg_log".
Dockerfile and other sources for this container image are available on
In that repository, Dockerfile for CentOS is called Dockerfile, Dockerfile
for RHEL is called Dockerfile.rhel7.