"After all, the engineers only needed to refuse to fix anything, and modern industry would grind to a halt." -Michael Lewis

Enable Massive Growth

How to Migrate a Real PostgreSQL Database Using Flyway with Spring Boot

Apr 2019

You can see the source code for this post on GitHub.

We spent the last post figuring out how to migrate an embedded PostgreSQL database using Spring, while trying to side-step the extra magic that comes along with the framework. Here, we are going to build on that work to migrate a real PostgreSQL instance, which we will build in a local Vagrant Virtual Machine.

To do this in a maintainable way, we will want to leverage Spring Profiles to allow us to retain local development as an option and switch to another setup relatively quickly. We will change our application.yml file to look like:

spring.profiles.active: dev

spring.profiles: dev
spring.flyway.enabled: false

spring.profiles: stage
spring.flyway.enabled: false
spring.datasource.password: postgres
spring.datasource.username: postgres
spring.datasource.url: jdbc:postgresql://
spring.datasource.driver-class-name: org.postgresql.Driver

Here, we are telling Spring Boot to use the "dev" profile by default, and if we decide to use the "stage" profile, then the application will attempt to connect to a PostgreSQL database at the IP address, at port 5432. It will connect to the testdb database and use postgres/postgres as the username/password combo.

Note: Never use postgres/postgres as the username/password combo for a database that you actually want to protect. This will last about five seconds on the internet.

With the work we have already done, this will ensure that our application runs the database migration scripts on application start time in an idempotent way. We just need a real database to validate this against, and I will use Vagrant. I've created a postgres-vm/Vagrantfile like:

Vagrant.configure("2") do |config|
  config.vm.box = "ubuntu/bionic64"

  config.vm.provider "virtualbox" do |v|
    v.memory = 2048
    v.cpus = 1

  config.vm.provision "file", source: "~/.ssh/id_rsa.pub", destination: "~/.ssh/me.pub"
  config.vm.provision "shell", inline: "cat /home/vagrant/.ssh/me.pub >> /home/vagrant/.ssh/authorized_keys"
  config.vm.provision "shell", inline: "mkdir -p /root && mkdir -p /root/.ssh/ && cat /home/vagrant/.ssh/me.pub >> /root/.ssh/authorized_keys"
  config.vm.provision :shell, path: "postgres-provision.sh"
  config.vm.network "private_network", ip: ""

This Vagrantfile needs a postgres-provision.sh bash script in the same directory, which looks like this:


sudo apt-get update && sudo apt-get -y install postgresql

# set the default to listen to all addresses
sudo sed -i "/port*/a listen_addresses = '*'" /etc/postgresql/10/main/postgresql.conf

# allow any authentication mechanism from any client
sudo sed -i "$ a host all all all trust" /etc/postgresql/10/main/pg_hba.conf

# create db named testdb
sudo su postgres -c "createdb testdb"

# restart the service to allow changes to take effect
sudo service postgresql restart

This creates a PostgreSQL database and exposes it (with no security) to the outside world, which in this case is just our local development environment.


$ cd postgres-vm
$ vagrant up

And, once the VM is up and running, you can:

$ cd ..
$ mvn clean install
$ SPRING_PROFILES_ACTIVE=stage java -jar target/flywaystuff-1.0.jar

The application will come up and connect to our local database at this point. You can verify that the migration ran properly with:

$ cd ../postgres-vm
$ vagrant ssh
$ sudo -i -u postgres
$ psql -d testdb
testdb=# \dt

You should get an output like this:

                 List of relations
 Schema |         Name          | Type  |  Owner   
 public | employee              | table | postgres
 public | flyway_schema_history | table | postgres
(2 rows)

And we have done it successfully.

Nick Fisher is a software engineer in the Pacific Northwest. He focuses on building highly scalable and maintainable backend systems.