Lesson Wednesday

If you'd like to follow along with the next lessons, you'll need to install a database. We're going to use PostgreSQL, or Postgres for short.

Postgres is a popular database that uses SQL, or Structured Query Language, the standard language of databases used across a multitude of different frameworks.

Other databases that use SQL are SQLite, MySQL, Microsoft SQL Server, and Oracle databases. They all have different implementations and features, but the common, basic SQL commands that we're going to learn are fairly standard among all of them.

Mac


If you've already followed along with earlier configuration lessons, you have Homebrew installed. Homebrew makes it really easy to install Postgres. Just run:

$ brew install postgres

After it finishes installing, you'll need to configure your computer a bit. First, you need to tell Postgres where to find the database cluster where your databases will be stored:

echo "export PGDATA=/usr/local/var/postgres" >> ~/.bash_profile

This command will help some programs find Postgres more easily:

echo "export PGHOST=/tmp" >> ~/.bash_profile

To load these configuration changes, run:

source ~/.bash_profile

To start the Postgres server, simply run:

postgres

The window with the server running will need to remain open. To use the terminal, open another terminal tab or window.

To stop the server, press Ctrl + C (not Cmd + C). If you want Postgres to boot at startup and run in the background, run:

ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents

And to start it now (since it won't boot automatically until you restart your computer), run:

pg_ctl start

The computers at Epicodus are configured to have Postgres launch at startup, so you don't need to start it manually in class.

To prepare for the next lesson, create a default database with your computer's username (to find the current username, type `whoami` at the prompt):

createdb $USER

And you're done.

Windows


Installing Postgres on Windows machines can be tricky. This is because Windows machines usually differ more greatly than Macs. Depending on your operating system, and various settings and configurations, your installation experience might differ.

We'll provide installation instructions that should work for the majority of Windows machines. Follow these instructions the best you can, but keep in mind there may be slight differences depending on your specific setup. If these instructions don't work for you, try Googling any errors or issues you encounter. If you're not able to get Postgres up and running on your own, we'll be happy to help you in class. You can also use this video as your guide, as well as this step-by-step guide on StackOverflow.

Download

  1. First, we'll need to download the Windows installer. We can do this by visiting the Download PostgreSQL page.

  2. On the Download PostgreSQL page, select a version. The latest stable version should work just fine.

  3. After selecting your version, select your operating system. Either Windows x86-32 for 32-bit Windows operating systems, or Windows x86-64, for 64-bit.

  4. Hit the Download Now button. This should prompt the download of an .exe file containing your installer.

Install

  1. After the download is complete, launch the .exe file.

  2. You may see an Open File - Security Warning popup asking you to confirm you'd like to run this file. Click Run.

  3. The installer should automatically launch with a welcome message. Click Next.

  4. You'll be prompted to provide an Installation Directory to install Postgres in. Keep the default. Click Next.

  5. You'll be prompted to choose a directory to store Postgres data. Again, the default should be just fine. Click Next.

  6. Next, you'll be asked to provide a username and password for the database superuser. Select a username and password. Do not forget your username and password. Then, again, click Next.

  7. After providing a username and password, the installer will ask you to select a port number for the server. Keep the default 5432. Again, click Next.

  8. You'll be prompted to select a Locale. Keep the [Default locale] option, and click Next.

  9. Next, the installer should state that it's Ready to Install. Again, click Next.

  10. You should be met with an Installing pop-up with a progress bar. It may take awhile for the installation to complete. This is absolutely normal.

  11. Once the installation is complete, you'll be asked if you'd like to download the Stack Builder tool. This isn't required for this course, but you may optionally download it if you'd like. Click Finish.

Adding Postgres to your PATH

Next, we need to add Postgres to our %PATH% environmental variable. This will ensure we can run Postgres commands from the terminal, and the terminal will recognize what we're referring to!

  1. Click the Windows Start button.

  2. Search for System in your Search bar.

  3. Click System in your Search results.

  4. Click on Advanced System Settings.

  5. Go to the Advanced tab.

  6. Select Environmental Variables.

  7. Look for something reading PATH, and click edit.

  8. Then append this to the existing PATH Variable Value: ;C:\Program Files\PostgreSQL\9.2\bin Note that your specific version number and installation location may differ. For instance, if you downloaded Postgres 9.5, this should read ;C:\Program Files\PostgreSQL\9.5\bin.

  9. Click OK three times to exit the various menus.

  10. Now, close the console and restart it.

Remove Username & Password Requirement

You may notice that the machines at Epicodus don't require a Postgres username and password; yet the Postgres installer for Windows required you provide one. Thankfully, there's a workaround we can easily implement. This is technically optional, but highly recommended, as it makes it easier to later clone and revisit projects (and easier for your instructors to grade them!):

  1. Visit the directory you instructed Postgres to store data in. (Step 5 under Install above). The default is usually C:\Program Files\PostgreSQL\9.0\data\ (Replace 9.0 with the specific version of Postgres you just installed).

  2. This directory should contain a pg_hba.conf file. Add the following line to this file: host all all 127.0.0.1/32 trust. This grants access without a password from your local machine.

  3. Then, you can connect to Postgres without a username and password with the following command:

$   psql.exe -h 127.0.0.1 -U postgres -w [YOUR_DB_NAME]

Terminology


  • SQL, or Structured Query Language: The standard language of databases used across a multitude of different frameworks.

  • PostgreSQL, or Postgres: An open source relational database management system that utilizes SQL.