How to Setup/Install PostgreSQL Server on Azure/AWS/GCP

To setup and install PostgreSQL server on Ubuntu or CentOS into any of the cloud platforms (Azure,AWS,GCP), the recommended way is to deploy using the PostgreSQL image from the marketplace. It also come pre installed with pgAdmin web tool.

Getting Started

 

Once your PostgreSQL server has been deployed, the following links explain how to connect to a Linux VM:

 

 

Once connected and logged in, you’re ready to start configuring PostgreSQL.

Add Postgres User to SUDO Group

 

First task is to add the postgres user to the local sudo group so it has root access:

 

For Ubuntu run:

sudo usermod -aG sudo postgres

 

For CentOS run:

sudo usermod -aG wheel postgres

Set PostgreSQL user password

 

Next step , is to set a password for the postgres user with the following command:

 

sudo passwd postgres

set-new-password

 

Next, run the following command to set the new password to the postgre sql account:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'password';"

Access PostgreSQL shell

 

PostgreSQL has been installed on your system. Now, you will log in to PostgreSQL as a user to access the databases and working shell using the following command:

 

sudo su -l postgres

 

Now, enter the following command to into the PostgreSQL shell:

 

psql

Create a database and user roles

 

You can create new databases and users using interactive Postgresql shell as follows.

 

As an example i will create a database called mytestdb with a user called mytestuser

 

I run the following commands to create a new database:

CREATE DATABASE mytestdb;

CREATE USER mytestuser WITH ENCRYPTED PASSWORD 'MyStr0ngP@SS';

GRANT ALL PRIVILEGES ON DATABASE mytestdb to mytestuser;
Create-Database

Then List created databases:

 

\l

list-databases

Connect to database:

\c mytestdb
Connect-to-database

Enable Remote Connections

 

Installation of PostgreSQL on Ubuntu only accepts connections from localhost. In ideal production environments, you’ll have a central database server and remote clients connecting to it – But of course within a private network (LAN).  This also needs to be enabled in order to use the pgAdmin tool which will allow you to manage your databases from a web interface.

 

To enable remote connections, edit PostgreSQL configuration file:

On Ubuntu

sudo nano /etc/postgresql/12/main/postgresql.conf 

On CentOS

sudo nano /var/lib/pgsql/12/data/postgresql.conf

Uncomment the line that says #listen_addresses and add an asterisk ‘*’ or you can set to listen on a specified private IP address

 

listen-address

Listen

Also set PostgreSQL to accept remote connections from allowed hosts.

 

Add the following line to accept all hosts

 

# Accept from anywhere
host all all 0.0.0.0/0 md5

 

Or add the following line to only allow certain subnet

 

# Accept from trusted subnet
host all all 10.10.10.0/24 md5

On Ubuntu

sudo nano /etc/postgresql/12/main/pg_hba.conf

On CentOS

sudo nano /var/lib/pgsql/12/data/pg_hba.conf

After the changes, restart postgresql service

On Ubuntu

sudo systemctl restart postgresql

On CentOS

sudo systemctl restart postgresql-12

Setup pgAdmin Web Tool

 

If you would like to use the pgAdmin web tool, run the following command to enable and set a login user and password:

sudo /usr/pgadmin4/bin/setup-web.sh
Install-pgAdmin

Then login via the servers IP address with /pgadmin4

 

In my example:

 

http://51.143.33.168/pgadmin4

 

Login with the email address and password you set with the intial pgadmin setup.

 

pgAdmin-Login

PostgreSQL Firewall Ports

 

PostgreSQL uses the following ports:

 

  • TCP 5432 – Default listening port for PostgreSQL Database
  • TCP 80 – Used by pgAdmin Web Admin
  • TCP 443 – Used by pgAdmin Web Admin

 

To setup AWS firewall rules refer to – AWS Security Groups

To setup Azure firewall rules refer to – Azure Network Security Groups

To setup Google GCP firewall rules refer to – Creating GCP Firewalls

Support / Documentation

 

For further documentation on using PostreSQL please refer to the official documentation on:

 

https://www.postgresql.org/docs/

 

For PostgreSQL support, refer to their support section:

 

https://www.postgresql.org/support/

 

For pgAdmin documentation refer to the following:

 

https://www.pgadmin.org/docs/

 

Disclaimer: PostgreSQL is a registered trademark of PostgreSQL Global Development Group, Inc. and is licensed under PostgreSQL License. No warrantee of any kind, express or implied, is included with this software. Use at your risk, responsibility for damages (if any) to anyone resulting from the use of this software rest entirely with the user. The author is not responsible for any damage that its use could cause.
Andrew Fitzgerald

Cloud Solution Architect. Helping customers transform their business to the cloud. 20 years experience working in complex infrastructure environments and a Microsoft Certified Solutions Expert on everything Cloud

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x