PostgreSQL
Introduction
E2E’s DBaaS provides a selection of node types optimized to fit different relational database use cases consisting of different database engines. Node Cluster configuration comprises varying combinations of CPU, memory, storage and gives you the flexibility to choose the appropriate mix of resources for your database.
E2E’s Relational DBaaS Service makes it easier for us to set up and operate Relational Databases in the cloud, providing us with Cost-efficient service and automating time-consuming administrator tasks such as Provisioning, Patching, and Setups.
What is PostgreSQL?
PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 35 years of active development on the core platform.
Why use PostgreSQL?
PostgreSQL comes with many features aimed to help developers build applications, administrators to protect data integrity and build fault-tolerant environments, and help you manage your data no matter how big or small the dataset. In addition to being free and open source , PostgreSQL is highly extensible. For example, you can define your own data types, build out custom functions, even write code from different programming languages without recompiling your database!
Navigate to Database Page
Once you have logged in to Myaccount Portal, You can navigate to the database page from the sidebar menu
Create Database
On the top right section of the manage database dashboard, You need to click on the “Create Database” Button which will prompt you to the cluster page where you will be selecting the configuration and entering the details of your database
Database Configuration and Setting
After creating the database, You need to select the required configuration and setting for your database which are mentioned below.
PostgreSQL Engine - PostgreSQL has various database engines available. You need to select the required database engine which you prefer, Below are the list of database engine which we currently support
PostgreSQL - 11.0
PostgreSQL - 12.0
PostgreSQL - 13.0
PostgreSQL - 14.0
PostgreSQL - 15.0
Cluster Configuration - Node Cluster configuration comprises varying combinations of CPU, memory, storage and gives you the flexibility to choose the appropriate mix of resources for your database.
DbaaS Name - You need to Provide the DbaaS name which you want for your cluster. This name will be visible on the Database page.
Database Setting - We need to update the database name, user and provide a secure password for our database. You will be using these credentials to log in to the database server.
With strong password authentication, your database controls and authenticates user accounts. If a DB engine has strong password management features, they can enhance security.
Once you have entered the required details, click on create a database. Your database might take a few minutes to Launch. The database node will be in a setting up state until it’s ready to use and when the status will change to running.
Link to download PostgreSQL:
Connecting to your database Cluster
After E2E’s database (DBS) provision of your nodes, you can use any standard PostgreSQL client application to connect to a database on the DB instance. In this example, you connect to a database on a Postgre DB instance using the PostgreSQL command-line tool.
To connect to your database node using PostgreSQL command line
Once your database has been provisioned and its running status, You can get the database connectivity information on the dashboard under the connection details.
Database Name
Public IPv4
Username
Port
Enter the following command at a command prompt on your local or client desktop to connect to a PostgreSQL database.
psql -h <host> -p 5432 -U <’username’> -d <’database name’>
After this enter the password which you have set for your database
Example:
psql -h 164.52.208.89 -p 5432 -U e2e -d postgre
After you enter the password for the user, you should see output similar to the following.
Managing Your Database Node
In this section, you can find instructions and details to Manage and Maintain your database.
To manage
Click on … (Action Button)
Security Setting
Allowed Host This setting controls the access of Allowed hosts that can be able to connect your Database node. If No trusted host is selected. Anyone can connect using credentials.
Database Details
In this section, You will be able to check User credentials and Software details of your database.
Stopping your Database
If you are using your DB Node for temporary testing or for any other daily development activity and want to test out your database by stopping it which will close all the connections in your database. You can perform this action by clicking on Action and selecting the stop button.
You can resume your database which was stopped earlier by clicking on the Start button in the Action menu. Resuming your database will retain the same IP and credentials for your database and there will not be any changes at configuration level.
Restart your Database
If you are testing out your DB nodes, restarting your database to reset connections or for any troubleshooting purpose, You can restart your database by clicking on the Restart button on the action menu.
Upgrade your database
The DBaaS upgrade feature enables customers to easily upgrade their DBaaS plan based on their specific usage requirements. For upgrading you database you have to click on Upgrade button under Action button.
After clicking on Upgrade the Upgrade plan will be show and you have to click on Upgrade button with the selected plan after that the confirmation popup will be open and you have to click on Upgrade button.
After this the Upgrading process will be start and the database status will be in upgrading status.
Note
Please ensure that your database is stopped when performing the upgrade action.
Delete your Database
To delete your database, click on delete option. Please note that once you have deleted your database you will not be able to recover your database.
Take Manual Snapshot of Database
E2E’s Database creates a snapshot of your database node, backing up the entire DB node and not just individual databases. To create a snapshot of your database, Click on Action and select Manual Snapshot option. A pop up menu will appear prompting you to enter the snapshot name. Enter the snapshot name and click on create snapshot.
The amount of time it takes to create a snapshot varies with the size your databases base snapshot,click on Action and select the Restore Snapshot option.The amount of time it takes to restore a snapshot varies with the size of your databases. As soon as the restore is complete your database will be in running state.
Read Replica of the Database
The read replica operates as a DB instance that allows only read-only connections; applications can connect to a read replica just as they would to any DB instance.
Monitoring
After you launch your database node, you can view the monitoring graphs for your node on the Monitoring tab. Each graph is based on one of the different metrics. This information is collected from your node and processes raw data into readable graphs. Monitoring is an important part of maintaining the reliability, availability, and performance of your database
Slow Log queries
The slow query log feature in MySQL allows you to log all queries that take longer than a given threshold of execution time. The queries in the slow query log are good candidates to start optimizing and eliminating the bottlenecks.
To configure slow log queries, Click on configure view and select the appropriate time duration to log the queries that will be displayed on the dashboard.
Alert Configuration
You can set up new alerts by defining trigger parameters as per your use case for your database. The alerting system works by sending automatic response notifications to your defined email list.
