get db up and deployed

[PESolut]

Get DB Up and Deployed on EC2 Instance

Description

Deploy our database to an Amazon EC2 instance to prepare for production use.

Tasks

1. [x] set up the database system (PostgreSQL)
2. [x] Configure EC2 instance
3. [x] Install database on EC2
4. [x] Configure database settings
5. [ ] #5
6. [ ] #6
7. [ ] #7

Detailed Steps

1. Configure EC2 instance:

   • Launch a new EC2 instance or use an existing one
   • Ensure proper security group settings (allow inbound traffic on database port)
   • Allocate and attach sufficient EBS volume for database storage

2. Install database on EC2:

   • SSH into the EC2 instance
   • Update the system and install required dependencies
   • Install the chosen database system

3. Configure database settings:

   • Set up database users and passwords
   • Configure database to listen on appropriate network interfaces
   • Optimize database performance settings for EC2 instance type

4. Configure monitoring and logging:

   • Set up CloudWatch or other monitoring tools
   • Configure database logs and log rotation

5. Test connection and functionality:

   • Verify application can connect to the database
   • Run integration tests to ensure all database operations work as expected
   • Perform load testing to ensure acceptable performance

Acceptance Criteria

• Database is successfully installed and running on EC2 instance

Related Issues

[#3 setup pipeline for backend

[PESolut]

EC2 server up and SSH'd into

[PESolut]

installing SQL

sudo dnf install -y postgresql16 postgresql16-server postgresql16-contrib

[PESolut]

password for postgres is written in notebook (JOURNAL)

[PESolut]

PostgreSQL 16 Service Configuration Issue and Resolution

Issue

The PostgreSQL 16 service was failing to start due to an incorrect path in the service file. The system was unable to find the PostgreSQL binary at the default location /usr/pgsql-16/bin/postgres.

Root Cause

The PostgreSQL binary was actually located at /usr/bin/postgres, which is different from the assumed default location.

Resolution

We updated the PostgreSQL service file to use the correct path for the PostgreSQL binary.

1. Located the correct PostgreSQL binary path: /usr/bin/postgres

2. Updated the service file at /etc/systemd/system/postgresql-16.service with the correct path:

   [Unit]
   Description=PostgreSQL 16 database server
   Documentation=man:postgres(1)
   After=network.target
   
   [Service]
   Type=notify
   User=postgres
   ExecStart=/usr/bin/postgres -D /var/lib/pgsql/16/leaf-me
   ExecReload=/bin/kill -HUP $MAINPID
   KillMode=mixed
   KillSignal=SIGINT
   TimeoutSec=0
   
   [Install]
   WantedBy=multi-user.target

3. Reloaded the systemd manager:

   sudo systemctl daemon-reload

4. Started the PostgreSQL service:

   sudo systemctl start postgresql-16

5. Verified the service status:

   sudo systemctl status postgresql-16

Key Takeaway

Always verify the actual installation paths of services when configuring systemd service files, as they may differ from default assumptions, especially in different Linux distributions or custom installations.

[PESolut]

Configure PostgreSQL on EC2 for LeafMe Project

1. Connect to your EC2 instance via SSH.

2. Create the database:

   sudo -u postgres psql -c "CREATE DATABASE leafme_db;"

3. Create a new PostgreSQL user for the application:

   sudo -u postgres psql -c "CREATE USER leafme_user WITH PASSWORD 'your_secure_password';"

4. Grant privileges to the new user:

   sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE leafme_db TO leafme_user;"

5. Copy the schema file to the EC2 instance:

   scp -i your-key.pem backend/db/schema.sql ec2-user@your-ec2-instance:/tmp/

6. Apply the schema:

   sudo -u postgres psql leafme_db < /tmp/schema.sql

7. Copy the seed data files to the EC2 instance:

   scp -i your-key.pem backend/db/seed.sql backend/db/moreItems.sql ec2-user@your-ec2-instance:/tmp/

8. Apply the seed data:

   sudo -u postgres psql leafme_db < /tmp/seed.sql
   sudo -u postgres psql leafme_db < /tmp/moreItems.sql

9. Update the .env file on your EC2 instance with the new database connection details:

   PG_HOST=localhost
   PG_PORT=5432
   PG_DATABASE=leafme_db
   PG_USER=leafme_user
   PG_PASSWORD=your_secure_password

10. Update the PostgreSQL configuration to allow connections:

    sudo nano /var/lib/pgsql/16/leaf-me/postgresql.conf

    Ensure the following line is uncommented and set:

    listen_addresses = '*'

11. Update the client authentication configuration:

    sudo nano /var/lib/pgsql/16/leaf-me/pg_hba.conf

    Add the following line at the end of the file:

    host    all    all    0.0.0.0/0    md5

12. Restart PostgreSQL to apply changes:

    sudo systemctl restart postgresql-16

13. Update the dbConfig.js file on your EC2 instance to use the new connection details.

14. Test the connection from your application to ensure it can connect to the database.

Note: Remember to secure your EC2 instance and database appropriately, including setting up proper security groups and using strong passwords.

[PESolut]

Blocker

8. Update the .env file on your EC2 instance with the new database connection details:

need to get the project repo onto the instance... ill use git...

git clone  https://github.com/PESolut/leaf-me.git

awesome, no git on the ec2 instance... lets install that...

installing git

sudo yum update -y

sudo yum install git -y

git -v

git config --global user.name "Jahaad Petty"
git config --global user.email "admin@jp-tech.dev

[PESolut]

Testing Connnection

1. Configure EC2 Security Group: Ensure that your EC2 instance's security group allows inbound traffic on port 5432 (default PostgreSQL port) from your local IP address.

2. connect using:

   psql -h <EC2-Public-IP> -U leafme_user -d leafme_db

[PESolut]

blocker

need to install PSQL on local machine then run task two from Testing Connection

[PESolut]

PSQL installed on local machine

[PESolut]

failed to execute psql from localmachine to ec2 instance public ip: lack of inbound group rule allowing inbound connections to psql port

[PESolut]

an admin can connect to the db; select and see the tables; delete tables. ticket complete

[PESolut]

create issues of all incomplete tasks that has been deemed non urgent as of right now (though important)