Learn how to install pgadmin 4 postgresql administration tool on debian 10.8 in the cloud on amazon aws lightsail
If you are familiar with Microsoft SQL Server then you would know that database management tasks are mainly done using MICROSOFT ENTERPRISE MANAGER (which is a graphic based application). However if you work in a linux only environment or if you have to use a open source database engine such as POSTGRESQL but would like to have a graphic based database management application such as MICROSOFT ENTERPRISE MANAGER, then pgADMIN 4 is a good solution. pgADMIN 4 is a free and open source web based application that can be used to manage POSTGRESQL databases. It is a feature rich web application that comes with features such as an ERD Tool for designing and documenting schemas, a powerful query tool with colour syntax highlighting, tools for creating, designing, monitoring, backing up, restoring, vacuuming, analyzing databases and more. pgADMIN 4 is built with support for all the major operating systems i.e Windows, MacOS and Linux. In this tutorial you will learn how to install POSTRESQL and pgADMIN 4 on a DEBIAN 10.8 instance – deployed in the cloud on on Amazon (AWS) Lightsail.
REQUIREMENTS
The following is a list of items that may be needed to complete this tutorial successfully. Please ensure to have these items available before taking implementation action on this tutorial to ensure success:
1) An AWS free tier account
2) An IAM user account with access rights to create instances on AWS lightsail
3) A static public IP address
4) A desktop or laptop with a dual core processor, 4GB of RAM, 50GB o free disk space
5) A web browser (Google chorome, firefox, safari, internet explorer, internet edge
OVERVIEW
1) pgADMIN 4 System Requirements
2) Login to your AWS account and open the AWS Lightsail web application
3) Create a DEBIAN 10.8 instance, set a static IP address and install POSTGRESQL & pgADMIN 4
4) Open a new browser tab and login to the pgADMIN 4 dashboard
5) Connect to POSTGRESQL and learn how to create a user and a database.
pgADMIN 4 System Requirements
1) The table on the right shows a list of minimum system requirements needed to successfully deploy pgADMIN 4. Please read through this table and verify that the system that you intend to use meets these requirements.
Step 1: Login To Your AWS Account And Open The AWS Lightsail Web Application
2) Click HERE to go to the Amazon Web Services console login page. Click on the IAM USER option, enter your 12 digit account ID and click on NEXT.
If you do not have an AWS account, click on the CREATE A NEW AWS ACCOUNT button to sign up for a FREE TIER account.
3) Proceed by entering your IAM user name and password and click on SIGN IN
4) Once you have signed in successfully, click on SERVICES and click on LIGHTSAIL to open the AWS LIGHTSAIL web application.
Step 2: Create A DEBIAN 10.8 Instance, Set A Static IP Address And Install POSTGRESQL & pgADMIN 4
5) The next step is to create a DEBIAN 10.8 instance, set a static IP address on the instance and to install POSTGRESQL and pgADMIN 4.
On the AWS LIGHTSAIL dashboard, click on the CREATE INSTANCE button.
6) On the CREATE AN INSTANCE, you can change the instance location by click on the CHANGE AWS REGION AND AVAILABILITY ZONE link.
On the SELECT A PLATFORM section choose the LINUX / UNIX option then on the SELECT A BLUEPRINT section, click on the OS ONLY button and select the DEBIAN 10.8 blue print
7) On the CHOOSE INSTANCE section, click on the PRICE PER MONTH tab and select any of the plans that give you FIRST 3 MONTHS FREE!.
Specify a name for the instance that you are creating on the IDENTIFY YOUR INSTANCE text box and click on CREATE INSTANCE
8) To set a static PUBLIC IP ADDRESS, click on the menu button with three dots and click on MANAGE.
9) Click on the NETWORKING tab and click on CREATE A STATIC IP ADDRESS.
10) The next step is to connect to the instance you have just created using the AWS LIGHTSAIL browser based SSH console and install POSTGRESQL & pgADMIN 4.
Click on the HOME button and the top of the page, click on the menu button with 3 dots on the PGAMIN DEBIAN INSTANCE and click on CONNECT.
11) On the SSH window, run the following commands to update and upgrade debian system packages
sudo su
apt-get update && apt-get upgrade
Once the installation process is complete, go to the AWS LIGHTSAIL DASHBOARD, click on the menu button with 3 dots on the PGADMIN INSTANCE and click on REBOOT
12) Reopen the browser based SSH client and run the following command to install the POSTGRESQL database engine:
apt-get install postgresql
13) Once the installation is complete, run the following commands to check the status of the POSTGRESQL system service:
systemctl is-enabled postgresql
systemctl status postgresql
14) The next step is to set a password for the default POSTGRESQL user. Run the following commands to connect to the POSTGRESQL SHELL and to set a password for the POSTGRES user:
sudo -u postgres psql
ALTER USER postgres WITH PASSWORD ‘simplestrongpassword’;
Then type ‘\q’ or press the ‘CTRL + D’ to exit from the PostgreSQL shell.
15) pgADMIN provides software repositories for several linux based systems that you can use to deploy pgADMIN on your server. Add the repository for DEBIAN 10.8 by running the following commands:
apt-get install gnupg gnupg2
echo deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main\
| sudo tee /etc/apt/sources.list.d/pgadmin4.list
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
sudo apt-get update
16) Next, run the following command to install the pgADMIN 4 packet using the apt package manager:
apt-get install pgadmin4-web
17) Once the pgADMIN4 package installation process is complete, run the following command to configure the pgAdmin4 user account and the apache virtual host configuration:
/usr/pgadmin4/bin/setup-web.sh
Enter an email address and password to use for the initial pgADMIN user account as requested by the command prompts.
18) The setup-web.sh script will start configuring the Apache Web server for you. The configuration process involves enabling the wsgi module and configuring the pgAdmin 4 application to mount at /pgadmin4.
When ask “Do you wish to continue (y/n)?” hit Y on your keyboard and press ENTER
19) Next, the setup-web.sh script will need to restart the apache web server for the changes made to take effect. To continue with the restart press Y on your keyboard and press enter.
Step 3: Open A New Browser Tab And Login To The pgADMIN 4 Dashboard
20) To start using pgADMIN 4, open a new web browser tab, enter the STATIC IP ADDRESS that you set in step 2 following by /pgadmin4 and press ENTER.
When the login page loads, proceed by entering your email address and password that you specified in the previous step and click on LOGIN.
21) The pgADMIN dashboard will now be displayed, but you will notice that there is no server information displayed, That is because pgADMIN4 is not yet connected to the POSTGRESQL database engine.
To connect pgADMIN to POSTGRESQL click on the ADD NEW SERVER button
22) A CREATE -SERVER wizard will be displayed and prompt you to enter thet details of the POSTGRESQL server that you would like to connect to. Add any name on the NAME text box and select SERVER on the SERVER GROUP drop down menu.
Click on the CONNECTION tab to proceed.
23) Set the HOST to 127.0.0.1, username to postgres and enter the password for the postgres user on the PASSWORD field and click on SAVE
24) If you entering the correct details , pgADMIN 4 will now be connected to the local POSTGRESQL database engine and you should see server statistics such as TRANSACTIONS PER SECOND, SERVER SESSIONS, etc
To create a new database user, expand the POSTGRESQL-SERVER , right click on LOGIN / GROUP ROLES and click on CREATE > LOGIN / GROUP ROLE.
25) Enter the name of the user on the NAME text box and click on the DEFINITION tab
26) Set a password for the new user on the PASSWORD filed and click on the PRIVILEGES tab
27) Set the CAN LOGIN parameter to YES, the SUPERUSER parameter to NO and set the following parameters to YES: CREATE ROLES, CREATE DATABASES and INHERIT RIGHTS FROM THE PARENT ROLES.
Click on SAVE to complete the user creation process
28) To create a POSTGRESQL database using pgADMIN 4, right click on DATABASES and click on CREATE > DATABASE
29) Next, set a name for the new database on the DATABASE text box, select the database owner on the OWNER drop down menu and click on SAVE.
30) You should now see the newly created database on the DATABASES list.
Conclusion
You have successfully installed pgAdmin 4 PostgreSQL Administration Tool on Debian 10.8 – In The Cloud On Amazon (AWS) Lightsail and can now host as many POSTGRESQL databases in a secure cloud environment!
