Postgres Setup

Welcome! This guide will help you set up PostgreSQL (often shorted to just Postgres) on your Windows or Mac computer. Postgres is a program for managing databases. We will build applications that communicate with Postgres to manage databases.

Table of Contents

• What even is a database?

• Installation Instructions

• Interacting with Databases

  • Play Around With psql

  • TablePlus

• Conclusion

• Important Commands / Configuration

  • Checking your Postgres server status

  • Connecting to the PSQL terminal

  • Using the PSQL terminal

  • Tableplus Postgres Server Connection Configuration

• Troubleshooting

What even is a database?

A database is a structured collection of data that is organized and stored in a way that computers can efficiently retrieve, manage, and update the data.

Postgres is a relational database management system (RDBMS), a program that makes it easier for users to find, update, and manage the data through the use of SQL queries.

If a database is like a library, then a relational database management system is like a librarian with a particular catalog system.

Here's some quick info about Postgres and relational database management systems:

• Data is separated into collections called tables, spreadsheet-like structures that represent a single type of value or "entity" (e.g. users, posts, comments, likes, etc...)

  • Each row represents a single resource in the table. (e.g. a single user in the users table)

  • Each column defines a property that all resources of a table share (e.g. a users table has id, username, and password columns).

• Tables can be related to each other, typically by referencing the id of another table (e.g. a posts table has a user_id column so that each post is related to the user in the users table that created it)

While there are many types of database management systems, each with their own approach to managing a database, Postgres is the most popular. The next 3 most popular are also RDBMSs too!

Popular non-relational database management systems include MongoDB, Redis, and Firebase.

Installation Instructions

Follow the instructions below according to your operating system:

Windows + WSL Instructions

1. Open up your Ubuntu Terminal

2. Make sure your Ubuntu packages are up to date by running the command: sudo apt update

3. Now, install Postgres by running the command: sudo apt install postgresql postgresql-contrib. You may have to type in Y, to allow it to proceed.

4. Confirm the installation and check the version number by running the command: psql --version and you should see a version number appear!

5. Check the status of your PostgreSQL server by running the command: sudo service postgresql status. It should say it is "down".

6. Turn on PostgreSQL. You can do so by running sudo service postgresql start. Check the status of postgres and it should be "online" on port 5432.

   Check out the Important Commands / Configuration section of this page for more commands like this.

7. In order to access your Postgres databases, you'll need a user account (called a "role"). By default, the installation process creates a user called postgres which you can use.

8. Now, connect to the Postgres service as the postgres user and open the psql shell by running the command: sudo -u postgres psql

9. Once you have successfully entered the psql shell, you will see your command line change to look like this: postgres=#

10. Now we'll add a password for the postgres user. Run the command: ALTER USER postgres WITH ENCRYPTED PASSWORD 'your password';.

    Replace the password with something short and memorable (e.g. '123' is fine). NOTE: Keep the quotation marks around your password and the semicolon!

Mac Instructions

1. Go to https://postgresapp.com/

2. Click on the downloads tab

3. You should see something like "Postgres.app with PostgreSQL [version]". Click on this link to download Postgres to your Mac.

4. After it's finished downloading, install the program, and run it.

5. Now, you'll have to initialize your database. Click the Initialize button on the right-hand side. The Postgres app should now say Running

6. To let us use Postgres CLI commands, open up a terminal window and run this command sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp

   • If you're not able to run this command, skip down to the TablePlus setup steps.

7. Restart your terminal

8. In your terminal, type in createdb example. You should not get an error after the command runs. Now let's see it in action!

9. In order to access your Postgres databases, you'll need a user account (called a "role"). By default, the installation process creates a user called postgres which you can use.

10. Now, connect to the Postgres service as the postgres user and open the psql shell by running the command: psql -U postgres

11. Once you have successfully entered the psql shell, you will see your command line change to look like this: postgres=#

12. Now we'll add a password for the postgres user. Run the command: ALTER USER postgres WITH ENCRYPTED PASSWORD 'your password';.

    Replace the password with something short and memorable (e.g. '123' is fine). NOTE: Keep the quotation marks around your password and the semicolon!

Now, let's play around with Postgres!

Interacting with Databases

Play Around With psql

Before you exit the psql terminal, let's learn a little bit about it. If you already exited it you can get back in through your terminal:

• Windows: sudo -u postgres psql

• Mac: psql -U postgres

The psql terminal is a way to manage your Postgres databases using a command-line interface. There are a few useful commands to know so you can get around. Try these out:

• \l

  • This lists all of the databases. By default, you are given one called postgres and two protected ones called template0 and template1.

• CREATE DATABASE test; (remember the semicolon!)

  • This will create a new database called test stored and managed by Postgres.

  • Use the \l command again to see the updated list.

• \c test

  • to connect to your new test database. You should see the command line prompt change to test=#

• CREATE TABLE friends ( id INT, name TEXT );

  • Remember the semicolon!

  • This will create a table called friends with 2 columns called id and name.

  • id values must be integers and name values can be any text.

• INSERT INTO friends (id, name) VALUES (1, 'reuben');

  • This will create a row in the friends table.

  • Try running this SQL query with different values to add more values to your table

• SELECT id FROM friends;

  • This will select all rows from the friends table, but only show the id column

• SELECT id, name FROM friends;

  • This will select the same row, but include the name column

• SELECT * FROM friends;

  • This will select all rows and all columns (a different approach to get the same result as above)

• SELECT name FROM friends WHERE id=1;

  • What do you think this one does?

You can now exit your psql shell by typing in \q and hitting enter

TablePlus

Before setting up TablePlus, double check that your Postgres Server is up and running:

Windows: in the terminal start your Postgres server with sudo service postgresql start.

Mac: Click the Start button so that the Red X turns into a Green checkmark.

Access Postgres via the toolbar on Mac OS

Interacting with Postgres through the command-line interface may make you feel like a pro, but let's face it, it isn't the best. TablePlus is a GUI (graphical user interface) that makes viewing your databases much nicer.

1. Download tableplus from their website. Make sure to download the corresponding version for your OS.

2. After installing and opening the application, click the "Create A New Connection..." towards the bottom of the window. You will see a prompt to select the type of database you want to connect to. Select Postgresql.

3. Now, you'll enter the configurations for this connection:

   • For Name, enter postgres since we are connecting to our Postgres server.

   • For Host, enter localhost or 127.0.0.1 (these are equivalent)

   • For Port, enter 5432.

   • For the user field, enter postgres.

   • In the password field, enter the password you created for user postgres in step 10.

   • Leave the remaining fields blank

4. Click the test button, and if everything is successful, all of the fields should be highlighted green! At that point, click connect, and you should be able to view the GUI client for your database.

Now, let's play around with TablePlus. Start by clicking on the Database Icon. Then create a database called tabeplus-test and open it!

Next, click on the SQL button and write your own SQL queries.

• CREATE TABLE friends ( id INT, name TEXT );

  • Remember the semicolon!

  • This will create a table called friends with 2 columns called id and name.

  • id values must be integers and name values can be any text.

• INSERT INTO friends (id, name) VALUES (1, 'reuben');

  • This will create a row in the friends table.

  • Try running this SQL query with different values to add more values to your table

• SELECT id FROM friends;

  • This will select all rows from the friends table, but only show the id column

• SELECT id, name FROM friends;

  • This will select the same row, but include the name column

• SELECT * FROM friends;

  • This will select all rows and all columns (a different approach to get the same result as above)

• SELECT name FROM friends WHERE id=1;

  • What do you think this one does?

Conclusion

That's it! Now that you can connect to your Postgres database using either the psql CLI or using a GUI like TablePlus, you're ready to learn more about how to access and manage your database using SQL.

Important Commands / Configuration

Checking your Postgres server status

For Windows users, check your Postgres server status in your terminal:

• sudo service postgresql status - to see if your server is running

• sudo service postgresql start - to start your postgresql server

• sudo service postgresql restart - to restart your postgresql server

For Mac users, check your Postgres server status through the Postgres application.

Mac Postgres App

Connecting to the PSQL terminal

To connect to your Postgres server as the postgres user:

• For Windows Users: sudo -u postgres psql

• For Mac Users: psql -U postgres

Using the PSQL terminal

In your psql terminal:

• \du to see a list of users

• \q to quit

• \l to see a list of databases

• \c database_name to connect to a database

• \dt to see a list of tables in the connected database

And you can also run any SQL commands from the psql terminal (remember the semicolon!):

• CREATE DATABASE db_name; to create a new database

• SELECT * FROM table; to see all rows from the given table

• ALTER USER username WITH ENCRYPTED PASSWORD 'password'; to set a password (use single quotes)

Tableplus Postgres Server Connection Configuration

• Host/Socket: 127.0.0.1 or localhost

• Port: 5432

• User: Enter your username or postgres

• Database: postgres (the default will be the same as the user value)

Troubleshooting

1. If you can't connect to your database because of FATAL: password authentication failed for user <username>, ask your instructor for help. They will do the following:

• Find and edit your pg_hba.conf using vim: sudo vim /etc/postgresql/12/main/pg_hba.conf (where 12 is the version number)

• Alternately, find notepad or notepad++ in your start menu, right click, choose "Run as administrator", then use File->Open to open pg_hba.conf that way.

• Update the "host" line for user "postgres" on host "127.0.0.1/32" from "md5" to "trust".

  • You can add the line if it isn't there; just insert host all postgres 127.0.0.1/32 trust before any other lines. (You can ignore comments, lines beginning with #).

• Restart the PostgreSQL service: sudo service postgresql restart

• Connect using sudo -u postgres psql / psql

• Run ALTER USER postgres PASSWORD 'fooBarEatsBarFoodBareFoot'; (don't forget the ;!)

• Remove the line you added to pg_hba.conf or change it back

• Restart PostgreSQL again to bring the changes to effect.

• Try connecting again