On this information, you can learn to set up PostgreSQL 14.7 to your Ubuntu method. The method is simple and is composed of the next steps:

1. Replace your method programs
2. Set up PostgreSQL
3. Arrange the superuser
4. Obtain the Northwind PostgreSQL SQL document
5. Create a brand new Database
6. Import the Northwind SQL document
7. Examine the Northwind database set up
8. Connect with the Database The use of Jupyter Pocket book

Must haves

To apply this instructional, you must be operating Ubuntu 20.04 LTS or later.

Step 1: Replace Machine Applications

First, you want to replace the method programs. Open the Terminal app (“Ctrl + Alt + T”) and input the next command:

sudo apt replace && sudo apt improve -y

Input your admin password when caused. This command will replace the package deal lists for upgrades for programs that want upgrading, in addition to new programs that experience simply come to the repositories, after which improve the these days put in programs. The -y possibility will robotically resolution ‘sure’ to all activates, making the method non-interactive.

Be aware: sudo is a prefix that offers you superuser permissions for a command, which is regularly essential when making system-wide adjustments like putting in or upgrading instrument. Watch out when the usage of sudo, because it supplies entire keep an eye on over your method, together with the power to wreck it if misused.

Step 2: Set up PostgreSQL

With the method programs up to date, you are prepared to put in PostgreSQL.

To put in the PostgreSQL package deal, use the apt package deal supervisor:

sudo apt set up postgresql-14

You’ll be caused to verify the volume of house the set up calls for to your native method. After the set up is entire, take a look at the standing of the PostgreSQL carrier:

systemctl standing postgresql

Whilst you run this command, it’ll show knowledge comparable to whether or not the carrier is lively or inactive, when it used to be began, the method ID, and up to date log entries. You’ll be able to know that it’s been put in effectively when you see a line very similar to Loaded: loaded (/lib/systemd/method/postgresql.carrier; enabled; dealer preset: enabled) indicating method has effectively learn the PostgreSQL carrier document.

After you run systemctl standing postgresql, you must in finding your self again on the command advised. If now not, and you might be caught in a view of log information, you could be in a “much less” or “extra” program that allows you to scroll throughout the logs. You’ll be able to generally go out this view and go back to the command advised by way of urgent q. If that does not paintings, then “Ctrl + C” will ship an interrupt sign to the present procedure and go back you to the command line.

Step 3: Putting in place the postgres person

PostgreSQL robotically creates a person (sometimes called a “function”) named postgres. To verify you are able to use PostgreSQL with none problems, let’s create a password for this person that has superuser privileges. You’ll be able to set a password for this person with this command:

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

Exchange your_password with a brand new password and ensure it’s wrapped in unmarried quotes. Please observe, this isn’t your native person account’s password. This password will likely be used to hook up with your PostgreSQL database with superuser privileges, so be certain it is robust and protected. This command will run the psql command because the postgres person, and cross it a SQL command to modify the postgres person’s password to your_password.

In PostgreSQL, the phrases “USER” and “ROLE” are necessarily interchangeable. The ALTER USER command is in fact an alias for ALTER ROLE, which is why you spot ALTER ROLE because the affirmation message.

So while you see ALTER ROLE, it simply implies that the password exchange used to be a hit and the postgres function (or person, in on a regular basis phrases) has a brand new password. You are now ready to make use of this new password to hook up with PostgreSQL because the postgres person.

Step 4: Obtain the Northwind PostgreSQL SQL document

First, you want to obtain a model of the Northwind database that is appropriate with PostgreSQL. You’ll be able to in finding an tailored model on GitHub. To obtain the SQL document, apply those two steps:

1. From the Terminal, create a brand new listing for the Northwind database and navigate to it:

   mkdir northwind && cd northwind

2. Obtain the Northwind PostgreSQL SQL document the usage of wget:

   wget <https://uncooked.githubusercontent.com/pthom/northwind_psql/grasp/northwind.sql>

   This will likely obtain the northwind.sql document to the northwind listing you created above.

Step 5: Create a brand new PostgreSQL database

Earlier than uploading the Northwind SQL document, you will have to create a brand new PostgreSQL database. Observe those 3 steps:

1. Connect with the PostgreSQL server because the postgres person:

   sudo -u postgres psql

   This command is telling the method to execute the psql command because the postgres person. psql is the interactive terminal for PostgreSQL, and when it begins, it adjustments the command advised to allow you to know that you are interacting with the PostgreSQL command-line and now not the method command-line.

   As soon as you might have run sudo -u postgres psql, your terminal advised will exchange to one thing very similar to postgres=# to suggest you might be hooked up to the postgres database.

2. Create a brand new database known as northwind:

   postgres=# CREATE DATABASE northwind;

   You’ll be able to see “CREATE DATABASE” is returned if the command is a hit.

3. Go out the psql command-line interface:

   postgres=# q

Step 6: Import the Northwind SQL document

With the northwind database created, you’ll be able to import the Northwind SQL document the usage of psql. Observe those steps:

• To your Terminal, make sure you’re within the northwind listing the place you downloaded the northwind.sql document.

• Run the next command to import the Northwind SQL document into the northwind database:

  sudo -u postgres psql -d northwind -f northwind.sql

  This command connects to the PostgreSQL server because the postgres person, selects the northwind database, and executes the SQL instructions within the northwind.sql document.

Step 7: Examine the Northwind database set up

To ensure that the Northwind database has been put in appropriately, apply those 4 steps:

1. Connect with the northwind database the usage of psql:

   sudo -u postgres psql -d northwind

2. Checklist the tables within the Northwind database:

   northwind=# dt

   You must see a listing of Northwind tables: classes, consumers, workers, orders, and extra.

3. Run a pattern question to verify the knowledge has been imported appropriately. As an example, you’ll be able to question the consumers desk:

   northwind=# SELECT * FROM consumers LIMIT 5;

   This must go back the primary 5 rows from the purchasers desk. Very similar to above while you used systemctl standing postgresql, you could be in a “much less” or “extra” program that allows you to scroll thru the result of the question. Press q to go back to the psql command-line interface.

4. Go out the psql command-line interface:

   northwind=# q

Step 8: Connect with the Database The use of Jupyter Pocket book

As we wrap up our set up, we can now introduce Jupyter Pocket book as probably the most equipment to be had for executing SQL queries and examining the Northwind database. Jupyter Pocket book gives a handy and interactive platform that simplifies the visualization and sharing of question effects, however you must observe that it’s an non-compulsory step. You’ll be able to additionally get admission to Postgres thru different way. On the other hand, we extremely counsel the usage of Jupyter Pocket book for its a large number of advantages and enhanced person enjoy.

To arrange the essential equipment and identify a connection to the Northwind database, here’s an outline of what every step will do:

• !pip set up ipython-sql: This command installs the ipython-sql package deal. This package deal lets you write SQL queries immediately on your Jupyter Pocket book, making it more uncomplicated to execute and visualize the result of your queries inside the pocket book atmosphere.
• %load_ext sql: This magic command a lot the sql extension for IPython. Through loading this extension, you’ll be able to use the SQL magic instructions, comparable to %sql and %%sql, to run SQL queries immediately within the Jupyter Pocket book cells.

• %sql postgresql://postgres@localhost:5432/northwind: This command establishes a connection to the Northwind database the usage of the PostgreSQL database method. The relationship string has the next structure:

  postgresql://username@hostname:port/database_name

  • On this case, username is postgres, hostname is localhost, port is 5432, and database_name is northwind. The %sql magic command means that you can run a single-line SQL question within the Jupyter Pocket book.

1. Replica the next textual content right into a code mobile within the Jupyter Pocket book:

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

2. Run the mobile by way of both:

   • Clicking the “Run” button at the menu bar.
   • The use of the keyboard shortcut: Shift + Input or Ctrl + Input.

3. Upon a hit connection, you must see an output very similar to the next:

   'Attached: postgres@northwind'

   This output confirms that you’re now hooked up to the Northwind database, and you’ll be able to continue with the guided mission on your Jupyter Pocket book atmosphere.

When you execute those instructions, you can be hooked up to the Northwind database, and you’ll be able to get started writing SQL queries on your Jupyter Pocket book the usage of the %sql or %%sql magic instructions.

Subsequent Steps

In response to what you might have completed, listed below are some attainable subsequent steps to proceed your studying adventure:

1. Deepen Your SQL Wisdom:
   • Check out formulating extra complicated queries at the Northwind database to reinforce your SQL abilities. Those may come with joins, subqueries, and aggregations.
   • Perceive the design of the Northwind database: investigate cross-check the tables, their relationships, and the way knowledge is structured.
2. Experiment with Database Control:
   • Discover ways to backup and repair databases in PostgreSQL. Check out making a backup of your Northwind database.
   • Discover other ways to optimize your PostgreSQL database efficiency like indexing and question optimization.
3. Integration with Python:
   • Discover ways to use psycopg2, a well-liked PostgreSQL adapter for Python, to engage along with your database programmatically.
   • Experiment with ORM (Object-Relational Mapping) libraries like SQLAlchemy to regulate your database the usage of Python.