Lesson Weekend

As you know, Postgres is already installed on all of our classroom macs. If you are interested in building Postgres databases, or practicing basic SQL commands on your own machine, you will need to either install a Postgres or use an online editor. This lesson will both recommend an online editor, and walk through the Postgres installation process. Choose whichever option best fits your needs.

Online Editors

Online editors are simple to use and practice general commands, but are not as full-fledged as a whole database.

We will be doing the vast majority of our practicing this week, and will be using scripts that contain SQL commands to help us set up our projects. So, in many cases, practicing SQL using an online editor will suffice to teach the basics and let you explore and practice SQL commands.

One of these editors is the W3Schools SQL Tryit Editor, which allows you to practice basic SQL commands, such as creating new records, deleting records, and selecting records. Take a look at it here:

W3Schools SQL Tryit Editor

Familiarize yourself with its functionality. You’ll see it works similarly to the psql command prompt on classroom Macs.

Installing Postgres

If you would like to go deeper and learn more, we strongly recommend installing a database on your home machine. Here are instructions you can follow for both Mac and Windows.

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

Take your time, here!

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 try our best to help you in class. You can also use this video as your guide, as well as this step-by-step guide on StackOverflow. It is a little older, but nevertheless contains relevant information that may be helpful.

Read through the windows installation instructions carefully, it can be difficult to fix errors in the installation process.

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]