Postgresql – How to persist data using postgres docker image

dockerpostgresqlredmine

I'm trying to get my data to persist in /home/me/redmine so that when my container stops, no data loss. I'm using the official postgres docker hub image.

Problem is, every time I start the postgres container, it immediately exits. This problem started when I appended 'data' to /var/lib/postgresql, otherwise, without adding 'data', it starts, but my data isn't being saved. Also, the path I have on my host machine is /var/lib/postgresql/9.4/data, but the image page says to use /var/lib/postgresql/data.

Here's how I'm trying to start/mount a volume from the host:

docker run --name postgres -d   \
    --env='DB_NAME=redmine_production' \
    --env='DB_USER=redmine' \
    --env='DB_PASS=secret' \
    --volume=/home/me/redmine/postgresql:/var/lib/postgresql/data \
    postgres

… and then after I would link redmine as follows …

docker run -d -p 3000:3000 --name redmine \
    -v /home/me/redmine/files:/usr/src/redmine/files \
    --link postgres:postgres redmine

In the redmine container, the /home/me/redmine/files volume is a seperate directory where I'm trying to persist files aside from the ones in the Postgres database.

Edit:

When I cd into my host machines /var/lib/postgresql directory, I find within it 9.4/data and then see that the owner and group are not listed in my /etc/passwd or /etc/groups and wonder if this doesn't have something to do with the problem.

/var/lib/postgresql/9.4/data # ls -la
total 88
drwx------ 18   70   70 4096 Aug 13  2015 .
drwxr-xr-x  3 root root 4096 Mar 28  2015 ..
drwx------  8   70   70 4096 Jul  2  2015 base
drwx------  2   70   70 4096 Jul  2  2015 global
drwx------  2   70   70 4096 Mar 28  2015 pg_clog
drwx------  2   70   70 4096 Mar 28  2015 pg_dynshmem
lrwxrwxrwx  1 root root   31 Mar 28  2015 pg_hba.conf -> /etc/postgresql-    9.4/pg_hba.conf
lrwxrwxrwx  1 root root   33 Mar 28  2015 pg_ident.conf -> /etc/postgresql-9.4/pg_ident.conf
drwx------  4   70   70 4096 Mar 28  2015 pg_logical
drwx------  4   70   70 4096 Mar 28  2015 pg_multixact
drwx------  2   70   70 4096 Jul  2  2015 pg_notify
drwx------  2   70   70 4096 Mar 28  2015 pg_replslot
drwx------  2   70   70 4096 Mar 28  2015 pg_serial
drwx------  2   70   70 4096 Mar 28  2015 pg_snapshots
drwx------  2   70   70 4096 Aug 13  2015 pg_stat
drwx------  2   70   70 4096 Aug 13  2015 pg_stat_tmp
drwx------  2   70   70 4096 Mar 28  2015 pg_subtrans
drwx------  2   70   70 4096 Mar 28  2015 pg_tblspc
drwx------  2   70   70 4096 Mar 28  2015 pg_twophase
-rw-------  1   70   70    4 Mar 28  2015 PG_VERSION
drwx------  3   70   70 4096 Mar 28  2015 pg_xlog
-rw-------  1   70   70   88 Mar 28  2015 postgresql.auto.conf
lrwxrwxrwx  1 root root   35 Mar 28  2015 postgresql.conf -> /etc/postgresql-9.4/postgresql.conf
-rw-------  1   70   70 1626 Aug 13  2015 postmaster.log
-rw-------  1   70   70  114 Jul  2  2015 postmaster.opts

Thoughts?

Best Answer

First, those environment variables look suspicious. Take a look at the documentation for the official Docker image, and note that you need POSTGRES_DB, POSTGRES_USER, and POSTGRES_PASSWORD, rather than DB_NAME, DB_USER, and DB_PASS.

Other than that, you seem to mostly be on the right track. Here's a complete example:

First, I start a Postgres container. I'm locating the persistent storage somewhere outside of my home directory, because as you have already noted that files won't be owned by you, which can be confusing in your home directory (though not necessarily problematic):

docker run --rm --name postgres \
  -v /tmp/postgres:/var/lib/postgresql/data \
  -e POSTGRES_DB=larstest \
  -e POSTGRES_USER=lars \
  -e POSTGRES_PASSWORD=secret postgres

Since this is the first time I've started postgres pointing at that data directory, we will see it initialize the database:

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:

Now, from another window, I can connect to it...

$ psql -h 172.17.0.4 -U lars larstest
Password for user lars: 
psql (9.5.4, server 9.6.0)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
Type "help" for help.

...and create some data:

larstest=# create table testtable (id integer);
CREATE TABLE
larstest=# insert into testtable values (1);
INSERT 0 1
larstest=# select * from testtable;
 id 
----
  1
(1 row)

Now, I exit the container:

^CLOG:  received fast shutdown request
LOG:  aborting any active transactions
FATAL:  terminating connection due to administrator command
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down

We can verify that it's no longer running:

$ docker ps | grep postgres

But if we start it up again with the same command line arguments;

docker run --rm --name postgres \
  -v /tmp/postgres:/var/lib/postgresql/data \
  -e POSTGRES_DB=larstest \
  -e POSTGRES_USER=lars \
  -e POSTGRES_PASSWORD=secret postgres

We see that it doesn't initialize the database, since it already exists, and skip straight to:

LOG:  database system was shut down at 2016-10-21 03:13:50 UTC
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

At this point, we can re-connect to the database and find that our data still exists:

$ psql -h 172.17.0.2 -U lars larstest
Password for user lars: 
psql (9.5.4, server 9.6.0)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
Type "help" for help.

larstest=# select * from testtable;
 id 
----
  1
(1 row)

That's pretty much all there is to it.

Related Topic