Setup - Ubuntu

To set up PostgreSQL on Ubuntu, follow these steps:

1. Update the System

sudo apt update
sudo apt upgrade -y

2. Install PostgreSQL

sudo apt install postgresql postgresql-contrib -y

3. Start and Enable the PostgreSQL Service

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:

sudo -i -u postgres

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

psql

You should now see the psql prompt:

postgres=#

6. Create a New Database

At the psql prompt, run:

CREATE DATABASE my_database;

7. Create a New User

CREATE USER my_user WITH PASSWORD 'my_password';

8. Grant Privileges

GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

9. Exit `psql`

Type:

\q

10. Allow Remote Connections (Optional)

To allow remote connections to PostgreSQL:

Edit the PostgreSQL configuration file:
```
sudo nano /etc/postgresql/<version>/main/postgresql.conf
```
Locate listen_addresses and change it to:
```
listen_addresses = '*'
```

Edit the pg_hba.conf file:

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

Add a line like this for IPv4 connections:

host    all             all             0.0.0.0/0               md5

Restart PostgreSQL:
```
sudo systemctl restart postgresql
```

11. Test the Setup

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

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:

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.

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

Update System Packages
```
sudo apt update
sudo apt upgrade -y
```

Add the PostgreSQL APT Repository

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

Update Repositories
```
sudo apt update
```
Install pgAdmin4 For the desktop version:
```
sudo apt install pgadmin4-desktop
```
For the web version:
```
sudo apt install pgadmin4-web
```
Configure pgAdmin4 (for Web Version) If you installed the web version, configure it:
```
sudo /usr/pgadmin4/bin/setup-web.sh
```
Follow the prompts to set up the admin user and configuration.
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.

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

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)

INSERT INTO tuser (name, email, age)
VALUES 
('Alice', 'alice@example.com', 25),
('Bob', 'bob@example.com', 30);

3. Retrieve Data from the `tuser` Table (Read)

Retrieve All Records:
```
SELECT * FROM tuser;
```
Retrieve Specific Columns:
```
SELECT name, email FROM tuser;
```
Retrieve Data with a Condition:
```
SELECT * FROM tuser WHERE age > 25;
```

4. Update Data in the `tuser` Table (Update)

Update a Specific Record:

UPDATE tuser
SET age = 28
WHERE email = 'alice@example.com';

Update Multiple Fields:

UPDATE tuser
SET name = 'Robert', age = 35
WHERE email = 'bob@example.com';

5. Delete Data from the `tuser` Table (Delete)

Delete a Specific Record:

DELETE FROM tuser
WHERE email = 'alice@example.com';

Delete All Records:
```
DELETE FROM tuser;
```

6. Drop the `tuser` Table

If you need to remove the table entirely:

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!

PreviousSetup - Mac M1 NextSimple CRUD

Last updated 1 year ago