Ckan: docker: postgreSQL changed from ver 9.6 to 10.3 compatibility problem

[terchris]

CKAN Version if known (or site URL)

Checked out the latest version from git

Please describe the expected behaviour

Expected a dunning docker_db container

Please describe the actual behaviour

The docker container did not start. I got the following error:
2018-04-02 06:56:12.993 UTC [1] FATAL: database files are incompatible with server
2018-04-02 06:56:12.993 UTC [1] DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.3 (Debian 10.3-1.pgdg90+1).

What steps can be taken to reproduce the issue?

Check out latest ckan/ckan. Change to ckan/contrib/docker. Do docker-compose build. Then docker-compose up -d

Trying to figure out why the database has changed from 9.6 to 10.3.
I see that ckan/contrib/docker/postgresql/Dockerfile
https://github.com/ckan/ckan/blob/master/contrib/docker/postgresql/Dockerfile

Is created from mdillon/postgis
FROM mdillon/postgis

When looking at mdillon/postgis
https://hub.docker.com/r/mdillon/postgis/~/dockerfile/

I see that it is created from postgres:9.6-alpine
FROM postgres:9.6-alpine

Looking at the postgres repository on docker hub I see that the latest version is 10.3
https://hub.docker.com/_/postgres/

I’m not sure how the tags work. But it seems to me that even though the mdillon/postgis refers to postgres:9.6-alpine it somehow ended up selecting the postgres:latest witch is debian and not alpine linux and version 10.3 and not 9.6

Looking at the :latest
https://github.com/docker-library/postgres/blob/0aaaf2094034647a552f0b1ec63b1b0ec0f6c2cc/10/Dockerfile
There is a
@docker-library-bot docker-library-bot Update to 10.3-1.pgdg90+1

And the “10.3-1.pgdg90+1” is the same version as displayed in the error message “....which is not compatible with this version 10.3 (Debian 10.3-1.pgdg90+1).”

[amercader]

Maybe it helps explicitly tagging the posrgres/postgis image that used in the Postgres Dockerfile to 9.6?

[terchris]

Thanks @amercader
Swopping the lines
FROM postgres:9.6

FROM mdillon/postgis

Gave me a running docker_db. But there are new problems. It seems that the database was not initialised properly. From reading the ckan trace log it seems to me that there is some upgrading/migrating related stuff that fail. Maybe it has something to do with changing from a postgis enabled version to plane postgres.

The docker_ckan logged this:
Traceback (most recent call last): File "/usr/local/bin/ckan-paster", line 11, in <module> sys.exit(run()) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/paste/script/command.py", line 102, in run invoke(command, command_name, options, args[1:]) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/paste/script/command.py", line 141, in invoke exit_code = runner.run(args) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/paste/script/command.py", line 236, in run result = self.command() File "/usr/lib/ckan/venv/src/ckan/ckan/lib/cli.py", line 356, in command model.repo.init_db() File "/usr/lib/ckan/venv/src/ckan/ckan/model/__init__.py", line 187, in init_db self.upgrade_db() File "/usr/lib/ckan/venv/src/ckan/ckan/model/__init__.py", line 284, in upgrade_db mig.upgrade(self.metadata.bind, self.migrate_repository, version=version) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/migrate/versioning/api.py", line 186, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "<decorator-gen-16>", line 2, in _migrate File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/migrate/versioning/util/__init__.py", line 160, in with_engine return f(*a, **kw) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/migrate/versioning/api.py", line 366, in _migrate schema.runchange(ver, change, changeset.step) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/migrate/versioning/schema.py", line 93, in runchange change.run(self.engine, step) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/migrate/versioning/script/py.py", line 148, in run script_func(engine) File "/usr/lib/ckan/venv/src/ckan/ckan/migration/versions/001_add_existing_tables.py", line 105, in upgrade meta.create_all() File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 3934, in create_all tables=tables) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1929, in _run_visitor conn._run_visitor(visitorcallable, element, **kwargs) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1538, in _run_visitor **kwargs).traverse_single(element) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single return meth(obj, **kw) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 733, in visit_metadata _is_metadata_operation=True) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single return meth(obj, **kw) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 767, in visit_table include_foreign_key_constraints=include_foreign_key_constraints File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute return meth(self, multiparams, params) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection return connection._execute_ddl(self, multiparams, params) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1002, in _execute_ddl compiled File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context context) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception exc_info File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "id" referenced in foreign key constraint does not exist [SQL: '\nCREATE TABLE package (\n\tid SERIAL NOT NULL, \n\tname VARCHAR(100) NOT NULL, \n\ttitle TEXT, \n\tversion VARCHAR(100), \n\turl TEXT, \n\tdownload_url TEXT, \n\tnotes TEXT, \n\tlicense_id INTEGER, \n\tstate_id INTEGER, \n\trevision_id INTEGER, \n\tPRIMARY KEY (id), \n\tUNIQUE (name), \n\tFOREIGN KEY(license_id) REFERENCES license (id), \n\tFOREIGN KEY(state_id) REFERENCES state (id), \n\tFOREIGN KEY(revision_id) REFERENCES revision (id)\n)\n\n']

And the docker_db logged this:

LOG: database system is ready to accept connections LOG: autovacuum launcher started ERROR: relation "user" does not exist at character 435 STATEMENT: SELECT "user".password AS user_password, "user".id AS user_id, "user".name AS user_name, "user".fullname AS user_fullname, "user".email AS user_email, "user".apikey AS user_apikey, "user".created AS user_created, "user".reset_key AS user_reset_key, "user".about AS user_about, "user".activity_streams_email_notifications AS user_activity_streams_email_notifications, "user".sysadmin AS user_sysadmin, "user".state AS user_state FROM "user" WHERE "user".name = 'default' OR "user".id = 'default' ORDER BY "user".name LIMIT 1
It did this 4 times. and then a:

ERROR: column "id" referenced in foreign key constraint does not exist STATEMENT: CREATE TABLE package ( id SERIAL NOT NULL, name VARCHAR(100) NOT NULL, title TEXT, version VARCHAR(100), url TEXT, download_url TEXT, notes TEXT, license_id INTEGER, state_id INTEGER, revision_id INTEGER, PRIMARY KEY (id), UNIQUE (name), FOREIGN KEY(license_id) REFERENCES license (id), FOREIGN KEY(state_id) REFERENCES state (id), FOREIGN KEY(revision_id) REFERENCES revision (id) )

[jbants]

Hi @terchris,
I had the same issue when trying to move from postgres:9.6 to mdillon/postgis.
I found that I had to remove the old pgdata volume that was initially created by postgres:9.6 and then run docker-compose build in order to recreate the volume with the correct version.

docker volume ls and then docker volume rm <volume-name>

[yogendra-kevre]

Hi @jbants ,

Removing the old volume and assigning a new volume is not a solution, it will just give you the new volume with a new data directory with an updated version of postges. in the case, we will not have the data which we have persisted in the previous version in the previous volume.

The question here is how we can make compatible the data file of the previous version which is there in the volume to the newer version of postges.