Twenty•9mo ago

Backup/restore/Migrate database

I have a local server in 0.3.2 and a (future) prod server in 0.3.3. I would like to move my database from the first one to the second one. Ok so I dumped DB:

pg_dump -U twenty -h localhost -p 5432 -Fc default > db_twenty.dump

pg_dump -U twenty -h localhost -p 5432 -Fc default > db_twenty.dump

I scp the dump to the server, copy in docker, and connect to this docker container:

docker cp ../db_twenty.dump twenty-db-1:
docker exec -it twenty-db-1 /bin/bash

docker cp ../db_twenty.dump twenty-db-1:
docker exec -it twenty-db-1 /bin/bash

I removed existing database and create a fresh one:

dropdb -U twenty -h localhost -p 5432 default
createdb  -U twenty -h localhost -p 5432 default

dropdb -U twenty -h localhost -p 5432 default
createdb  -U twenty -h localhost -p 5432 default

Next I restore the database

cd

pg_restore -U twenty -h localhost -p 5432 -C --clean --create --if-exists -d default db_twenty.dump 
Password: 
pg_restore: error: could not execute query: ERROR:  cannot drop the currently open database
Command was: DROP DATABASE IF EXISTS "default";
pg_restore: error: could not execute query: ERROR:  database "default" already exists
Command was: CREATE DATABASE "default" WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';


pg_restore: warning: errors ignored on restore: 2

cd

pg_restore -U twenty -h localhost -p 5432 -C --clean --create --if-exists -d default db_twenty.dump 
Password: 
pg_restore: error: could not execute query: ERROR:  cannot drop the currently open database
Command was: DROP DATABASE IF EXISTS "default";
pg_restore: error: could not execute query: ERROR:  database "default" already exists
Command was: CREATE DATABASE "default" WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';


pg_restore: warning: errors ignored on restore: 2

In server side:

docker exec -it twenty-server-1 sh

/app/packages/twenty-server $ yarn database:migrate:prod

docker exec -it twenty-server-1 sh

/app/packages/twenty-server $ yarn database:migrate:prod

And restart:

docker-compose restart

docker-compose restart

It seems to work. Do you think I did it the right way? Any improvement to that process?

8 Replies

Freebios•9mo ago

@charles if it seems okay to you you (both migrating DB and upgrading the schema) I could write it down in the doc 🙂 I see that there is an "upgrade guide" in "soon" status !

charles•9mo ago

There are additionnal steps to migrate workspace schemas. We will start documenting that starting 0.4 Are you facing any issue with your Twenty instance @iero? I can help if needed 🙂

Freebios•9mo ago

He actually migrated to the new docker-compose and was wondering how to move the data to the new instance

charles•9mo ago

once the data is migrated, we will still need to take care of application level changes

greg [iero]OP•9mo ago

Indeed, it seems to work, I don't see any error yet but I still work mainly in the old 0.3.2 database yet. I will be happy to do the additional step in this test environment as I will have to migrate my database @charles any commands to test? I will migrate my main database as well to be ready for 0.3.4

charles•9mo ago

great! It will actually be 0.4.0! I'm deploying right now on our production, I will face all the issues first so I can help others once the 0.4.0 is released

greg [iero]OP•9mo ago

Great news! Will it possible to migrate my db directly from 0.3.2 to 0.4.0?

charles•9mo ago

I'll help you 🙂

Gaming

Programming

Backup/restore/Migrate database