PostgreSQL tutorial: Get started with PostgreSQL 16

For years now, PostgreSQL has been one of the best-kept secrets of enterprise software and open source alike—a powerful, robust, expertly maintained relational database. MySQL may be better known, but PostgreSQL offers many features MySQL has only started to provide.

In this article, I’ll walk through the steps to set up and get running a basic PostgreSQL installation. Most of this discussion is aimed at folks who are starting from scratch, but I’ll also discuss how to migrate data from a previous PostgreSQL installation or another database solution. This article focuses on the PostgreSQL 16 family of releases, the most recent and current revision of PostgreSQL as of this writing.

[ Beware of these 10 common PostgreSQL mistakes and learn why serverless is the future of PostgreSQL. ]

How to install PostgreSQL Windows, Linux, and macOS

PostgreSQL is available in binary form for all the major operating systems: desktop and server editions of Windows, all the popular flavors of Linux, macOS, and a few others, such as Solaris.

By and large, PostgreSQL’s capabilities are the same across all the platforms it supports. There are some exceptions to that rule, however, which I will explain in detail shortly. Keep in mind how these differences might affect any deployment or application development plans.

Installing PostgreSQL on Windows

Developers who want to set up PostgreSQL on Windows have a choice of installers packaged by third parties—everything from simple .zip archives to interactive GUI-driven installers.

The most “blessed” option, the one linked to in PostgreSQL’s own documentation, is the EnterpriseDB installer. It includes PostgreSQL’s graphical installation wizard, an easy step-by-step tool for setting up the database and its supporting tools. It also includes “Stack Builder,” a tool for downloading and installing a plethora of additional tools. These include database drivers, other editions of PostgreSQL (in the event you need to run them side by side), EnterpriseDB’s own PostgreSQL add-ons, and the Slony-I replication system for PostgreSQL. For experts, EnterpriseDB also offers a simple .zip archive of nothing more than PostgreSQL’s binaries for Windows.

If you want to run PostgreSQL on Windows without installing it or leaving any data behind, there is a recipe, albeit a slightly complex one, for running PostgreSQL from its .zipped archive distribution. This is highly useful for testing on a desktop where software installations are restricted, or where you want to confine your PostgreSQL work to a non-system or removable drive. A third-party project, PostgreSQL Portable, packages a standalone version of PostgreSQL for for the PortableApps application launcher. Unfortunately, it has not been updated for any newer version of PostgreSQL since version 10.

Installing PostgreSQL on Linux

Linux distributions vary greatly in terms of how software is installed and maintained, with different package managers, package formats, and software-repository philosophies. PostgreSQL isn’t exempt from these differences, which can be a barrier to obtaining the latest version of the software on your platform.

Ubuntu, for instance, maintains a version of PostgreSQL in its APT repositories. You can typically install PostgreSQL with the command apt install postgresql. In the event the Ubuntu repository doesn’t have the most recent version, you’ll need to add the apt.postgresql.org repository to your repo list and fetch PostgreSQL from there.

Both the Red Hat and Fedora Linux repos host an edition of PostgreSQL, although the specific version of PostgreSQL varies between distributions. Fedora is updated more often than Red Hat, so Red Hat’s version may lag. The most surefire way to install the most recent version of PostgreSQL for Red Hat or Fedora is to download the installation artifact directly from PostgreSQL via an interactive download page or from PostgreSQL’s yum repository.

Docker container images for every major edition of PostgreSQL on Linux are available from Docker Hub. Note that you will need to provide the container with a location for persistent data storage; the default location is /var/lib/postgresql/data. You can point to a different location by editing the PGDATA environment variable.

An additional consideration on Linux is which file system to use to store PostgreSQL data. Recent benchmarks show ext4/XFS are good overall choices. Although the PostgreSQL FAQ cites ZFS for its delegated administration and snapshotting functionality, Ubuntu is the only major Linux distribution that bundles ZFS today; in other Linux distros, you will have to set up ZFS on your own.

Installing PostgreSQL on macOS

Mac users often rely on Homebrew, an open-source package manager for macOS that has become the de facto way to install open source projects on the Mac from the command line. Homebrew allows you to install PostgreSQL and all its dependencies with one command (brew install postgresql). You can also install a specific version of PostgreSQL using Homebrew; for instance, to install PostgreSQL 12, use brew install postgresql@12.

Another way to install PostgreSQL on macOS is by way of Postgres.app, a third-party repackaging of PostgreSQL as a conventional Mac app. The website provides installers for the latest versions of PostgreSQL and all the major PostgreSQL GUI clients, and even self-updates if needed. Postgres.app also performs some automatic preconfiguration for you, such as initializing the data directory and user database, and bundles many common tools like pg_upgrade.

PostgreSQL connection issues on Windows

One big caveat to keep in mind when running PostgreSQL on Windows is a documented limit—around 125—on the number of connections PostgreSQL can hold open at a given time. This limitation applies only when PostgreSQL is run as a service, as opposed to being invoked from the command line.

According to the PostgreSQL wiki, this is because some PostgreSQL dependencies depend on user32.dll, which allocates memory from the desktop heap. Because the heap has a fixed size, and because each connection depletes a certain amount of memory, failures may occur with more than approximately 125 connections.

That said, the limit on connections isn’t likely to become an issue in most usage scenarios, since there is rarely a need to make that many simultaneous direct connections to PostgreSQL. A well-designed application can use connection pooling to avoid running into a connection limit. And connection pooling is something you should be doing anyway, to keep the number of connections to the database commensurate with the number of hardware threads available on your server.

Configure PostgreSQL on Linux

If you use one of the graphical installers for PostgreSQL described above, particularly for Windows, you’ll be walked through all the steps to have a running PostgreSQL installation. With Linux, however, in most cases you’ll need to take those steps yourself.

Configuring PostgreSQL on Fedora Linux

PostgreSQLis included in Fedora’s default repositories, so running sudo dnf install postgresql-server postgresql-contrib will install those bits directly. After that, you will likely want to enable PostgreSQL to start at boot time:

sudo systemctl enable postgresql

You will also need to manually run PostgreSQL’s setup script to create the initial databases:

sudo postgresql-setup --initdb --unit postgresql

Set the postgres system password; i.e., the password for the account the PostgreSQL service runs under:

sudo passwd postgres

Start the PostgreSQL server:

sudo systemctl start postgresql

Then set the password for the postgres internal user account; i.e., the account used to connect directly to PostgreSQL. Run the psql command-line app to connect to PostgreSQL, and issue the command \password postgres (note the leading backslash).

Many additional notes about installing PostgreSQL on Fedora Linux are available in the Fedora wiki, but these steps should suffice for a basic setup.

Configuring PostgreSQL on Ubuntu Linux

Ubuntu includes PostgreSQL in its default repositories, so the setup process is similar to Fedora’s but more streamlined:

By default, PostgreSQL only listens for connections on the local system. To change this behavior, edit the file /etc/postgresql/<version>/main/postgresql.conf, where <version> is the version number for PostgreSQL. Then, in the file, remove the # in front of the listen_addresses parameter and set its value to '*'.

Next, set the user account password. To do this, run the command sudo -u postgres psql template1 to connect to PostgreSQL and issue the SQL command:

ALTER USER postgres with encrypted password 'your_password';

Install the base packages:

sudo apt update
sudo apt install postgresql

To enforce SHA-256 authentication for connections, edit the file /etc/postgresql/*/main/pg_hba.conf. For instance, if we wanted to enforce SSL for database template1 with the user postgres connecting from a given address range, we’d add a line like this:

hostssl template1    postgres    192.168.122.1/24    scram-sha-256

Set the PostgreSQL service to autostart, and restart it to make changes take effect:

sudo systemctl enable postgresql.service 
sudo systemctl restart postgresql.service

Change users and connect to the server to test:

sudo -i -u postgres
psql

Note that there is no postgresql-setup step required here. The installation process contains all the triggers needed.

Migrate data between PostgreSQL versions

One of the last steps you’ll take is to move your data from any old instances of PostgreSQL to your new instance. As of version 16, PostgreSQL offers three main ways to migrate data during an upgrade.

The pg_upgrade utility is the preferred way to migrate data between versions of PostrgreSQL. It is typically used when you are installing a new version of PostgreSQL side-by-side with an old one. You run pg_upgrade from the new installation, point it at the old one, and let it move the data over. pg_upgrade takes advantage of consistencies between point revisions of the PostgreSQL data format to speed up the migration process, so it is not only the most convenient way to migrate but typically the fastest.

Another way to perform a migration is to use PostgreSQL replication (e.g., Slony). Here you use the new version of PostgreSQL to create a standby server for a server running the older version. This method results in the least amount of downtime, because the replication happens in the background. Once the replication is finished, all you need to do is redirect all connections from the old server to the new one. However, if you’re not using replication in the first place, this might involve jumping through a few too many hoops.

The pg_dumpall utility exports the entire contents of a PostgreSQL server to one or more SQL dump files. The resulting script or archive file can then be executed on or imported to the new server. It’s also possible to set up the old and new servers side-by-side, and use pg_dumpall to “pipe” the dump operation between them. The biggest downside of using pg_dumpall is that it’s slow, but it can be used as an absolute fallback method.

Install pgAdmin 4 on Windows, macOS, or Linux

A third-party administration tool for PostgreSQL, pgAdmin 4, provides a useful web GUI for managing a PostgreSQL installation.

PGAdmin.org

While it’s very useful, the tool is somewhat ornery to get up and running, as it’s written in Python and has a great many dependencies from that language. Windows and macOS users can download a binary installer for pgAdmin 4 from the pgAdmin website. For Ubuntu, there’s an APT package. There is also a post in the Ask Ubuntu forum that runs down the steps needed to install pgAdmin 4, get it running, and create a convenient shortcut to a startup script. For Fedora and Red Hat, there’s an RPM package. And if you’re a Docker user, you can avoid most of the headache by pulling and using a Docker container with pgAdmin 4 and all its dependencies.