Setup - Ubuntu

1

To set up PostgreSQL on Ubuntu, follow these steps:

1. Update the System

Copy

sudo apt update
sudo apt upgrade -y

2. Install PostgreSQL

Copy

sudo apt install postgresql postgresql-contrib -y

3. Start and Enable the PostgreSQL Service

Copy

sudo systemctl start postgresql
sudo systemctl enable postgresql

4. Switch to the PostgreSQL User

PostgreSQL creates a user named postgres by default. Switch to this user to manage the database:

Copy

sudo -i -u postgres

5. Access the PostgreSQL Command-Line Interface (psql)

Copy

psql

You should now see the psql prompt:

Copy

postgres=#

6. Create a New Database

At the psql prompt, run:

Copy

CREATE DATABASE my_database;

7. Create a New User

Copy

CREATE USER my_user WITH PASSWORD 'my_password';

8. Grant Privileges

Copy

GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

9. Exit psql

Type:

Copy

\q

10. Allow Remote Connections (Optional)

To allow remote connections to PostgreSQL:

1. Edit the PostgreSQL configuration file:

   Copy

   sudo nano /etc/postgresql/<version>/main/postgresql.conf

   Locate listen_addresses and change it to:

   Copy

   listen_addresses = '*'

2. Edit the pg_hba.conf file:

   Copy

   sudo nano /etc/postgresql/<version>/main/pg_hba.conf

   Add a line like this for IPv4 connections:

   Copy

   host    all             all             0.0.0.0/0               md5

3. Restart PostgreSQL:

   Copy

   sudo systemctl restart postgresql

11. Test the Setup

Use the following command to log in as the new user:

Copy

psql -U my_user -d my_database -h 127.0.0.1 -W

Enter the password when prompted.

12. Install Additional Tools (Optional)

Install pgAdmin or any other PostgreSQL GUI tool for easier management:

Copy

sudo apt install pgadmin4

Notes

• Replace <version> with the installed PostgreSQL version, e.g., 14 or 15.

• Use strong passwords for database users in production environments.

---

2

pgadmin4

The error indicates that pgadmin4 is not available in the default Ubuntu repositories. To install pgAdmin4, you need to add the official pgAdmin repository to your system.

Steps to Install pgAdmin4 on Ubuntu

1. Update System Packages

   Copy

   sudo apt update
   sudo apt upgrade -y

2. Add the PostgreSQL APT Repository

   Copy

   curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/pgadmin-archive-keyring.gpg
   echo "deb [signed-by=/usr/share/keyrings/pgadmin-archive-keyring.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" | sudo tee /etc/apt/sources.list.d/pgadmin4.list

3. Update Repositories

   Copy

   sudo apt update

4. Install pgAdmin4 For the desktop version:

   Copy

   sudo apt install pgadmin4-desktop

   For the web version:

   Copy

   sudo apt install pgadmin4-web

5. Configure pgAdmin4 (for Web Version) If you installed the web version, configure it:

   Copy

   sudo /usr/pgadmin4/bin/setup-web.sh

   Follow the prompts to set up the admin user and configuration.

6. Access pgAdmin4

   • Desktop Version: Launch it from your system's application menu.

   • Web Version: Open your browser and go to http://<server-ip>/pgadmin4 (usually http://localhost/pgadmin4).

Troubleshooting

• If pgAdmin4 is still unavailable for your Ubuntu version, verify that your Ubuntu distribution is supported by pgAdmin. Check pgAdmin's official documentation for further instructions.

---

3

CRUD

Here’s a simple set of CRUD (Create, Read, Update, Delete) SQL commands for a tuser table in PostgreSQL:

1. Create the tuser Table

Copy

CREATE TABLE tuser (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

2. Insert Data into the tuser Table (Create)

Copy

INSERT INTO tuser (name, email, age)
VALUES 
('Alice', '[e-mail]', 25),
('Bob', '[e-mail]', 30);

---

3. Retrieve Data from the tuser Table (Read)

• Retrieve All Records:

  Copy

  SELECT * FROM tuser;

• Retrieve Specific Columns:

  Copy

  SELECT name, email FROM tuser;

• Retrieve Data with a Condition:

  Copy

  SELECT * FROM tuser WHERE age > 25;

---

4. Update Data in the tuser Table (Update)

• Update a Specific Record:

  Copy

  UPDATE tuser
  SET age = 28
  WHERE email = '[e-mail]';

• Update Multiple Fields:

  Copy

  UPDATE tuser
  SET name = 'Robert', age = 35
  WHERE email = '[e-mail]';

---

5. Delete Data from the tuser Table (Delete)

• Delete a Specific Record:

  Copy

  DELETE FROM tuser
  WHERE email = '[e-mail]';

• Delete All Records:

  Copy

  DELETE FROM tuser;

---

6. Drop the tuser Table

If you need to remove the table entirely:

Copy

DROP TABLE tuser;

These commands cover basic operations you might perform on the tuser table. Let me know if you need more advanced queries or examples!

---

4

---

5

---

6

---

7

---

8

---

9

---

10

---

11

---

12

---

13

---

14

---

15

---

16

---

17

---

18

---

19

---

20

---

21

---

22

---

23

---

24

---

25

---