Getting started with postgres

Summary

In this video, I provide an introduction to Postgres basics. I explain how Postgres allows multiple databases and typical environments (development, staging, production). I show how Postgres uses peer authentication and demonstrate creating a superuser and database. I cover the importance of careful superuser management, especially in production environments. I explain schemas, particularly the default 'public' schema, and show hands-on examples using psql to create tables and manage data. Finally, I touch on migrations for database changes and mention GUI tools like dbBeaver and TablePlus as alternatives to terminal commands.

Transcript

This video will give you an introduction to the basics of Postgres. I'm not going to dive too deep into the inner workings, but I want to provide you with the tools so you can run and manage your own database. When you install Postgres, you didn't only install a single database, you installed a system that can have multiple databases running at the same time. So you can create as many databases as you want, but you typically only have one database per project. Now in real world applications, we might split databases out into different environments based on where we are in the stages of development. So these are typically named development or local staging production, so it's clear where we are. So let me try and explain here. Development databases, which you can call short for dev or local, this is where you work locally on your own machine, where you develop the application on your own computer. You can make as many mistakes here as you want, you can delete the data, anything goes here. This is a sandbox environment that is yours and nobody else can touch. So this is typically what we have here. And now if we go to staging, this is more like a rehearsal, a final test before we go live. You will see later how we work with data and how we evolve our database schema over time. So we can have a staging environment database where if we break stuff, it's probably going to be fine. I mean, we might have some internal uses of the staging system, but if staging breaks, it's okay in a sense so that we know that, okay, we made some mistakes somewhere that we caught before we go to production. And this is the last step. So we work locally on our machine, everything looks good, right? Then we go to staging. We didn't break staging, we can still access the data and everything is good. And then the final step here is a database or what I'm going to be calling prod. This is where our actual data lives, like where all our user data are. And here we want to be extra, extra careful, right? So we have layers so we can play around in a sandbox and then we can say like our sandbox looks good. Let's go to the next stage. This is live on a server somewhere. So it mimics what we have on production. And then if it looks good over here, okay, fine. Then we can go to the next step. So for example, with our block here, I mean, it's a singular developer, right? You're probably not going to need this setup, but it's good to know about it so you can also play around if you want. So if you're building our block, we will build locally or in our development environment to test new features, push it live to our staging server, test out these new database changes on the staging server. And then finally, if everything looks good, we push it to production. So this separation makes it so that we don't accidentally break things because the database is where we are the most careful. If you introduce a bug in our code, it's fine. But if you take down production in terms of database, that's going to be where the real big hurdles are, like recreating data and all of these kind of things. So this is typically how you think of a database environment in real world applications. When you install Postgres, it also came with two tools, one called pgAdmin and one called pgAdmin. And these two tools can be used to manage and maintain our Postgres databases and Postgres system. Now, pgAdmin is a web-based tool that you can access for your browser and create users, databases, whatever you need, where pgAdmin is a CLI tool. So you can access and manage databases through your terminal. We're not going to be touching pgAdmin right now. We're going to be using psql. And for that, we need a user with some credentials, right? And during installation, a system user was created for you that's called Postgres, as well as a database called Postgres. But if you were to right now try and log in through psql with that user, it wouldn't allow you. And this is because Postgres, at least on Mac and Linux, uses what is known as peer authentication. And to illustrate this, I created this little diagram right here, where the white box is simply just your computer, your laptop, whatever, whatever you're watching this on, right? That's the white box. And then the red box is the system user, the one you're logged in with. For me, that's, I think it's MBV or Morton, something like that. But whatever username you have for your computer, right? And then the green box is Postgres. Now, Postgres doesn't have users, they have roles. And a role can be thought of as a database user or a group of database users. And when Postgres was installed, it ultimately created a role that has full admin rights. Now, since Postgres uses what I mentioned earlier as peer authentication, it expects that the system user matches the Postgres role that you're trying to log into. So what this means is that we need to either log into the Postgres user on the system level that was created for us, and then log into the database. Or we can create a new user with admin rights that matches our system username. And then after we've done that, we can create a database with the same name as our system level user. And then we don't have to switch to Postgres to start interacting with our database through PSQL. We can simply just type PSQL into our terminal and might also be prompted for a password, but then we can access PSQL through our normal, let's say, normal user. All right, so before we can create our own user and our own database, we need to go through the Postgres system level user. So to do that, simply say sudo i.u and Postgres. Now, you might get prompted for a password here. It's simply the system level user that you always log into the system with. So just type that in and you're going to get switched to the Postgres user. Now, we can make changes as a super user, which means we can create other super users. So type in create user dash dash pb prompt. So we also get a password and then dash dash super user. And here you put your username. I already have an account in my name, but just to illustrate, I'm going to do mbv2 and then a password. There we go. Now we have our super user, but we also need the database. So let's jump into PSQL with the Postgres user and then say create database. And it's going to be the same name as before, the same name as your user, right? And then I'm going to say owner mbv2. And there we go. Now we have a user and the database. So I'm going to jump out of PSQL and out of the Postgres user and into my regular user. Now, I'm going to type PSQL now and I'm going to get logged into my user with the mbv2 because I don't have a system user, right? But now you're able to log in with your system level user to PSQL and you can start to do all kinds of commands, right? You can create databases, you can query databases, you can do whatever you want. So let's get a little bit ahead with our project here and create a new database called blockdeo. And there we go. And to connect it, you do, I'll call a backslash like this and the C for connect and blockdeo. Right. And you can see it went from mbv2 to blockdeo. So this is where we have the, again, the Postgres role called mbv, the database called mbv, which is what you get logged into as a default. And now we also have blockdeo, which the mbv role is the owner of. We just created a super user and associated database, but I didn't really touch upon what a super user is. And as the name implies, it's an admin counter. Here, everything goes right. Like you do you. You can create data, delete data, update data. You can create users, delete users, you know, there's no, there's nothing stopping you. Now that, of course, carries some security implications. And there are some clear benefits that if we are local, you know, you can do what you need to do without being prompted for permissions and you can fix the stuff you need to fix. However, if you go to back to our example of the different environments that you might have, like a staging environment or production environment, if someone were to get access to a super user, they could just delete all of your data, create users with access to data they shouldn't have access to, right? But probably very common is that you might, you yourself might actually accidentally delete data because there's nothing stopping you. There's no one saying like, hey, you're not allowed. So when working with super users and Postgres, the best practices is that you have one super user for initial setup and maintenance. And then when you create your database, you create it with a specific user in mind. So that would mean that we have our staging user and our production user that have different passwords. And the staging user cannot access production and production cannot access staging. So doing this splits the environments and makes things more secure. It's harder to make mistakes and it's harder to breach the system. And we want to make sure that we keep our super user credentials extra secure and extra under lock and key, you know. So for development on your personal machine, the super user is fine, but be aware that when you move to the more, let's say, serious environments like production, that you need to be extra aware of this super user. I want to touch on one more important concept, which is that of schemas. Now, when you create a new database, Postgres will automatically create a schema for you called public. The reason it's called public is for historical reasons. It's backward compatibility. Some applications and tools expect it to be there. So that's just the name of it. But what it does is it's like a folder that holds all of your tables, all of your views. It is also where all the data lives in the database. Now, we can have multiple schemas, and if you look at my illustrations here, you can see we have our database we just created, and then some schemas. We have the public, which is the default one. Background jobs is an example, or sensitive data is also an example. We talked about this earlier, where we talked about maybe isolating data. So we could have some service-sensitive customer data that we want to be very certain we don't curate or access it by accident. So we can have multiple schemas. You would probably only have one in most applications. It has the advantages that we can organize large applications into specific schemas. We can have an authentication schema, for example, or in the example I just gave with multiple users. So you can have a multi-tenant system that has very isolated data accesses. We can manage permissions at a broader level. So there are a lot of options here with schemas, but we are just going to be using public, and it's probably also what you will see in most applications that you're going to be working on. All right, we caught quite a few topics now, but I want to show you how you can work with data using psql. So if you jump back into the tool, log in, and then connect to our database, which is called blog-dev. Now we can start to add structure, and this is not how we're going to be adding structure in the application, but just to showcase that you can create tables and do whatever you want now. So create a users table. It's going to have a column called id of type integer as a primary key. That is not null. There we go. Now we have a table, and we can see here that we have a table on schema public named users. It's a type of table, and the owner is mbb. Great. Now we can also insert data if we want to. So insert into users, insert an id with the value of one. Now let's query on the users table, and we should have one record, and we see we have one row, right? You can add more if you want to. We have two rows now. So you can also delete and update if you want to, but this is how you can quickly get up and running with a database, add some data if you want to. Normally, we are not going to be adding tables like this. We're going to be using what is called migration, and migration you will see later on the course, but the gist of it is that you have an up version and a down version, and the up version is whatever change you want to have to the database that's going to be persistent. So let's say we just had a migration that created a users table, and then we have another migration that adds a, I don't know, post table, right? The action that creates those two tables is going to be the up version. Each up version will have a corresponding down version so that if we make a mistake, if we need to go back in time, we can then have, we can run down with undo the changes that we just did. Now we are not limited to doing, to creating tables. We can also change tables. We can also drop tables, drop columns, add columns, do whatever we want in the up version. It's just to think that the up one is going forward and the down one is going backwards. So you might also not want to rely on a terminal. You can use, there's a lot of great desktop apps or GUIs. I use something called dbBeaver, which you'll see later in the course. You can also use something called TablePlus if you're on macOS. It's also a nice candidate, but for now this is Postgres. This is pretty much all you need to know. There are some fine tuning that we can get into when we get to production stages, but now this is what you need to run and manage your database.