Development and Testing

Every instance of a running PostgreSQL server manages one or more databases. Databases are therefore the topmost hierarchical level for organizing SQL objects (“database objects”). In an instance of Redrock Postgres, roles and tablespaces are database-level objects, each database has its own independent data architecture (including role, user, schema, and object permission information), and the data of each database is completely isolated.

We often use a version management system when developing application software to manage the branches of code used to develop various features. Similarly, in Redrock Postgres, we can also use its multi-database service capabilities to manage databases for developing and testing various features.

The application codebase should have a database with only one main branch that contains the basic data required by the application. All official versions of application software products available to users are released based on this master branch database. Here we call the master branch database master.

We can dump the master branch database into an SQL script file and release the basic data required by the application:

$ pg_dump master > base.sql

If we need to reset the basic data needed for application development and testing, we can drop and recreate a new master branch database:

DROP DATABASE master;
CREATE DATABASE master;

Import the SQL script file with the base data into the main branch database and reset the base data required for application development and testing:

$ psql -d master -f base.sql

The master branch database is only used to release major releases, and day-to-day development should be done on another branch. We can call the branch used for development develop.

This branch can be used to generate the latest overnight version of the application. If you want to officially release it to the public, merge the development branch data on the main branch database to build a new main branch basic data.

SQL command to create a development branch database:

CREATE DATABASE develop TEMPLATE master;

The feature branch database is cloned from the development branch in order to develop a specific function. After development is complete, the difference data is merged into the development branch.

The name of the feature branch database can be named with the prefix feature_ followed by the feature name.

SQL command to create a feature branch database:

CREATE DATABASE feature_x TEMPLATE develop;

There is also a bug-fix branch. After the official release of the software, bugs will inevitably occur. At this point, you need to create a branch database for bug fix.

The bug-fix branch is cloned from the master branch database. After bugs are fixed, merge into the main branch and development branch. The name of the bug-fix branch database can be prefixed with bugfix_ followed by the bug name.

SQL command to create a bug-fix branch database:

CREATE DATABASE bugfix_x TEMPLATE master;

The test database is the database used by testers to test the released application software, and the data environment needs to be as consistent as possible with the production database.

The name of the test database can be prefixed with test_ plus the name of the test purpose.

SQL command to create a test database:

CREATE DATABASE test_x TEMPLATE master;