Some of the more up-and-coming web development stacks such as LEPP, Gunicorn/Django and Ruby/Rails prefer to use the PostgreSQL database platform instead of the more traditional MySQL configuration. This article will help you install and configure PostgreSQL on your self-managed Linux Ubuntu server.
Overview of PostgreSQL
PostgreSQL, or simply ‘postgres,’ is an open-source relational database management system, or RDBMS for short. Just like MySQL, SQLite, and Microsoft SQL Server, Postgres uses the Structured Query Language (SQL) to manipulate data. PostgreSQL is cross-platform, and has packages for all major Linux distributions, as well as Windows Server platforms. While not as popular as MySQL, Postgres is arguably the more powerful database platform as it is truly ANSI/ISO compliant, unlike MySQL which is not due to some of its limitations. It also supports stored procedures, a feature that MySQL does not support. This isn’t saying you should immediately dump MySQL for Postgres for all your applications or projects – but it may be a good fit for some.
Advantages of PostgreSQL
- An open-source SQL standard compliant RDBMS
- Strong community
- Strong third-party support:
- Stored Procedures / Extensible
Disadvantages of PostgreSQL
- Struggles with large read-heavy operations against a database
- Less popularity = less open-source documentation and collaboration
- Different SQL syntax than traditional MySQL/Microsoft SQL solutions
Like many software solutions, which database system you should use depends entirely on the project at hand. However, in our research we have found that there are a few guidelines you can follow when making your decision:
- If you want stronger security, compliance, stored procedures, and need a database for a strong back-end application – PostgreSQL may be the right fit for you
- If you want to use the more ubiquitous platform, high security, and work mostly with website CMS platforms – MySQL is still probably the right fit for you
We highly suggest doing research on the different database platforms at their respective sites before making an informed decision. Now let’s focus on the nitty-gritty — how to install and set up PostgreSQL on your server.
In order to perform this task, you’ll need an active Ubuntu server, as well as an SSH client such as PuTTY (for Windows) or Terminal (Mac). We recommend a GoDaddy VPS if you’re just getting started, or a full dedicated server if you’re ready to take total control.
Installing PostgreSQL using APT
By default, Ubuntu includes PostgreSQL in the base APT repositories. This means that installing Postgres will be a cinch.
1. Log into your server via SSH.
2. Access root by running the following command:
sudo su -
3. To install PostgreSQL, as well as the necessary server software, run the following command:
apt-get install postgresql postgresql-contrib
4. Configure PostgreSQL to start up upon server boot.
update-rc.d postgresql enable
5. Start PostgreSQL.
service postgresql start
Connecting to PostgreSQL
By default, PostgreSQL sets up the user and database “postgres” upon a new installation. We interact with the postgres database software through an interface called “psql.”
1. Log into the postgres user:
su - postgres
2. This will bring you to a new prompt. Log into the database by typing:
3. You should now see a prompt for postgres=#. This means you are at a PostgreSQL prompt. To exit the interface, you can type:
From there, you can get back to root by typing:
This will bring you back to the root user for your server. You now have PostgreSQL installed on your system.
You’re now ready to start using PostgreSQL for your database management. You can deploy PostgreSQL for a variety of software solution stacks, or even configure a common application to work with PostgreSQL if it has support for that platform. For more information on using PostgreSQL, syntax, and other utilities that are available, check out the documentation at PostgreSQL’s official website.