How to Install PostgreSQL on Ubuntu: A Beginner's Guide

In this in-depth article, we will learn how to install PostgreSQL on Ubuntu, Fedora and Arch Linux. We will also learn how PostgreSQL differs from MySQL and how to migrate from MySQL to a PostgreSQL database.

​Databases are crucial tools for any developer or development enterprise. If you are a software developer you already know that your application needs a database to store data. One thing to consider is choosing the right database for your application.

There are two types of databases, SQL and NoSQL databases. The first is the oldest. SQL databases are very famous and still used in big organizations. Most SQL solutions are paid but there are good free solutions out there with MySQL Community Edition and PostgreSQL at the top. In this article, we will tell you more about these two databases and how to migrate from MySQL to PostgreSQL.

MySQL Database

MySQL is an open-source relational database management system (RDBMS). Its name is a combination of My, the name of co-founder Michael Widenius’ daughter, and SQL, the abbreviation for Structured Query Language. A company called MySQL AB created MySQL originally and sold it to Oracle Corporation. There is a paid version of MySQL available.

MySQL AB was founded in 1995 and acquired by Sun Microsystems in 2008 and in 2010 was owned by Oracle Corporation. MySQL is one of the building blocks of LAMP and WAMP Technology Stack where the first letter represents the Operating System (L for Linux and W for Windows).

LAMP is an acronym for Linux, Apache, MySQL, Perl/PHP/Python that uses the MySQL database. High-profile, large-scale websites such as Google, Facebook, Twitter, Flickr, and Youtube also use it.

In 2004, it was claimed that more than 10 million downloads and 5 million installations had taken place.

Advantages of MySQL

• ​Easy to work with: MySQL can be installed very easily;
• Feature rich: MySQL supports a lot of the SQL functionality that is expected from an RDBMS;
• Secure: A lot of security features, some rather advanced, are built in MySQL.
• Scalable and powerful: MySQL can handle a lot of data and furthermore it can be used at scale if needed;
• Speedy: Works very efficiently and cut corners, thus providing speed gains.

Disadvantages of MySQL

• ​Known limitations: By design, MySQL does not intend to do everything and it comes with functional limitations that some state-of-the-art applications might require;
• Stagnated development: There are not big changes in the latest releases.

PostgreSQL

​PostgreSQL, often simply Postgres, is an object-relational database (with additional/optional uses of object features) – with an emphasis on extensibility and standards compliance.

The PostgreSQL Global Development Group, made up of individuals, joint companies and free and open-source contributors, develops PostgreSQL. PostgreSQL started as a project called Ingres (Interactive Graphics Retrieval System.) with a database named Ingres database. A commercial company supports PostgreSQL, despite it being free and open-source software.

Ingres remained active from 1970 to 1985 with a research project at the University of California, Berkeley.

Ingress contributed to a number of commercial database applications like Sybase, Microsoft SQL Server, NonStop SQL and others.

Postgres (Post Ingres), a project started in the mid-1980s, evolved into PostgreSQL.

Advantages of PostgreSQL

• ​It is Open Source: It’s open-source and free, yet a very powerful relational database management system;
• Strong community: Supported 24/7 by a large community.
• Strong third-party support: Many great and open-source third-party tools are available for designing, managing, and using PostgreSQL, despite its advanced features.
• Extensible: It is possible to extend it programmatically with stored procedures like an advanced RDBMS should be.
• Objective: PostgreSQL is not just a relational database management system but an objective one – with support for nesting, and more.

Disadvantages of PostgreSQL

• Performance: Less performant in writing heavy data to database in comparison to MySQL;
• Popularity: Since is not as popular as MySQL there are lesser people who would give local or remote support if needed by an enterprise;
• Hosting: Due to above-mentioned factors, it is harder to come by hosts or service providers that offer managed PostgreSQL instances.

How to Install PostgreSQL on Ubuntu

Now that we know what PostgreSQL is and how it differs from MySQL, it’s time to learn how to install PostgreSQL on Ubuntu. After installing PostgreSQL, we will learn how to migrate MySQL database to PostgreSQL.

PostgreSQL package and required dependencies are available in almost all major Linux distributions. So we can easily install it using the distro’s package manger. However for this tutorial, I will show you how to install PostgreSQL on Ubuntu, Fedora and Arch Linux.

Install PostgreSQL on Ubuntu

Before installing PostgreSQL verify that the system is up-to-date by running the following command –

sudo apt update; sudo apt upgrade -y

Install PostgreSQL –

sudo dnf install postgresql

Install PosgreSQL on Fedora

Enable PostgreSQL repository in Fedora –

sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-34-x86_64/pgdg-fedora-repo-latest.noarch.rpm

Install PostgreSQL –

sudo dnf install postgresql-server postgresql-contrib

Initialize the database –

sudo postgresql-setup --initdb

Start postgreSQL service and make it automatically start on system boot –

sudo systemctl start postgresql
sudo systemctl enable postgresql

Verify the installation by checking its status –

sudo systemctl status postgresql

At last, we can connect to the database by logging in as postgres user and running the psql command –

sudo su - postgres

psql

Install PostgreSQL on Arch Linux

Install PostgreSQL –

sudo pacman -S postgresql

Initialize the database –

sudo su - postgres

initdb -D '/var/lib/postgres/data'

Start PostgreSQL and enable it to start on system startup –

sudo systemctl start postgresql; sudo systemctl enable postgresql

Verify the installation –

sudo systemctl status postgresql

PostgreSQL creates a default database. We can connect to using the following commands –

sudo su - postgres

psql

PostgreSQL Configurations

Similar to MySQL configuration file, PostgreSQL also creates two configuration file to secure and alter the server behavior. The two files are postgresql.conf and pg_hba.conf. The location of the two configuration files depend on the Linux distribution you’re using.

On Ubuntu postgresql.conf and pg_hba.conf files are located at –

/etc/postgresql/<version>/main/ 

On Fedora and Arch Linux, postgresql.conf and pg_hba.conf are stored in –

/var/lib/pgsql/data/

postgresql.conf

This file contains various settings that control how the PostgreSQL server should behave. Settings such as listening address, port, max connections and data directory location.

pg_hba.conf

pg_hba.conf is extremely important file for the database server’s security. This file contains configuration options such as which IP address is allowed to make connections to the server.

How to migrate from MySQL to PostgreSQL?

​Check that the Server is Running

Most likely you don’t need this chapter, but very briefly: after you’ve installed your PostgreSQL package on your Linux machine (whether from a package or following these notes), you need to do something like:

su -
su - postgres
createdb test
psql test
=# create user username password ' password ';
-- To change a password:
=# alter role username password ' password ';
=# create database databasename with encoding 'utf8';
=# grant all privileges on database databasename to username;
=# \l
=# \c databasename
=# \q
vi /etc/postgresql/pg_hba.conf
host all all 0.0.0.0 0.0.0.0 md5

​Be SURE to cover this security issue with iptables!

/etc/init.d/postgresql reload 

Or

/usr/lib/postgresql/bin/pg_ctl reload

​postmaster successfully signaled

psql -h server -d databasename -U usernamedatabasename=>

Convert and import

Using pgloader

​Have a look at pgloader and migrate your MySQL database to PostgreSQL in a single command:

pgloader mysql://user@localhost/dbname postgresql:///dbname

​This will handle type casting with default casting rules set, and also schema discovery in MySQL and creation in PostgreSQL. This will include tables, columns, constraints (primary keys, foreign keys, NOT NULL), default values, and secondary indexes. We can transform the dates on the fly and PostgreSQL accepts them. We remove zero dates because PostgreSQL does not allow empty date fields. If a date field is empty, PostgreSQL considers it not a date. MySQL accepts empty date fields.

Using SQL dumb

Use the following command:

mysqldump -u username -p --compatible=postgresql databasename > outputfile.sql

​But even then you will have to change escaped chars (replacing \t with ^I, \n with ^M, single quote (‘) with doubled single quote and double (escaped) backslash (\\) with a single backslash). This can’t be accomplished with the sed command, so you may need to write a script for it (Ruby, Perl, etc). There is a MySQL to PostgreSQL Python convert script (you need to use –default-character-set=utf8 when exporting your mysqldump to make it work). It is much better and proven solution to prepend your dump with the following lines.

SET standard_conforming_strings = 'off';
SET backslash_quote = 'on';

​These options will force the PostgreSQL parser to accept non-ANSI-SQL-compatible escape sequences (Postgre will still issue HINTs; you can ignore them). Do not set these options globally: this may compromise the server’s security! You also have to manually modify the data types etc. as discussed later.

After you convert your tables, import them the same way you were used to in MySQL, that is –

psql -h server -d databasename -U username -f data.sql

​There are other options for converting that you can explore it here.

Conclusion

Every developer needs a database, and choosing a good one can also be a difficult decision based on speed, scalability, and performance factors. There are plenty of SQL databases and you just have to choose what best suits your needs.