!
  + 
!!
FINAL DAYS: GET 60% OFF LIFETIME – PAY ONCE, LEARN FOREVER
June 2, 2023
# Install PostgreSQL 14.7 on your Ubuntu system
In this guide, you'll learn how to install PostgreSQL 14.7 on your Ubuntu system. The process is straightforward and consists of the following steps:
1. Update your system packages
2. Install PostgreSQL
3. Set up the superuser
4. Download the Northwind PostgreSQL SQL file
5. Create a new Database
6. Import the Northwind SQL file
7. Verify the Northwind database installation
8. Connect to the Database Using Jupyter Notebook
## Prerequisites
To follow this tutorial, you should be running Ubuntu 20.04 LTS or later.
## Step 1: Update System Packages
First, you need to update the system packages. Open the Terminal app ("Ctrl + Alt + T") and enter the following command:
```
sudo apt update && sudo apt upgrade -y
Enter your admin password when prompted. This command will update the package lists for upgrades for packages that need upgrading, as well as new packages that have just come to the repositories, and then upgrade the currently installed packages. The `-y` option will automatically answer 'yes' to all prompts, making the process non-interactive.
Note: `sudo` is a prefix that gives you superuser permissions for a command, which is often necessary when making system-wide changes like installing or upgrading software. Be careful when using `sudo`, as it provides complete control over your system, including the ability to break it if misused.
## Step 2: Install PostgreSQL
With the system packages updated, you're ready to install PostgreSQL.
To install the PostgreSQL package, use the `apt` package manager:
sudo apt install postgresql-14
You may be prompted to confirm the amount of space the installation requires on your local system. After the installation is complete, check the status of the PostgreSQL service:
systemctl status postgresql
When you run this command, it will display information such as whether the service is active or inactive, when it was started, the process ID, and recent log entries. You'll know that it has been installed successfully if you see a line similar to `Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)` indicating system has successfully read the PostgreSQL service file.
After you run `systemctl status postgresql`, you should find yourself back at the command prompt. If not, and you're stuck in a view of log files, you might be in a "less" or "more" program that lets you scroll through the logs. You can typically exit this view and return to the command prompt by pressing `q`. If that doesn't work, then "Ctrl + C" will send an interrupt signal to the current process and return you to the command line.
## Step 3: Setting up the `postgres` user
PostgreSQL automatically creates a user (also known as a "role") named `postgres`. To ensure you'll be able to use PostgreSQL without any issues, let’s create a password for this user that has superuser privileges. You can set a password for this user with this command:
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your_password';"
Replace `your_password` with a new password and make sure it is wrapped in single quotes. Please note, this is not your local user account's password. This password will be used to connect to your PostgreSQL database with superuser privileges, so make sure it's strong and secure. This command will run the `psql` command as the `postgres` user, and pass it a SQL command to change the `postgres` user's password to `your_password`.
In PostgreSQL, the terms "USER" and "ROLE" are essentially interchangeable. The `ALTER USER` command is actually an alias for `ALTER ROLE`, which is why you see `ALTER ROLE` as the confirmation message.
So when you see `ALTER ROLE`, it just means that the password change was successful and the postgres role (or user, in everyday terms) has a new password. You're now able to use this new password to connect to PostgreSQL as the `postgres` user.
## Step 4: Download the Northwind PostgreSQL SQL file
First, you need to download a version of the Northwind database that's compatible with PostgreSQL. You can find . To download the SQL file, follow these two steps:
1. From the Terminal, create a new directory for the Northwind database and navigate to it:
   ```
   mkdir northwind && cd northwind
2. Download the Northwind PostgreSQL SQL file using `wget`:
   `wget <`
   This will download the `northwind.sql` file to the `northwind` directory you created above.
## Step 5: Create a new PostgreSQL database
Before importing the Northwind SQL file, you must create a new PostgreSQL database. Follow these three steps:
1. Connect to the PostgreSQL server as the `postgres` user:
   sudo -u postgres psql
   This command is telling the system to execute the `psql` command as the `postgres` user. `psql` is the interactive terminal for PostgreSQL, and when it starts, it changes the command prompt to let you know that you're interacting with the PostgreSQL command-line and not the system command-line.
   Once you've run `sudo -u postgres psql`, your terminal prompt will change to something similar to `postgres=#` to indicate you're connected to the `postgres` database.
2. Create a new database called `northwind`:
   postgres=# CREATE DATABASE northwind;
   You'll see "CREATE DATABASE" is returned if the command is successful.
3. Exit the `psql` command-line interface:
   postgres=# \\q
## Step 6: Import the Northwind SQL file
With the `northwind` database created, you can import the Northwind SQL file using `psql`. Follow these steps:
* In your Terminal, ensure you're in the `northwind` directory where you downloaded the `northwind.sql` file.
* Run the following command to import the Northwind SQL file into the northwind database:
  ```
  sudo -u postgres psql -d northwind -f northwind.sql
  This command connects to the PostgreSQL server as the `postgres` user, selects the `northwind` database, and executes the SQL commands in the `northwind.sql` file.
## Step 7: Verify the Northwind database installation
To verify that the Northwind database has been installed correctly, follow these four steps:
1. Connect to the `northwind` database using `psql`:
   sudo -u postgres psql -d northwind
2. List the tables in the Northwind database:
   northwind=# \\dt
   You should see a list of Northwind tables: `categories`, `customers`, `employees`, `orders`, and more.
3. Run a sample query to ensure the data has been imported correctly. For example, you can query the `customers` table:
   northwind=# SELECT * FROM customers LIMIT 5;
   This should return the first five rows from the customers table. Similar to above when you used `systemctl status postgresql`, you might be in a "less" or "more" program that lets you scroll through the results of the query. Press `q` to return to the `psql` command-line interface.
4. Exit the `psql` command-line interface:
   northwind=# \\q
## Step 8: Connect to the Database Using Jupyter Notebook
As we wrap up our installation, we will now introduce Jupyter Notebook as one of the tools available for executing SQL queries and analyzing the Northwind database. Jupyter Notebook offers a convenient and interactive platform that simplifies the visualization and sharing of query results, but it's important to note that it is an optional step. You can also access Postgres through other means. However, we highly recommend using Jupyter Notebook for its numerous benefits and enhanced user experience.
To set up the necessary tools and establish a connection to the Northwind database, here is an overview of what each step will do:
* `!pip install ipython-sql`: This command installs the `ipython-sql` package. This package enables you to write SQL queries directly in your Jupyter Notebook, making it easier to execute and visualize the results of your queries within the notebook environment.
* `%load_ext sql`: This magic command loads the `sql` extension for IPython. By loading this extension, you can use the SQL magic commands, such as `%sql` and `%%sql`, to run SQL queries directly in the Jupyter Notebook cells.
* `%sql postgresql://postgres@localhost:5432/northwind`: This command establishes a connection to the Northwind database using the PostgreSQL database system. The connection string has the following format:
  `postgresql://username@hostname:port/database_name`
  + In this case, `username` is `postgres`, `hostname` is `localhost`, `port` is `5432`, and `database_name` is `northwind`. The `%sql` magic command allows you to run a single-line SQL query in the Jupyter Notebook.
1. Copy the following text into a code cell in the Jupyter Notebook:
   `!pip install ipython-sql`
   `%load_ext sql`
    `%sql postgresql://postgres@localhost:5432/northwind`
2. Run the cell by either:
   * Clicking the "Run" button on the menu bar.
   * Using the keyboard shortcut: `Shift + Enter` or `Ctrl + Enter`.
3. Upon successful connection, you should see an output similar to the following:
   `'Connected: postgres@northwind'`
   This output confirms that you are now connected to the Northwind database, and you can proceed with the guided project in your Jupyter Notebook environment.
Once you execute these commands, you'll be connected to the Northwind database, and you can start writing SQL queries in your Jupyter Notebook using the `%sql` or `%%sql` magic commands.
### Next Steps
Based on what you've accomplished, here are some potential next steps to continue your learning journey:
1. **Deepen Your SQL Knowledge:**
   * Try formulating more complex queries on the Northwind database to improve your SQL skills. These could include joins, subqueries, and aggregations.
   * Understand the design of the Northwind database: inspect the tables, their relationships, and how data is structured.
2. **Experiment with Database Management:**
   * Learn how to backup and restore databases in PostgreSQL. Try creating a backup of your Northwind database.
   * Explore different ways to optimize your PostgreSQL database performance like indexing and query optimization.
3. **Integration with Python:**
   * Learn how to use `psycopg2`, a popular PostgreSQL adapter for Python, to interact with your database programmatically.
   * Experiment with ORM (Object-Relational Mapping) libraries like `SQLAlchemy` to manage your database using Python.
About the author
#### Celeste Grupman
Celeste Grupman is the CEO at Dataquest She is passionate about creating affordable access to high-quality skills training for students across the globe.
## More learning resources
## SQL Subqueries: A Beginner’s Guide (with Code Examples)
## SQL Commands: The Complete List (w/ Examples)
Learn data skills 10x faster
Join 1M+ learners
Start Now
Enroll for free
All rights reserved.
Dataquest Labs, Inc.
### About
### Career Paths
### Skill Paths
### Explore
  +