Database Cloning

During the development of an ERP system, you will need a testing environment. You can create one for scratch, but most of the time you would need some real test data. If you have a production system, you can clone its database, but not all data. This document explains the procedure to clone a PostgreSQL database from a production environment.

There are a few steps to follow in order to ensure a smooth database cloning. These steps must be followed because there is no tool that can do them all (for the moment). You will need some third-party tools to complete this task:

• DBeaver database manager for data export;
• pgAdmin database manager for database schema export.

Known issues

When exporting selected tables with DBeaver, some data and tables might not get exported even those were selected. In this case you need to export the whole schema or take a look at the configuration saved in the file located at:

C:\Users\<user_name>\AppData\Roaming\DBeaverData\workspace6\General\.dbeaver\tasks.json

See if you can manually configure it. You might not be able to do that, so you will need to export the whole schema and manually remove some data.

Hot fix 1

You should unselect all the schemas and tables then manually select the ones you want. It seems there is a bug in the DBeaver app that unselects in the background all the tables in a schema even if you only unchecked one.

Hot fix 2 (safer)

Create a project and save it. Go to the configuration file mentioned above and manually add your schemas and tables to the structure and back it up. This way the export should work as expected.

1.1     Exporting the database schema

We need to be sure the source database is at its latest state. Run the update-database command in the Package Manager Console for all your plug-ins.

We will need to use pgAdmin to export the database schema. We can’t directly use the EF migration tool because the foreign keys will be added and we won’t be able to import the data.

Open pgAdmin, select the source database then right-click and select Backup…. You can also go up in the menu to Tools then Backup. In the form select the Filename used to export the data to, select the Format – Custom.

• Select the file to save to

In the Data/Objects tab select in the Type of objects section Only schema and in the Do not save select Owner and Privilege.

• Select schema only export

In the Options tab, leave as is or match the flowing configuration:

• The options foe schema export

Click Backup and wait for the schema export. Close pgAdmin.

1.2     Export the data

In order to only export the required data, we use DBeaver. Open DBeaver and select the source database. Right click and then go to Tools then Backup. You can also go in the main menu to Database – Tools – Backup. A form will be displayed that allows you to export only specific schema and tables.

In the next tab we need to select the Format as Plain and use INSERT instead of COPY. We also need to discard the objects owner. Set the output folder and the file name pattern then click Proceed.

1.2.1     Saving the export configuration

Configuring the data to be exporting takes a lot of time. You don’t want to do it every time. When you are done configuring (and during) click the Save Task button in the configuration form. DBeaver saves the configuration in a Database Task (located at the bottom of the screen). You can then edit this task later if needed. To export the task, you can find the configuration in the file located at:

C:\Users\<user_name>\AppData\Roaming\DBeaverData\workspace6\General\.dbeaver\tasks.json

Back up this file if you want to keep your configuration safe!

You can also save the Project, and that will also include the file that stores the task. To do that, go to the main menu to File – Export, then select in the tree DBeaver then Project.

• Select exported schemas and tables from Database – Tools – Backup
• Data export options: plain SQL, no compression, no owners or privileges

1.3     Editing the exported files

The data export will generate a file that contains SQL statements to create schemas and tables, insert the data then set the foreign keys and other table dependencies. We will need to edit this file because the exported statements to create the schemas and tables only include the selected ones and not all. If we were to import this file, the schemas and tables that were not selected will not be created. We also can’t use the update-database command because the dependency restrictions would be applied and we couldn’t import the data.

The file used to import the data will have this structure:

• The first part of the pgAdmin file, containing mostly the CREATE SCHEMA and CREATE TABLE statements;
• A BEGIN TRANSACTION; statement;
• The middle part of the DBeaver file, containing the INSERT INTO statements;
• A COMMIT; statement;
• The last part of the pgAdmin file, containing mostly the ALTER TABLE and CREATE INDEX

• The replacement of the statements in the data file with the contents of the schema file

1.3.1     Create schemas and tables

Open the file containing the schema of the database. Cut the first part of the file containing the schema, until the CREATE TABLE public.”__EFMigrationsHistory” statement, including it, and paste it into another file. Save the remaining of the file as is. The remaining part of the file creates the object dependencies after the data import.

1.3.2     Edit the data file

Open the data file and go to the first INSERT INTO statement. Remove all content from the begging of the file up to the first INSERT INTO statement. The inserts must be processed into a transaction. Add just before the first statement INSERT INTO the flowing:

BEGIN TRANSACTION;

Paste before the BEGIN TRANSACTION; statement the contents of the schema file created before. This will add all the schemas and tables CREATE statements before the data insertion.

In the data file, go to the last INSERT INTO statement (usually it is the last INSERT INTO public.”__EFMigrationsHistory”… statement). Remove all contents after the last complete INSERT INTO statement (the part that would create the foreign keys). Paste the remaining of the original schema file at the end of the data file, adding the statements that will create the object dependencies.

1.4     Importing the data in a new database

1.4.1     Create a new database

You first need to create a new empty database. You can use either of the tools used here or the command line.

1.4.2     Run the SQL script

In order to import the schema and data, take the edited file that now contains all the database information and data and using the DBeaver import it. With the new database selected in DBeaver, go to Database – Tools – Execute Script, select the edited SQL file and click Start.

If all goes to plan, you should have your cloned database ready after the script finishes.

You can also use pgAdmin or other tools to execute the SQL script.