Installing PostgreSQL on Azure VM

This assignment outlines the process of installing PostgreSQL on an Azure virtual machine. It begins by creating a free Azure account, provisioning a new virtual machine, and selecting a Linux distribution (Ubuntu Server). The user is then guided through ssh-ing into the VM, installing PostgreSQL using apt-get, configuring PostgreSQL by creating a database user and allowing IP addresses to access the instance via the PostgreSQL configuration file. Finally, the user is shown how to connect to the PostgreSQL instance via pgAdmin.

Table of Contents
1. Introduction................................................................................................................................4
1.1 Client Profile......................................................................................................................................4
1.2 Requirement of the Client.................................................................................................................4
2. Choice of Database Management Systems for Bentley Partners.............................................4
2.1 Current Situation...............................................................................................................................4
2.2 Solution one: MySQL........................................................................................................................5
Pros of MySQL.....................................................................................................................................................5
Cons of MySQL....................................................................................................................................................5
2.3 Solution two: PostgreSQL................................................................................................................6
Pros of PostgreSQL...............................................................................................................................................6
Cons of PostgreSQL..............................................................................................................................................6
2.4 Recommendation: PostgreSQL........................................................................................................6
3. Optimal Operating System for PostgreSQL: Linux..................................................................7
4. How to handle security and manage backups..........................................................................7
5. Migrating Access Databases to PostgreSQL.............................................................................8
6. How to set up and configure a Linux Virtual Machine to run PostgreSQL............................8
References:...................................................................................................................................14

1. Introduction
1.1 Client Profile
Bentley Partners Accounting and Taxation (herein referred as BP) is a tax accounting firm based
in Adelaide SA. Being founded by Daniel Bentley and Joseph Cross in 1994, BP has served their
clients now for 25 years.
Having started by two best friends as a two-person accounting firm, BP provided very focused,
personalized accountancy and taxation services to their clients, and after 25 years, BP still
continues their integrity by focusing on individual clients and their needs and thus remains a
small-scale business with only 18 employees. Therefore, their clientele too, consists of other very
small-scale businesses and individuals.
1.2 Requirement of the Client
BP has close to 25 years of data of their clients stored in their current database which is
Microsoft Access. Since their clientele is 78% individuals and rest very small-scale businesses, it
consists of lots of single entries. As their clientele grows, the amount of data to be stored also
increases.
BP has now decided it is time for a system upgrade. Hence clearly requires following to be
answered by Precise IT Solutions;
1. Best database management system for BP
2. How secure the solution is for BP’s database as its data are highly confidential
3. Best operating system to run the database upon
2. Choice of Database Management Systems for Bentley Partners
2.1 Current Situation
BP currently uses Microsoft Access’s embedded Jet DB database to store its data. Meanwhile it’s
highly user and beginner friendly, as in non-technical people being able to grab the ropes fast
and use it, it becomes very cumbersome to use once you have more than a single user using it at
the same time. Even considering in terms of username/password security of its databases MS
Access’s security is very limited. As the database grows up, a better solution is eventually
required, especially to a business.

2.2 Solution one: MySQL
MySQL is a very widely used open-source database management system. So popular that it
powers some of the world’s largest websites in the likes of Twitter and Facebook. It is a
relational database management system (RDBMS) which means that data is in tuples and
grouped into relations, which is similar to the existing database BP currently has in MS Access.
MySQL itself is designed for speed and reliability though it lacks full compliance to SQL
standards. It is not ACID compliant, so integrity of data is not ensured.
Pros of MySQL
 Easy to use and low cost: It is arguably the most popular DBMS so there is no shortage
of database administrators, third-party tools (i.e., phpMyAdmin, Azure Database for
MySQL) and numerous documentations which are available both online and in print. So,
getting started is straightforward and easy. Also, it is a very low-cost options in terms of
DBMS; though it is available under variety of propriety licenses it is free and open source
GNU General public license also most third-party tools for MySQL are free to use as
well.
 Security: It comes installed with a script to secure your database. It further allows you to
grant access privileges to individual users
 Speed: MySQL is designed for speed and it’s usually known as a very fast database
solution.
 Supports replication: MySQL supports a number of different types of sharing
information across two or more hosts. This helps to improve reliability, availability and
of course tolerance of faults. This is helpful for setting up a database backup solution
Cons of MySQL
 Not fully compliant with SQL standards; MySQL has certain limitations because it’s
not fully SQL compliant.
 Data integrity is not ensured: MySQL is a fast DBMS however at the expense of
compliancy and data integrity. Accounting means transactions and that means ACID
compliancy (talked more in detail in PostgreSQL) is essential. Therefore, this is a
disadvantage of MySQL.
 It is dual licensed. Meaning both free and proprietary versions are available. So some
features are not for free however, with free MySQL BP database can be managed without
hiccups.

2.3 Solution two: PostgreSQL
PostgreSQL is more advanced than MySQL and much more SQL compliant than MySQL.
However, it is not as speedy as MySQL. It is primarily a relational database management system
but with added features that are more often seen in object databases. PostgreSQL can efficiently
multitask at the same time, also known as ‘concurrency’. It is implemented with ‘Multiversion
Concurrency Control’ (MVCC), that ensures the atomicity, consistency, isolation, and durability
of its transactions, also known as ACID compliance. Because PostgreSQL is not as widely used
as MySQL there is still considerable number of third-party tools and documentation to simplify
working with PostgreSQL.
Pros of PostgreSQL
 SQL compliant: PostgreSQL closely adheres to SQL standards and supports 160 out of
the 179 features required for full core SQL:2011 compliance.
 ACID Compliant : As explained above it is fully ACID compliant is this is crucial for
the type of data to be managed at an Accounting firm. Because data integrity is highluy
critical for such firms
 Fully free to use: A fully open-source project, PostgreSQL’s source code is developed
by a large and devoted community. Users can do anything that they want with the code.
Cons of PostgreSQL
 Not as fast as MySQL
2.4 Recommendation: PostgreSQL
After careful analyzing of both the software I on behalf of Precise IT Solutions would like to
recommend PostgreSQL as the best solution for Bentley Partners. Simply considering the size of
the firm and the nature of the firm I felt that more than the speed, data integrity should be of
utmost importance to an Accounting firm. Given that BP is a smaller firm, there shouldn’t be any
problems with the speed of PostgreSQL even though it is not as speedy as MySQL. It is just as,
if not more secure than MySQL as well and it is fully free to use. Also as it is capable of
handling complex databases, it shouldn’t be a problem if Bentley Partners evolve into a very
large scale organization as well.

3. Optimal Operating System for PostgreSQL: Linux
PostgreSQL can be compiled on both Unix-like operating systems and Windows. But
performance on Windows is much less compared to Unix-like system, therefore I on behalf of
Precise IT Solutions would like to recommend Linux as the best OS for hosting PostgreSQL.
Most commonly used Linux distribution for hosting PostgreSQL is a Red Hat based system, like
CentOS or Scientific Linux. The community behind Red Hat and CentOS focus on stability and
performance and they make sure they are the most secure and most reliable build of Linux.
4. How to handle security and manage backups
To harden the security around the PostgreSQL database many steps can be taken and following
are few
Client Authentication Control: In PostgreSQL the file which control client authority can be
defined a specific connection type, a client IP address range, a database name, a username, and
the authentication method to be used for connections matching these parameters.
Server Configuration: There are some parameters that can be modified to enhance security.
User Management: users can be granted the minimum amount of access they need.
Maintain a strong password policy
Data Encryption: PostgreSQL has native support for using SSL(Secure Sockets Layer)
connections to encrypt client/server communications for increased security. SSL is the standard
security technology for establishing an encrypted link between a web server and a browser. This
encrypted link makes sure that all data passed between the web server and browsers remain
private and integral.
Logging: PostgreSQL provides a wide variety of parameters to control what, when, and where to
log.
Patching: There are critical security updates and patches released by the administrators
regularly.
Further to this, third party tools can be used to enhance the security as well.

As for backups PostgreSQL is pretty much rock solid as it is ACID Compliant and has cloud
support. However, for added peace of mind, third party tools such as ‘Clustercontrol’ can be used
as a backup solution for your database.
5. Migrating Access Databases to PostgreSQL
Third party apps can be used for this purpose. Below is the simplified process by using such tool.
Select the database
Analyze and amend any parameters if necessary.
Fillout SQL database name and create SQL and export SQL
FTP these files to a suitable empty directory on your PostgresSQL server.
Log into your Linux server and change to the directory where you have uploaded the files
Test your database at the server console.
Please use a third-party tool and follow exact steps to migrate your database, one such tool I can
recommend is ‘FullConvert’ by Spectralcore. Please use below link to get it.
https://www.spectralcore.com/fullconvert/
6. How to set up and configure a Linux Virtual Machine to run PostgreSQL
For testing purposes, you will need to run this on a virtual machine. Below I have outlined a
basic process for this.
This is how to install Azure command-line interface (CLI) to deploy a Linux virtual machine
(VM) in Azure
Create a free azure account before you begin. Once you get it,

In the bottom drawer bar click on new:
Next select a new Compute Resource -> Virtual Machine -> Quick Create. Give the VM a
unique name and be sure to pick a Linux distro.
In this case it is an Ubuntu Server. You will need to provide a password, region to host the VM
(select the location closest to you) and your Azure subscription. Click the Create button in the
bottom right and a few minutes you will have a VM.

To install PostgreSQL you will need to ssh (secure shell) into the VM you just set up. Make sure
that the VM says that it is running in the dashboard:
Now you can ssh into the machine. If you are on windows you are can use PuTTY.
When you are logging into the VM you will provide the username and password that you created
when provisioning the VM in the previous step.

Once you are connected to the machine, use apt-get to install PostgreSQL. At the terminal type:
sudo apt-get install postgresql
sudo apt-get install postgresql-contrib
Configuring PostgreSQL
First you need to create a PostgreSQL database user:
sudo -u postgres createuser --superuser <username> -P
Next you need to allow IP addresses to access the PostgreSQL instance via the PostgreSQL
configuration file. The path to the configuration file will change according to the version of
PostgreSQL (or stack you installed). Using Vi, you should specify the exact IP addresses you
want to allow access. You can find you IP address by google/binging ‘my ip address’.
#open vi
sudo vi /etc/postgresql/9.3/main/pg_hba.conf
#add the following line to the configuration file
host all all 192.1.0.190/32 md5
Next tell PostgreSQL which IP’s to listen to. In this example I am enabling PostgreSQL to listen
to all IP addresses but it would be more secure to specify an exact IP address.
#open vi
sudo vi /etc/postgresql/9.3/main/postgresql.conf
#enable remote connections
listen_addresses = '*'
Restart the PostgreSQL server:
sudo service postgresql restart
Finally, inside the Azure Portal you need to open of the Firewall to all remote connection to the
PostgreSQL port:

Connecting via pgAdmin
You can connect to the PostgreSQL instance via a command prompt like so:
psql -h <namespace>.cloudapp.net -U <user>

Voila! All done.
Word count without transmittal letter, table of content, process of setting up VM and references :
1503

References:
Technology Evaluation Centre, Microsoft Access vs. MySQL Comparison<
https://www3.technologyevaluation.com/sd/comparisons/microsoft-access-65015/mysql-66259>
Oracle Australia, Oracle MySQL World’s Most Popular Open Source Database,
<https://www.oracle.com/au/mysql/>
PostgreSQL, PostgreSQL: The World's Most Advanced Open Source Relational Database <
https://www.postgresql.org/>
Ostezer & Drake M 2019, SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational
Database Management Systems,Community
<https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-
comparison-of-relational-database-management-systems>
Stackshare, Microsoft Access vs MySQL< https://stackshare.io/stackups/microsoft-access-vs-
mysql>
2nd Quadrant PostgreSQL, PostgreSQL vs MySQL
<https://www.2ndquadrant.com/en/postgresql/postgresql-vs-mysql/>
DB Engines, System Properties Comparison Microsoft Access vs. MySQL vs.
PostgreSQL<https://dbengines.com/en/system/Microsoft+Access%3BMySQL%3BPostgreSQL>
Setting up PostgreSQL in Azure VM < https://www.jamessturtevant.com/posts/setting-up-
postgresql-in-azure-vm/>