Creating a Database Restoration

Restoring a Database on a Test EC2 Instance

PostgreSQL 16 is a major release of the open-source relational database system, featuring new functionalities and improvements. These enhancements include better monitoring, improved performance, logical replication, new server configurations, and security updates.

  1. Update package index: sudo apt update
  2. Install necessary packages: sudo apt install gnupg2 wget vim
  3. Add PostgreSQL 16 repository: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
  4. Import the repository signing key: curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
  5. Update package list again: sudo apt update
  6. Install PostgreSQL 16: sudo apt install postgresql-16 postgresql-contrib-16
  7. Start and enable the PostgreSQL service: sudo systemctl start postgresql sudo systemctl enable postgresql
  8. Confirm installation: psql –version
  9. The output should be like psql (PostgreSQL) 16.0 (Ubuntu 16.0-1.pgdg22.04+1).
  10. Configure PostgreSQL 16
  11. Allow remote connections by editing postgresql.conf: sudo nano /etc/postgresql/16/main/postgresql.conf
  12. Change listen_addresses to *.
  13. Configure md5 password authentication in pg_hba.conf for remote connections: sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/16/main/pg_hba.conf sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/16/main/pg_hba.conf echo "host all all 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/16/main/pg_hba.conf
  14. Restart PostgreSQL to apply changes: sudo systemctl restart postgresql
  15. Allow PostgreSQL port through the firewall: sudo ufw allow 5432/tcp
  16. Connect to PostgreSQL: sudo -u postgres psql
  17. Set a password for the postgres user: ALTER USER postgres PASSWORD 'VeryStronGPassWord@1414';
  18. Create and Populate a New Database in PostgreSQL
  19. Once you have successfully connected to the PostgreSQL database server using the psql command-line tool, you are ready to start creating a database and a table. Below, you will find detailed instructions on how to create a new database, add a table to it, and insert data into the table.
  20. Create Database: To create a new database named restore, execute: CREATE DATABASE restore;
  21. Connect to Database: To switch to the newly created restore database, use: \c restore

Create VPC

Create VPC