Database Lab is used to boost software development and testing processes via enabling ultra-fast provisioning of databases of any size.
In this tutorial, we are going to set up a Database Lab Engine for an existing PostgreSQL DB instance on Amazon RDS. If you don't have an RDS instance and want to have one to follow the steps in this tutorial, read the official RDS documentation. Database Lab Engine will be installed on an AWS EC2 instance with Ubuntu 18.04, and an additional EBS volume to store PostgreSQL data directory. The data will be automatically retrieved from the RDS database.
Compared to RDS clones, Database Lab clones are ultra-fast (RDS cloning is "thick": it takes many minutes, and, depending on the database size, additional dozens of minutes or even hours to warm up, see "Lazy load") and do not require additional storage and instance. A single Database Lan instance can be used by dozens of engineers simultaneously working with dozens of thin clones located on a single instance and single storage vole. RDS Aurora clones are "thin" by nature, which is great for development and testing. However, they also require additional instances, meaning significant extra costs. Database Lab clones are high-speed ("thin"), budget-saving ("local"), and can be used for a source database located anywhere.
Database Lab Engine is hosted and developed on GitLab.com. Why? GitLab Inc. is our (Postgres.ai) long-term client and early adopter (see GitLab Development Docs). GitLab has an open-source version. Last but not least: GitLab uses PostgreSQL.
However, nowadays, not many open-source projects are hosted at GitLab.com unfortunately.
⭐️ Please support the project giving a star on GitLab! It's on the main page of the Database Lab Engine repository, at the upper right corner:
- Prepare an EC2 instance with an additional EBS volume to store data, install Docker to run containers, and ZFS to enable copy-on-write for thin cloning;
- Configure and launch the Database Lab Engine;
- Start using Database Lab API and client CLI to clone Postgres database in seconds.
Step 1. Prepare an EC2 instance with additional volume, Docker and ZFS
Prepare an instance
Create an EC2 instance with Ubuntu 18.04 and an additional EBS volume to store data. You can find detailed instructions on how to create an AWS EC2 instance here.
(optional) Ports need to be open in the Security Group being used
You will need to allow working with the following ports (outbound rules in your Security Group):
22: to connect to the instance using SSH;
2345: to work with Database Lab Engine API (can be changed in the Database Lab Engine configuration file);
6000-6100: to connect to PostgreSQL clones (this is the default port range used in the Database Lab Engine configuration file, can be changed if needed).
For real-life use, it is not a good idea to open ports to the public. Instead, it is recommended to use VPN or SSH port forwarding to access both Database Lab API and PostgreSQL clones, or to enforce encryption for all connections using NGINX with SSL and configuring SSL in PostgreSQL configuration.
Additionally, to be able to install software, allow accessing external resources using HTTP/HTTPS (edit the inbound rule in your Security Group):
Here is how the inbound and outbound rules in your Security Group may look like:
If needed, you can find the detailed installation guides for Docker here.
Further, we will need
$DBLAB_DISK environment variable. It must contain the device name corresponding to the disk where all the Database Lab Engine data will be stored.
To understand what needs to be specified in
$DBLAB_DISK in your case, check the output of
AWS local ephemeral NVMe disks; EBS volumes for instances built on the Nitro system:$ sudo lsblkNAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT...xvda 202:0 0 8G 0 disk└─xvda1 202:1 0 8G 0 part /nvme0n1 259:0 0 777G 0 disk$ export DBLAB_DISK="/dev/nvme0n1"
AWS EBS volumes for older (pre-Nitro) EC2 instances:$ sudo lsblkNAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT...xvda 202:0 0 8G 0 disk└─xvda1 202:1 0 8G 0 part /xvdb 202:16 0 777G 0 disk$ export DBLAB_DISK="/dev/xvdb"
Install and prepare ZFS
Create a new ZFS storage pool (make sure
$DBLAB_DISK has the correct value, see the previous step!):
And check the result using
zfs list and
lsblk, it has to be like this:
Step 2. Configure and launch the Database Lab Engine
To make your work with Database Lab API secure, do not open Database Lab API and Postgres clones ports to the public and use VPN or SSH port forwarding. It is also a good idea to encrypt all the traffic: for Postgres clones, set up SSL in the configuration files; and for Database Lab API, install, and configure NGINX with a self-signed SSL certificate. See the How to Secure Database Lab Engine.
We have two options to connect to the RDS database: password-based, and IAM-based. The former is always available, while the latter is more secure and recommended, but it is available only if you specified it in Database Authentication Options when creating your RDS instance (it is not selected by default). To see if the IAM-based option is available for already created RDS instance, open the "Configuration" tab and check if "IAM db authentication" is
- IAM database authentication. This option can be used only if Password and IAM database authentication was specified during the creation of the RDS instance, it requires AWS user credentials and does not require the master password, use this option for granular control of the access to your database.
- Password authentication (master password). This option can always be used. It requires specifying of database master password in the Database Lab Engine configuration file or in
For the sake of simplicity, we will use the password-based authentication in this tutorial. If you want to use IAM database authentication, read how to enable it here.
Option 1: Password authentication
You need to know the master password. If you lost the password it can be reset.
Configure Database Lab Engine
Copy the contents of configuration example
config.example.logical_generic.yml from the Database Lab repository to
~/.dblab/server.yml and edit the following options:
- Set secure
server:verificationToken, it will be used to authorize API requests to the Database Lab Engine.
- Set connection options in
dbname: database name to connect to;
host: database server host;
port: database server port;
username: database user name;
password: database master password (can be also set as
PGPASSWORDenvironment variable and passed to the container using
- If your Postgres major version is not 12 (default), set the proper version in Postgres Docker images tags:
Run Database Lab Engine
Run Database Lab Engine:
How to check Database Lab Engine logs
Need to start over? Here is how to clean up
If something went south and you need to make another attempt to follow the steps in this tutorial, use the following steps to clean up:
Step 3. Start cloning!
Option 1: GUI (Database Lab Platform)
To use GUI, you need to sign up for Database Lab Platform.
Currently, Database Lab GUI is in "private beta" mode. The onboarding consists of two steps. Step 1: you sign up using either Google, or LinkedIn, or GitLab, or GitHub account. Step 2: the Postgres.ai team contacts you and schedules a demo, during which your account will be activated.
Add Database Lab Engine to the Platform
- On the Database Lab instances page of your organization click the Add instance button.
- One the Add instance page fill the following:- `Project`: choose any project name, it will be created automatically;- `Verification token`: specify the same verification token that you've used in the Database Lab Engine configuration file;- `URL`: Database Lab API server (EC2 instance public IP or hostname).
- Click the Verify URL button to check the availability of the Engine. Ignore the warning about insecure connection – in this Tutorial, we have skipped some security-related steps.
- Click the Add button to add the instance to the Platform.
Create a clone
- Go to the Database Lab instance page.
- Click the Create clone button.
- Fill the ID field with a meaningful name.
- (optional) By default, the latest data snapshot (closest to production state) will be used to provision a clone. You can choose another snapshot if any.
- Fill database credentials. Remember the password (it will not be available later, Database Lab Platform does not store it!) – you will need to use it to connect to the clone.
- Click the Create clone button and wait for a clone to be provisioned. The process should take only a few seconds.
- You will be redirected to the Database Lab clone page.
Connect to a clone
- From the Database Lab clone page under section Connection info copy psql connection string field contents by clicking the Copy button.
- Here we assume that you have
psqlinstalled on your working machine. In the terminal, type
psqland paste psql connection string field contents. Change the database name
DBNAMEparameter, you can always use
postgresfor the initial connection.
- Run the command and type the password you've set during the clone creation.
- Test established connection by listing tables in the database using
Option 2: CLI
Install Database Lab client CLI
CLI can be used on any machine, you just need to be able to reach Database Lab Engine API (port 2345 by default). In this tutorial, we will install and use CLI locally on the EC2 instance.
Initialize CLI configuration:
Check the configuration by fetching the status of the instance:
Create a clone
After a second or two, if everything is configured correctly, you will see that the clone is ready to be used. It should look like this:
Connect to a clone
Now you can work with this clone using any PostgreSQL client, for example,
psql. Use connection info (
db section of the response of the
dblab clone create command):
Check the available table:
Now let's see how quickly we can reset the state of the clone. Delete some data or drop some table.
To reset, use the
clone reset command (replace
my_first_clone with the ID of your clone if you changed it). You can do it not leaving psql -- for that, use the
Check the status of the clone:
Notice how fast the resetting was, just a few seconds! 💥
Reconnect to the clone:
Now check the database objects you've dropped or partially deleted – everything should be as when we started.
For more, see the full client CLI reference.
Have questions? Reach out to our team, we'll be happy to help! Use the Intercom widget located at the right bottom corner.
👋 Database Lab "Private Beta" program
Database Lab Platform (SaaS) is currently in a "private beta" mode, being tested by several hundred engineers. Want to become an early adopter? Join Database Lab by Postgres.ai "Private Beta" program today: https://postgres.ai/console/.