20 May

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

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;

Then List created databases:

Connect to database:

\c mytestdb

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

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

Then login via the servers IP address with /pgadmin4

In my example:

http://51.143.33.168/pgadmin4

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.

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

Setup PostgreSQL on Azure

Install PostgreSQL on AWS

Setup PostgreSQL on GCP

Getting Started

Add Postgres User to SUDO Group

Set PostgreSQL user password

Access PostgreSQL shell

Create a database and user roles

Enable Remote Connections

Setup pgAdmin Web Tool

PostgreSQL Firewall Ports

Support / Documentation

Related Posts:

Andrew Fitzgerald

Recent Posts

Pages

Follow Us