What Is PostgreSQL? Ubuntu Install and MySQL Differences

What is PostgreSQL?

PostgreSQL is an open source relational database management system that stores data in tables and answers queries written in SQL. Its JSONB type, extension system and close fit to the SQL standard make it a common choice for small web projects and for complex applications alike.

This guide serves two readers. The first owns a website, a store or a software project and has to make a database decision. The second runs a VPS and wants commands to follow step by step.

We are a digital marketing and web team, not a hosting company. So everything here rests on the official PostgreSQL and Ubuntu documentation. We say "current stable release" instead of a version number, because numbers go stale quickly.

You will also find an honest section near the end. It explains which steps you should not take yourself and which ones belong with your hosting provider. A database mistake can take down your whole site.

What is PostgreSQL, and what does a relational database do?

A relational database keeps information in tables made of rows and columns. In a store, for example, customers, products and orders live in separate tables. Keys link the tables together, so a query can tell you which customer placed which order.

PostgreSQL does this work as a server program, so it needs a process of its own. Your application, meaning your website or API, talks to it over the network or a local socket. Then the app sends a query and receives the result. The database process runs on its own and keeps the data on disk in its own layout.

In short, what is PostgreSQL? It is the memory of your site. Product lists, user accounts, carts and order history all sit there. If the database slows down, your pages slow down too. Likewise, if it stops answering, your site shows errors.

You talk to the database in SQL. Therefore, if SQL is new to you, start with our roadmap for learning SQL. For example, for interview style query scenarios, read the SQL interview questions guide.

What are PostgreSQL's strengths?

When people ask what is PostgreSQL good at, a handful of features set it apart, and you can check each one in the official documentation. Most of them also come without any extra license fee.

• Close SQL standard fit: According to the docs, it meets at least 170 of the 177 mandatory features for SQL:2023 Core conformance.
• The JSONB type: It stores JSON documents in a binary form and lets you index them.
• Extensibility: Extensions, user defined functions and several procedural languages add new abilities.
• Foreign data wrappers: You can query other data sources as if they were tables.
• A steady release policy: Each major version receives roughly five years of support.

Keep one point in mind while reading this list. A strength does not always mean an advantage for your project. For instance, a simple company website will never touch most of these features. Once the project grows, however, that flexibility starts to pay off.

The official page adds a useful caveat: no current database system claims full conformance to Core SQL:2023. So "high conformance" is the honest phrase, not "complete". Source: the PostgreSQL SQL conformance page.

What is JSONB, and how does it differ from JSON?

PostgreSQL offers two JSON types, json and jsonb. The json type stores your input text exactly as you typed it. Instead, the jsonb type parses the document and keeps it in a binary form. As a result, jsonb is faster to query but slightly slower to write.

The documentation is clear here. Unless you have a very specialized need, store JSON data as jsonb. However, old assumptions about the order of object keys are the main exception.

The example below creates a GIN index on a jsonb column and then queries it with the containment operator. The table and column names are examples.

CREATE TABLE orders (id serial PRIMARY KEY, details jsonb);
CREATE INDEX orders_details_gin ON orders USING GIN (details);
SELECT details->>'customer' FROM orders WHERE details @> '{"payment": "card"}';

Here the ->> operator returns a value as text. The @> operator checks whether the document contains the given fragment. Source: the PostgreSQL JSON types documentation.

What does PostgreSQL extensibility mean?

Think of PostgreSQL as a platform rather than a single product. The core stays lean, so you add abilities through extensions. You load an extension into a database with the CREATE EXTENSION command. The official feature matrix even lists trusted extensions as their own topic.

Functions, triggers and stored procedures belong to the same flexibility. Procedural languages such as PL/pgSQL, PL/Perl and PL/Python let you write logic inside the database. However, putting all your logic there is rarely wise. Draw a deliberate line between application code and database code.

Moreover, foreign data wrappers add another strength. For example, you can read a table from another PostgreSQL server straight from your own query. That also helps with data migration and reporting work.

So what does this mean for marketing and web projects? The benefit is indirect but real. As search, reporting and data collection needs grow, you can often use the database's own abilities instead of installing a separate tool. That lowers the maintenance load.

What is PostgreSQL next to MySQL: how do they differ in practice?

Both are open source, widespread relational databases that speak SQL. Most differences, however, sit in the details and in the ecosystem. The table below lists the differences that are documented or beyond dispute.

The JSON row comes from the official docs. In MySQL, for example, you do not index a JSON column directly. Instead, you index a generated column that extracts a value from the JSON expression. InnoDB also supports multi-valued indexes on JSON arrays. Source: the MySQL JSON documentation.

We also cover MySQL setup and the MariaDB comparison in sibling guides, so we do not repeat those details here.

Which projects fit PostgreSQL, and which fit MySQL?

After you know what is PostgreSQL, the real question is the choice. No fixed rule exists, but a solid filter does. First, check which software your project runs. Many ready made systems, in fact, pick the database for you.

• WordPress and its plugins: Use MySQL or MariaDB. The core does not run on another database.
• Apps built from scratch with frameworks such as Laravel or Django: Both databases work, so the choice is yours.
• Projects with heavy JSON data, complex reporting and advanced SQL needs: PostgreSQL is a strong candidate.
• Simple sites on a hosting plan that offers only MySQL: Do not fight the plan. Stay with MySQL.

Your team's skills count as a criterion too. If your team has used MySQL for years and the project is simple, switching costs more than it gains. On the other hand, if you are building a new software product with a complex data model, PostgreSQL is a sensible start.

There is also a trap. Asking "which database is fastest?" is asking the wrong question. Speed depends on your schema, indexes, queries and hardware, so a single number tells you little. So treat bold benchmark numbers with caution.

If you are planning a custom software or store backend, our custom software development service includes choosing the database together, based on your needs.

What should you decide before installing?

Answer three questions before you touch a command. First, do you have root or sudo access to the server? On shared hosting you cannot install PostgreSQL yourself. Some control panels do offer a ready PostgreSQL option, so check your plan's feature list.

The second question is the version. Ubuntu ships one fixed PostgreSQL version in its own repository, and that version stays supported for the life of that Ubuntu release. Then again, if you want a newer one, you add the PostgreSQL project's own apt repository, known as PGDG.

The third question is backup. Without a working backup, you should not touch the database. Our website backup strategy guide explains how to plan one.

Know the version policy as well. According to the official page, a new major version arrives about once a year and each major version gets five years of support. Minor releases come at least every three months. Therefore staying on an unsupported version is a security risk. Source: the PostgreSQL versioning policy.

How do you install PostgreSQL on Ubuntu?

The shortest path is Ubuntu's own repository. The official download page and the Ubuntu server documentation give the same command. Update the package list first, then install the package.

sudo apt update
sudo apt install postgresql

After the install finishes, confirm that the service runs. The commands below show the service state and the client version. On Debian based systems, the postgresql-common package also gives you the pg_lsclusters tool, which lists clusters.

sudo systemctl status postgresql
psql --version
pg_lsclusters

To restart the service, use the command from the Ubuntu documentation: sudo systemctl restart postgresql.service. The configuration files live in the /etc/postgresql/<version>/main/ folder. In other words, the version part is the number of the release you installed.

After installation, the server accepts no outside connections, because the default value of listen_addresses is localhost. That is a safe start, and a later section shows how to open access.

How do you install a newer version from the PGDG repository?

If the version in Ubuntu's repository is enough for you, skip this section. If you need a newer major version, however, you can add the official PostgreSQL apt repository. The download page offers an automated script.

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

The script adds the repository and its signing key. Then you install the version you want by its package name. Also, the package name follows the pattern postgresql-<version>. Read the current stable version from the download page, because we do not print a number here.

sudo apt update
sudo apt install postgresql-<version>

Note that downloading a script and running it as root is a trust decision. Here, you trust the official PostgreSQL site. So do not extend the same trust to similar commands from other addresses. The manual setup steps also appear on the same download page.

Before you put a new version on a production server, try it on a test machine. Major upgrades need a dump and restore or the pg_upgrade tool, because the data directory is not compatible between major versions.

How do you connect with psql for the first time?

First, the install creates a system user named postgres and a database role with the same name. You make the first connection as that user. The Ubuntu documentation gives this command.

sudo -u postgres psql

The prompt changes and you can type psql commands. Commands that start with a backslash belong to psql and are not SQL. For now, these are enough to begin with.

• Type \l to list databases.
• Use \du to list roles, which means users.
• Run \conninfo to see which database and user you are connected as.
• Finish with \q to quit psql.

Do not use the postgres role for your application. It is a superuser, so it can do anything. Instead, creating a separate role with limited rights for the app is the right approach for both security and error control.

How do you create a user and a database?

You have two routes, SQL commands or shell tools. The createuser tool is a shell wrapper around the CREATE ROLE command, and the docs say there is no effective difference between them. We suggest writing SQL inside psql, because you stay in one place.

CREATE ROLE app_user WITH LOGIN;
\password app_user
CREATE DATABASE app_db OWNER app_user;

The \password command asks you for the password and sends it to the server in encrypted form. Typing a password into a command line or SQL text can leave it in shell history and logs. So prefer the interactive command. Also, store the password in a password manager.

If you prefer the shell, the two commands below do the same job. For createuser the -P flag prompts for a password, and for createdb the -O flag sets the owner.

sudo -u postgres createuser -P app_user
sudo -u postgres createdb -O app_user app_db

To test the connection, run psql -h localhost -U app_user -d app_db. If it asks for a password, then the role and pg_hba.conf work. If you get an error, the next section explains the likely cause.

What is pg_hba.conf, and how does it work?

The pg_hba.conf file controls client authentication. Its name comes from "host based authentication". Each line defines a rule: who may connect, to which database, from which address and by which method. On Ubuntu the file sits at /etc/postgresql/<version>/main/pg_hba.conf.

A rule has these fields: connection type, database, user, address and method. For example, a type of local means a Unix socket and host means a TCP/IP connection. The hostssl type matches only encrypted TCP/IP connections.

The most important rule is this one. PostgreSQL applies the first line that matches the connection type, address, database and user. It never moves on to later lines, because there is no fall through. If authentication fails on the first matching line, it does not try the lines below. Finally, if no line matches, PostgreSQL denies access.

How do you add the right line to pg_hba.conf?

If your application runs on the same server and connects over TCP, one password line for the local address is enough. The documentation shows this format. Replace the names with your own database and role.

host    app_db    app_user    127.0.0.1/32    scram-sha-256

Choose scram-sha-256 as the password method. Also, according to the docs, the default value of password_encryption is scram-sha-256. The same page also says MD5 support is deprecated. So do not choose MD5 on a new install.

Next, after you edit the file, you must reload the configuration. A restart is not required. Either of the commands below does the job.

sudo systemctl reload postgresql
sudo -u postgres psql -c "SELECT pg_reload_conf();"

The most dangerous method is trust. The docs say it lets anyone who can reach the server log in as any user without a password. Do not leave a trust line in place, even for a quick test. Otherwise you will forget it and the door stays open.

Therefore, read the existing lines and keep a copy before you delete anything. Because line order matters, a rule placed in the wrong spot either does nothing or allows more than you meant.

How do you open remote access safely?

Most web projects, in practice, never need the database on the internet. If the app and the database share a server, localhost is enough. A separate app server or a reporting tool is the case that needs outside access. In that case, you change three settings together.

1. Change listen_addresses in postgresql.conf. Its default is localhost, which allows only local connections. The server reads this setting only at startup, so it needs a restart.
2. Add a line to pg_hba.conf that allows only the needed address and role and forces an encrypted connection.
3. Open port 5432 in the firewall for that one address only.

In the example below, 203.0.113.10 is a documentation address. Write your own client address instead. The Ubuntu documentation also shows the hostssl and scram-sha-256 combination.

hostssl    app_db    app_user    203.0.113.10/32    scram-sha-256

sudo ufw allow from 203.0.113.10 to any port 5432 proto tcp
sudo systemctl restart postgresql

Never open the port to everyone. Attackers scan database ports on the internet quickly and try passwords. An encrypted connection also needs a TLS certificate on the server. For the certificate basics, read our SSL certificate guide.

How do you troubleshoot connection problems?

If you cannot connect, do not guess. Check in order, because each step rules out one possibility. First confirm that the service runs, then move to the network layer.

1. First, run sudo systemctl status postgresql and confirm the service is active.
2. Run sudo ss -ltnp | grep 5432 to see whether the port is listening and on which address.
3. Test whether the client can reach the server at all. Review the firewall rule and the provider's network filter.
4. Read the error message. A message with "pg_hba.conf entry" tells you the rule is missing or wrong.
5. Check the PostgreSQL log. On Ubuntu the logs usually sit under /var/log/postgresql/.

If you connect by domain name, verify the DNS record too. Our DNS lookup tool and IP lookup tool help with that.

The message "password authentication failed" means a rule matched but the password or role is wrong. The message "no pg_hba.conf entry" means no rule matched at all. Telling these two apart turns a half hour search into two minutes.

How do you summarize the whole PostgreSQL setup?

Let us pull the scattered commands into one flow. The order below is a sensible roadmap for someone starting from zero on an Ubuntu VPS. Each step has its detail in the sections above.

1. First, prepare a backup and your access details. Confirm that you can log in to the server with sudo rights.
2. Install PostgreSQL with apt and check the service status.
3. Connect as postgres with psql, then create a limited role and a database for the app.
4. Open pg_hba.conf and read the existing lines. If needed, add a narrow scram-sha-256 line and reload the configuration.
5. Keep your application's connection details in an environment variable or a private config file. Never commit the password to a code repository.
6. Take a backup and test it by restoring it into a test database.

Answering what is PostgreSQL is easy, but running it takes discipline. Write these six steps down once and apply the same list to every new project. That way you are less likely to skip a step.

Where you store the connection string matters too. You may run WordPress, Laravel or custom software. In all of them the connection details live in a config file or an environment variable. Do not put that file in a public folder and do not send it to version control.

How should you think about backups and upgrades?

PostgreSQL's own backup tool is pg_dump. It takes a logical backup of a single database. You restore a custom format backup with pg_restore. The commands below are examples, so change the names to fit your project.

pg_dump -h localhost -U app_user -Fc -f app_db.dump app_db
pg_restore -h localhost -U app_user -d new_db app_db.dump

Taking a backup is not enough. You also need to test a restore. A backup that fails a restore test gives you no safety. Try it regularly on a test database. Keep the backups off the server, in a separate place.

On upgrades, the official advice is plain. The community considers a minor upgrade less risky than running an old minor version. For a minor upgrade you stop the server, install the updated binaries and restart. No dump is needed. On Ubuntu an apt update usually does this job.

A major upgrade is different. The data directory is incompatible, so you need a dump and reload or the pg_upgrade tool. Plan it for a maintenance window and take a backup first.

When should you leave PostgreSQL to your hosting provider?

Knowing what is PostgreSQL is one thing; running it on a live system is another. Running your own VPS gives you control. Control also brings responsibility. In the cases below, it is wiser to leave the job to your provider or to an experienced system administrator.

• A major version upgrade is due on the database of a live store.
• Your backup is missing, or you have never tested a restore.
• The database must face the internet and you are unsure about firewall rules.
• Advanced setups such as replication, high availability or very large data volumes are on the table.
• The server is under heavy attack and the logs show unusual connection attempts.

A managed database service is another option. The provider handles maintenance, backups and upgrades, and you focus on the application. In return, weigh the cost against the flexibility you give up.

We collected the questions to ask a provider in our guide on how to choose web hosting. When you open a support ticket, include the time of the problem, the error message and the steps you tried. That way the provider finds the cause much faster.

Does your database choice affect site speed and SEO?

It does not affect them directly, but it does indirectly. Search engines do not look at the brand of your database. However, slow queries delay the first response from your server, and that delay shows up in page speed. So the problem is query and index quality, not the database brand.

When speed suffers, the order is simple. Find the slow queries first, then check the indexes, then add a cache. To understand caching, look at how caching works with Redis and Memcached. For the role of speed in search, read how site speed affects SEO.

In e-commerce the effect is easier to see. Cart and checkout steps write to the database, so delays there lower conversions. We cover this in e-commerce page speed and sales.

Database security also ties into SEO. A compromised database can let an attacker plant unwanted content or links on your site. For that risk, see our OWASP Top 10 guide.

What mistakes show up most often in a PostgreSQL setup?

The mistakes below are common patterns. They are not a field statistic. They are a checklist drawn from the official docs and general security practice.

• Connecting the application as the postgres superuser.
• Leaving trust in pg_hba.conf.
• Opening listen_addresses to everyone and forgetting the firewall.
• Editing pg_hba.conf and skipping the reload.
• Skipping the restart after changing listen_addresses.
• Taking backups but never testing a restore.
• Staying on a major version that lost support.
• Typing the password in plain text on the command line.

Use this list as a post install checklist. Tick each item one by one. Also note down your changes, because in six months you will struggle to remember what you changed and why.

What is PostgreSQL in short, and how do you start?

PostgreSQL is a powerful, standards friendly and extensible database. Its JSONB type and extension ecosystem make it especially attractive for projects with complex data models. MySQL, on the other hand, remains the right choice for many simple sites because of its reach and its fit with ready made systems such as WordPress.

Two commands install it on Ubuntu. The real work comes afterwards: a role with limited rights, the correct pg_hba.conf line, a port that stays closed and a backup you have tested. Together these four items form the security backbone of the setup.

If you would rather not go it alone, we stand with you on software and infrastructure decisions. Talk to our team about custom software development and we can weigh up which database suits your project. To understand the server side better, what is backend development is a good place to begin.