I'm learning to build data apps with Budibase, and I have decided to use PostgreSQL as my database. It should let me do things I can't do with the default database built-in to Budibase. I am not a complete PostgreSQL beginner thanks to a Codecademy course some time ago, but I found that course a bit too beginner-friendly as it didn't go into as much depth as I liked. This time as a refresher I tried the W3Schools course and didn't finish it, finding it to be too shallow as well. In the quest for something with more meat, I went to the source: PostgreSQL official documentation. The tutorial section itself is just about perfect for what I want today, and the remainder of the reference is there when I'm ready for more detail.

I'm running Ubuntu Server in my Budibase VM, and PostgreSQL is available as a part of Ubuntu's application distribution package. For security partitioning purposes, PostgreSQL uses the "postgres" user account instead of the logged-in user and I had trouble logging in with the correct credentials. After a bit of unsuccessful fiddling, I tried an alternative approach of running PostgreSQL as a Docker container. Now I can log in by specifying -u postgres at the docker exec command line.

Once logged in, I verified the Docker image included two tools that would greatly help with my experiments. I had thought I would need to write similar tools myself via PostgreSQL API but reading PostgreSQL tutorial I learned they already existed.

The first is psql, a text command-line interface to PostgreSQL. It allows me to experiment and iterate through my queries at a low level before I try to make them fancy and easy to use with Budibase. The second tool is pg_dump, which backs up my database data in plain text format. The text file is a list of psql commands to recreate my database. I always have the fear that I would accidentally make a hugely destructive change to my database and not even realize it until much later. Now I can periodically take snapshot backups with pg_dump so I can recover from such mistakes. And more than that, I can check the text file diff between these snapshots. I can't always mentally parse every line of difference but I can rest assured scope of my changes were not more drastic than I had intended as I work with my data.