How to Setup/Install PostgreSQL Server on Azure/AWS/GCP
To setup and install PostgreSQL server on Ubuntu, Debian 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.
Run PostgreSQL Server on the Cloud
Setup PostgreSQL on Azure
Install PostgreSQL on AWS
Setup PostgreSQL on GCP
Table of Contents
PostgreSQL Server Features
- Compatible with Data Integrity
- Support multiple features of SQL
- Compatible with multiple data types
- Concurrency, Performance
- Internationalisation, Text Search
- Highly extensible
- Secure
- Highly Reliable
- pgAdmin – Web based management tool.
- Supports geospatial databases for geographic information systems (GIS).
- Robust database in the LAPP stack
- Many languages supported: Python, Java, C#, C/C+, Ruby, JavaScript (Node.js), Perl, Go, Tcl
Getting Started with PostgreSQL Server
Once your PostgreSQL server has been deployed, the following links explain how to connect to a Linux VM:
- How to connect to a Linux VM on Azure
- How to connect to a Linux VM on AWS
- How to connect to a Linux VM on GCP
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/Debian 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:
\l
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/Debian
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/Debian
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/Debian
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
Login with the email address and password you set with the intial pgadmin setup.
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:
