# 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?](#what-even-is-a-database) * [Installation Instructions](#installation-instructions) * [Interacting with Databases](#interacting-with-databases) * [Play Around With `psql`](#play-around-with-psql) * [TablePlus](#tableplus) * [Conclusion](#conclusion) * [Important Commands / Configuration](#important-commands--configuration) * [Checking your Postgres server status](#checking-your-postgres-server-status) * [Connecting to the PSQL terminal](#connecting-to-the-psql-terminal) * [Using the PSQL terminal](#using-the-psql-terminal) * [Tableplus Postgres Server Connection Configuration](#tableplus-postgres-server-connection-configuration) * [Troubleshooting](#troubleshooting) ## Installation Instructions Follow the instructions below according to your operating system: {% tabs %} {% tab title="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](#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! > {% endtab %} {% tab title="Mac Instructions" %} 1. Go to 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: ```sh sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp ```

If you are using a Marcy loaner laptop, you will get an error that says Marcy_Student not on sudoers list. If that is the case then first run the following command:

su marcyadmin

The password is unlock#. This will open a temporary session in your terminal as the super user marcyadmin.

Then, copy and paste the sudo mkdir ... command above and then type and enter exit to exit the super user session.

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! > {% endtab %} > {% endtabs %} 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 ( friend_id INT, name TEXT );` * Remember the semicolon! * This will create a table called `friends` with 2 columns called `friend_id` and `name`. * `friend_id` values must be integers and `name` values can be any text. * `INSERT INTO friends (friend_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 friend_id FROM friends;` * This will select all rows from the `friends` table, but only show the `friend_id` column * `SELECT friend_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 friend_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](/files/RGcR67doTt92EyZWBBjQ) 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](https://tableplus.com/) 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! ![](/files/rwP5nKGSyCwWDczbzwvs) Next, click on the `SQL` button and write your own SQL queries. * `CREATE TABLE friends ( friend_id INT, name TEXT );` * Remember the semicolon! * This will create a table called `friends` with 2 columns called `friend_id` and `name`. * `friend_id` values must be integers and `name` values can be any text. * `INSERT INTO friends (friend_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 friend_id FROM friends;` * This will select all rows from the `friends` table, but only show the `friend_id` column * `SELECT friend_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 friend_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](/files/e3Ycy2AgVsDBx2UoYXdS) ### 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 `, 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 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://marcylabschool.gitbook.io/marcy-lab-school-docs/environment-setup/postgres-setup.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.