Lesson Weekend

Starting in section 3 of Ruby and Rails, we'll be using a tool called Postgres for SQL database management. Even though we won't be using this tool for a few weeks, it's important to install it now so you'll have ample time to troubleshoot if there are any issues.

If you run into errors at any point in the installation process, scroll down to the section called "Common Errors". It's not a complete list of errors, but it does have some resources that could be helpful.

Windows


Installation

Begin by downloading and installing Postgres from its main site. Make sure to install version 12.1, for Windows. Use the default values for the setup, and make sure to keep note of the password you use for Postgres. For the purposes of this lesson, we will assume the password epicodus.

Configure System Environment Variable for "Path"

Next we need to make our GitBash shell recognize the command psql (more on this below) to open and run the PostgreSQL console. We'll do this by configuring our system environment variables for "Path" so that the psql command can be recognized in any shell (including, Powershell as well as GitBash).

To do this, we must include Postgres in the System Environment "Path" Variable. This is its own multi-step process. The image below shows the buttons we click starting at step #2 below. If you find the image is hard to read, open it in a new tab.

  1. Open the Control Panel:
    • Select "System and Security", then select "System".
    • Select the option "Change Settings" within the section titled "Computer name, domain, and workgroup settings", and a pop-up window for "System Properties" will display.
  2. Within the "System Properties" pop-up window:
    • Select the "Advanced" tab,
    • Then select the "Environment Variables..." button (red circle in image below); this will open a new window called "Environment Variables".
  3. Within the "Environment Variables" window, find the section called "System variables" which will have a table listing variable names and their values. In the table, find and double click "Path" under the "variable" column (red square in image below); this will open a new window called "Edit environment variable", listing all environment variables for "Path". Note: you won't and shouldn't have all of the same information in your system "Path" variables as shown in the image below.
    • From this window, click "New" (green circle) and add the exact location of your Postgres installation (green square), and click OK (orange circle) to save your changes.
    • This location is likely C:\Program Files\PostgreSQL\12\bin, but may differ depending on your specific installation.
    • Exit out of the "Edit environment variable" window, but keep the "Environment Variables" window open for the next step.

This image shows steps 2 and 3, both the windows and buttons that should be clicked.

Configure System Environment Variable for "PGDATA"

Next, we'll create a new environment variable for our system so that we can use the command postgres to start our database server. Follow the steps below, and reference the image as needed.

  • From within the "Environment variables" window (which we accessed by completing steps #1 and #2 above), and below the "System variables" section, select the "New" button (circled in red in the image below); this will open a new window (highlighted with a green square) to add a new variable to our system environment variables.
  • In the "Variable Name" input, enter PGDATA.
  • In the "Variable Value" input, enter C:\Program Files\PostgreSQL\12\data.
  • Select "Ok" to save your changes, and exit out of all of the windows.
  • Restart GitBash.

This images shows how to create a new system environment variable, for PGDATA.

Configure pgAdmin 4

Open the application pgAdmin 4. This should be located in the directory C:\Program Files\PostgreSQL\12\pgAdmin 4\bin. You can find it by:

  • Opening the Start menu,
  • Finding and expanding the PostgreSQL 12 menu item,
  • And then selecting pgAdmin4.

Once pgAdmin 4 is open, you may be prompted to enter a master password. It's optional to set a master password, but anytime passwords are involved for our database in the curriculum, we'll use epicodus.

Within pgAdmin 4, on the left site of the window you should see the "Browser" panel. In this panel, there should be a tree including Servers, containing PostgreSQL 12. Under PostgreSQL 12, right-click on Login/Group Roles, and select Create > Login/Group Role... to create a new role:

  • The Name for this role should be the same as the name of your Operating System username - so, whatever user you would see at C:\Users\.
  • In the Privileges tab, set all privileges for your account to Yes.
  • Save your changes to create the role.

If you ran into an error when trying to access PostgreSQL 12 in pgAdmin 4 that looks similar to this one:

connection to server at "localhost" (::1), port 5432 failed: Connection refused (0x0000274D/10061) Is the server running on that host and accepting TCP/IP connections? connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused (0x0000274D/10061) Is the server running on that host and accepting TCP/IP connections?

Try first starting your Postgres server by entering this command into a new GitBash window (don't exit the window until you've completed the above steps):

$ pg_ctl -D "C:\Program Files\PostgreSQL\12\data" start

Next Steps

Jump down to the section titled "Starting and Stopping the Postgres Server", and work through the rest of this lesson's topics.

Mac


Installation

If you've already set up Ruby according to the earlier lessons, you have Homebrew installed. Homebrew makes it really easy to install Postgres. To install the version of Postgres used at Epicodus, run the following command:

$ brew install [email protected]

Note: While you may choose to install the latest version of Postgres on your home environment, do not ever try to change the version on Epicodus computers. Remember that in a professional environment, you will be expected to adapt to the environment used at your workplace, not the other way around!

Also, make sure to keep note of the password you use for Postgres. For the purposes of this lesson, we will assume the password epicodus.

Configuration

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:

For bash users:

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

For zsh users:

echo "export PGDATA=/usr/local/var/[email protected]" >> ~/.zshrc

Next, enter this command to help some programs find Postgres more easily:

For bash users:

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

For zsh users:

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

Starting and Stopping the Postgres Server


First, exit out of your Terminal or GitBash and reopen it. This ensures that our shell is running with the most up to date environment variables.

Since we set up the PGDATA environment variable, we should now be able to start the database server with the postgres command. See the following code snippet for an example of running this command and the output you'll see. You may be prompted at this time to set your firewall permissions to allow PostgreSQL Server to run on your private network.

$ postgres
2022-03-31 14:29:10.348 PDT [21148] LOG:  starting PostgreSQL 12.10, compiled by Vis
ual C++ build 1914, 64-bit
2022-03-31 14:29:10.349 PDT [21148] LOG:  listening on IPv6 address "::", port 5432
2022-03-31 14:29:10.349 PDT [21148] LOG:  listening on IPv4 address "0.0.0.0", port
5432
2022-03-31 14:29:10.379 PDT [21148] LOG:  redirecting log output to logging collecto
r process
2022-03-31 14:29:10.379 PDT [21148] HINT:  Future log output will appear in director
y "log".

Take note, you'll have to leave that window open while you use the server.

If you are on a Mac and you get an error message similar to postgres: could not access directory "/usr/local/var/postgres", scroll down to the "Common Errors" section that discusses a possible solution to this.

To stop the server, press Ctrl + C (not Cmd + C).

You can also start/stop the server with the pg_ctl utility:

$ pg_ctl start
$ pg_ctl stop

If you start a database server with the pg_ctl utility, you'll also have to leave that terminal window open while you use the server. You can also check the status of a server and reload a server with the pg_ctl utility. To learn more, read the 'description' section of these docs.

For Mac users, if you want Postgres to boot at startup and run in the background, run:

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

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

Create a Database for your User


To prepare for upcoming lessons, create a default database with your computer's username.

Windows Instructions

Open GitBash, and start your Postgres server with postgres.

In a new GitBash window, enter the following command, where <user> is the name of the Operating System username that you set when you created the Login/Group role in pgAdmin 4 (in a previous section):

$ createdb <user>

Mac Instructions

Open the Terminal, and start your Postgres server with postgres. Open a new Terminal window, and enter in this command:

$ createdb $USER

Verifying Command Line Access


Once you've completed the set up instructions, ensure you can access Postgres server via the command line. Open GitBash and run this command:

$ psql

This should open the Postgres command prompt. You may be prompted to enter a password for the user you created in this process. You can type \q to exit from this prompt.

Common Errors


Forgetting to Start the Postgres Server

After running psql or createdb in your terminal and you get the following error response, it likely means you haven't started your Postgres server. The database server always needs to be running before we can access it.

$ psql
psql: error: could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 5432?
could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?

Mac Error postgres: could not access directory "/usr/local/var/postgres"

If you get an error after running postgres that states something similar to postgres: could not access directory "/usr/local/var/postgres": No such file or directory, double check the name of the postgres file, by entering the following commands:

$ cd ~
$ cd ../..
$ cd usr/local/var
$ ls

The terminal output should include a folder for postgres. It may be named postgres or [email protected]. Whatever it is, the path we set for PGDATA in the shell's config file needs to be updated. Enter the following commands to open your shell's config file in VS Code:

For bash:

$ code ~/.bash_profile

For zsh:

$ code ~/.zshrc

Once opened, update the path you have for PGDATA. This may change from:

export PGDATA=/usr/local/var/postgres

To:

export PGDATA=/usr/local/var/[email protected]

Windows Configuration Help

Postgres requires a fair amount of configuration to run correctly on Windows. You might find it helpful to use this Stack Overflow answer to guide you.

Trouble Starting a Postgres Server: lock file "postmaster.pid" already exists

Sometimes when you start a Postgres Server, you'll get a message about FATAL: lock file "postmaster.pid" already exists. This usually comes up when there's already a Postgres server running! Try shutting down all servers and restarting your server.

Below are two code snippets and example outputs from running pg_ctl -D "C:\Program Files\PostgreSQL\12\data" start and postgres on a Windows computer.

$ pg_ctl -D "C:\Program Files\PostgreSQL\12\data" start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2022-03-31 16:16:30.467 PDT [13248] FATAL:  lock
file "postmaster.pid" already exists
2022-03-31 16:16:30.467 PDT [13248] HINT:  Is another postmaster (PID 10592) run
ning in data directory "C:/Program Files/PostgreSQL/12/data"?
 stopped waiting
pg_ctl: could not start server
Examine the log output.
$ postgres
2022-03-31 16:18:23.771 PDT [16968] FATAL:  lock file "postmaster.pid" already e
xists
2022-03-31 16:18:23.771 PDT [16968] HINT:  Is another postmaster (PID 10592) run
ning in data directory "C:/Program Files/PostgreSQL/12/data"?

Lesson 6 of 7
Last updated May 11, 2022