Repository files navigation

• Introduction
  • Contributing
  • Issues
• Getting started
  • Installation
  • Quickstart
  • Persistence
  • Trusting local connections
  • Settingpostgres user password
  • Creating database user
  • Creating databases
  • Granting user access to a database
  • Enabling extensions
  • Creating replication user
  • Setting up a replication cluster
  • Creating a snapshot
  • Creating a backup
  • Command-line arguments
  • Logs
  • UID/GID mapping
• Maintenance
  • Upgrading
  • Shell Access

Dockerfile to create aDocker container image forPostgreSQL.

PostgreSQL is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance [source].

If you find this image useful here's how you can help:

• Send a pull request with your awesome features and bug fixes
• Help users resolve theirissues.
• Support the development of this image with adonation

Before reporting your issue please try updating Docker to the latest version and check if it resolves the issue. Refer to the Dockerinstallation guide for instructions.

SELinux users should try disabling SELinux using the commandsetenforce 0 to see if it resolves the issue.

If the above recommendations do not help thenreport your issue along with the following information:

• Output of thedocker vers6 anddocker info commands
• Thedocker run command ordocker-compose.yml used to start the image. Mask out the sensitive bits.
• Please state if you are usingBoot2Docker,VirtualBox, etc.

Automated builds of the image are available onDockerhub and is the recommended method of installation.

Note: Builds are also available onQuay.io

docker pull sameersbn/postgresql:15-20230628

Alternatively you can build the image yourself.

docker build -t sameersbn/postgresql github.com/sameersbn/docker-postgresql

Start PostgreSQL using:

docker run --name postgresql -itd --restart always \  --publish 5432:5432 \  --volume postgresql:/var/lib/postgresql \  sameersbn/postgresql:15-20230628

Login to the PostgreSQL server using:

dockerexec -it postgresql sudo -u postgres psql

Alternatively, you can use the sampledocker-compose.yml file to start the container usingDocker Compose

For PostgreSQL to preserve its state across container shutdown and startup you should mount a volume at/var/lib/postgresql. If you don't like the default volume destination then you can change it

TheQuickstart command already mounts a volume for persistence.

SELinux users should update the security context of the host mountpoint so that it plays nicely with Docker:

mkdir -p /srv/docker/postgresqlchcon -Rt svirt_sandbox_file_t /srv/docker/postgresql

By default connections to the PostgreSQL server need to authenticated using a password. If desired you can trust connections from the local network using thePG_TRUST_LOCALNET variable.

docker run --name postgresql -itd --restart always \  --env'PG_TRUST_LOCALNET=true' \  sameersbn/postgresql:15-20230628

Note

The local network here is network to which the container is attached. This has different meanings depending on the--net parameter specified while starting the container. In the default configuration, this parameter would trust connections from other containers on thedocker0 bridge.

By default thepostgres user is not assigned a password and as a result you can only login to the PostgreSQL server locally. If you wish to login remotely to the PostgreSQL server as thepostgres user, you will need to assign a password for the user using thePG_PASSWORD variable.

docker run --name postgresql -itd --restart always \  --env'PG_PASSWORD=passw0rd' \  sameersbn/postgresql:15-20230628

Note

• Whenpersistence is in use,PG_PASSWORD is effective on the first run.
• This feature is only available in thelatest and versions >9.4-10

A new PostgreSQL database user can be created by specifying theDB_USER andDB_PASS variables while starting the container.

docker run --name postgresql -itd --restart always \  --env'DB_USER=dbuser' --env'DB_PASS=dbuserpass' \  sameersbn/postgresql:15-20230628

Notes

• The created user can login remotely
• The container will error out if a password is not specified for the user
• No changes will be made if the user already exists
• Only a single user can be created at each launch

A new PostgreSQL database can be created by specifying theDB_NAME variable while starting the container.

docker run --name postgresql -itd --restart always \  --env'DB_NAME=dbname' \  sameersbn/postgresql:15-20230628

By default databases are created by copying the standard system database namedtemplate1. You can specify a different template for your database using theDB_TEMPLATE parameter. Refer toTemplate Databases for further information.

Additionally, more than one database can be created by specifying a comma separated list of database names inDB_NAME. For example, the following command creates two new databases nameddbname1 anddbname2.

This feature is only available in releases greater than9.1-1

docker run --name postgresql -itd --restart always \  --env'DB_NAME=dbname1,dbname2' \  sameersbn/postgresql:15-20230628

If theDB_USER andDB_PASS variables are specified along with theDB_NAME variable, then the user specified inDB_USER will be granted access to all the databases listed inDB_NAME. Note that if the user and/or databases do not exist, they will be created.

docker run --name postgresql -itd --restart always \  --env'DB_USER=dbuser' --env'DB_PASS=dbuserpass' \  --env'DB_NAME=dbname1,dbname2' \  sameersbn/postgresql:15-20230628

In the above exampledbuser with be granted access to both thedbname1 anddbname2 databases.

The image also packages thepostgres contrib module. A comma separated list of modules can be specified using theDB_EXTENSION parameter.

docker run --name postgresql -itd \  --env'DB_NAME=db1,db2' --env'DB_EXTENSION=unaccent,pg_trgm' \  sameersbn/postgresql:15-20230628

The above command enables theunaccent andpg_trgm modules on the databases listed inDB_NAME, namelydb1 anddb2.

NOTE:

This option deprecates theDB_UNACCENT parameter.

Similar to the creation of a database user, a new PostgreSQL replication user can be created by specifying theREPLICATION_USER andREPLICATION_PASS variables while starting the container.

docker run --name postgresql -itd --restart always \  --env'REPLICATION_USER=repluser' --env'REPLICATION_PASS=repluserpass' \  sameersbn/postgresql:15-20230628

Notes

• The created user can login remotely
• The container will error out if a password is not specified for the user
• No changes will be made if the user already exists
• Only a single user can be created at each launch

It is a good idea to create a replication user even if you are not going to use it as it will allow you to setup slave nodes and/or generate snapshots and backups when the need arises.

When the container is started, it is by default configured to act as a master node in a replication cluster. This means that you can scale your PostgreSQL database backend when the need arises without incurring any downtime. However do note that a replication user must exist on the master node for this to work.

Begin by creating the master node of our cluster:

docker run --name postgresql-master -itd --restart always \  --env'DB_USER=dbuser' --env'DB_PASS=dbuserpass' --env'DB_NAME=dbname' \  --env'REPLICATION_USER=repluser' --env'REPLICATION_PASS=repluserpass' \  sameersbn/postgresql:15-20230628

Notice that no additional arguments are specified while starting the master node of the cluster.

To create a replication slave theREPLICATION_MODE variable should be set toslave and additionally theREPLICATION_HOST,REPLICATION_PORT,REPLICATION_SSLMODE,REPLICATION_USER andREPLICATION_PASS variables should be specified.

Create a slave node:

docker run --name postgresql-slave01 -itd --restart always \  --link postgresql-master:master \  --env'REPLICATION_MODE=slave' --env'REPLICATION_SSLMODE=prefer' \  --env'REPLICATION_HOST=master' --env'REPLICATION_PORT=5432'  \  --env'REPLICATION_USER=repluser' --env'REPLICATION_PASS=repluserpass' \  sameersbn/postgresql:15-20230628

In the above command, we used docker links so that we can address the master node using themaster alias inREPLICATION_HOST.

Note

• The default value ofREPLICATION_PORT is5432
• The default value ofREPLICATION_SSLMODE isprefer
• The value ofREPLICATION_USER andREPLICATION_PASS should be the same as the ones specified on the master node.
• Withpersistence in use, if the container is stopped and started, for the container continue to function as a slave you need to ensure thatREPLICATION_MODE=slave is defined in the containers environment. In the absense of which the slave configuration will be turned off and the node will allow writing to it while having the last synced data from the master.

And just like that with minimal effort you have a PostgreSQL replication cluster setup. You can create additional slaves to scale the cluster horizontally.

Here are some important notes about a PostgreSQL replication cluster:

• Writes can only occur on the master
• Slaves are read-only
• For best performance, limit the reads to the slave nodes

Similar to a creating replication slave node, you can create a snapshot of the master by specifyingREPLICATION_MODE=snapshot.

Once the master node is created as specified inSetting up a replication cluster, you can create a snapshot using:

docker run --name postgresql-snapshot -itd --restart always \  --link postgresql-master:master \  --env'REPLICATION_MODE=snapshot' --env'REPLICATION_SSLMODE=prefer' \  --env'REPLICATION_HOST=master' --env'REPLICATION_PORT=5432'  \  --env'REPLICATION_USER=repluser' --env'REPLICATION_PASS=repluserpass' \  sameersbn/postgresql:15-20230628

The difference between a slave and a snapshot is that a slave is read-only and updated whenever the master data is updated (streaming replication), while a snapshot is read-write and is not updated after the initial snapshot of the data from the master.

This is useful for developers to quickly snapshot the current state of a live database and use it for development/debugging purposes without altering the database on the live instance.

Just as the case of setting up a slave node or generating a snapshot, you can also create a backup of the data on the master by specifyingREPLICATION_MODE=backup.

The backups are generated withpg_basebackup using the replication protocol.

Once the master node is created as specified inSetting up a replication cluster, you can create a point-in-time backup using:

docker run --name postgresql-backup -it --rm \  --link postgresql-master:master \  --env'REPLICATION_MODE=backup' --env'REPLICATION_SSLMODE=prefer' \  --env'REPLICATION_HOST=master' --env'REPLICATION_PORT=5432'  \  --env'REPLICATION_USER=repluser' --env'REPLICATION_PASS=repluserpass' \  --volume /srv/docker/backups/postgresql.$(date +%Y%m%d%H%M%S):/var/lib/postgresql \  sameersbn/postgresql:15-20230628

Once the backup is generated, the container will exit and the backup of the master data will be available at/srv/docker/backups/postgresql.XXXXXXXXXXXX/. Restoring the backup involves starting a container with the data in/srv/docker/backups/postgresql.XXXXXXXXXXXX.

You can customize the launch command of PostgreSQL server by specifying arguments forpostgres on thedocker run command. For example the following command enables connection logging:

docker run --name postgresql -itd --restart always \  sameersbn/postgresql:15-20230628 -c log_connections=on

Please refer to the documentation ofpostgres for the complete list of available options.

By default the PostgreSQL server logs are sent to the standard output. Using theCommand-line arguments feature you can configure the PostgreSQL server to send the log output to a file using the-c logging_collector=on argument:

docker run --name postgresql -itd --restart always \  sameersbn/postgresql:15-20230628 -c logging_collector=on

To access the PostgreSQL logs you can usedocker exec. For example:

dockerexec -it postgresql tail -f /var/log/postgresql/postgresql-9.4-main.log

The files and processes created by the container are owned by thepostgres user that is internal to the container. In the absense of user namespace in docker the UID and GID of the containerspostgres user may have different meaning on the host.

For example, a user on the host with the same UID and/or GID as thepostgres user of the container will be able to access the data in the persistent volumes mounted from the host as well as be able to KILL thepostgres server process started by the container.

To circumvent this issue you can specify the UID and GID for thepostgres user of the container using theUSERMAP_UID andUSERMAP_GID variables respectively.

For example, if you want to assign thepostgres user of the container the UID and GID999:

docker run --name postgresql -itd --restart always \  --env'USERMAP_UID=999' --env'USERMAP_GID=999' \  sameersbn/postgresql:15-20230628

To upgrade to newer releases:

1. Download the updated Docker image:

docker pull sameersbn/postgresql:15-20230628

2. Stop the currently running image:

docker stop postgresql

3. Remove the stopped container

docker rm -v postgresql

4. Start the updated image

docker run --name postgresql -itd \  [OPTIONS] \  sameersbn/postgresql:15-20230628

For debugging and maintenance purposes you may want access the containers shell. If you are using Docker version1.3.0 or higher you can access a running containers shell by startingbash usingdocker exec:

dockerexec -it postgresql bash