Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to update via Docker? #139

Closed
pzehle opened this issue Jun 29, 2021 · 15 comments
Closed

How to update via Docker? #139

pzehle opened this issue Jun 29, 2021 · 15 comments
Labels
documentation Improvements or additions to documentation

Comments

@pzehle
Copy link

pzehle commented Jun 29, 2021

I keep pulling the 'latest' image from Docker, but I cannot see the latest changes in the project and Docker keeps saying that I have the 'latest' image, even though the image it is pulling has the tag that it was created 2 months ago, instead of 5 days ago, like the latest image here: Docker Hub.

@Syndamia
Copy link

Syndamia commented Jun 30, 2021

image
The current latest version is 1.0.0-beta, so you'll need to pull that image:

docker pull meltyshev/planka:1.0.0-beta

BUT IF YOU WANT TO UPDATE AN EXISTING SETUP

Back up your database! This update features some database structure changes, you'll either need to manually update the database or just start from scratch.

How I updated my database (Linux)

  1. Get into the planka image:
    docker exec -it planka_planka_1 sh
  2. Change into the db directory and install knex:
    cd db && npm install -g knex
  3. Apply migrations
    knex migrate:up 20180721234154_create_project_manager_table.js && knex migrate:up 20180722001747_create_board_membership_table.js
    • You should also be able to just do: knex migrate:latest, but I haven't tested it
    • If you get Error: The migration directory is corrupt, just do
    wget https://raw.githubusercontent.com/plankanban/planka/d6cb1f6683774ffa52012f42b6483a8a93e704d1/server/db/migrations/20180721234154_create_project_membership_table.js -P migrations
    and try again
  4. Get into the PostgreSQL database, inside the planka_postgres_1 image:
    docker exec -it planka_postgres_1 sh && psql --username postgres --dbname planka
  5. Add board members
    1. Run select * from board; to view information about all boards, then select * from user_account; for viewing all accounts
    2. Insert values about to what board each user has access:
      insert into board_membership values (DEFAULT, boardid, userid, NULL, NULL);
      where boardid must be replaced with the desired board id, and userid - desired user id
  6. Transfer project_membership data to project_manager
    insert into project_manager (id, project_id, user_id, created_at, updated_at) select id, project_id, user_id, created_at, updated_at from project_membership;
    • If you feel like it, you can also remove the project_membership table afterwards, since it seems that it's not needed anymore, but it won't to any harm if you leave it
      drop table project_membership;
  7. Update card table
    alter table card add creator_user_id bigint not null default '0';
    where 0 should be replaced by a User's id.
  8. Update column name of attachment table
    alter table attachment rename column user_id to creator_user_id;

And everything should be working. If you're getting stuck on loading anywhere, you can check database output messages with:

docker logs planka_postgres_1 

@pzehle
Copy link
Author

pzehle commented Jun 30, 2021

Thank you very much. Will try and if anything goes wrong I will post it here.

@meltyshev meltyshev added the documentation Improvements or additions to documentation label Jul 12, 2021
@johnchristopher
Copy link

johnchristopher commented Aug 16, 2021

@Syndamia :

Great ! Should you destroy the containers first and recreate them with the 1.0.0-beta version image and then follow the steps for manual migration or should the manual migration steps be done before switching to new image ?

edit: to answer my own question: it seems you have to stop your running containers, change planka image tag, recreate containers and follow the steps.

Unfortunately it doesn't seem to work. I completed the procedure three times from a back-up and I get the same results: black screen in the browser and no logs from either containers and a lot of javascript error in the browser console.

@Syndamia
Copy link

Syndamia commented Aug 17, 2021

Edit I revisited my old explanation and my server and I found the original answer out of place.

I also found a mistake (that is now fixed). To check the database output, run this command:

docker logs planka_postgres_1

If you still don't find any info, could you send some of the JavaScript error messages?

@johnchristopher
Copy link

johnchristopher commented Aug 19, 2021

@Syndamia : Here's the complete output:

jc@localhost ~/tmp/planka
$ docker exec -it planka_planka_1 sh
/app # cd db && npm install -g knex

added 177 packages in 9s

2 packages are looking for funding
  run `npm fund` for details
npm notice 
npm notice New patch version of npm available! 7.20.3 -> 7.20.6
npm notice Changelog: https://github.com/npm/cli/releases/tag/v7.20.6
npm notice Run npm install -g [email protected] to update!
npm notice 
/app/db # wget https://raw.githubusercontent.com/plankanban/planka/d6cb1f6683774ffa52012f42b6483a8a93e704d1/server/db/migrations/20180721234154_create_project_membership_ta
ble.js -P migrations
Connecting to raw.githubusercontent.com (185.199.111.133:443)
saving to 'migrations/20180721234154_create_project_membership_table.js'
20180721234154_creat 100% |****************************************************************************************************************************|   539  0:00:00 ETA
'migrations/20180721234154_create_project_membership_table.js' saved
/app/db # knex migrate:up 20180721234154_create_project_manager_table.js && knex migrate:up 20180722001747_create_board_membership_table.js
Batch 2 ran the following migrations:
20180721234154_create_project_manager_table.js
Batch 3 ran the following migrations:
20180722001747_create_board_membership_table.js
/app/db # exit

jc@localhost ~/tmp/planka
$ docker exec -it planka_postgres_1 sh 
/ # psql --username postgres --dbname planka
psql (13.2)
Type "help" for help.

planka=# insert into board_membership values (DEFAULT, 410087937368130564, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into board_membership values (DEFAULT, 410206038659171426, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into board_membership values (DEFAULT, 410129813408318545, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into board_membership values (DEFAULT, 410104802068923421, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into board_membership values (DEFAULT, 410127174461293642, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into board_membership values (DEFAULT, 410213794833237101, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into board_membership values (DEFAULT, 410218076949185655, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into board_membership values (DEFAULT, 410229564292203678, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into board_membership values (DEFAULT, 410286856303805612, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into board_membership values (DEFAULT, 410287631209858226, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into board_membership values (DEFAULT, 414922820984767711, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into board_membership values (DEFAULT, 410119819900224564, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into board_membership values (DEFAULT, 478909522312693254, 410050284119655425, NULL, NULL);
INSERT 0 1
planka=# insert into project_manager (id, project_id, user_id, created_at, updated_at) select id, project_id, user_id, created_at, updated_at from project_membership;
INSERT 0 2
planka=# alter table card add creator_user_id bigint not null default '410050284119655425';
ALTER TABLE
planka=# alter table attachment rename column user_id to creator_user_id;
ALTER TABLE
planka=# exit
/ # exit

jc@localhost ~/tmp/planka
$ docker-compose restart
Restarting planka_planka_1   ... done
Restarting planka_postgres_1 ... done

jc@localhost ~/tmp/planka
$ docker-compose ps
      Name                     Command               State           Ports         
-----------------------------------------------------------------------------------
planka_planka_1     docker-entrypoint.sh bash  ...   Up      0.0.0.0:3000->1337/tcp
planka_postgres_1   docker-entrypoint.sh postgres    Up      5432/tcp              

jc@localhost ~/tmp/planka
$ docker-compose logs
Attaching to planka_planka_1, planka_postgres_1
planka_1    | debug: Detected Sails environment is "production", but NODE_ENV is `undefined`.
planka_1    | debug: Automatically setting the NODE_ENV environment variable to "production".
planka_1    | debug: 
planka_1    | debug: It looks like your `sails.config.sockets.onlyAllowOrigins` array only includes
planka_1    | debug: references to the `localhost` origin.  This is completely valid, but be sure
planka_1    | debug: to add any other origins to this list that you'd like to accept socket
planka_1    | debug: connections from!
planka_1    | debug: 
planka_1    | debug: -------------------------------------------------------
planka_1    | debug: :: Thu Aug 19 2021 16:21:49 GMT+0000 (Coordinated Universal Time)
planka_1    | debug: Environment : production
planka_1    | debug: Port        : 1337
planka_1    | debug: -------------------------------------------------------
planka_1    | error: Sending 500 ("Server Error") response: 
planka_1    |  AdapterError: Unexpected error from database adapter: relation "public.project_manager" does not exist
planka_1    |     at fn (/app/api/helpers/project-managers/get-many.js:10:27)
planka_1    |     at wrapper (/app/node_modules/@sailshq/lodash/lib/index.js:3282:19)
planka_1    |     at Deferred.parley.retry [as _handleExec] (/app/node_modules/machine/lib/private/help-build-machine.js:1014:29)
planka_1    |     at Deferred.exec (/app/node_modules/parley/lib/private/Deferred.js:286:10)
planka_1    |     at Deferred.tryCatcher (/app/node_modules/bluebird/js/release/util.js:11:23)
planka_1    |     at ret (eval at makeNodePromisifiedEval (/app/node_modules/bluebird/js/release/promisify.js:184:12), <anonymous>:14:23)
planka_1    |     at Deferred.toPromise (/app/node_modules/parley/lib/private/Deferred.js:572:19)
planka_1    |     at Deferred.then (/app/node_modules/parley/lib/private/Deferred.js:431:22)
planka_1    |     at processTicksAndRejections (internal/process/task_queues.js:95:5)
planka_1    | debug: Detected Sails environment is "production", but NODE_ENV is `undefined`.
planka_1    | debug: Automatically setting the NODE_ENV environment variable to "production".
planka_1    | debug: 
planka_1    | debug: It looks like your `sails.config.sockets.onlyAllowOrigins` array only includes
planka_1    | debug: references to the `localhost` origin.  This is completely valid, but be sure
planka_1    | debug: to add any other origins to this list that you'd like to accept socket
planka_1    | debug: connections from!
planka_1    | debug: 
planka_1    | debug: -------------------------------------------------------
planka_1    | debug: :: Thu Aug 19 2021 16:26:11 GMT+0000 (Coordinated Universal Time)
planka_1    | debug: Environment : production
planka_1    | debug: Port        : 1337
planka_1    | debug: -------------------------------------------------------
postgres_1  | 
postgres_1  | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres_1  | 
postgres_1  | 2021-08-19 16:21:45.690 UTC [1] LOG:  starting PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
postgres_1  | 2021-08-19 16:21:45.690 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres_1  | 2021-08-19 16:21:45.690 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres_1  | 2021-08-19 16:21:45.699 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_1  | 2021-08-19 16:21:45.718 UTC [20] LOG:  database system was shut down at 2021-08-19 16:21:37 UTC
postgres_1  | 2021-08-19 16:21:45.726 UTC [1] LOG:  database system is ready to accept connections
postgres_1  | 2021-08-19 16:21:59.295 UTC [28] ERROR:  relation "public.project_manager" does not exist at character 71
postgres_1  | 2021-08-19 16:21:59.295 UTC [28] STATEMENT:  select "id", "created_at", "updated_at", "project_id", "user_id" from "public"."project_manager" where "user_id" = $1 order by "id" ASC limit $2
postgres_1  | 2021-08-19 16:25:58.152 UTC [1] LOG:  received fast shutdown request
postgres_1  | 2021-08-19 16:25:58.158 UTC [1] LOG:  aborting any active transactions
postgres_1  | 2021-08-19 16:25:58.158 UTC [1] LOG:  background worker "logical replication launcher" (PID 26) exited with exit code 1
postgres_1  | 2021-08-19 16:25:58.158 UTC [21] LOG:  shutting down
postgres_1  | 2021-08-19 16:25:58.210 UTC [1] LOG:  database system is shut down
postgres_1  | 
postgres_1  | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres_1  | 
postgres_1  | 2021-08-19 16:25:59.142 UTC [1] LOG:  starting PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
postgres_1  | 2021-08-19 16:25:59.142 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres_1  | 2021-08-19 16:25:59.142 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres_1  | 2021-08-19 16:25:59.153 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_1  | 2021-08-19 16:25:59.165 UTC [20] LOG:  database system was shut down at 2021-08-19 16:25:58 UTC
postgres_1  | 2021-08-19 16:25:59.173 UTC [1] LOG:  database system is ready to accept connections

I just noticed this line:

postgres_1  | 2021-08-19 16:21:59.295 UTC [28] ERROR:  relation "public.project_manager" does not exist at character 71
postgres_1  | 2021-08-19 16:21:59.295 UTC [28] STATEMENT:  select "id", "created_at", "updated_at", "project_id", "user_id" from "public"."project_manager" where "user_id" = $1 order by "id" ASC limit $2

And here's the console output:

TypeError: n is null
    value User.js:290
    value User.js:289
    zn user.js:39
    Redux 4
    nE ProjectsContainer.js:10
    Redux 5
    React 2
    j Redux
    React 10
    unstable_runWithPriority scheduler.production.min.js:18
    React 4
    Redux 4
    a middleware.js:26
    Redux 12
react-dom.production.min.js:216:199
TypeError: n is null
    value User.js:290
    value User.js:289
    zn user.js:39
    Redux 4
    nE ProjectsContainer.js:10
    Redux 5
    React 2
    j Redux
    React 10
    unstable_runWithPriority scheduler.production.min.js:18
    React 4
    Redux 4
    a middleware.js:26
    Redux 12
io-6de156f3.js:111:10
The above error occurred in task ji
    created by Gj
    created by Qj
Tasks cancelled due to error:
Gj
Xb
uj
lj
pj
mj
Oj
Ej
xj
Cj
Rj
kj
yj
Nj
Uj
wj
Mj
Bj
Fj
Vj io-6de156f3.js:112:10

I fixed(?) the postgresql error by replacing insert into project_manager (id, project_id, user_id, created_at, updated_at) select id, project_id, user_id, created_at, updated_at from project_membership;with insert into public.project_manager (id, project_id, user_id, created_at, updated_at) select id, project_id, user_id, created_at, updated_at from project_membership; but the console javascript errors remain.

edit: and to be perfectly clear, I have backups of every volumes and I restore them and recreate 0.16 containers before each tr and I get a functioning planka populated with my data.

@TheFrazer
Copy link

I didn't have any problems with the database update and also had no errors there, but get the same js errors.

@johnchristopher
Copy link

johnchristopher commented Aug 20, 2021

I keep pulling the 'latest' image from Docker, but I cannot see the latest changes in the project and Docker keeps saying that I have the 'latest' image, even though the image it is pulling has the tag that it was created 2 months ago, instead of 5 days ago, like the latest image here: Docker Hub.

As a rule of thumb latest tag should never be used because there are not guarantee it's the actual latest build (or that the build was produced from the most up to date code). The tag label is just a free field without constraints. See https://vsupalov.com/docker-latest-tag/ and https://www.cloudsavvyit.com/10691/understanding-dockers-latest-tag/

What happens is that since you most likely have a local image of Planka tagged with latest the container is being created from that local image. It doesn't pull out latest from the web. Remove that image and it will pull latest from the web. That's why you can never be sure you have the image you think you have if you rely on the latest tag.

@TheFrazer
Copy link

Ok, I created a new database and let planka create the schemes. Then I copied each table from the old database one by one to the new database. When I copy board_membership it results in the js error and the site not loading. Leaving this table out and adding myself to every board on the website seems to work just fine. So it is working for me again.

@johnchristopher
Copy link

johnchristopher commented Aug 21, 2021

OK, I was also able to upgrade to planka:1.0.0-beta from planka:0.1.6 but since @TheFrazer went one way (copying tables data) I decided to go another (updating tables).

If you know your user id here's the SQL query to run. Then login and reclaim ownership of each board:

ALTER TABLE attachment RENAME COLUMN user_id TO creator_user_id;

CREATE TABLE "public"."board_membership" (
    "id" bigint DEFAULT next_id() NOT NULL,
    "board_id" bigint NOT NULL,
    "user_id" bigint NOT NULL,
    "created_at" timestamp,
    "updated_at" timestamp,
    CONSTRAINT "board_membership_board_id_user_id_unique" UNIQUE ("board_id", "user_id"),
    CONSTRAINT "board_membership_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

CREATE INDEX "board_membership_user_id_index" ON "public"."board_membership" USING btree ("user_id");

ALTER TABLE card ADD COLUMN "creator_user_id" bigint;
UPDATE card SET creator_user_id=410050284119655425;
ALTER TABLE card ALTER COLUMN creator_user_id SET NOT NULL;

ALTER TABLE project_membership RENAME TO project_manager;
DROP INDEX "project_membership_user_id_index";
CREATE INDEX "project_manager_user_id_index" ON "public"."project_manager" USING btree ("user_id");
ALTER TABLE project_manager RENAME CONSTRAINT project_membership_pkey TO project_manager_pkey;
ALTER TABLE project_manager RENAME CONSTRAINT project_membership_project_id_user_id_unique TO project_manager_project_id_user_id_unique;

Those steps are for when only one user created projects and cards. It's possible to adapt but you most likely may have to manually update ownership of cards, boards and projects. Either through Planka's web UI (easy but can take some time) or through SQL (harder but can be processed in batches).

On a running docker setup:

  1. Stop and remove the planka container:
$ docker-compose stop planka ; docker-compose rm planka;
  1. I am using adminer http://localhost:3001 out of convenience but you could run an SQL file as an input query to the database server from the postgres container just as easily.

Add the following to the docker-compose.yml that ships with Planka (user:postgres, password: postgres, server:postgres) :

  adminer:
    image: adminer:4.8.1-standalone
    ports:
      - 3001:8080

and run

$ docker-compose up -d adminer
  1. Get your user id with this query:
SELECT id, username, name, email FROM user_account;
-- SELECT id, username, name, email FROM user_account WHERE username=johnchristopher;
  1. Run this query and modify this line UPDATE card SET creator_user_id= with the user id you got at step 3:
ALTER TABLE attachment RENAME COLUMN user_id TO creator_user_id;

CREATE TABLE "public"."board_membership" (
    "id" bigint DEFAULT next_id() NOT NULL,
    "board_id" bigint NOT NULL,
    "user_id" bigint NOT NULL,
    "created_at" timestamp,
    "updated_at" timestamp,
    CONSTRAINT "board_membership_board_id_user_id_unique" UNIQUE ("board_id", "user_id"),
    CONSTRAINT "board_membership_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

CREATE INDEX "board_membership_user_id_index" ON "public"."board_membership" USING btree ("user_id");

ALTER TABLE card ADD COLUMN "creator_user_id" bigint;
UPDATE card SET creator_user_id=410050284119655425;
ALTER TABLE card ALTER COLUMN creator_user_id SET NOT NULL;

ALTER TABLE project_membership RENAME TO project_manager;
DROP INDEX "project_membership_user_id_index";
CREATE INDEX "project_manager_user_id_index" ON "public"."project_manager" USING btree ("user_id");
ALTER TABLE project_manager RENAME CONSTRAINT project_membership_pkey TO project_manager_pkey;
ALTER TABLE project_manager RENAME CONSTRAINT project_membership_project_id_user_id_unique TO project_manager_project_id_user_id_unique;
  1. Update docker-compose.yml:
  planka:
    image: meltyshev/planka:1.0.0-beta
  1. Recreate planka container:
$ docker-compose up -d planka
  1. Now you should be able to login and display your boards. There could be a ten-seconds waiting the first time you refresh http://localhost:3000.
    7b. Step 5 from @Syndamia should also work if you don't want to click through Planka's web UI to add yourself back as board owner but there could be problems.
    For instance: after inserting all the rows Planka's web UI would turn black again. Upon inspection I found an orphaned board in the board table (that board had no associated cards in the card table and its name was identical to another board... maybe it got deleted from the UI, is hidden because it has no child board but it's still present in the database ?) and when I removed the insert statement that referenced that board then Planka's web UI was operating normally again.

Qestion to the pgql guru and @meltyshev. Is it safe to do that:

DROP INDEX "project_membership_user_id_index";
CREATE INDEX "project_manager_user_id_index" ON "public"."project_manager" USING btree ("user_id");

@meltyshev meltyshev pinned this issue Apr 21, 2022
@immanuelfodor
Copy link

Is there a bash one-liner or something like that for backing up the whole Planka 0.1.6 DB? And maybe an easy restore one-liner? 😀

PG is not so easy to backup and restore, I had bad experience regarding it with complex DBs before, so I would like a bullet-proof method instead of a trial&error process with lots of downtime if things go sour with the DB upgrade.

@upuldi
Copy link

upuldi commented Apr 25, 2022

OK, I was also able to upgrade to planka:1.0.0-beta from planka:0.1.6 but since @TheFrazer went one way (copying tables data) I decided to go another (updating tables).

If you know your user id here's the SQL query to run. Then login and reclaim ownership of each board:

ALTER TABLE attachment RENAME COLUMN user_id TO creator_user_id;

CREATE TABLE "public"."board_membership" (
    "id" bigint DEFAULT next_id() NOT NULL,
    "board_id" bigint NOT NULL,
    "user_id" bigint NOT NULL,
    "created_at" timestamp,
    "updated_at" timestamp,
    CONSTRAINT "board_membership_board_id_user_id_unique" UNIQUE ("board_id", "user_id"),
    CONSTRAINT "board_membership_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

CREATE INDEX "board_membership_user_id_index" ON "public"."board_membership" USING btree ("user_id");

ALTER TABLE card ADD COLUMN "creator_user_id" bigint;
UPDATE card SET creator_user_id=410050284119655425;
ALTER TABLE card ALTER COLUMN creator_user_id SET NOT NULL;

ALTER TABLE project_membership RENAME TO project_manager;
DROP INDEX "project_membership_user_id_index";
CREATE INDEX "project_manager_user_id_index" ON "public"."project_manager" USING btree ("user_id");
ALTER TABLE project_manager RENAME CONSTRAINT project_membership_pkey TO project_manager_pkey;
ALTER TABLE project_manager RENAME CONSTRAINT project_membership_project_id_user_id_unique TO project_manager_project_id_user_id_unique;

Those steps are for when only one user created projects and cards. It's possible to adapt but you most likely may have to manually update ownership of cards, boards and projects. Either through Planka's web UI (easy but can take some time) or through SQL (harder but can be processed in batches).

On a running docker setup:

  1. Stop and remove the planka container:
$ docker-compose stop planka ; docker-compose rm planka;
  1. I am using adminer http://localhost:3001 out of convenience but you could run an SQL file as an input query to the database server from the postgres container just as easily.

Add the following to the docker-compose.yml that ships with Planka (user:postgres, password: postgres, server:postgres) :

  adminer:
    image: adminer:4.8.1-standalone
    ports:
      - 3001:8080

and run

$ docker-compose up -d adminer
  1. Get your user id with this query:
SELECT id, username, name, email FROM user_account;
-- SELECT id, username, name, email FROM user_account WHERE username=johnchristopher;
  1. Run this query and modify this line UPDATE card SET creator_user_id= with the user id you got at step 3:
ALTER TABLE attachment RENAME COLUMN user_id TO creator_user_id;

CREATE TABLE "public"."board_membership" (
    "id" bigint DEFAULT next_id() NOT NULL,
    "board_id" bigint NOT NULL,
    "user_id" bigint NOT NULL,
    "created_at" timestamp,
    "updated_at" timestamp,
    CONSTRAINT "board_membership_board_id_user_id_unique" UNIQUE ("board_id", "user_id"),
    CONSTRAINT "board_membership_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

CREATE INDEX "board_membership_user_id_index" ON "public"."board_membership" USING btree ("user_id");

ALTER TABLE card ADD COLUMN "creator_user_id" bigint;
UPDATE card SET creator_user_id=410050284119655425;
ALTER TABLE card ALTER COLUMN creator_user_id SET NOT NULL;

ALTER TABLE project_membership RENAME TO project_manager;
DROP INDEX "project_membership_user_id_index";
CREATE INDEX "project_manager_user_id_index" ON "public"."project_manager" USING btree ("user_id");
ALTER TABLE project_manager RENAME CONSTRAINT project_membership_pkey TO project_manager_pkey;
ALTER TABLE project_manager RENAME CONSTRAINT project_membership_project_id_user_id_unique TO project_manager_project_id_user_id_unique;
  1. Update docker-compose.yml:
  planka:
    image: meltyshev/planka:1.0.0-beta
  1. Recreate planka container:
$ docker-compose up -d planka
  1. Now you should be able to login and display your boards. There could be a ten-seconds waiting the first time you refresh http://localhost:3000.
    7b. Step 5 from @Syndamia should also work if you don't want to click through Planka's web UI to add yourself back as board owner but there could be problems.
    For instance: after inserting all the rows Planka's web UI would turn black again. Upon inspection I found an orphaned board in the board table (that board had no associated cards in the card table and its name was identical to another board... maybe it got deleted from the UI, is hidden because it has no child board but it's still present in the database ?) and when I removed the insert statement that referenced that board then Planka's web UI was operating normally again.

Qestion to the pgql guru and @meltyshev. Is it safe to do that:

DROP INDEX "project_membership_user_id_index";
CREATE INDEX "project_manager_user_id_index" ON "public"."project_manager" USING btree ("user_id");

This fixed my issue. Saved me 1000s of hours. THANK YOU.......

ALSO, I had to manually update the migration table to match the new migration table. I created a fresh DB and pointed PLANKA to it, once it got populated, copied the migrations table from the new database to the old database. What I noticed is even though both of the tables have the same number of records they are different to one. another. After updating the migration table to match with the new migration table I pointed the PLANKA back to the old DB. Thats it.

If you find you don't have access to projects or boards, You will have to manually add records to the tables. XXX is your user id.

-- insert into board memebership table
INSERT INTO public.board_membership(
id, board_id, user_id, created_at, updated_at)

	select  next_id() , b.id ,XXXXX,now(), null
	from board b 
	where b.id not in (
		select m.board_id from board_membership m where m.user_id = XXXXX
	)	
commit;

You might have to do the same for the project membership table.

Also, make sure to run a scheduled backup of the DB.
pg_dump -U postgres planka | gzip > planka.gz

Add the following to the cron tab.
24 1 * * * /backup/.postgres-planka-backup -U postgres

@Pheggas
Copy link

Pheggas commented May 4, 2022

After downloading the membership table, and applying migration, knox throws error: Migration "20180721234154_create_project_manager_table.js" not found.

While that file is clearly there:
image

Is this a bug?

@shamoon
Copy link

shamoon commented Jun 27, 2022

Any ideas why I get this same error now every time the container is recreated? I no longer have to perform migrations of course just wget the file and planka is happy again. Im running latest and I see the migration exists in my db:

...
 17 | 20180721234154_create_project_manager_table.js    |     2 | 2022-05-01 06:59:34.286+00
...

Also tbh I dont really understand why that file is still not in the docker image and this error persists?

@johngalactic
Copy link

After following the instructions in @johnchristopher 's post, I still had to deal with migrations being broken. After I updated my 0.1.6 DB to 1.0.0-beta following these instructions: #139 (comment)

I ran this to update the migration table on 1.0.0-beta. Afterwards I was able to update to 1.1.0, then 1.1.3, 1.2.1, 1.3.1, 1.4.0. I stepped through it to verify the migrations run automatically.

UPDATE migration SET name='20180721234154_create_project_manager_table.js' WHERE name='20180721234154_create_project_membership_table.js';

INSERT INTO migration (name, batch, migration_time) VALUES ('20180722001747_create_board_membership_table.js', 2, CURRENT_TIMESTAMP);

I believe the reason people have to do wget is because 20180721234154_create_project_membership_table.js was deleted from the migration folder and replaced with 20180721234154_create_project_manager_table.js. The migration table in 0.1.6 has a record for a migration with 20180721234154_create_project_membership_table.js which no longer exists in the newest code base.

@daniel-hiller
Copy link
Member

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
documentation Improvements or additions to documentation
Projects
None yet
Development

No branches or pull requests