Mastering SQL: Setting Up PostgreSQL and DBeaver with Sample Data

What You'll Need

 

1. Installing PostgreSQL on Ubuntu

Let’s set up PostgreSQL on your Ubuntu server.

 

Step 1: Update the Package Index

sudo apt update

 

Step 2: Install PostgreSQL

sudo apt install postgresql

 

Step 3: Start PostgreSQL

Ensure the service is running:

sudo systemctl status postgresql

If not, start it: 

sudo systemctl start postgresql.service

 

---

Understanding Roles in PostgreSQL

PostgreSQL uses roles for managing access. Upon installation:

Switch to the postgres user:

sudo -i -u postgres

Access the PostgreSQL CLI:

psql

Exit the CLI:

\q

---

 

Step 4: Create Roles and Databases

Create an admin role:

sudo -u postgres createuser --interactive

Grant it superuser privileges during the prompt.

Create the database:

sudo -u postgres createdb <database_name>

In our case it will be called pagila.

Add a regular user (name it user):

sudo -u postgres createuser --interactive

Answer "No" for superuser privileges, creating roles, and creating databases.

 

Step 5: Set Passwords for Roles

Log in to PostgreSQL:

sudo -u postgres psql

Set passwords:

ALTER ROLE admin WITH PASSWORD '<your_admin_password>';
ALTER ROLE \"user\" WITH PASSWORD '<your_user_password>';

 

Step 6: Enable Remote Connections

To connect the database from another machine:

Edit /etc/postgresql/<version>/main/pg_hba.conf. Add the following line:

host    all     all     <DBeaver_IP>/32   md5

For all IPs (less secure):

host    all     all     0.0.0.0/0        md5

Edit /etc/postgresql/<version>/main/postgresql.conf. Find and update listen_addresses:

listen_addresses = '*'

Restart PostgreSQL:

sudo systemctl restart postgresql

Allow connections through the firewall (if not allowed):

sudo ufw allow 5432/tcp

 

2. Connecting with DBeaver

 

Step 1: Install DBeaver

Download and install from DBeaver's official site.

 

Step 2: Set Up a Connection

sudo tail -f /var/log/postgresql/postgresql-<version>-main.log

 

3. Populating the Database with Pagila Database

 

Step 1: Download Pagila

Clone the repository:

git clone https://github.com/devrimgunduz/pagila.git

Step 2: Load the Schema and Data

Run these commands:

sudo psql -h <host> -p 5432 -U admin -d pagila -f path/to/pagila-schema.sql
sudo psql -h <host> -p 5432 -U admin -d pagila -f path/to/pagila-data.sql

 

4. Querying Pagila in DBeaver

Reconnect to the database in DBeaver. Test your setup with a simple query:

SELECT * FROM film LIMIT 10;

Or list how many films there are in each film category:

select category.name, count(category.name) category_count
from category
left join film_category on category.category_id = film_category.category_id
left join film on film_category.film_id = film.film_id
group by category.name
order by category_count desc;

Explore the data, run queries, and practice SQL to your heart’s content!

 

---

You’ve successfully set up PostgreSQL, configured it for remote access, connected with DBeaver, and loaded a sample database. With this setup, you're ready to master SQL and build your database expertise.

Got stuck or need further assistance? Drop a comment below, and let’s solve it together! Happy querying! 🎉

Category: DIY Tips