Manage Database Change with 1-Click Data Rollback

When changing data in the database, it's advisable to have a backup of the data you plan to modify in case you need to roll back. Bytebase offers a feature called 1-Click Data Rollback to assist with this. You can also enable auto-backup feature to automatically backup the data when you create a change data issue. This tutorial will guide you through this process.

Preparation#

1. Make sure you have Docker installed, and if you don’t have important existing Bytebase data locally, you can start over from scratch by rm -rf ~/.bytebase/data.

Step 1 - Start Bytebase#

1. Copy and paste the commands to start one Bytebase via Docker.

   docker run --rm --init \
     --name bytebase \
     --publish 8080:8080 --pull always \
     --volume ~/.bytebase/data:/var/opt/bytebase \
     bytebase/bytebase:3.1.0

2. Regsiter an admin account and it will be granted the workspace admin role automatically.

Step 2 - Prepare schema bbdataarchive#

Bytebase stores the backup data in a dedicated place. For Postgres, it's stored under the bbdataarchive schema for the changing database.

1. Go to Sample Project, click Database > Databases on the left side, choose hr_test, and then click Edit Schema.

2. In the Schema Editor, click ... and then click Create Schema. Fill it with the name bbdataarchive and click Create.

   

3. You can now see the bbdataarchive schema in green color. Click Preview issue. Create the issue and wait till it rolls out automatically. Now the bbdataarchive schema is created.

   

Step 3 - Change Data with Backup#

1. Before the change, go to SQL Editor, choose hr_test and double click employee table, and you'll see the current data. We'll try to change the first_name for Georgi.

   

2. Go to Sample Project and Database > Databases again, choose hr_test, and then click Change Data.

3. Turn on Prior Backup on the right, input the SQL into the field, and then click Create.

   UPDATE employee SET first_name = 'Adela' WHERE emp_no = 10001;

   

   If you haven't created the bbdataarchive schema, you'll see the error message.

   

4. After the issue is created and then rolled out, you can see there is an activity saying the data is backed up to a new table under the previously created bbdataarchive schema.

   

5. Go to Database > Databases again, and click hr_test. You'll be redirected to the database page.

6. Choose bbdataarchive schema, and you can see the backup table.

   

7. To verify, go to SQL Editor. Choose employee table in hr_test under public schema, input the following SQL script and click Run, and you'll see the data is changed.

   SELECT * FROM "public"."employee" ORDER BY emp_no LIMIT 50;

   

8. Choose bbdataarchive schema, and double-click the table below. You'll see the backup data in the query result.

   

Step 4 - 1-Click Rollback Data#

1. Go back to the issue page, click the Rollback button.

   

2. You'll be redirected to a new issue creation page. Click Create to execute the rollback.

   

3. To verify, go to SQL Editor, you can see Georgi is back.

Step 5 - Upgrade and Enable Auto-Backup#

1. Go to Setting in the Project, there're two options under Issue related:

   • Auto enable backup
   • Skip backup errors

   If you don't want to turn on the backup feature every time you create a change data issue. You can upgrade to Pro or Enterprise plan to enable the auto-backup feature.

   

2. You can contact us to request a trial license key.

3. After you get a license, click the Setting icon on the top right, and then click Workspace > Subscription to upload the license.

4. Click the pen icon, select the instances you want to enable Enterprise features, and click Confirm.

   

5. Go back to the setting page, now you can enable those two options.

Noteworthy Difference#

If you want to use MySQL/SQL Server/Oracle, instead of creating a bbdataarchive schema, you should create a bbdataarchive database instead of schema to store the backup data. Check the doc for more details.