How to Use VirtualBox As a Remote PostgreSQL Server
I think this would be an interesting post about creating a “remote” PostgreSQL server by VirtualBox. Both guest and host machines are Debian.
VB Guest Debian Installation (netinst)
The installation is pretty straightforward. There is only one personal preference I would like to make - Deselect all extra packages. After all, this is just a remote server, we don’t need GUI or anything extra fancy.
Network
One important step in VB setup is to switch the default network from NAT to Bridged Adapter. If you would like to know the difference, here is the official doc on this topic.
sudo Group
Log into VB guest Debian with your credentials, add yourself to sudo group and reboot.
su
apt install sudo
/usr/sbin/adduser bo sudo
/usr/sbin/reboot
PostgreSQL Installation in VB Guest Debian
When you have logged back in, run the command below to install PostgreSQL
sudo apt update
sudo apt install -y postgresql postgresql-contrib
Check if everything works ok.
sudo service postgresql status
By default, the installation process will create a superuser named postgres. This particular account is recommended to be used locally without password.
To access this account locally, you have two options:
- Switch to postgres account and type psql
sudo su - postgres
psql
- Or you can run this command instead
sudo -u postgres psql
To exit out of PostgreSQL shell, type \q or Ctrl + D.
Create Roles and Database
Within the VB guest Debian, type
sudo su - postgres -c "createuser bo"
sudo su - postgres -c "createdb bodb"
Connect PostgreSQL shell and grant privileges
sudo -u postgres psql
In PostgreSQL shell, type
grant all privileges on database bodb to bo;
Enable Remote Access
In the terminal, run the following command
sudo nano /etc/postgresql/11/main/postgresql.conf
Find the line below by Ctrl + w, and change it accordingly.
listen_addresses = ‘*’ # what IP address(es) to listen on;
Restart PostgreSQL
sudo service postgresql restart
Accept remote connections
sudo nano /etc/postgresql/11/main/pg_hba.conf
Add this line to pg_hba.conf
host bodb bo 0.0.0.0/0 trust
One more thing before we move on to the host machine, we need to find out the IP address of the guest machine on our local network.
ip address
Mine is 192.168.1.24
Host Machine Debian
We need to install the client package from the repo.
sudo apt install postgresql-client
Now we can connect to our “remote” postsql server by running this command
psql -U bo -d bodb -h 192.168.1.24
Voilà!
I hope you liked this short tutorial. Stay tuned by signing up for my newsletter. If you have any questions/comments/proposals, feel free to shoot me a message on Twitter/Discord/Patreon. Happy coding!