I’ve been using PostgreSQL for about 8 years, and I like the way the project itself organized binaries and configuration files. During my development and system administration tasks, I sometimes find myself creating multiple databases in guest machines, and accidentally drop the guest machine which then makes me create those databases again…

In this guide, I will be using vagrant-libvirt provider which some configuration will differ from VirtualBox or Hyper-V provider. Therefore, please look configuration for the provider you are using.

Sample Vagrantfile could be like this:

Vagrant.configure('2') do |config|
  config.vm.box = 'debian/bullseye64'

  config.vm.provider :libvirt do |domain|
    domain.cpus = 1
    domain.memory = 1024
  end

  config.vm.provision :shell, inline: <<-SHELL
    apt-get update
    apt-get install --yes --no-install-recommends postgresql-client
  SHELL
end

Changes in HBA

All trick is happening in here. If you are using some other providers, then you will likely change IP address in here. For instance, VirtualBox default network adapter is NAT, so yo will change it in here. In my case, libvirt provider for Vagrant will create a host-only network adapter.

# pg_hba.conf
host    potato_development        potato        192.168.121.1/24    md5

This change requires PostgreSQL to be restarted (not sure about reloading). From 192.168.121.1 to 192.168.121.254 will be allowed to login potato_development database with potato user. Of course, you can make it all and all, so you don’t think about configuring pg_hba.conf again and again. You might find pg_hba.conf documentation helpful while making changes in the file.

Client configuration and testing

You have two options to test the changes you made successful:

1. Directly use the software you were connecting to PostgreSQL
2. Use psql utility

I used psql because I had tarballs to restore from backup, and I guess having official client in the hand will make troubleshooting easier.

psql -h 192.168.121.1 -U potato -W potato_development

Following command will connect to a PostgreSQL instance that is on 192.168.121.1 with potato username, and psql will ask us the password. After all these parameters, we will be in potato_development database.