How to Migrate a Real PostgreSQL Database Using Flyway with Spring Boot
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://192.168.56.111:5432/testdb 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 192.168.56.111 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 end 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: "192.168.56.111" end
This Vagrantfile needs a postgres-provision.sh bash script in the same directory, which looks like this:
#!/bin/bash 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.