Choosing a Self-Hosted Database
The speaker opts for a self-hosted database on a VPS instead of a managed solution due to cost and learning benefits. Managed databases scale in price quickly, while a VPS setup is sufficient for a small personal project. Self-hosting also improves understanding of database internals and troubleshooting.
Installing PostgreSQL on the Server
PostgreSQL is installed via apt after updating the system. The service is verified to be running using system status commands. This establishes the database infrastructure required before deploying the application.
Understanding PostgreSQL Authentication
PostgreSQL uses roles rather than traditional users and relies on peer authentication by default on Debian/Ubuntu systems. The installation creates a postgres system user and role, which must be used to access the database initially. Access requires matching the Linux username to an existing PostgreSQL role.
Creating a Production Database and Role
A dedicated database and role are created for the application. The role is granted privileges only on the specific database and schema. This prevents the use of a superuser in production, reducing risk if credentials are compromised.
Security and Access Configuration
External database access is disabled by default and limited to localhost. While remote access can be configured, keeping it internal reduces security exposure. Only applications running on the server can access the database.
Handling Migrations in Production
Migrations can be executed at application startup for small projects. In larger setups, CI/CD pipelines (e.g., via GitHub Actions) can manage migrations before deployment. Production migrations must avoid breaking changes and be applied incrementally to prevent downtime.
Next Deployment Steps
The next phase involves deploying the application, running migrations, applying seed data, and configuring a systemd service. Once completed, the application will be ready to go live.
We need one more component before we can go live with our application, and that is a database. Now, you should probably go with a managed database at this point if you wanted to handle real scale, especially if you have never managed a database before yourself. But since this is our personal database, our personal project, I think this is a really good place to configure and set up our self-hosted version. It also gives you a more deeper understanding of how a database works.
And also when you have to fix something that's broken right, having set it up yourself from scratch and work with it gives you a much deeper understanding of what's going on. The other part is that a managed database gets expensive very, very fast. It starts around 30, 40 bucks, but that will only get you so far. You can't really handle any real traffic with that size and they easily go up to multiple thousands of dollars. So for this small type of project running it on the VPS,
It's going to be perfectly fine, and it's also going to result in a faster application. So we're going to take this approach for learning, but also from a cost perspective, because this is a personal project. It shouldn't cost you that much money to run it. So we're going to begin by SSH into our server. Then we're going to say sudo.
APT update. Provide the password. Then we can say sudo apt install, postgresql and postgresql contrip. Yes. Let that one finish. And then let's just verify that it's running.
So, sudo system-detail-status-post-ql. It is active. Great. Now, next thing we need to understand is authentication or how Postgres does authentication because it operates on roles instead of users. So, now that we just run this installation process, it will also have created a system user and a role called Postgres. It also comes with a CLI tool that's called pbsql.
And if I try to type in psql now, you can see role admin does not exist because on our Linux based, or at least Ubuntu or Debian based system, it uses peer authentication by default. So it checks the Linux username and to see if that matches a Postgres role. It does not right now because we don't have our admin role in our Postgres system. So what we can do is we can switch over to the newly created Postgres user.
or Postgres. What did I do wrong here? sudo-i-u. All right, so now we're in the Postgres user, and now we can run psql because we have a role called Postgres in the system. Then we can create our database. I'm going to create a database called bleeding edge prod.
And then I'm gonna create a user called, or create a role called a bleeding edge admin with encrypted password. Password, and of course you're gonna be using a real password for your system. The role is created. Then we're gonna grant all privileges on database bleeding edge prod to bleeding edge edge admin.
Min, there we go. Then we need to connect to the bleeding edge database and say grants all on schema public to bleeding edge admin. So what we did here, we created, of course, a database and then we created a specific user to interact that database. And this is really important because you don't want to have a super user interacting with all of your production databases. If your credential gets leaked,
they have access to all your data on all your applications, right? So it's really good practice to create specific users for each database or each application that does the only entry point into that database, right? So whenever we're in development, a super user is fine, right? We can just have all our databases live locally on our system. But once we get to production, a dedicated user with limited permissions is where we want to be at.
You cannot access this database externally because that is locked down by default. We could go into the files to change this. I'm going to link it in the show notes or episode notes for you to explore yourself if you want to. But right now, we can only access this from localhost. We can open it up, and then we can, through catty, create an URL or an endpoint that we can actually access the database from. But this also opens up a lot of security.
potential security issues right now. Only what we put onto the server can access the database. So that also gives us another security layer for our data. So we'll keep it like this. We have our user and our database. Now we just need to get our application onto the server. And then we also need to run our migration. And we're also going to be applying seed data. We could technically just create the user and then create our own articles. When it comes to migrations,
For the level that this project is at, it's probably fine to just have our application run all the migrations when we boot up. You could create a release pipeline using something like GitHub Actions that would log into the server, log into the database, apply all the pending migrations, and then maybe release a new version or just continue with what is currently running, right?
You have to think of migrations when you're in production or something that's running live as changing the wheel of a running car. So if you have to make changes, they cannot be breaking changes. And if you have to make a breaking change, you have to try and do your migrations in a way where it doesn't stop the car from running. So you have to do them in steps. And that is why doing these smaller releases with migrations
the right approach and having the logic encapsulated into the release flow of the startup process of the application is perfectly fine for our needs. So that's going to be the topic of the next video. We're also going to be setting up a system D service to manage our application and then we are ready to go live.
