DEV Community

Cover image for Onboarding with posgress
Eddie Gulay
Eddie Gulay

Posted on

Onboarding with posgress

Getting started with PostgreSQL is straightforward. Here’s a step-by-step guide to help you set up PostgreSQL and start using it (One i used):

1. Install PostgreSQL

For Ubuntu (or other Debian-based distributions):

Open a terminal and run the following commands:

sudo apt update
sudo apt install postgresql postgresql-contrib
Enter fullscreen mode Exit fullscreen mode

For Windows:

  • Download PostgreSQL from the official website: PostgreSQL Windows Installer.
  • Run the installer, and it will guide you through the setup process. The default installation includes the PostgreSQL server, pgAdmin (a graphical management tool), and command-line utilities.

For macOS:

You can install PostgreSQL using Homebrew:

brew install postgresql
Enter fullscreen mode Exit fullscreen mode

After installation, start the PostgreSQL service:

brew services start postgresql
Enter fullscreen mode Exit fullscreen mode

Alternatively, download the installer from the PostgreSQL website here.


2. Start the PostgreSQL Service

Once installed, you’ll need to start the PostgreSQL service.

On Linux (Ubuntu):

sudo systemctl start postgresql
Enter fullscreen mode Exit fullscreen mode

To make PostgreSQL start automatically on boot:

sudo systemctl enable postgresql
Enter fullscreen mode Exit fullscreen mode

On Windows or macOS, PostgreSQL typically starts automatically after installation. If not, you can manually start the PostgreSQL service via system services or brew.


3. Access the PostgreSQL Shell (psql)

You can access the PostgreSQL command-line tool (psql) to start interacting with your database.

On Linux/macOS:

sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode

On Windows:

  • Open the SQL Shell (psql) from the Start menu.
  • It will ask for a few configuration details (like server, database, and user), but pressing Enter will default to local settings.

Once inside psql, you’ll be greeted with the PostgreSQL prompt:

postgres=#
Enter fullscreen mode Exit fullscreen mode

4. Create a New User and Database

PostgreSQL typically creates a default user named postgres. You can create a new user (role) and database if you prefer.

To create a new user:

CREATE USER your_username WITH PASSWORD 'your_password';
Enter fullscreen mode Exit fullscreen mode

To create a new database:

CREATE DATABASE your_database;
Enter fullscreen mode Exit fullscreen mode

To grant all privileges to the user on that database:

GRANT ALL PRIVILEGES ON DATABASE your_database TO your_username;
Enter fullscreen mode Exit fullscreen mode

5. Connect to the Database

Exit the psql prompt using \q or Ctrl+D. Now you can log in as the new user and connect to the database:

psql -U your_username -d your_database
Enter fullscreen mode Exit fullscreen mode

It will prompt you for the password, and you’ll be connected to your database.


6. Basic SQL Operations

Now that you’re connected, you can start running SQL queries. Here are some basic operations:

Create a table:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    role VARCHAR(50),
    salary NUMERIC
);
Enter fullscreen mode Exit fullscreen mode

Insert data:

INSERT INTO employees (name, role, salary) VALUES ('Alice', 'Manager', 50000);
Enter fullscreen mode Exit fullscreen mode

Query data:

SELECT * FROM employees;
Enter fullscreen mode Exit fullscreen mode

Update data:

UPDATE employees SET salary = 55000 WHERE name = 'Alice';
Enter fullscreen mode Exit fullscreen mode

Delete data:

DELETE FROM employees WHERE name = 'Alice';
Enter fullscreen mode Exit fullscreen mode

7. Using pgAdmin (Optional)

pgAdmin is a graphical user interface for managing your PostgreSQL databases.

  • If you installed PostgreSQL on Windows, pgAdmin comes with it. You can launch it from the Start menu.
  • For Linux/macOS, you can install pgAdmin separately:

On Ubuntu:

sudo apt install pgadmin4
Enter fullscreen mode Exit fullscreen mode

After installation, you can access pgAdmin via your browser (usually at localhost:5050).

Once logged into pgAdmin, you can connect to your PostgreSQL server, create new databases, run SQL queries, and manage tables visually.


8. Using PostgreSQL in Code

You can connect to PostgreSQL from different programming languages. Here's an example in Python using the psycopg2 library.

Install the psycopg2 package:

pip install psycopg2
Enter fullscreen mode Exit fullscreen mode

Example Python script:

import psycopg2

try:
    connection = psycopg2.connect(
        dbname="your_database",
        user="your_username",
        password="your_password",
        host="localhost",
        port="5432"
    )
    cursor = connection.cursor()

    # Sample query
    cursor.execute("SELECT * FROM employees;")
    records = cursor.fetchall()
    for record in records:
        print(record)

except Exception as e:
    print(f"Error: {e}")
finally:
    if connection:
        cursor.close()
        connection.close()
Enter fullscreen mode Exit fullscreen mode

9. Backup and Restore

To back up a PostgreSQL database:

pg_dump your_database > backup.sql
Enter fullscreen mode Exit fullscreen mode

To restore from a backup:

psql your_database < backup.sql
Enter fullscreen mode Exit fullscreen mode

10. Further Learning Resources

These steps should help you get started with PostgreSQL! Let me know if you need more guidance on specific features or tasks.

Top comments (2)

Collapse
 
gugaguichard profile image
Gustavo Guichard (Guga)

Great beginner’s guide, Eddie! If you’re looking for a simpler way to manage PostgreSQL without dealing with pgAdmin's complexity, I’m one of the creators of Flashboard, a web-based alternative. It’s fast, intuitive, and handles connections securely. Perfect for those who want a no-hassle admin experience. Would love to hear your thoughts!

Collapse
 
eddiegulay profile image
Eddie Gulay

That's amazing Gustavo, I will give it a try, also share it with my friends to try it out 🚀