PostgreSQL with Docker: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
No edit summary
 
(3 intermediate revisions by the same user not shown)
Line 5: Line 5:
=Overview=
=Overview=


In its simplest form:
This article describes how to stand up and operate a Docker-based PostgreSQL instance. The instance will be accessible on the standard PostgreSQL port on the local host and it will be secured by a conventional user and password. Since the password is visible in the terminal used to create the container image, this approach is not really secure, but it is good enough for development purposes. The container-generated data will be stored on an [[Docker_Storage_Concepts#Anonymous_Volume|anonymous]] [[Docker_Storage_Concepts#Local_Volume_Driver|local]] volume.


<syntaxhighlight lang='bash'>
=Operations=
docker run postgres
</syntaxhighlight >


command starts a transient PostgreSQL container instance that does not expose any port on the local host, it is initialized on the fly to allow access to any user and uses an [[Docker_Storage_Concepts#Anonymous_Volume|anonymous]] [[Docker_Storage_Concepts#Local_Volume_Driver|local]] volume. To get a usable Postgres instance we can shut down and restart, and for which we can access the data directory independently, in case it is needed for debugging and troubleshooting, we should use create a dedicated [[Docker_Storage_Concepts#Named_Volume|named volume]] or a [[Docker_Storage_Concepts#Bind_Mount|bind mount]], initialize the database with a username and a password during the first initialization run and map ports to the local host.
==Create the Container==


==Dedicated Storage==
This is an one-time operation:


We prefer using a [[Docker_Storage_Concepts#Bind_Mount|bind mount]] as it gives easy access to the stored state, in case we need to troubleshoot. A [[Docker_Storage_Concepts#Named_Volume|named volume]] can be also used.
docker run -p 5432:5432/tcp -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=<some-password> --name postgres postgres


<syntaxhighlight lang='bash'>
If you want to use a local directory for storage, which will be made available to the container with a [[Docker_Storage_Concepts#Bind_Mount|bind mount]], add the following arguments. Note that this is only necessary if you want to have convenient access to the database files and logs, for troubleshooting and debugging purposes:
docker run ... --mount type=bind,source=/Users/ovidiu/runtime/docker-volumes/postgres,target=/var/lib/postgresql/data --name postgres ... postgres
</syntaxhighlight >


==Authenticated Access==
--mount type=bind,source=<local-directory>,target=/var/lib/postgresql/data


When the container is run the first time, it goes through an initialization sequence, and if no specific authentication configuration is provided in form of environment variables, it will configure itself to allow unauthenticated access. To prevent that from happening, start the container '''for the first time''' as shown:
==Start the Container==


<syntaxhighlight lang='bash'>
docker start postgres
docker run --mount type=bind,source=/Users/ovidiu/runtime/docker-volumes/postgres,target=/var/lib/postgresql/data -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=<some-password> --name postgres postgres
</syntaxhighlight >


This will initialized the database to allow access as admin/<''some-password''>.
==Stop the Container==


Subsequent startups will not require specification of -e POSTGRES_USER and -e POSTGRES_PASSWORD, as long as we use the same local directory Postgres initialized its state on.
docker stop postgres


==Exposing Ports on the Local Host==
==Access Container Logs==


Postgres binds by default on port 5432 and this is the port published by the following command:
  docker logs -f postgres
 
<syntaxhighlight lang='bash'>
docker run ... -p 5432:5432/tcp  ...
</syntaxhighlight>
 
=Procedures=
 
==Database Initialization==
 
Create a local directory <tt>/Users/ovidiu/runtime/docker-volumes/postgres</tt> and then execute the initialization run:
 
<syntaxhighlight lang='bash'>
docker run --mount type=bind,source=/Users/ovidiu/runtime/docker-volumes/postgres,target=/var/lib/postgresql/data -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=<some-password> --name postgres-init-container postgres
</syntaxhighlight>
 
Initialization log:
<syntaxhighlight lang='text'>
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
 
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
 
Data page checksums are disabled.
 
fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
 
Success. You can now start the database server using:
 
    pg_ctl -D /var/lib/postgresql/data -l logfile start
 
 
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2018-10-18 17:51:21.203 UTC [43] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-10-18 17:51:21.253 UTC [44] LOG:  database system was shut down at 2018-10-18 17:51:19 UTC
2018-10-18 17:51:21.269 UTC [43] LOG:  database system is ready to accept connections
done
server started
CREATE DATABASE
 
 
/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
 
waiting for server to shut down...2018-10-18 17:51:22.349 UTC [43] LOG:  received fast shutdown request
2018-10-18 17:51:22.351 UTC [43] LOG:  aborting any active transactions
.2018-10-18 17:51:22.356 UTC [43] LOG:  worker process: logical replication launcher (PID 50) exited with exit code 1
2018-10-18 17:51:22.356 UTC [45] LOG:  shutting down
2018-10-18 17:51:22.387 UTC [43] LOG:  database system is shut down
done
server stopped
 
PostgreSQL init process complete; ready for start up.
 
2018-10-18 17:51:22.478 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-10-18 17:51:22.478 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2018-10-18 17:51:22.482 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-10-18 17:51:22.546 UTC [61] LOG:  database system was shut down at 2018-10-18 17:51:22 UTC
2018-10-18 17:51:22.562 UTC [1] LOG: database system is ready to accept connections
</syntaxhighlight>
 
The container can be removed after initialization with:
 
<syntaxhighlight lang='bash'>
docker stop postgres-init-container
docker rm postgres-init-container
</syntaxhighlight>
 
==Production Container Creation==
 
<syntaxhighlight lang='bash'>
docker run --mount type=bind,source=/Users/ovidiu/runtime/docker-volumes/postgres,target=/var/lib/postgresql/data -p 5432:5432/tcp --name postgres postgres
</syntaxhighlight>
 
Note that the container initialized as shown above has only one user "admin", with all possible privileges, so for production you should consider creating regular users with less privileges. See [[PostgreSQL_DDL_Operations#Create_User|Create User]].
 
==Lifecycle==
 
<syntaxhighlight lang='bash'>
docker stop postgres
docker start [-i] postgres
</syntaxhighlight>
 
If actively experimenting or testing with it, it's probably a good idea to start it in interactive mode (-i). If you happen to close the terminal, you can reattach with:
 
<syntaxhighlight lang='bash'>
docker attach postgres
</syntaxhighlight>
 
==Log Into the Container==
 
<syntaxhighlight lang='bash'>
docker exec -it postgres bash
</syntaxhighlight>

Latest revision as of 20:20, 19 October 2018

Internal

Overview

This article describes how to stand up and operate a Docker-based PostgreSQL instance. The instance will be accessible on the standard PostgreSQL port on the local host and it will be secured by a conventional user and password. Since the password is visible in the terminal used to create the container image, this approach is not really secure, but it is good enough for development purposes. The container-generated data will be stored on an anonymous local volume.

Operations

Create the Container

This is an one-time operation: 

docker run -p 5432:5432/tcp -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=<some-password> --name postgres postgres

If you want to use a local directory for storage, which will be made available to the container with a bind mount, add the following arguments. Note that this is only necessary if you want to have convenient access to the database files and logs, for troubleshooting and debugging purposes: 

--mount type=bind,source=<local-directory>,target=/var/lib/postgresql/data

Start the Container

docker start postgres

Stop the Container

docker stop postgres

Access Container Logs

docker logs -f postgres
Retrieved from "https://kb.novaordis.com/index.php?title=PostgreSQL_with_Docker&oldid=50370"