# Database migration

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

* [Full instance migration](#full-instance-migration)
* [Partial instance migration](#partial-instance-migration-ui_bakery_credentials_secret-is-the-same)
* [Data-only migration](#data-only-migration)

{% hint style="danger" %}
**Back up all the databases** on both instances before performing any actions.
{% endhint %}

## Full instance migration

If you're moving from one instance to another and all the conditions below are <mark style="color:green;">true</mark>, 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 <mark style="color:red;">false</mark>, 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`:

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

2. Connect to MySQL:

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

3. 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.**

```sql
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> |

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

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

<table><thead><tr><th width="126.8671875">id</th><th>slug</th><th>name</th></tr></thead><tbody><tr><td>5</td><td>my-uib-workspace</td><td>test@uibakery</td></tr></tbody></table>

5. Using the **id** (as an *organization\_id*) from the previous step, get your **database\_name** and identify the **datasource\_id**:

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

<table><thead><tr><th width="74.1953125">id</th><th width="115.08984375">datasource_id</th><th width="320.421875">database_name</th><th>type</th></tr></thead><tbody><tr><td>24</td><td>7</td><td>db_f6ba67bd851143108fffd117c7830983</td><td>BAKERY_POSTGRES</td></tr></tbody></table>

6. Using the **datasource\_id** from the previous step, get the **credentials** of your DB:

```sql
SELECT credentials FROM datasource_credentials WHERE datasource_id=7;
```

<table><thead><tr><th width="152.8203125">credentials</th></tr></thead><tbody><tr><td>&#x3C;redacted></td></tr></tbody></table>

### Actions required on the NEW instance

1. Restore your PostgreSQL data:

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

2. Identify the user assigned to your database:

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

```sql
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 |

3. Migrate ownership of all tables to your user:

```
\c <YOUR_DB_NAME_FROM_PREV_STEP>
```

```sql
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 $$;
```

4. Connect to MySQL:

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

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

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

<table><thead><tr><th width="147.7265625">id</th><th>slug</th><th>name</th></tr></thead><tbody><tr><td>5</td><td>my-new-uib-workspace</td><td>test-new@uibakery</td></tr></tbody></table>

6. Using the **id** (as an *organization\_id*) from the previous step, identify your **database\_name** and identify the **datasource\_id**:

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

<table><thead><tr><th width="55.92578125">id</th><th width="121.8046875">datasource_id</th><th width="333.1171875">database_name</th><th>type</th></tr></thead><tbody><tr><td>24</td><td>7</td><td>db_6c5a251217314da884fbb441f996cd0b</td><td>BAKERY_POSTGRES</td></tr></tbody></table>

7. Replace **database\_name** with the value from the OLD instance:

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

8. Using the **datasource\_id** from the 6th step, identify the **credentials** of your DB:

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

<table><thead><tr><th width="107.00390625">id</th><th width="136.890625">datasource_id</th><th width="131.7265625">credentials</th></tr></thead><tbody><tr><td>7</td><td>7</td><td>&#x3C;redacted></td></tr></tbody></table>

9. Replace **credentials** with the value from the OLD instance:

```sql
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`:

```bash
docker exec -it bakery-db psql -U bakery
```

2. Identify your database via the `\l` command (most likely it will be the third one, where the name starts with *db\_*).
3. Dump your database:

```bash
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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.uibakery.io/extras/ui-bakery-postgres/database-migration.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.