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 an adapted version on GitHub. 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 <https://raw.githubusercontent.com/pthom/northwind_psql/master/northwind.sql>

   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.