How To Install And Configure PostgreSQL
Hello Everyone
Welcome to CloudAffaire and this is Debjeet.
In this blog post, we will discuss how to install and configure PostgreSQL in different operating systems on Cloud platform like AWS, GCP or Azure.
What Is PostgreSQL:
PostgreSQL also is known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. After a review in 2007, the development team decided to keep the name PostgreSQL and the alias Postgres.
PostgreSQL features transactions with Atomicity, Consistency, Isolation, Durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. It is the default database for macOS Server and is also available for Linux, FreeBSD, OpenBSD, and Windows. Most of the major cloud platform also provides PAAS version PostgreSQL as their database offering.
How To Install PostgreSQL On AWS EC2 Instance With RHEL or CentOS or Oracle Linux Version 7 AMI:
Step 1: Create an EC2 instance with any RHEL based AMI.
Step 2: Connect to your EC2 instance and update the OS packages.
|
1 2 3 4 5 6 7 8 9 10 |
############################################# ## How To Install And Configure PostgreSQL ## ############################################# ##----------------------------------------- ## RHEL | CentOS | Oracle Linux: Version 7 ##----------------------------------------- ## Update your OS packages sudo yum update -y |
Step 3: Install PostgreSQL On AWS EC2 Instance.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
## Install PostgreSQL repository sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm ## Install PostgreSQL Version 10 sudo yum install -y postgresql10-server ## Initialize the PostgreSQL database sudo /usr/pgsql-10/bin/postgresql-10-setup initdb ## Enable automatic start of PostgreSQL service during start up sudo systemctl enable postgresql-10 sudo systemctl start postgresql-10 |
How To Install PostgreSQL On Google Compute Engine VM Instance With Ubuntu 18 Image:
Step 1: Create a Google Compute Engine VM instance with Ubuntu 18 Image.
Step 2: Connect to your Compute Engine VM instance.
Step 3: Install PostgreSQL On Google Compute Engine VM Instance.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
##-------------------- ## Ubuntu : Version 18 ##-------------------- ## Create PostgreSQL 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' ## Import the repository signing key wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - ## Update your OS packages sudo apt-get update ## Install PostgreSQL Version 10 sudo apt-get -y install postgresql-10 ## Enable automatic start of PostgreSQL service during start up sudo systemctl enable postgresql sudo systemctl start postgresql |
How To Install PostgreSQL On Azure VM Instance With Windows Server 2012 Image:
Step 1: Create An Azure VM Instance With Windows Server 2012 Image.
Step 2: Log in to your Azure VM Instance And Download PostgreSQL Version 10 using the below link.
https://www.enterprisedb.com/postgresql-tutorial-resources-training?cid=62
Step 3: Double click on the downloaded installer package and click “Run”.
Step 4: Follow the installation wizard and complete the installation. You will be prompted for a superuser password, provide the password.
How To Install PostgreSQL On Alibaba Cloud ECS Instance With Debian Image:
Step 1: Create an Alibaba Cloud EC2 instance with Debian 10 Image.
Step 2: Connect to your ECS instance.
Step 3: Install PostgreSQL On Alibaba Cloud ECS Instance.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
##------------ ## Debian : 10 ##------------ ## Create PostgreSQL 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' ## Import the repository signing key wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - ## Update your OS packages sudo apt-get update ## Install PostgreSQL Version 10 sudo apt-get -y install postgresql-10 ## Enable automatic start of PostgreSQL service during start up sudo systemctl enable postgresql sudo systemctl start postgresql |
How To Connect To A PostgreSQL Server Using psql:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
## --------------------------------- ## Connect To Your PostgreSQL Server ## --------------------------------- ## For Linux and MAC OS, type below command sudo -u postgres psql ## For Windows OS, open a command prompt and execute the below commands cd C:\Program Files\PostgreSQL\10\scripts\ runpsql.bat ## Provide a password when prompted which you set up during installation. ## Rest you can leave the default ## Server [localhost]: ## Database [postgres]: ## Port [5432]: ## Username [postgres]: ## Password for user postgres: |
How To Configure PostgreSQL Server For 1st Time Usage:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
## Create a new PostgreSQL database create database mypsqldb; ## Create a new PostgreSQL user with passowrd create user mypsqluser with encrypted password 'Pa$$w0rd'; ## Grant full access to user mypsqluser on database mypsqldb grant all privileges on database mypsqldb to mypsqluser; ## Set a password for super user postgres ALTER USER postgres with encrypted password 'Pa$$w0rd'; ## Get PostgreSQL config file location SHOW config_file; ## exit psql \q ## Edit the pg_hba.conf file and change from peer to md5 ## Else you will get an error like Peer authentication failed for user ## local all postgres trust ## local all postgres md5 ## restart PostgreSQL service |
Create A Table And Insert Some Value In Your PostgreSQL Database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
## Try to connect to psql using a password psql -d mypsqldb -u mypsqluser ## Switch to mypsqldb database \c mypsqldb ## Create a table CREATE TABLE mytable ( id serial PRIMARY KEY, name VARCHAR NOT NULL, email VARCHAR NOT NULL ); ## Insert some values into your table INSERT INTO mytable (name, email) VALUES ('debjeet', 'debjeettoni@gmail.com'); INSERT INTO mytable (name, email) VALUES ('cloudaffaire', 'cloudaffaire@gmail.com'); ## Select some values from the table Select * From mytable; |
PostgreSQL psql command:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
## ------------- ## PSQL Commands ## ------------- ## switch database \c database_name; ## list all databases \l ## list all schema \dn ## list all stored procedures and functions \df ## list all views: \dv ## lists all tables in a current database \dt \dt+ ## get detailed information on a table. \d+ table_name ## show a stored procedure or function code \df+ function_name ## show query output in the pretty-format \x ## list all users \du ## outputs information about the current database connection \conninfo ## exit psql \q |
Hope you have enjoyed this blog post. Please refer below PostgreSQL documentation for more details.
https://www.postgresql.org/download/
https://www.postgresql.org/docs/10/tutorial-install.html
https://www.postgresql.org/docs/13/app-psql.html