Setup pgAdmin

[00:00:00]
>> I'm gonna show you how to use a GUI for this. So far, I've been doing everything on the command line. Honestly, this is how I work, but I really like command lines. That's why I teach a whole class on the command line because I like it so much.

[00:00:14]
I know not everyone's in the same boat as me. There's lots of tools, I've seen people using various tools throughout the day, and which are all great. I'm gonna show you how to use a web-based one. So having learned MySQL first, that's the first relational database that I used extensively at work, it comes with PHP Admin.

[00:00:34]
Basically, everyone that uses MySQL has at least used PHP Admin to some degree. There is a very similar project for Postgres called pgAdmin. There's another one that's also called PHP PostgresAdmin or something like that, that's actually almost a clone. So if you wanna use that, you can. Anyway, we're not, but I'm gonna show you how to setup pgAdmin.

[00:01:01]
So I'm gonna kill my SQL database right here. And then this is this directory that I'm in right now, It's the same one that we've been working in with all of our projects. You'll notice that there is a docker-compose file that I checked in there, that looks just like this.

[00:01:27]
You don't have to know a damn thing about docker-compose. I do talk about it in my containers course if you wanna learn more about docker-compose, but all this is gonna do is it's gonna start two containers. It's gonna start the container that you and I have been working with, this one, the btholt/complete-intro-to-sql.

[00:01:46]
And it's also gonna start another one called pgadmin, which you can see I'm pulling directly off the docker hub. It's just a web server that connects to your Postgres database that can run queries for you, okay? So keep in mind that I gave it a default email of admin@example.com.

[00:02:07]
You will need that here in a second, and the password is to the various safe and secure password. Yeah, I highly suggest you use this for all of your passwords, all the time. Okay, and then it'll be exposed on port 1234. So you should be able to come in here.

[00:02:24]
Notice, I have nothing running in, cuz I killed my container. If you don't kill your container, it's going to conflict on the port, just so you know. I'm gonna say docker compose up. Now, I have all this stuff already downloaded and ready to go. There's a good chance that you are gonna have to download the pgadmin container, which will take a little bit longer than mine.

[00:02:50]
But once you see something like this that says postgres-apps-pgadmin-1, this part is probably different. But this part, the pgadmin-1 is probably the correct one. Once you see that that's working, you should be able to go to your browser and go to localhost:1234. You'll see something that looks a lot like this.

[00:03:23]
Okay, put in your super secure, very top secret admin@example.com and password. Cuz we are people of class, we use secure things. I literally updated this yesterday, and they've released a new version, typical. Anyway, I pinned it to 6.12, you can probably update this to 6.13. And I doubt much changed.

[00:03:54]
Okay, by default, it will not connect to your database, you actually have to go and set that up. So I had that step by step with pictures here. I'm gonna click Add Server, you have to add a couple of things. And then once you've done that, it will connect to your database, so let's go ahead and do that.

[00:04:17]
Now, we're gonna add new server, give it some sort of name, does not matter. I called mine db. Click on Connection. The host name is going to be db because this is on a docker network, so it actually names the name of the host whatever you call it, we called ours db.

[00:04:35]
If you look in your docker-compose file, port 532, that's exactly what we want. It means this database is Postgres. That's correct. The name of our user is Postgres, and the password, if you remember, again, very secure passwords here, lol. And you can save the password there, and you can click Save.

[00:04:59]
Lo and behold, we now have all of our stuff in a nice little GUI for us to interact with. So I am by no means a pgAdmin expert. I don't really use it very much, but it is undeniably a very useful tool. So if you prefer a GUI, here you go, this is what you get.

[00:05:26]
This is what I'm giving you, you get no more from me. The thing that I did find useful here is we can just click Query Tool, which is right here, or you can also use Tools > Query Tool. You can come in here and say, looks like it's connected to recipeguru.

[00:05:47]
Let's connect this instead to server db database. You can switch it to omdb right here, and click Save. That's fine, I don't care. Postgres, so you can see here now I'm connected to the omdb/postgres@db. We are going to SELECT * FROM movies. So you can see, you get a nice, little autocomplete here.

[00:06:16]
LIMIT 5, and press Play. You can see here, I've got a nice table output of all the stuff that I got back. Again, I don't really know very much about pgAdmin. But I think if you go back here to Dashboard, you'll actually get a bunch of information about it.

[00:06:37]
You can see the sessions that I've had, the transactions per second, how much it's blocking it out. Tuples, tuples, whatever you wanna call that in and out, pretty cool stuff. I showed you how to set up with docker_compose, cuz that was easy for us to fit in. But you don't have to use this with docker_compose, you can just set up pgAdmin locally in your computer.

[00:07:05]
It's just a web server. I think it's all written in Python and Flask, if I'm not mistaken. All right, so you have the instructions there, you can see how to do it yourself. You can leave this up, if you want to, you can write the rest of the queries for the rest of the class in this, if you want to.

[00:07:23]
I'm gonna go back to the command line cuz that's my safe place. [LAUGH]