Prerequisites:
Before we begin, make sure you have the following:
1. An Amazon EC2 instance running Ubuntu.
2. Linux commands.
3.PgAdmin on your Local Machine.
Step 1: Update Your System(Ubuntu)
First, let's ensure that your Ubuntu system is up to date. SSH into your EC2 instance and run the following commands:
sudo apt update
sudo apt upgrade
Step 2: Install PostgreSQL
Next, we'll install PostgreSQL using the package manager. Run the following command:
sudo apt install postgresql
This command will install PostgreSQL along with its dependencies.
Step 3: Verify Installation
Once the installation is complete, you can verify that PostgreSQL is running by checking its status:
sudo service postgresql status
If PostgreSQL is running, you should see an output indicating that it's active and running.
Step 4: Access PostgreSQL
By default, PostgreSQL creates a user named "postgres" with administrative privileges. You can switch to this user to access PostgreSQL:
sudo -i -u postgres
Now you're logged in as the PostgreSQL user. You can access the PostgreSQL prompt by typing:
psql
This will open the PostgreSQL command-line interface.
Step 5: Create a Database and User
Now that you're in the PostgreSQL prompt, you can create a new database and user for your application. Here's an example of how to create a database named "mydatabase" and a user named "myuser":
CREATE DATABASE mydatabase;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;
Replace "mydatabase," "myuser," and "mypassword" with your desired database name, username, and password, respectively.
Step 6: Configuring Remote Access
By default only connections from the local system are allowed, to enable all other computers to connect to your PostgreSQL server, edit the file /etc/postgresql/*/main/postgresql.conf. Locate the line: #listen_addresses = ‘localhost’ and change it to *:
listen_addresses = '*'
Step 7: Configuring Remote Access
If you need to access your PostgreSQL database remotely, you'll need to configure it to accept remote connections. Open the PostgreSQL configuration file:
sudo nano /etc/postgresql/<version>/main/pg_hba.conf
Replace `<version>` with your PostgreSQL version number. Look for the section that controls remote access and modify it to allow connections from your IP address or network. Save the file and restart PostgreSQL for the changes to take effect:
Change the IPV4 to:
host all all 0.0.0.0/0 scram-sha-256
Change IPV6 to:
host all all ::0/128 scram-sha-256
Step 7: Restart postgres and verify
sudo service postgresql restart
After restarting postgres,
sudo -i -u postgres
Psql
Check whether it’s working.
Let’s Move on to the Local Machine to Register our Server.
Register Server in Local PgAdmin:
Prerequisites:
Before we begin, make sure you have the following:
1.Make sure you have installed PgAdmin on your local machine.
Step 1: Launch PgAdmin
After Launching PgAdmin, you can see the below output:
Step 2: Right click on the Servers
Right Click on the Servers at the left of the screen, and click Register -> Server
Step 3: In General fill the Name.
I have named the server as EC2-Ubuntu.
Step 4: Click on Connection Tab and Fill the Following.
Host Name: Paste the IP address of your EC2 instance.
Port: Leave it as 5432, which is the default port for PostgreSQL.
Maintenance Database: Type the name of your database.
Username: Enter the username you created in PostgreSQL on the remote machine.
Password: Enter the password you assigned when creating the user.
Click on save. Your server will be added to the local pgadmin.
