Install and configure PostgreSQL on Azure
PostgreSQL is an advanced open-source database similar to Oracle and DB2. It includes enterprise-ready features such as full ACID compliance, reliable transactional processing, and multi-version concurrency control. It also supports standards such as ANSI SQL and SQL/MED (including foreign data wrappers for Oracle, MySQL, MongoDB, and many others). It is highly extensible with support for over 12 procedural languages, GIN and GiST indexes, spatial data support, and multiple NoSQL-like features for JSON or key-value-based applications.
In this article, you will learn how to install and configure PostgreSQL on an Azure virtual machine running Linux.
Install PostgreSQL
Note
You must already have an Azure virtual machine running Linux in order to complete this tutorial. To create and set up a Linux VM before proceeding, see the Azure Linux VM tutorial.
In this case, use port 1999 as the PostgreSQL port.
Connect to the Linux VM you created via PuTTY. If this is the first time you're using an Azure Linux VM, see How to Use SSH with Linux on Azure to learn how to use PuTTY to connect to a Linux VM.
Run the following command to switch to the root (admin):
sudo su -
Some distributions have dependencies that you must install before installing PostgreSQL. Check for your distro in this list and run the appropriate command:
Red Hat base Linux:
yum install readline-devel gcc make zlib-devel openssl openssl-devel libxml2-devel pam-devel pam libxslt-devel tcl-devel python-devel -y
Debian base Linux:
apt-get install readline-devel gcc make zlib-devel openssl openssl-devel libxml2-devel pam-devel pam libxslt-devel tcl-devel python-devel -y
SUSE Linux:
zypper install readline-devel gcc make zlib-devel openssl openssl-devel libxml2-devel pam-devel pam libxslt-devel tcl-devel python-devel -y
Download PostgreSQL into the root directory, and then unzip the package:
wget https://ftp.postgresql.org/pub/source/v9.3.5/postgresql-9.3.5.tar.bz2 -P /root/ tar jxvf postgresql-9.3.5.tar.bz2
The above is an example. You can find the more detailed download address in the Index of /pub/source/.
To start the build, run these commands:
cd postgresql-9.3.5 ./configure --prefix=/opt/postgresql-9.3.5
If you want to build everything that can be built, including the documentation (HTML and man pages) and additional modules (
contrib
), run the following command instead:gmake install-world
You should receive the following confirmation message:
PostgreSQL, contrib, and documentation successfully made. Ready to install.
Configure PostgreSQL
(Optional) Create a symbolic link to shorten the PostgreSQL reference to not include the version number:
ln -s /opt/postgresql-9.3.5 /opt/pgsql
Create a directory for the database:
mkdir -p /opt/pgsql_data
Create a non-root user and modify that user’s profile. Then, switch to this new user (called postgres in our example):
useradd postgres chown -R postgres.postgres /opt/pgsql_data su - postgres
Note
For security reasons, PostgreSQL uses a non-root user to initialize, start, or shut down the database.
Edit the bash_profile file by entering the commands below. These lines will be added to the end of the bash_profile file:
cat >> ~/.bash_profile <<EOF export PGPORT=1999 export PGDATA=/opt/pgsql_data export LANG=en_US.utf8 export PGHOME=/opt/pgsql export PATH=\$PATH:\$PGHOME/bin export MANPATH=\$MANPATH:\$PGHOME/share/man export DATA=`date +"%Y%m%d%H%M"` export PGUSER=postgres alias rm='rm -i' alias ll='ls -lh' EOF
Execute the bash_profile file:
source .bash_profile
Validate your installation by using the following command:
which psql
If your installation is successful, you will see the following response:
/opt/pgsql/bin/psql
You can also check the PostgreSQL version:
psql -V
Initialize the database:
initdb -D $PGDATA -E UTF8 --locale=C -U postgres -W
You should receive the following output:
Set up PostgreSQL
Run the following commands:
cd /root/postgresql-9.3.5/contrib/start-scripts
cp linux /etc/init.d/postgresql
Modify two variables in the /etc/init.d/postgresql file. The prefix is set to the installation path of PostgreSQL: /opt/pgsql. PGDATA is set to the data storage path of PostgreSQL: /opt/pgsql_data.
sed -i '32s#usr/local#opt#' /etc/init.d/postgresql
sed -i '35s#usr/local/pgsql/data#opt/pgsql_data#' /etc/init.d/postgresql
Change the file to make it executable:
chmod +x /etc/init.d/postgresql
Start PostgreSQL:
/etc/init.d/postgresql start
Check if the endpoint of PostgreSQL is on:
netstat -tunlp|grep 1999
You should see the following output:
Connect to the Postgres database
Switch to the postgres user once again:
su - postgres
Create a Postgres database:
createdb events
Connect to the events database that you just created:
psql -d events
Create and delete a Postgres table
Now that you have connected to the database, you can create tables in it.
For example, create a new example Postgres table by using the following command:
CREATE TABLE potluck (name VARCHAR(20), food VARCHAR(30), confirmed CHAR(1), signup_date DATE);
You have now set up a four-column table with the following column names and restrictions:
- The “name” column has been limited by the VARCHAR command to be under 20 characters long.
- The “food” column indicates the food item that each person will bring. VARCHAR limits this text to be under 30 characters.
- The “confirmed” column records whether the person has RSVP’d to the potluck. The acceptable values are "Y" and "N".
- The “date” column shows when they signed up for the event. Postgres requires that dates be written as yyyy-mm-dd.
You should see the following if your table has been successfully created:
You can also check the table structure by using the following command:
Add data to a table
First, insert information into a row:
INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('John', 'Casserole', 'Y', '2012-04-11');
You should see this output:
You can add a couple more people to the table as well. Here are some options, or you can create your own:
INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('Sandy', 'Key Lime Tarts', 'N', '2012-04-14');
INSERT INTO potluck (name, food, confirmed, signup_date) VALUES ('Tom', 'BBQ','Y', '2012-04-18');
INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('Tina', 'Salad', 'Y', '2012-04-18');
Show tables
Use the following command to show a table:
select * from potluck;
The output is:
Delete data in a table
Use the following command to delete data in a table:
delete from potluck where name=’John’;
This deletes all the information in the "John" row. The output is:
Update data in a table
Use the following command to update data in a table. For this one, Sandy has confirmed that they are attending, so we will change the RSVP from "N" to "Y":
UPDATE potluck set confirmed = 'Y' WHERE name = 'Sandy';
Get more information about PostgreSQL
Now that you have completed the installation of PostgreSQL in an Azure Linux VM, you can enjoy using it in Azure. To learn more about PostgreSQL, visit the PostgreSQL website.