Easily Manage PostgreSQL Database Migrations With DBeaver And Redgate Flyway

Introduction 

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. 

DBeaver is a free multi-platform database tool for developers, database administrators, analysts and all people who need to work with databases. It supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto, etc. 

Flyway is an open-source database migration tool. It strongly favors simplicity and convention over configuration. 

It is based around just 7 basic commands: Migrate, Clean, Info, Validate, Undo, Baseline and Repair. 

Migrations can be written in SQL (database-specific syntax (such as PL/SQL, T-SQL, ...) is supported) or Java (for advanced data transformations or dealing with LOBs). 

It has a Command-line client. If you are on the JVM, they recommend using the Java API (also works on Android) for migrating the database on application startup. Alternatively, you can also use the Maven plugin or Gradle plugin. 

In this post, we will see how to administer PostgreSQL with DBeaver and migrate database using flyway commands.  

Download and install PostgreSQL latest version 

We can download and install the official certified PostgreSQL database from EnterpriseDB (EDB) site. 

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads;

As of today, 13 is the latest version of Postgres. We can download the setup for windows.  

We can install the Postgres now. 

Postgres setup will automatically install pgAdmin tool to administer Postgres. But we will use DBeaver to manage database. Hence, we can uncheck this default option. 

We must give a valid password to the database superuser. 

Download and install DBeaver free community version 

Download community version from below URL 

https://dbeaver.io/download/ 

We can select Postgres database from the listed options. 

We must download driver files for Postgres. 

We must give the same password for database superuser which has been given while installation time.  

We can test the connection using the Test Connection button and ensure that Postgres is connected properly.  

We can create a new database now. 

There will be a “public” schema inside every database. We can create all the database objects manually inside this schema.  

But if you are working on an enterprise project with various developers, it is difficult to manage the objects created by different people. In this situation, we can use flyway tool to publish database objects easily from scripts.  

Download Flyway  

Flyway is an awesome tool from Redgate. We can download the free community version from below URL. 

https://flywaydb.org/download 

Some features are not available in free community version. But for our testing purpose, we can use this version.  

Since, this is a command line version, no need to install it. We can extract the zip file and keep in any of your drive.  

We must add our database object scripts inside the “sql” folder.  

We must update the database configuration details in a “conf” file inside the “conf” folder. 

flyway.conf 

flyway.url=jdbc:postgresql://localhost:5432/Sarathlal
flyway.user=postgres
flyway.password=Sarath
flyway.locations=filesystem:D:\flyway-7.12.0\sql

We can create a simple database script inside the “sql” folder to create new customer table. 

V1.1__customer-table.sql 

CREATE TABLE public.Customer (
    id serial NOT NULL,
    name character varying(100) NOT NULL,
    address character varying(100) NULL,
    city character varying(100) NULL,
    country character varying(100) NULL,
    CONSTRAINT pk_customer_id PRIMARY KEY (id)
);

It is very important to keep the naming convention of the script. Name must start with letter “V” and followed by a major version and minor version and with double underscore. If you do not follow the correct name, Flyway will throw an error.  

We can execute the Flyway commands now. 

Please open Command Prompt and locate to the Flyway extracted folder. 

Execute below info command with configuration file, which was created earlier by us.  

flyway -configFiles=D:\flyway-7.12.0\conf\flyway.conf info

We must first execute with baseline command to ensure that database baseline is created properly.  

flyway -configFiles=D:\flyway-7.12.0\conf\flyway.conf baseline

If you check the database, you can see that a new table “flyway_schema_history” created now. 

This table is used to control the migration history.  

We can use migrate command to complete the migration. 

flyway -configFiles=D:\flyway-7.12.0\conf\flyway.conf migrate

We can see that one more record is inserted inside the migration history table. 

Also notice that, “customer” table is created now. 

We can add one more script to modify customer table with a new column. 

You must follow the version number in ascending order. Previous version was V1.1. So next version should be V1.2. You can give any other next number also. If you give next version as V1.3, then another version should be one greater than current version. You can’t give V1.2 after V1.3. Flyway will ignore it. I will show you that.  

I am creating new script as V1.3__customer-table-alter.sql 

V1.3__customer-table-alter.sql 

alter table customer add column if not exists isactive bool;

We can execute migrate command. 

flyway -configFiles=D:\flyway-7.12.0\conf\flyway.conf migrate

We can see the current database status using info command. 

flyway -configFiles=D:\flyway-7.12.0\conf\flyway.conf info

As we discussed, I am giving next script version as V1.2. We can see that what happens with Flyway command. 

V1.2__customer-data-insert.sql 

insert into public.Customer (name,address,city,country,isactive)
values ('Sarath','Kakkanad','Kochi','India',true);

We can see the current database status using info command. 

You may notice that current version 1.2 is ignored by Flyway.  

Hence, we can change the version to 1.4 for same file (V1.4__customer-data-insert.sql).  

We can again execute migrate command and see that new script is executed successfully. 

Please note that, you can’t edit the previous script after executed migrate command. Flyway is keeping checksum for each script statement. If you try to modify existing script, it will show error. We can try to modify last script.  

V1.4__customer-data-insert.sql 

insert into public.Customer (name,address,city,country,isactive)
values ('Sarath','Kakkanad','Kochi','India',true);
insert into public.Customer (name,address,city,country,isactive)
values ('Aradhya','Kakkanad','Kochi','India',true);

You can’t run any further migration with this error. To overcome this situation, you can revert the code to original or use repair command. 

flyway -configFiles=D:\flyway-7.12.0\conf\flyway.conf repair

Though, the repair command will resolve the migration issue, it will not execute the new modified statement. It retains the same old SQL statement and database changes. Flyway will also support CI/CD pipeline to automate the deployments.  

Conclusion 

In this post, we have seen the details of installing PostgreSQL latest version along with DBeaver community version. After that we have downloaded the Flyway command line tool. Using Flyway tool, we have published database objects from database scripts. We have seen the usage of various Flyway command like “baseline”, “info”, “migrate” and “repair”. I have covered very basic things but hopefully it will be useful to administer PostgreSQL.