Docker ftcli db upgrade

Question

Docker ftcli db upgrade

Closed this issue a year ago · 12 comments

Up to version 0.7.12 I was able to upgrade my Docker containers with:
sudo git pull origin master
and then within the docker container:
ftcli db upgrade
but since then the database has not been upgrading with the output:
'FLASK_ENV' is deprecated and will not be used in Flask 2.3. Use 'FLASK_DEBUG' instead. INFO [alembic.runtime.migration] Context impl PostgresqlImpl. INFO [alembic.runtime.migration] Will assume transactional DDL.
Even when I tried to rebuild everything from scratch but still using the existing database (as I didn't want to loose my records) the same problem persists and Fittrackee does not load.

Answer 1 · 2023-03-11T19:26:24.000Z

Hi,

Are containers built with the docker-compose-dev.yml file present in this repo?
Just for information, this file is just provided for evaluation (it is not really intended to run migrations).

But I was able to run migration on master, after creating new containers from scratch with docker-compose-dev.yml on v0.7.12:

$ make docker-shell
root@f51afac2a87d:/usr/src/app# ftcli db upgrade
'FLASK_ENV' is deprecated and will not be used in Flask 2.3. Use 'FLASK_DEBUG' instead.
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 0f375c44e659 -> 374a670efe23, add privacy policy
root@f51afac2a87d:/usr/src/app#

Are there any errors in logs?

Answer 2 · 2023-03-11T20:02:19.000Z

Are containers built with the docker-compose-dev.yml file present in this repo?

Yes they are.

Are there any errors in logs?

Not when fetching the master or when building the new containers, but I get an error when running the container with all new images created but no database update applied, which I find understandable, since the db has not upgraded:
fittrackee-db | FROM users
fittrackee-db | WHERE users.id = 1
fittrackee-db | LIMIT 1
fittrackee | Traceback (most recent call last):
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
fittrackee | self.dialect.do_execute(
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
fittrackee | cursor.execute(statement, parameters)
fittrackee | psycopg2.errors.UndefinedColumn: column users.accepted_policy_date does not exist
fittrackee | LINE 1: ...en, users.display_ascent AS users_display_ascent, users.acce...
fittrackee | ^
fittrackee |
fittrackee |
fittrackee | The above exception was the direct cause of the following exception:
fittrackee |
fittrackee | Traceback (most recent call last):
fittrackee | File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 2551, in call
fittrackee | return self.wsgi_app(environ, start_response)
fittrackee | File "/usr/local/lib/python3.10/site-packages/werkzeug/middleware/proxy_fix.py", line 187, in call
fittrackee | return self.app(environ, start_response)
fittrackee | File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 2531, in wsgi_app
fittrackee | response = self.handle_exception(e)
fittrackee | File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 2528, in wsgi_app
fittrackee | response = self.full_dispatch_request()
fittrackee | File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1825, in full_dispatch_request
fittrackee | rv = self.handle_user_exception(e)
fittrackee | File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1823, in full_dispatch_request
fittrackee | rv = self.dispatch_request()
fittrackee | File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1799, in dispatch_request
fittrackee | return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
fittrackee | File "/usr/src/app/fittrackee/oauth2/resource_protector.py", line 39, in decorated
fittrackee | auth_user = User.query.filter_by(id=resp).first()
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2824, in first
fittrackee | return self.limit(1)._iter().first()
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2916, in _iter
fittrackee | result = self.session.execute(
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1714, in execute
fittrackee | result = conn._execute_20(statement, params or {}, execution_options)
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
fittrackee | return meth(self, args_10style, kwargs_10style, execution_options)
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
fittrackee | return connection._execute_clauseelement(
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
fittrackee | ret = self._execute_context(
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
fittrackee | self.handle_dbapi_exception(
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in handle_dbapi_exception
fittrackee | util.raise(
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise
fittrackee | raise exception
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
fittrackee | self.dialect.do_execute(
fittrackee | File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
fittrackee | cursor.execute(statement, parameters)
fittrackee | sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column users.accepted_policy_date does not exist
fittrackee | LINE 1: ...en, users.display_ascent AS users_display_ascent, users.acce...
fittrackee | ^
fittrackee |
fittrackee | [SQL: SELECT users.id AS users_id, users.username AS users_username, users.email AS users_email, users.password AS users_password, users.created_at AS users_created_at, users.admin AS users_admin, users.first_name AS users_first_name, users.last_name AS users_last_name, users.birth_date AS users_birth_date, users.location AS users_location, users.bio AS users_bio, users.picture AS users_picture, users.timezone AS users_timezone, users.date_format AS users_date_format, users.weekm AS users_weekm, users.language AS users_language, users.imperial_units AS users_imperial_units, users.is_active AS users_is_active, users.email_to_confirm AS users_email_to_confirm, users.confirmation_token AS users_confirmation_token, users.display_ascent AS users_display_ascent, users.accepted_policy_date AS users_accepted_policy_date
fittrackee | FROM users
fittrackee | WHERE users.id = %(id_1)s
fittrackee | LIMIT %(param_1)s]
fittrackee | [parameters: {'id_1': 1, 'param_1': 1}]
fittrackee | (Background on this error at: https://sqlalche.me/e/14/f405)
fittrackee-mailhog | [APIv1] KEEPALIVE /api/v1/events

Chris.

Answer 3 · 2023-03-12T06:46:44.000Z

Hi,

Thanks for the logs.
I don't understand why the last migration was not executed.

Just to be sure, can you check the version in database?

fittrackee=# SELECT * FROM alembic_version;
 version_num  
--------------
 374a670efe23
(1 ligne)

fittrackee=#

Answer 4 · 2023-03-12T15:54:22.000Z

Hi again,
I really appreciate your time on this one. That is what I am getting when issuing commands within fittrackee-db container:

postgres@088b0299e19e:/etc$ psql
psql (13.10 (Debian 13.10-1.pgdg110+1))
Type "help" for help.

postgres=# SELECT * FROM alembic_version;
ERROR:  relation "alembic_version" does not exist
LINE 1: SELECT * FROM alembic_version;

I am sure I am doing something wrong here... I am most probably issuing this command in the wrong place...

Answer 5 · 2023-03-12T16:42:09.000Z

I will try a different approach...
I have just dumped the db and will try to deploy new instance without the Docker, first within VirtualBox for testing and the production...

Answer 6 · 2023-03-12T16:51:24.000Z

Ok.

postgres@088b0299e19e:/etc$ psql
psql (13.10 (Debian 13.10-1.pgdg110+1))
Type "help" for help.

postgres=# SELECT * FROM alembic_version;
ERROR:  relation "alembic_version" does not exist
LINE 1: SELECT * FROM alembic_version;

The query was not executed on fittrackee database.
It can be selected directly in psql command with psql -U postgres -d fittrackee or with postgres=# \c fittrackee.

Answer 7 · 2023-03-12T19:05:52.000Z

FYI I just updated docker sample files, if you want to give a try.

Answer 8 · 2023-03-12T19:15:49.000Z

Hi,

Thanks for the logs. I don't understand why the last migration was not executed.

Just to be sure, can you check the version in database?
fittrackee=# SELECT * FROM alembic_version;
 version_num  
--------------
 374a670efe23
(1 ligne)

fittrackee=#

A result:


# psql -U postgres -d fittrackee
psql (13.10 (Debian 13.10-1.pgdg110+1))
Type "help" for help.

fittrackee=# SELECT * FROM alembic_version;
 version_num  
--------------
 99a8014de25a
(1 row)

Looks different to your version...
I will try the updated docker sample files now...

Answer 9 · 2023-05-29T07:20:29.000Z

I have had time to get back to it... I am trying to dump whole database to be transplanted to another instance as everything else failed but the dumps seem to be empty. What command should I use to dump the fittrackee database?

Answer 10 · 2023-05-29T08:10:59.000Z

Hi,

with the docker sample files, you can use the following command:

$ docker-compose exec fittrackee-db /bin/bash -c "pg_dump -U postgres -d fittrackee" > <DESTINATION_PATH_ON_YOUR_MACHINE>/fittrackee.pgsql

Answer 11 · 2023-05-30T04:25:29.000Z

Hi,

with the docker sample files, you can use the following command:
$ docker-compose exec fittrackee-db /bin/bash -c "pg_dump -U postgres -d fittrackee" > <DESTINATION_PATH_ON_YOUR_MACHINE>/fittrackee.pgsql

Cool, thanks it worked with a slight change, it should be docker command and not docker-compose:

docker exec fittrackee-db /bin/bash -c "pg_dump -U postgres -d fittrackee" > <DESTINATION_PATH_ON_YOUR_MACHINE>/fittrackee.pgsql

Answer 12 · 2023-05-30T21:45:33.000Z

@SamR1 Thanks for your time Sam!
All sorted!
ftcli db upgrade
The command above still stubbornly did not want to work, but after I dumped the database, rebuilt everything from scratch, uploaded the dumped db and copied workout files over I am officially on the 0.7.16 version now with all my data intact!
After further investigation and testing I found out that command:
make docker-init
which dropped the db I was able to create the user and upload the earlier dumped db without the need of building everything from scratch. All I had to do is to pull new files from git!