Getting connected to PostgreSQL for the first time

PostgreSQL continues to be all the rage in 2023, whether in “vanilla” form of the fully open-source distribution or a variant like Amazon RDS, Neon, Yugabyte, and others. If you’re interested in trying PostgreSQL but only have experience with another database like SQL Server, it can feel a bit daunting to get started.

In this small series, we’ll walk you through the process of connecting to a version of PostgreSQL locally or in the cloud. Next, we’ll discuss some options for sample databases and show you how to restore them. And finally, we’ll finish by demonstrating a few ways to connect to and query the database.

So, let’s get right to it.

Getting Access to PostgreSQL

PostgreSQL is available in nearly every possible way. Local install, Docker, IaaS options, and fully supported DBaaS providers that you’re probably already using for at least one project. However, for most of this article we’ll focus on ways you can start to easily use PostgreSQL for free, either locally or online.

Local Installation

PostgreSQL has installation options on every major operating system. That said, Linux-based operating systems have more rigorous support and testing. Windows supports an installation package created and maintained by Enterprise DB (EDB) and can be useful for testing PostgreSQL on a local windows machine. As a rule of thumb, however, you’d be hard pressed to find a production workload running on the Windows installation.

Below, we’ll start by demonstrating how to start working with a version of PostgreSQL on your local computer using the supported methods. We won’t specifically show you how to connect to the server here.

Instead, you can follow our tutorial on using psql or wait for the third part of this series where we discuss a handful of currently supported and popular tools for connection to and managing PostgreSQL.

Docker Containers (all platforms)

For anyone that’s seriously considering having one or more local installations of PostgreSQL, Docker continues to be the easiest path forward. The official containers are maintained regularly and quickly updated as new releases are made. Also, when the release cycle begins each year for PostgreSQL, beta versions are made available as Docker images for convenient testing and exploration. The official PostgreSQL images are located on the Docker Hub.

You must have Docker installed and available to use the PostgreSQL Docker images.

The simplest way to create a PostgreSQL container is to run the following Docker command, which will make the server available on the default port 5432 from your local computer so that you can use graphical IDEs or terminal applications like psql without having to connect into the container itself.

$ docker run --name postgres-demo -e POSTGRES_PASSWORD=password 
-d postgres -p 5432:5432

The big part here is that if you don’t add the port parameter, then PostgreSQL won’t be accessible from outside of the container and you’d have to enter the shell of the container to connect.

Postgres.app (MacOS)

This is a community-maintained project that provides easy installation and access to stable releases of PostgreSQL on MacOS. You can select from numerous installers which provide different sets of PostgreSQL versions from the Postgres.app downloads page. Once installed and running, Postgres.app gives you the ability to start different supported versions of PostgreSQL (the most recent five releases). All packages also include all of the standard PostgreSQL command-line tools (psql, pg_dump, pg_restore, etc.) which can be mapped for easy use with the terminal.

The real value for MacOS users is that it’s a self-contained, native application that you can install and uninstall with simple drag-and-drop. And, because the project is actively maintained, there are even packages with recent Beta versions of the upcoming release of PostgreSQL that you can try.

Linux Package Installers

Most major Linux distributions have recent packages available in their packaging system. For the last number of years, Devrim Gunduz has spearheaded the maintenance of these packages and works hard to keep core PostgreSQL versions and numerous extensions ready and available for installation on your Linux distribution.

Follow your normal installation steps if you’re comfortable running native packages on Linux. Otherwise, Docker is a great choice on Linux.

Windows Installer

I want to briefly mention that there is a Windows installer that is maintained by EDB and available for installation. Over the years, Windows specific support has improved and there should be little difference running PostgreSQL on Windows vs. Linux.

That said, Windows installations are not widely used in the larger PostgreSQL community. While this generally shouldn’t be an issue with recent releases, just recognize that there are very few production installations of PostgreSQL running on Windows. If that is your only option, I’d strongly consider Docker or a good database hosting provider, some of which we mention in the next section.

Easy Database-as-a-Service (DBaaS) Providers

Another option for getting easy, quick access to PostgreSQL is to sign-up for a hosting provider with a plan and feature set that can meet your needs. Obviously, if you go this route, you will only be able to access the PostgreSQL instance when connected to the Internet. However, all of the mundane tasks of maintaining the server are taken care of by someone else.

All the options mentioned below are current as of August 2023. Because PostgreSQL is being heavily developed for all kinds of use cases, new database service providers are coming online often. We’ll try to keep this list updated if something changes, but realize that within a year, there will probably be more options. Your mileage may vary, and you should do some additional research.

None of the offerings below are specifically recommended or supported by Redgate or Simple Talk.

The Big Three

All major cloud providers have some kind of PostgreSQL offering. But, Amazon Web Services, Microsoft Azure, and Google Cloud Platform all have quick and easy options. Chances are you already use one of these platforms in your business and can quickly spin up a PostgreSQL cluster, often free or very low cost for hobbyist-like workloads.

AWS RDS

RDS PostgreSQL has been the leading DBaaS for over a decade now. Because so many companies have invested a lot within the AWS cloud, RDS and RDS Aurora are very easy to add onto a project to begin using PostgreSQL.

Generally speaking, regular RDS PostgreSQL is very close to standard PostgreSQL, the main limitations being that you do not get superuser access and you can’t install and use whatever extensions you want to.

Aurora PostgreSQL, on the other hand, has a lot more modifications to it under the covers which allows AWS to provide a serverless environment that doesn’t require as much daily tuning as something more standard.

Either option will allow you to start using PostgreSQL with minimal effort, so choose the one that makes sense for you already use AWS services.

Microsoft Azure

Similar to the AWS offerings above, Azure has a more traditional PostgreSQL offering, and a powerful multi-node database backed by the Citus extension. Microsoft has invested a lot of time and money into helping the PostgreSQL project grow and the Citus team within the Microsoft database group is very active and helpful to the community.

Azure Database for PostgreSQL is similar to RDS, while Azure Cosmos DB for PostgreSQL is more akin to Aurora, from a serverless, scalability perspective.

Google Cloud Platform

Last but not least, Google Cloud Platform (GCP) has two of their offerings as well. AlloyDB, the newest entry into the serverless PostgreSQL space has some compelling performance features that might help certain workloads.

Other PostgreSQL DBaaS Platforms

This grouping of cloud providers focusses solely on providing easy, scalable PostgreSQL instances in the cloud. Each of them has a specific focus or niche, and with a little research, you can find other similar platforms.

Crunchy Bridge – A product of the larger PostgreSQL focused company called Crunchy Data; Crunchy Bridge is a fully native PostgreSQL offering. They provide enterprise-grade hosting for large projects but have a long history of contributing to PostgreSQL and supporting smaller workloads too.

This past year, Crunchy Data introduced a “hobby” tier that will be free if you manage the instance and spend $5 or less a month.

Neon.tech – Neon is a newer player on the scene, originally opening for private beta in 2022. Neon is labelled as an open-source alternative to AWS Aurora with a lot of interesting features around database branching, bottomless object-level storage, and more. They have a free tier, and the sign-up process is quick and easy, getting you to a PostgreSQL database within a minute or two.

Recently, Neon has been spending effort on adding vector-based features and extensions to their offering, leaning heavily into using PostgreSQL as a part of AI applications.

aiven – aiven is an infrastructure platform provider that also provides a hobby tier for trying PostgreSQL. Their offering is very similar to Crunchy Bridge, meaning that it’s not PostgreSQL plus some additional changes. They’re offering a traditional install of PostgreSQL with different options to match your workload requirements.

PostgreSQL Playground by Crunchy Data – This isn’t a hosted database, specifically, but a neat way to try and learn more about PostgreSQL without spending any money. The playground is an in-browser version of PostgreSQL, and they provide loadable datasets that can help you learn different aspects of PostgreSQL. This is definitely worth a look, keeping an eye out for future development of this tool. You can access the PostgreSQL Playground here.

Connecting to PostgreSQL

Regardless of what method you chose to use for your initial access to PostgreSQL, the next thing you’ll need to do is use a tool to get connected for the first time. If you’re coming from another database like SQL Server, there are often commonly supported IDEs that allow you to easily connect to the database for querying and management tasks.

With PostgreSQL, there is no official graphical tool. While pgAdmin is listed on the PostgreSQL.org website, it’s maintained by EDB, a major contributor to the PostgreSQL project. But there are a multitude of other options, both graphical and terminal in nature, that are actively used throughout the community.

The goal for you is to determine the level of graphical support you need to feel a sense of success and security. Below we’ll briefly discuss a few options for you to explore based on our experience, and in some cases, community recommendations.

psql

The psql command line tool has been a part of the PostgreSQL project for nearly 30 years. It has hundreds of built-in meta commands and connecting to a PostgreSQL database is quick. I always tell folks that eventually it’s going to be helpful to learn a little bit about psql. Read our two-part series (part 1, part 2) on installing and using this powerful query and management tool.

DBeaver

DBeaver is an open-source graphical IDE that allows you to connect to PostgreSQL, and tens of other databases. In my experience over the last few years, this tool is the closet I’ve come to SSMS, although it lacks many of the powerful SQL Server-like features because it’s a general database IDE.

For most people that don’t have another tool in mind, I often tell folks to start with DBeaver and then move on if you find it doesn’t meet your needs.

pgAdmin

This is another popular, open-source IDE that is maintained by EDB. pgAdmin does have some unique features, like a visual query plan explorer. At its core, pgAdmin is a web application being served in a desktop format, and so some of the design decisions and lack of intuitive key commands can feel cumbersome to some users.

Lots of people do use pgAdmin, so it’s worth a look if you have no other options.

Azure Data Studio

Microsoft released Azure Data Studio in 2017 for SQL Server. In 2019, they released an extension that allows you to connect to PostgreSQL for querying and some maintenance tasks. If you already use ADS and are used to the various key commands and extensions that can be used with PostgreSQL, you should at least give it a try. Azure Data Studio supports Windows, MacOs, and Linux.

Datagrip

Finally, Datagrip is a paid product from Jetbrains. If your company already owns licenses from Jetbrains, it’s definitely worth doing a trial of the IDE, but for most day-to-day work, any of the other open-source tools you can find will probably serve your needs.

Selecting and Restoring a Sample Database

Within the PostgreSQL community there are many sources to find sample databases. However, there are a few databases that are commonly used in demos and blog posts.

Pagila

The Pagila database is a has a simple schema that could be used to run a simple video rental store. It feels somewhat dated in concept, but it continues to be updated in the various forks that people keep of it. This is the simplest database to restore in many cases because it is provided as a set of SQL scripts, both for schema generation and data insertion. You can run the SQL scripts in an IDE, or you can do it with the psql application as shown on the GitHub repo.

To get started with Pagila, create a database on your PostgreSQL cluster and then execute the schema and data scripts on that newly created database.

psql -h localhost -p 5432 -U postgres -c 'CREATE DATABASE pagila;'
psql -h localhost -p 5432 -U postgres -d pagila 
-f pagila-schema.sql -f pagila-data.sql

Postgres Air

The Postgres Air database was created by Henrietta Dombrovskya to accompany her book, PostgreSQL Query Optimization. The database contains fake data for an airline company, including ticketing and routes. Henrietta and her team regularly update the database with new features, improved processes for creating more realistic data, and modifying the timestamps to stay closer to the current year.

The Postgres Air database is provided in both SQL script format and as a backup file from pg_dump. The backup files will be slightly smaller, but they do require a little more knowledge of using pg_restore. If you decide to use the SQL files, follow a similar workflow shown above with the Pagila database.

To restore the backup file instead, try the following with your locally installed pg_restore tooling. Many IDEs also contain interfaces for restoring a backup using a version of pg_restore that’s included into the application.

psql -h localhost -U postgres -p 5432 -c 'CREATE DATABASE postgres_air;'
pg_restore -h localhost -p 5432 -U postgres -W -v -Fc 
-d posgres_air < postgres_air_2023.backup

Wide World Importers

For years, the standard SQL Server sample database was Adventure Works. Over the years, Microsoft decided to create a new database to try and showcase the advanced features that had been added to the application in modern versions of the software. They called the database Wide World Importers. There are a lot of similarities to Adventure Works, but it also contains examples of new datatypes and features like temporal tables.

In 2017, a team at Microsoft converted the SQL Server schema to PostgreSQL and provided an updated workload generator application to fill it with data. If you’ve played with Wide World Importers before and are looking for something similar, this might be a good option to start with.

Be aware, however, that the sample data is very outdated, and you can’t fill it with data unless you use the workload generator because the necessary stored procedures don’t exist in the backup. Instead, they are created, if necessary, when the workload generator application starts.

This database is also provided as a pg_dump backup file, so the same pg_restore command will restore the database into your newly created database.

psql -h localhost -U postgres -p 5432 
-c 'CREATE DATABASE wide_word_importers_pg;'

pg_restore -h localhost -p 5432 -U postgres -W -v -Fc 
-d wide_word_importers_pg < wide_world_importers_pg.dump

Summary

One of the great things about PostgreSQL is that it can be used with many operating systems in many ways. Of course, that also can make it daunting to figure out how to get started. In this article, I have pointed out the many ways you can install and access a PostgreSQL database, along with setting up some sample data to work with.

 

The post Getting connected to PostgreSQL for the first time appeared first on Simple Talk.

from Simple Talk https://ift.tt/2Edcx4m
via