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 14.4, for Windows.

During the installation process, use the default values, with one exception: when you are prompted to select which tools to install, we suggest that you deselect Stack Builder. Stack Builder is a tool for installing additional modules that compliment Postgres. However, we won't be using any of those modules.

Otherwise, make sure to keep note of the password you use for Postgres, since you will use it over and over. 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. Access "System Environment Variables"; to do this, you can try either of the following routes:
   • Use the search bar next to the Start menu and search for "environment variables". Select the option for "edit the system environment variables".
   • Open the Control Panel, select "System and Security", then select "System". Next, search for "Environment Variables" in the search bar. You should see an option for "edit the system environment variables". Select that!
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\14\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.

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\14\data.
• Select "Ok" to save your changes, and exit out of all of the windows.
• Restart GitBash.

Start the Postgres Server

Next, start the Postgres Server by running the following command in a GitBash window:

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

You should see output similar to this:

waiting for server to start....2022-08-04 22:47:52.594 PDT [2412] LOG:  redirect
ing log output to logging collector process
2022-08-04 22:47:52.594 PDT [2412] HINT:  Future log output will appear in direc
tory "log".
 done
server started

We'll learn more ways to start and stop the Postgres server later in the lesson. Right now we need to run the server in order to configure pgAdmin 4.

Configure pgAdmin 4

Next, open the application pgAdmin 4. This should be located in the directory C:\Program Files\PostgreSQL\14\pgAdmin 4\bin. You can find it by:

• Opening the Start menu,
• Finding and expanding the PostgreSQL 14 menu item,
• And then selecting pgAdmin 4.

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 14. Under PostgreSQL 14, 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 username for your computer - so, whatever user you would see at C:\Users\. This is the user that is set to your home ~ folder in GitBash.
• In the Privileges tab, turn on all privileges.
• Save your changes to create the role.

If you ran into an error when trying to access the PostgreSQL 14 server 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?

This means that your Postgres server is not running! Start your Postgres server by entering this command into GitBash:

$ pg_ctl -D "C:\Program Files\PostgreSQL\14\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/postgresql" >> ~/.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-06-04 14:50:31.292 PDT [20140] LOG:  redirecting log output to logging coll
ector process
2022-06-04 14:50:31.292 PDT [20140] HINT:  Future log output will appear in dire
ctory "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 do not 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 not configured to have Postgres launch at startup, so you will need to start the server 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 (earlier in this lesson):

$ 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\14\data" start and postgres on a Windows computer.

$ pg_ctl -D "C:\Program Files\PostgreSQL\14\data" start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2022-06-04 14:01:25.926 PDT [13672] FATAL:  lock
file "postmaster.pid" already exists
2022-08-04 23:01:25.926 PDT [13672] HINT:  Is another postmaster (PID 18616) run
ning in data directory "C:/Program Files/PostgreSQL/14/data"?
 stopped waiting
pg_ctl: could not start server
Examine the log output.

$ postgres
2022-06-04 14:01:52.397 PDT [15520] FATAL:  lock file "postmaster.pid" already e
xists
2022-0s-04 14:01:52.397 PDT [15520] HINT:  Is another postmaster (PID 18616) run
ning in data directory "C:/Program Files/PostgreSQL/14/data"?