How to Install PostgreSQL on Ubuntu, CentOS and Windows

PostgreSQL is a powerful, open source database management system that is a great option for many applications and web projects. With its reliability, robust feature set, and performance, PostgreSQL is highly popular among developers and growing in use for enterprise systems.

In this beginner-friendly guide, we will walk through how to install PostgreSQL on three common operating systems – Ubuntu, CentOS, and Windows. By the end, you will have PostgreSQL up and running and learn some key steps for configuring it on each OS. Let‘s get started!

A Brief History of PostgreSQL

But first, a quick background on the project – where did PostgreSQL come from?

PostgreSQL originated in 1986 as part of the POSTGRES project at U.C. Berkeley. The aim was to add SQL capabilities to the Ingres database. After decades of development, PostgreSQL became an open source project in 1996.

Some key milestones:

1996 – Version 1.0 released as open source software
1996 – PostgreSQL development group formed
2001 – Version 7.1 added window functions, recursive queries, schemas, and more
2005 – PostgreSQL Global Development Group formed to expand contributors
2013 – PostgreSQL 10 added logical replication using publish/subscribe
2017 – Version 10 added declarative table partitioning
2020 – Version 13 added parallelized vacuuming for higher performance

Today, PostgreSQL has over 30 years of active progress behind it. The project continues to enhance PostgreSQL with new SQL capabilities, performance improvements, and features.

Why Consider PostgreSQL?

So what makes PostgreSQL worth using? Here are some of its key strengths:

SQL compliance – Adheres closely to SQL standards and supports complex queries
ACID compliance – Provides fully atomic, consistent, isolated, durable transactions
Stored procedures – Functions and logic can be defined in the database
Extensibility – Custom data types, functions, aggregates, operators, etc
NoSQL support – Column storage via JSONB, XML, key-value pairs in hstore
Replication – Asynchronous and synchronous replication options
High availability – Hot standby servers for high uptime
Security – Roles, encryption, SSL, and authentication protocols

According to DB-Engines which tracks database popularity, PostgreSQL ranks 4th overall based on mentions on websites, search results, and job postings.

It has seen strong industry adoption with companies like Apple, Reddit, Netflix, Spotify, Skype, and thousands more using PostgreSQL for their core data storage needs. The high configurability, robust feature set, strong performance, and smooth upgrades make PostgreSQL well suited for enterprises.

How PostgreSQL Compares to Other Databases

How does PostgreSQL fit in compared to the other major relational databases? Here is a quick comparison to MySQL and Oracle Database:

Feature	PostgreSQL	MySQL	Oracle
License	Open source	Open source (some proprietary components)	Proprietary/commercial
SQL standards compliance	Closely compliant	Partial compliance	Compliant
Transactions	Full ACID compliance	ACID compliant in some storage engines	Full ACID compliance
Stored procedures	Yes (many languages)	Yes	Yes (PL/SQL)
Triggers	Yes	Yes	Yes
Extensibility	Highly extensible	UDFs and plugins	Packages, objects
JSON support	JSONB with indexing	JSON document storage	JSON binary storage
Replication	Asynchronous/synchronous built-in	Asynchronous via plugins	Oracle Data Guard
Indexes	B-tree, GiST, SP-GiST, GIN, BRIN	B-tree, R-tree	B-tree, bitmap, function-based, domain

While there is some overlap in capabilities, PostgreSQL excels in areas like extensibility, open source governance, and standards compliance. It‘s a great fit for many types of database applications from web apps to analytics use cases.

When to Use PostgreSQL

So when is PostgreSQL a good choice? Here are some top use cases where PostgreSQL shines:

High volume web apps – Proven reliability at scale for web workloads
Analytics databases – Column store via JSONB great for analytics
Financial apps – Robust ACID compliance for transactions
Scientific data – Excellent geomapping via PostGIS extension
Mobile & IoT data – JSON support ideal for unstructured data
High availability systems – Replication, failover, load balancing

Developers praise the high configurability, stability, open standards, and performance of PostgreSQL. It‘s a great fit for projects that require scalability and reliability.

Inside PostgreSQL Architecture

Under the hood, how does PostgreSQL work? Here is a quick dive into some key architecture components:

Processes – PostgreSQL has background processes for writing, committing, checkpoints
Memory – Shared buffers cache data in memory; write-ahead log to disk
Disk – Tablesspaces store data across multiple physical disks
Locking – MVCC multiversion concurrency provides transaction isolation
Executors – Query optimizer with rule-based planner and genetic query optimizer
WAL – Write-ahead logs durably store committed transactions

This is just a glimpse of what makes PostgreSQL tick. Robust architecture provides durability, performance, and rock solid stability.

Now let‘s jump in and walk through installing PostgreSQL on Ubuntu, CentOS, and Windows step-by-step…

Installing PostgreSQL on Ubuntu

PostgreSQL is included in Ubuntu‘s default repositories, so you can install it with apt. However, the version provided may not always be the latest release.

For newer versions, you can use the PostgreSQL apt repository. Let‘s cover both options:

Installing Default PostgreSQL (Ubuntu Repository)

To install the default PostgreSQL version included with your Ubuntu release, run:

$ sudo apt update
$ sudo apt install postgresql

This will install a supported PostgreSQL release for the lifetime of your Ubuntu version.

To check the status of the service, use:

$ sudo systemctl status postgresql

This should show the postgresql service as active and running.

Using the PostgreSQL Apt Repository

For newer PostgreSQL versions, add the PostgreSQL apt repository:

$ sudo sh -c ‘echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list‘

Import the repository signing key:

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Update apt and install PostgreSQL:

$ sudo apt update
$ sudo apt install postgresql-12

This installs PostgreSQL 12 specifically, rather than just the latest version.

When installing from the PostgreSQL repository, you can choose from packages like:

postgresql-client – Client libraries and binaries
postgresql-server – Core server
postgresql-contrib – Additional modules
libpq-dev – Libraries and headers for C development
postgresql-server-dev-12 – Libraries and headers for backend development

I recommend including the postgresql-contrib package which provides helpful additional functions, operators, aggregates and types.

After installation, verify that the service is running:

$ sudo systemctl status postgresql

The PostgreSQL apt repository provides more flexibility to install specific versions. You can always upgrade to newer minor versions easily later.

Troubleshooting PostgreSQL Installation on Ubuntu

If the service fails to start on Ubuntu, some things to check:

Verify that the data directory has proper permissions and ownership assigned to the postgres user. The data directory is located at /var/lib/postgresql/X.X/main where X.X is the PostgreSQL version.
Check /var/log/postgresql/postgresql-X.log for errors during startup. Look for permissions issues or configuration problems.
Make sure you don‘t have another instance of PostgreSQL already on the default 5432 port. This will prevent the service from starting. You can optionally specify a different port for the new install if needed.
Check for any apt errors for missing dependencies during install. Run sudo apt install -f to fix any issues.

With hundreds of Ubuntu deployments, I‘ve found these are the most common startup issues. Overall, PostgreSQL is very solid once up and running.

Installing PostgreSQL on CentOS

CentOS also provides a couple installation options:

Use the version included in CentOS repositories
Add the PostgreSQL yum repository to get newer versions

Let‘s walk through both methods.

Installing Default PostgreSQL (CentOS Repository)

To use the supported PostgreSQL for your CentOS release:

$ sudo yum install postgresql-server

Or with DNF on CentOS 8:

$ sudo dnf install postgresql-server

After installing from the CentOS repository, initialize the database:

$ sudo postgresql-setup initdb

This creates the data directory, sets up access permissions, and initializes the database cluster.

Then enable and start the postgresql service:

$ sudo systemctl enable postgresql
$ sudo systemctl start postgresql

Check that the service is running:

$ sudo systemctl status postgresql

This installs a stable PostgreSQL version supported throughout the CentOS release lifecycle.

Using the PostgreSQL Yum Repository

For specific PostgreSQL versions, add the PostgreSQL yum repository:

$ sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Then install a desired version, like 13:

$ sudo yum install postgresql13-server

The PostgreSQL yum repo provides additional packages like:

postgresql-client – Client libraries and binaries
postgresql-server – Core server
postgresql-contrib – Extra modules
postgresql-devel – Libraries and headers for C development

I recommend including postgresql-contrib which adds helpful extensions.

After installing, initialize the cluster:

$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

Enable and start the postgresql service:

$ sudo systemctl enable postgresql-13
$ sudo systemctl start postgresql-13

Check status to confirm the service is running:

$ sudo systemctl status postgresql

The PostgreSQL yum repository lets you install specific versions like PostgreSQL 13 for newer features.

Troubleshooting PostgreSQL on CentOS

If PostgreSQL fails to start on CentOS, some things to try:

Check that SELinux is not blocking the service. You may need to adjust SELinux policies to allow PostgreSQL to initialize clusters and accept connections.
Verify the data directory ownership and permissions are set properly for the postgres user.
Check /var/log/postgresql/postgresql-X-main.log for startup errors or crash reports.
Make sure your postgresql.conf does not contain invalid settings. Test by renaming the file and starting with original postgresql.conf.
Check for yum installation errors related to missing dependencies. Run yum install -f to fix.

PostgreSQL is very solid once up and running. Startup issues are usually due to platform configuration, permissions, or environment problems.

Installing PostgreSQL on Windows

On Windows, PostgreSQL offers an interactive installer for fast and easy setup.

Downloading the PostgreSQL Installer

Download the PostgreSQL installer for Windows from the official site.

Select the version and either 32-bit or 64-bit based on your system.

For this guide, we will use the 64-bit installer for PostgreSQL 13, the latest stable release.

The download is around 130 MB and comes with everything you need prepackaged.

Running the PostgreSQL Interactive Setup Wizard

Once downloaded, launch the installer (.exe file) to begin the setup wizard:

Click Next on the first welcome screen to continue.
Leave the default installation directory or change it if desired. Click Next.
On the next screen, select all components. This includes the client tools, server, Stack Builder, etc. Click Next.
Enter and confirm a superuser (postgres) password. This secures the admin user account. Click Next.
The default 5432 port works for most cases. Click Next.
Select your locale and click Next. US English is the default.
Review all settings on the summary page. Go back to modify anything needed or click Next to start installation.
Click Next when ready to confirm starting the installation.

The wizard will proceed with unpacking and installing all selected PostgreSQL components.

Once complete, the final screen will confirm PostgreSQL is installed successfully. Uncheck the Stack Builder option to avoid launching into more setup. Just click Finish.

PostgreSQL is now fully set up on your Windows machine!

Connecting to PostgreSQL in Windows

There are a few ways to connect to the PostgreSQL server:

Search for SQL Shell in the start menu. This launches the psql command line shell.
Use pgAdmin 4 for a graphical administration interface (installed by default).
Connect from a programming language like Python using a PostgreSQL driver.
Use a database tool like DBeaver with the PostgreSQL connector.

Let‘s launch psql to connect from the command line.

When prompted, enter the connection details:

Server: localhost
Database: postgres
Port: 5432
Username: postgres (the default superuser)
Password: The password you chose during installation

This will connect you to PostgreSQL where you can begin running commands. Try a simple query like:

SELECT version();

This should print the PostgreSQL version installed. Congrats, you‘re connected!

The psql shell provides full admin access to create databases, users, tables, and more. I suggest looking through the psql docs to learn all the commands available.

Key Takeaways from Installing PostgreSQL

Now that you‘ve installed PostgreSQL on Ubuntu, CentOS, and Windows, what are some key lessons?

PostgreSQL offers native packages for all major platforms. Ubuntu provides deb packages while RPM packages are available for CentOS.
For most versions, using the official PostgreSQL repositories gives more flexibility over versions.
The interactive installer makes it very quick to get PostgreSQL running on Windows.
Packages like postgresql-contrib provide additional useful extensions.
The postgres user and psql shell provide admin access to PostgreSQL.
PostgreSQL is highly configurable once installed. Dive into postgresql.conf to enable features like replication, SSL, and more.

Overall, I‘ve found PostgreSQL to have an excellent getting started experience across platforms. It‘s easy to install and has great documentation to help you build production-ready environments.

Next Steps After Installing PostgreSQL

Once up and running, where to go next? Here are some suggestions:

Work through a PostgreSQL tutorial to learn the basics
Refer to the thorough PostgreSQL docs
Learn to develop applications against PostgreSQL in your language of choice
Check out various PostgreSQL distributions that provide GUI admin tools and enterprise capabilities.
Explore PostgreSQL extensions like PostGIS for spatial data and pg_cron for scheduling
Consider hosting options like AWS RDS, Azure Database for PostgreSQL, and more

There‘s a robust PostgreSQL ecosystem with many resources available. Understanding how to install PostgreSQL is just the beginning. This guide should provide a solid foundation for your database projects. From testing environments to production deployments, PostgreSQL has you covered.

Any questions on the installation process? Let me know in the comments!