Database migration

This guide describes how to migrate UI Bakery Database to your instance. Here, we review three cases:

• Full instance migration

• Partial instance migration

• Data-only migration

Back up all the databases on both instances before performing any actions.

Full instance migration

If you're moving from one instance to another and all the conditions below are true, then no extra actions are required. UI Bakery Postgres will be available from the start.

• Internal MySQL migrated (db container settings and its data)

• UI Bakery Postgres migrated (bakery-db container settings and its data)

• UI Bakery Postgres is available via the same hostname/container name (bakery-db by default)

• UI_BAKERY_CREDENTIALS_SECRET contains the same value

Partial instance migration (UI_BAKERY_CREDENTIALS_SECRET is the same)

If any of the conditions listed above are false, then you would need to manually migrate some technical data to make your UI Bakery Postgres available on the new instance.

Actions required on the OLD instance

1. Dump your bakery-db:

docker exec -t bakery-db pg_dumpall --no-owner -c -U bakery > bakery-db.sql

1. Connect to MySQL:

docker exec -it db /bin/bash
mysql -u bakery -p bakery

1. If you're sure that your workspace is the only workspace on your instance, then execute the query below to get all the required data at a time and skip to the new instance configuration. Otherwise, skip this step.

SELECT
    idb.database_name,
    dc.credentials
FROM
    organization o
JOIN
    internal_database idb ON o.id = idb.organization_id
JOIN
    datasource_credentials dc ON idb.datasource_id = dc.datasource_id
WHERE
    o.id != 1
    AND idb.type = 'BAKERY_POSTGRES';

database_name	credentials
db_f6ba67bd851143108fffd117c7830983	<redacted>

1. Identify your workspace. Most likely it will be the only workspace but you can match its slug:

SELECT id,slug,name FROM organization WHERE id!=1;

id	slug	name
5	my-uib-workspace	test@uibakery

1. Using the id (as an organization_id) from the previous step, get your database_name and identify the datasource_id:

SELECT id,datasource_id,database_name,type FROM internal_database WHERE organization_id = 5 AND type='BAKERY_POSTGRES'

id	datasource_id	database_name	type
24	7	db_f6ba67bd851143108fffd117c7830983	BAKERY_POSTGRES

1. Using the datasource_id from the previous step, get the credentials of your DB:

SELECT credentials FROM datasource_credentials WHERE datasource_id=7;

credentials
<redacted>

Actions required on the NEW instance

1. Restore your PostgreSQL data:

docker cp bakery-db.sql standalone-bakery-db:/bakery-db.sql
docker exec -t standalone-bakery-db psql -U bakery -f bakery-db.sql

1. Identify the user assigned to your database:

docker exec -it standalone-bakery-db psql -U bakery

SELECT datname, pg_catalog.array_to_string(datacl, E'\n') AS access_privileges
FROM pg_database
WHERE datname = '<YOUR_DB_NAME_FROM_OLD_INSTANCE>';

datname	access_privileges
db_f6ba67bd851143108fffd117c7830983	bakery=CTc/bakery=T/bakeryu_9424ec8c70d14f1898bd410def46797c=c/bakery

1. Migrate ownership of all tables to your user:

\c <YOUR_DB_NAME_FROM_PREV_STEP>

DO $$ 
DECLARE 
    r RECORD;
    new_owner TEXT := '<YOUR_USER_FROM_PREV_STEP>'; -- replace with yours, u_9424ec8c70d14f1898bd410def46797c in my case
BEGIN
    FOR r IN (SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema')) LOOP
        EXECUTE format('ALTER TABLE %I.%I OWNER TO %I;', r.schemaname, r.tablename, new_owner);
    END LOOP;
END $$;

1. Connect to MySQL:

docker exec -it db /bin/bash
mysql -u bakery -p bakery

1. Identify your workspace. Most likely it will be the only workspace but you can match its slug:

SELECT id,slug,name FROM organization WHERE id!=1;

id	slug	name
5	my-new-uib-workspace	test-new@uibakery

1. Using the id (as an organization_id) from the previous step, identify your database_name and identify the datasource_id:

SELECT id,datasource_id,database_name,type FROM internal_database WHERE organization_id = 5 AND type='BAKERY_POSTGRES';

id	datasource_id	database_name	type
24	7	db_6c5a251217314da884fbb441f996cd0b	BAKERY_POSTGRES

1. Replace database_name with the value from the OLD instance:

UPDATE internal_database SET database_name='<YOUR_DB_NAME_FROM_OLD_INSTANCE>' WHERE id=24;

1. Using the datasource_id from the 6th step, identify the credentials of your DB:

SELECT id,datasource_id,credentials FROM datasource_credentials WHERE datasource_id=7;

id	datasource_id	credentials
7	7	<redacted>

1. Replace credentials with the value from the OLD instance:

UPDATE datasource_credentials SET credentials='<YOUR_DB_CREDENTIALS_FROM_OLD_INSTANCE>' WHERE datasource_id=7;

Data-only migration

If the two cases above do not apply to your new instance, there's one more way to get your data migrated.

Actions required on the OLD instance

1. Connect to your bakery-db:

docker exec -it bakery-db psql -U bakery

1. Identify your database via the \l command (most likely it will be the third one, where the name starts with db_).

2. Dump your database:

docker exec -t bakery-db pg_dump -U bakery -d <YOUR_DB_NAME> -F p --no-owner --no-acl --inserts > bakery-db.sql

Actions required on the NEW instance

1. Open UI Bakery in a web browser, log in, and navigate to Data Sources.

2. Click on Query runner in the lower left corner, create a Query, and select UI Bakery Postgres in the Run query against dropdown.

3. Paste bakery-db.sql file contents in the query window.

4. Verify that everything is correct and execute the query.