How to set up a Postgres database on your Windows 10 PC

0:00Hey, it's Tim here. And in today's video, I

0:01'm going to be doing something slightly

0:03different.

0:03One of the most common bits of feedback I

0:05've been receiving in my comments is that

0:07most of my demos

0:08are done using Excel. And essentially, I'm

0:11connecting Tableau or Tableau prep directly

0:14to

0:14Excel files. And that doesn't simulate the

0:16experiences that most people have in their

0:18businesses. So what I'm doing in this video

0:20is I'm actually going to show you the setup

0:23for a

0:23Postgres database that I'm going to be

0:25using in the videos in the future. Instead

0:28of just setting

0:28it up, I thought I'd make a video out of it

0:30and show you how you can do the same thing.

0:32I'm also

0:32going to be referencing this video in

0:34future videos if you want to set up a

0:36database for

0:36yourself so you can follow along. Okay, let

0:39's get stuck in. So the first question we

0:41need to answer

0:42is why Postgres? Well, there's not really

0:44that much logic to it. It's just something

0:47that I've,

0:47I guess I'm more common with and I see more

0:49often in terms of day-to-day usage. If I

0:52head over to

0:53the Amazon website here, they have a page

0:55on the relational databases that they have

0:58available.

0:59And they have a list here. This is pretty

1:01much a universal list of the key databases

1:03that are used

1:04in enterprise set-ups. Obviously, they have

1:06their own Amazon Aurora, which is kind of

1:08like a hybrid

1:09relational database in some senses. But

1:12there's obviously Postgres, MySQL, MariaDB,

1:15MariaDB, Oracle and SQL Server. Now, one of

1:19the reasons I'm gravitating toward Postgres

1:22is simply

1:23because it's open source. MySQL is also

1:25open source. And I'm sure the others have

1:27developer

1:27versions so you don't actually need to pay

1:29to have them running on your desktop. But

1:32the key thing is

1:33Postgres just comes up again and again in

1:35lots of enterprise instances that I come

1:38across. So

1:39I just thought let's set something up that

1:40's as close to something that's actually

1:42going to be

1:42used in real life. And I also know in terms

1:44of Tableau, it's got really good spatial

1:46support.

1:46So you can install a spatial plugin so you

1:49can have a database with spatial objects in

1:52there

1:52as well. So we're going to go through the

1:54set-up of installing all of this. Slightly

1:56longer video

1:57than normal, but let's get stuck in. Okay,

1:59so the first thing we need to do is to go

2:01to the

2:01Postgres website, postgressql.org. And

2:04essentially, I don't normally click on the

2:07download button on

2:08the homepage because this typically sends

2:10you to the enterprise solution, which is

2:11not what you want

2:12to do. You want to get the free version. So

2:15let's head on to the download page here at

2:17the top.

2:18And you obviously want to pick the right

2:19thing for your platform. Now, I'm doing

2:20this video for

2:21Windows as per the title of the video. I

2:23will maybe do a video for the Mac as well.

2:25So if you

2:26want to see that in the future, drop a

2:28comment down below. And I'm going to hit

2:30Windows as an

2:31option there. Now, it's important to know

2:33which version you're installing. Now,

2:35because I'm just

2:36going to be using Tableau or Tableau Prep

2:38on my laptop or on my desktop, it doesn't

2:41actually

2:41really matter. I don't have to worry too

2:43much about compatibility unless I start

2:45trying to

2:46connect to it from elsewhere. So I'm just

2:48going to go ahead and install version 12.

2:50So if I hit

2:51download the installer, it takes me to this

2:54page, which is hosted on enterprisedb.com.

2:57And you just

2:57go ahead and install the latest version,

3:00which in this case is 12.4. Go ahead,

3:02download that. That

3:03will obviously kick off the download. In

3:05this case, I'm going to save it to my

3:07desktop. And

3:08you'll actually see I've already got this

3:10file on my desktop here. So you'll see this

3:11. I've got this

3:12unconfirmed download over here because I've

3:14already got the file here on the top left

3:17of my window.

3:19Now, once you have the file on your desktop

3:23, it's time to run the install. And it's

3:25important to

3:25bear in mind a couple of things. When you

3:27set this up, you're going to need a few

3:28bits of information

3:29to have. First of all, your Windows

3:31username and password. We're going to need

3:33this a little later

3:34on to set things up. And secondly, it's

3:36good to have your password manager open

3:38because you're

3:39going to need to write down certain

3:40passwords because you don't want to forget

3:42these. Because

3:43honestly, the quickest way to reset these

3:46is just to install it again. So remember to

3:49note

3:49these things down as we go through the

3:51install so you know what they are in the

3:52future. Let's go

3:53ahead and double click the installer. I'm

3:55going to delete this one. You can see it's

3:56a duplicate.

3:57So let's just go ahead and do that. Let's

3:59double click the installer and minimize the

4:01browser window.

4:02So you can see here it's opened up the

4:09installer. I'm just going to go ahead and

4:11click next.

4:13And you obviously get a little sort of

4:15interface to ask you what components to

4:17install. They're

4:17all ticked. So go ahead and click next.

4:20Click next and it will actually kick off

4:23the install.

4:24It'll give you a list of what it's about to

4:26install. And when that's done, you can

4:29basically

4:30just let that run. Please ensure PG admin

4:33server is shut down before proceeding. I

4:36actually have

4:37PG admin running somewhere. I think it is

4:39shut down. I'm pretty sure it's shut down.

4:41So there

4:42we go. So I had this installed earlier on

4:45and I uninstalled it just for this video.

4:48And then

4:48I'm installing it again to make sure

4:50everything works. So let's just let this

4:52finish.

4:52Okay, I'm back and we're nearly at the end

4:57of the install. I'm just going to let this

5:00last step finish and then we're pretty much

5:03ready to go ahead and start trying to

5:05figure out how to

5:06set this up. Okay, so we're pretty much

5:10done. If I look at the instructions here, "

5:14Completing the

5:15PostgreSQL setup wizard." Setup has

5:17finished installing the PostgreSQL on your

5:19computer.

5:20Launch Stack Builder at exit. So this is

5:23basically the next step. The Stack Builder

5:26essentially

5:26allows you to build your Postgres

5:27environment. So we're going to go ahead and

5:29do that. Let's

5:30hit finish. And you can see Stack Builder

5:32has launched almost straight away. Now the

5:35key thing

5:35here is obviously we're going to choose a

5:37database type. And I previously had Post

5:39gres 9.4 installed

5:40on this and I've also had Postgres 12

5:43previously installed. So it's actually

5:45picked that up

5:46directly here. So I'm going to go ahead and

5:47select Postgres 12 in this particular case.

5:50Then I'm going to hit next. Now if you want

5:53to install any plugins, you can go ahead

5:56and select

5:56them from this list. There's various

5:58categories and if I expand one of these,

6:00you'll see these

6:01are some sort of add-ons that can be added

6:03on. Now the only one I'm actually

6:04interested in is

6:05a spatial one because Tableau has some

6:07spatial objects capabilities. So I want to

6:10be able to

6:11connect to a database and see that

6:13functionality. So I'll install that. In

6:15terms of everything else,

6:17you don't really need it. I'm not going to

6:19be doing anything else. And you can always

6:20come back here

6:21and install these in the future. You don't

6:23have to do this when you launch. It's

6:25obviously easier

6:25when you do it here, but you can do this

6:28later. So just go ahead and hit next. And

6:30then it obviously

6:31asks where to download these plugins

6:33because these plugins are not part of Post

6:35gres. They're sort of

6:36an add-on to Postgres. So go ahead, click

6:39next, click next. And you basically start

6:42the installation

6:42process for each of these bundles. I've

6:44only got one here. I'm going to ask it to

6:46create a spatial

6:47database. So I'll have something there

6:49rather than having to create something

6:51myself. Just allows you

6:52to make sure that everything's there. And

6:54then we can actually use this in Tableau to

6:55check that it's

6:56installed properly. So go ahead and hit

6:58next. It's going to ask for the destination

7:01folder for this.

7:02And then it's going to ask for the password

7:05. Now, in my case, I actually had a master

7:07password

7:08before. Now, when you install Postgres for

7:10the very first time in your machine, it

7:11will ask you

7:12for a master password very early on. If you

7:14set that up, this is the same password that

7:16you

7:16basically need to use. So this is mine.

7:20Okay, I'm going to hit next. And then

7:24obviously it's called

7:26Postgres sample. I'm just going to call

7:28this spatial_sample. So it's just a little

7:34bit easier

7:35and nicer to read. I'll use a lowercase s

7:38and hit install. Now we'll let that run.

7:43Okay, would you like us to register GDL

7:51data environment variable? If you needed

7:54for raster

7:54transformation to work properly, this will

7:56overwrite existing settings if you have

7:58them.

7:59Yes, these are obviously bundles that they

8:01're suggesting. This one, post_gis_enabled_

8:06drivers

8:07to common drivers. These look like image

8:09drivers. I'm just going to go ahead and

8:11click yes,

8:12enable out db rasters environment needs to

8:15be set to one. Okay, yes. It's always worth

8:18reading

8:19though. Sometimes they sneak in an option

8:21to install like a bit of software into

8:23these things.

8:23So always read those things, make sure they

8:25're not sneaking something in. Go ahead and

8:28hit close,

8:28and that's it. We've pretty much finished

8:30the installation and we're now ready to

8:32basically

8:32set it up. If I go to my start menu here

8:35and I go to the Postgres option, which is

8:38Postgres 12 here,

8:40and hit pg_admin4, this will open up the

8:43Postgres management tools. It's important

8:46to go here just

8:46to make sure everything's running and

8:48everything's working. Okay, so let me enter

8:50my master password.

8:51Click okay. Then yeah, that's pretty much

8:56it. So if I just expand this, never save

9:02that,

9:03Postgres 12, and it looks like everything's

9:06running great. If I go to schemas,

9:09you'll see that I actually potentially have

9:11a table here called Tableau Prep, which I

9:14created before.

9:15So it's kept my data and my information

9:17from the previous installation, but now you

9:19can see

9:20this is there, it's running, it's ready to

9:22work. So if I open up Tableau,

9:24I'll go ahead and open Tableau Prep at the

9:28same time so you can see that as well.

9:31And then here if we just hit Postgres, you

9:33can see that it's got the sample that I

9:36previously

9:36connected to. I just need to enter my

9:39master password. Actually, I didn't call

9:42this Postgres

9:4330, I called it something else. So let's

9:46just double check that here. Let's see what

9:49the

9:49database should be called. Postgres sample,

9:52this is called spatial sample. So the old

9:54one is called

9:55that, but this one is called spatial sample

9:57. So let's go ahead and connect to that.

10:04The master password for your database. You

10:08can of course create alternate usernames

10:10and passwords,

10:11but I'll just go ahead and create the

10:13default one that I created when I set it up

10:15as the admin.

10:16And here we have it. We have Postgres

10:18running on our machine and we're ready to

10:20go. We can connect

10:21to these fields. We can do what we

10:23typically do in Tableau, drag and drop, and

10:26we have these

10:27wonderful capabilities available to us. And

10:30the cool thing here is now that we have

10:32this,

10:32we can go ahead and actually start also

10:35investigating how databases work in the

10:38future.

10:38So whilst I'm doing all these things in the

10:41background, you can see that my Postgres

10:43database

10:44instance is obviously started to do things

10:46like transactions and it's loading and sort

10:48of logging

10:49what's going on. So I can obviously

10:51interrogate the SQL being run behind these.

10:53I'm not a Postgres

10:54expert, so I don't know where that is. That

10:56's going to be a learning journey for me as

10:57well.

10:58But that's pretty much it. That's pretty

11:00much it in a nutshell. We're just going to

11:01check the

11:02Tableau prep can connect to it as well. Let

11:04me just close this open up a previous

11:06workflow that

11:06I actually adapted already. And what we're

11:10going to do is connect to a database table,

11:12select Postgres. And this one, obviously,

11:15we're going to need to give it the database

11:17,

11:17so local host. And this one, the database

11:22is called spatial_sample. And the username

11:27and password is, in this case, is postgres

11:32and my master password.

11:34And go ahead, hit sign in. This might be

11:36the wrong password. Yes, I thought so,

11:38because I'm

11:38missing an exclamation mark. Hit sign in

11:41and we're in. And so here we have the

11:43database and we can,

11:45of course, connect to the spatial_sample,

11:48create another one, just call it test. And

11:51we're just

11:52going to make sure that it's there, create

11:53a new table. We don't want it to append. We

11:56want it to

11:56replace the data every single time. Hit

11:58replace. I'll do another video to go

11:59through this in more

12:00detail. This is a new feature in 2020.3 for

12:03Tableau prep. I'm just going to hit run

12:05flow.

12:06And we should now have that database in our

12:11table. So let's go ahead to spatial_samples

12:14,

12:14go to the schemas, go down to, oh, not sche

12:17mas, actually. Is it the schemas? Yeah, it

12:20should be

12:20correct. Oh, why am I not seeing what I'm

12:23expecting here? Why have I gone wrong here?

12:25So

12:26hold on. Postgres, schemas, yeah, public.

12:29Okay, so this is where I should go. I am in

12:31the right place.

12:32Schemas, tables, and there we are. There we

12:36have the table that we had in Tableau prep.

12:41And you

12:41can see the fields there are available to

12:43us. And so we can actually go ahead, go

12:46full circle,

12:48go ahead, go back, go in here, just search,

12:53test. See, if I hit search, this might

12:56refresh this

12:57connection. No. Okay, so let's click edit

13:00connection, click sign in. I'm hoping that

13:04refreshes this list. There we go. And then

13:06hopefully I can connect to that new table

13:08again.

13:08So I'll go ahead and remove this just by

13:11going out here, remove that.

13:16And then once that's gone, we can drag in

13:18the test table we just published in Tableau

13:20prep,

13:20just to test everything's working. And it's

13:23not going to be like lightning fast like a

13:26normal database, but we should have some

13:28information available to us. It says just

13:30put sales region,

13:31and then we can just put sales. And there

13:34we have it. We have a working database on

13:37our machine.

13:37We've created it, we've tested it in Table

13:40au, we've created some data in Tableau prep,

13:43we've published that to the database, then

13:45we've connected to it again in Tableau.

13:47So that's pretty much it. We did that. The

13:49recording here says 15 or so minutes. So

13:52hopefully that's been a useful video.

13:54Follow along. If you've got any questions

13:56at all,

13:57drop them in the comments below. And we'll

13:58try and address this in the future. In the

14:01next video,

14:02I'm going to be showing you the new feature

14:03in 2020.3 for Tableau prep where you can

14:06write to

14:06databases. So stay tuned for that. And

14:09hopefully if you're using this video for

14:10one of my other

14:11tutorials, hopefully you find this helpful

14:13for learning a little bit of SQL and how

14:15databases

14:16work. Okay, if you've enjoyed this video,

14:18you know what to do. If you haven't, drop a

14:20comment below,

14:20let me know what you'd like to see in the

14:22future. I'll catch you in the next one.