Install Postgres QL 14.7 on Ubuntu – DataQuest

In this tutorial, you will learn how to install Postgres QL 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 superuser
4. Download the Northwind Postgres SQL file
5. Create a new database
6. Import the Northwind SQL file
7. Verify the installation of the Northwind database
8. Connect to the database using Jupiter Notebook

Conditions

To follow this tutorial, you must be running Ubuntu 20.04lts 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 upgrade package lists for packages that need to be upgraded, as well as new packages that have just arrived in the repositories, and then upgrade the currently installed packages. -y The option will automatically answer ‘yes’ to all prompts, making the process non-interactive.

Note: sudo is a prefix that gives you superuser permissions to commands, which is often necessary when making system-wide changes like installing or upgrading software. Be careful when using sudoas 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 are ready to install PostgresQL.

To install the PostgreSQL package, use apt Package Manager:

sudo apt install postgresql-14

You may be prompted to confirm the required installation location 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 enabled or disabled, when it was started, the process ID, and recent log entries. You’ll know it’s installed successfully if you see a line Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Indicates the system has successfully read the PostgreSQL service file.

After you run away systemctl status postgresqlyou should find yourself back at the command prompt. If not, and you’re stuck in the log files view, you may be in a “less” or “more” program that lets you scroll through the logs. You can normally 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: Set up postgres User

PostgreSQL automatically assigns a user name (also known as a “role”) to postgres. To make sure you’ll be able to use PostgresQL without any issues, let’s create a password for a user with superuser privileges. You can set the password for this user with this command:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your_password';"

change your_password with a new password and make sure it is wrapped in single quotes. Please note, this is not the password for your local user account. This password will be used to connect to your PostgreSQL database with superuser privileges, so make sure it’s strong and secure. This will run the command psql As a command postgres user, and pass it a SQL command to change it postgres User password your_password.

In PostgreSQL, the terms “user” and “role” are essentially interchangeable. ALTER USER The command is actually an alias ALTER ROLEthat’s why you see ALTER ROLE as a confirmation message.

So when you see ALTER ROLEit just means that the password change was successful and the Postgres role (or user, depending on the day) has a new password. You are now able to use this new password to connect to PostgreSQL postgres User

Step 4: Download the Northwind PostgreSQL SQL file

First, you need to download a version of the Northwind database that is 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:

   mkdir northwind && cd northwind

2. Download using Northwind Postgres SQL file wget:

   wget 

   It will download northwind.sql File to file northwind directory you created above.

Step 5: Create a new PostgreSQL database

Before importing the Northwind SQL file, you must create a new Postgres SQL database. Follow these three steps:

1. Connect to the PostgreSQL server as postgres user:

   sudo -u postgres psql

   This command is asking the system to execute psql As a command postgres User psql Postgres is an interactive terminal for SQL, and when it starts, it changes the command prompt to tell you that you are interacting with the Postgres SQL command line and not the system command line.

   Once you run sudo -u postgres psqlyour terminal prompt will change to something similar to postgres=# To indicate that you are connected to it postgres Database

2. Create a new database called northwind:

   postgres=# CREATE DATABASE northwind;

   If the command is successful you will see “Create database” is returned.

3. Get out psql Command line interface:

   postgres=# \q

Step 6: Import the Northwind SQL file

with the northwind Database created, you can import using Northwind SQL file psql. Follow these steps:

• In your terminal, make sure you’re in northwind The directory where you downloaded 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 postgres The user selects northwind Database, and execute SQL commands northwind.sql File

Step 7: Verify the installation of the Northwind database

To verify that the Northwind database has been installed correctly, follow these four steps:

1. Connect to northwind Using a database psql:

   sudo -u postgres psql -d northwind

2. List the tables in the Northwind database:

   northwind=# \dt

   You should see a list of North Wind tables: categoriesfor , for , for , . customersfor , for , for , . employeesfor , for , for , . ordersand more.

3. Run a sample query to ensure data is imported correctly. For example, you can query customers table:

   northwind=# SELECT * FROM customers LIMIT 5;

   This returns the first five rows from the users table. As above when you use systemctl status postgresqlyou can have a “less” or “more” program that lets you scroll through the query results. Press q to come back psql Command line interface.

4. Get out psql Command line interface:

   northwind=# \q

Step 8: Connect to the database using Jupiter Notebook

As we wrap up our installation, we will now introduce Jupiter Notebook as one of the tools available to execute SQL queries and analyze the Northwind database. Jupyter Notebook offers a simple and interactive platform that makes it easy to visualize and share query results, but it’s important to note that this is an optional measure. You can also access Postgres through other means. However, we recommend using Jupyter Notebook for its many advantages and better user experience.

To set up the necessary tools and establish a connection to the Northwind database, here’s an overview of what each step will do:

• !pip install ipython-sql: This command installs ipython-sql Package This package enables you to write SQL queries directly in your Jupyter notebook, making it easy to execute and visualize the results of your queries in the notebook environment.
• %load_ext sql: This loads the magic command sql Extension for ipython. By loading this extension, you can use SQL magic commands, such as %sql And %%sqlto run SQL queries directly into cells in a Jupyter notebook.

• %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 postgresfor , for , for , . hostname is localhostfor , for , for , . port is 5432and database_name is northwind. %sql The magic command allows you to run a single-line SQL query in a Jupyter notebook.

1. Copy the following text into a code cell in Jupiter Notebook:

   !pip install ipython-sql
   %load_ext sql
   %sql postgresql://postgres@localhost:5432/northwind

2. Run the cell either:

   • Clicking the “Run” button on the menu bar.
   • Using keyboard shortcuts: Shift + Enter or Ctrl + Enter.

3. After a successful connection, you should see 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 Jupiter notebook environment.

Once you run these commands, you will be connected to the Northwind database, and you can start writing SQL queries in your Jupyter notebook. %sql or %%sql Magic commands

Next Steps

Based on what you’ve achieved, here are some possible next steps to continue your learning journey:

1. Deepen your SQL knowledge:
   • Try developing more complex queries on the Northwind database to improve your SQL skills. These can include joins, subs and sums.
   • Understand Northwind database design: Examine tables, their relationships, and how data is structured.
2. Experience with database management:
   • Learn how to backup and restore databases in PostgreSQL. Try making a backup of your Northwind database.
   • Explore different ways to improve the performance of your PostgreSQL database, such as indexing and query optimization.
3. Integration with Python:
   • Learn how to use psycopg2a popular PostgreSQL adapter for Python, to interact with your database program.
   • Experiment with ORM (Object-Oriented Mapping) libraries SQLAlchemy To manage your database using Python.