PostgreSQL® tutorial: Get started with PostgreSQL in 4 easy steps

What Is PostgreSQL?

PostgreSQL is an open source object-relational database system. It has a strong reputation for reliability and performance and an extensive feature set. It supports SQL and enables complex queries, making it suitable for large sets of data across various applications. It also manages concurrency, allowing multiple users to interact with the database simultaneously.

In addition to its SQL support, PostgreSQL offers extensibility, which means users can define their own data types and functions, making PostgreSQL customizable and compatible with modern data pipelines. Its architecture supports diverse programming languages and frameworks, and it has an active development community.

Key features of PostgreSQL

PostgreSQL offers the following main features:

• Efficient data handling and complex queries: Facilitates efficient processing capabilities beyond typical database systems.
• Support for complex data types: Arrays and JSON allow for complex operations and structures, making PostgreSQL ideal for data-intensive applications.
• Full-text search functionality: Enables handling of unstructured data efficiently, which is crucial for real-time data analysis.
• Concurrency support without locking: Allows stable operations under heavy load without the need to lock data.
• ACID compliance: Guarantees safe transactions with atomicity, consistency, isolation, and durability, ensuring system reliability.
• Support for procedural languages: Languages like PL/pgSQL enable the creation of custom rules and triggers, enhancing automation and scalability in enterprise applications.

Related content: Read our guide to Postgres support (coming soon)

Tips from the expert

Sharath Punreddy

Solutions Architect

Sharath Punreddy is a Solutions Enginee with extensive experience in cloud engineering and a proven track record in optimizing infrastructure for enterprise clients

In my experience, here are tips that can help you better utilize PostgreSQL:

• Take advantage of the JSONB data type for semi-structured data: Unlike the standard JSON type, JSONB stores JSON data in a binary format, making it faster to process and query. It is ideal for applications that require flexibility in data schema or need to handle large volumes of semi-structured data.
• Optimize query performance using EXPLAIN and ANALYZE: Regularly analyze query execution plans using EXPLAIN and ANALYZE to understand how queries are processed. Look for slow queries or operations that can benefit from indexing, rewriting, or optimization.
• Use server-side programming with PL/pgSQL: PL/pgSQL allows you to write functions and triggers directly within the database, reducing the need for external application logic. This can improve performance and maintainability by keeping business logic close to the data.
• Regularly monitor and tune autovacuum settings: Autovacuum is crucial for maintaining database health by reclaiming storage and preventing table bloat. Adjusting parameters such as autovacuum_naptime, autovacuum_vacuum_cost_limit, and autovacuum_max_workers can help balance performance and maintenance.
• Explore PostgreSQL extensions for added functionality: PostgreSQL’s extensibility allows for adding custom functionality. Extensions like PostGIS for spatial data, hstore for key-value storage, and pg_stat_statements for tracking query performance can significantly enhance the capabilities of your database. Install and configure only those extensions that meet your needs to avoid unnecessary complexity.

PostgreSQL tutorial for beginners

This tutorial covers the basic installation and configuration of PostgreSQL on Windows and Ubuntu, as well as how to connect to a PostgreSQL database server and load a database. The instructions are adapted from the PostgreSQL documentation.

Step 1: Set Up PostgreSQL

Windows Installation

To install PostgreSQL on Windows, follow these steps:

Download the PostgreSQL installer:
Begin by navigating to the EnterpriseDB download page and selecting the latest version of PostgreSQL for Windows, such as version 16.1. Choose the x86-64 architecture, which is suitable for most modern systems. The download process will take a few minutes.

Run the installer:
Once the download is complete, double-click the installer file. The PostgreSQL installation wizard will launch, guiding you through the setup process:

1. Click Next on the welcome screen.
2. Choose the installation directory. You can either accept the default location or specify a different directory based on your preference, then click Next.
3. Select the components you want to install. The available options include:
   • PostgreSQL server: Installs the PostgreSQL database server.
   • pgAdmin 4: Installs the graphical user interface management tool for PostgreSQL.
   • Command line tools: Installs tools like psql<c/ode> and pg_restore for command-line interaction with the database.
   • Stack builder: Assists in downloading and installing additional drivers and tools. This can be skipped for now.
4. After selecting the necessary components, click Next.

Configure the database directory and credentials
Next, set the relevant configurations:

1. Choose the directory for storing the database data. You can accept the default or specify a different directory.
2. Set a password for the PostgreSQL superuser (commonly postgres). Retype the password to confirm, and then click Next.
3. Specify the port number for PostgreSQL to listen on, typically 5432, ensuring no other applications use this port.
4. Select the default locale for the PostgreSQL server. Leaving it as the operating system default is generally sufficient.

Complete the installation
Review the installation summary:

1. If everything is correct, click Next to start the installation. The process may take a few minutes.
2. After installation is complete, click the Finish button.

Add PostgreSQL to the system PATH
To ensure you can use PostgreSQL’s command-line tools from any directory, you need to add the PostgreSQL bin directory to your system’s PATH environment variable:

1. Locate the bin directory, usually found in C:\Program Files\PostgreSQL\\bin.
   Press Win + R, type sysdm.cpl, and press Enter to open the System Properties dialog.
   Go to the Advanced tab and select Environment Variables.
   Under User variables or System variables, select the Path variable and click Edit.
   Click New, enter the path to the PostgreSQL bin directory, and click OK.
   Finally, close all dialog boxes by clicking OK. PostgreSQL is now installed and configured on your Windows system, ready for use.

Installation on Ubuntu

To install PostgreSQL on Ubuntu, follow these steps:

Add the PostgreSQL repository
First, ensure your package index is up to date and install the necessary packages:

Next, add the PostgreSQL repository to your system:

Then, import the repository signing key:

Update your package list again to include the new PostgreSQL repository:

Install PostgreSQL 16

To install PostgreSQL 16 along with its additional modules:

Run the following command:

After installation, start the PostgreSQL service:

To ensure PostgreSQL starts automatically on boot, enable the service:

Configure the PostgreSQL server

PostgreSQL stores its configuration files in the postgresql.conf file. You can edit this file using a text editor like nano:

Use the following command:

If you want to allow remote connections, set listen_addresses to *:

Next, configure PostgreSQL to use MD5 password authentication by modifying the pg_hba.conf file. This is important if you plan to enable remote connections:

Restart PostgreSQL to apply the changes:

If your system uses a firewall, allow traffic on PostgreSQL’s default port (5432) with:

PostgreSQL is now installed and configured on your Ubuntu system, ready for use.

Step 2: Connect to a PostgreSQL Database Server

Connecting to a PostgreSQL database server can be done through several tools. Below, we’ll guide you through connecting using both the psql terminal-based utility and the pgAdmin<.code> graphical interface.

Connect to the PostgreSQL database server using psql

The psql tool is a command-line interface for interacting directly with the PostgreSQL server. It allows you to execute SQL commands, manage database objects, and perform various administrative tasks:

1. Open the command prompt or terminal:
   1. On Windows, open the Command Prompt.
   2. On Unix-like systems (Linux, macOS), open Terminal.
2. Use the following command to connect to the PostgreSQL server:
   psql -U postgres

   1	psql -U postgres

3. After running the command, you will be prompted to enter the password for the postgres user:
   Password for user postgres:

   1	Password for user postgres:

   Enter the password you set during installation.

4. If the credentials are correct, you will be connected, and the prompt will change to something like this:
   postgres=#

   1	postgres=#

   This indicates you are now connected to the PostgreSQL server’s postgres database.

5. To verify your connection, you can check the PostgreSQL version by running:
   SELECT version();

   1	SELECT version();

   The output will display the version of PostgreSQL running on your server.

6. You can also check the current database with:
   SELECT current_database();

   1	SELECT current_database();

   And the server’s IP address and port with:

   SELECT inet_server_addr(), inet_server_port();

   1	SELECT inet_server_addr(), inet_server_port();

Connect to the PostgreSQL database server using pgAdmin

pgAdmin is a web-based interface that simplifies database management with a graphical interface. It’s suitable for those who prefer not to work from the command line:

1. Open pgAdmin from the Start menu or your applications list. It will launch in your default web browser.
2. Right-click on the Servers node in the left-hand panel and select Register, followed by Server.
3. In the dialog box, enter a name for the server connection (e.g., “Local”).
4. In the Connection tab, enter the host (usually localhost) and the password for the postgres user.
5. Click the Save button to establish the connection and save the configuration.
6. Expand the Servers node, and you will see the connected PostgreSQL instance with the default postgres database.
7. To run SQL queries, select Tool, then Query Tool from the menu. Enter your SQL commands in the editor, and click the Execute button to run them.
8. The results will be displayed in the Data Output tab.

Step 3: Load a PostgreSQL Sample Database

Loading a sample database is a great way to practice and explore PostgreSQL’s features. Below are the steps to load the dvdrental sample database using both the psql command-line tool and the pgAdmin graphical interface.

Load the sample database using psql and pg_restore

First, you need to create a new database where the sample data will be loaded. Open your Command Prompt on Windows or Terminal on Unix-like systems, and connect to the PostgreSQL server using the psql tool:

Enter the password for the postgres user when prompted. Once connected, create the dvdrental database:

Verify that the database was created successfully by listing all databases:

You should see dvdrental listed among the databases.

After creating the database, download the sample database (dvdrental.zip), extract it, and then use the pg_restore tool to load the data into the dvdrental database. For example, if the extracted file is located at D:\sampledb\postgres\dvdrental.tar on Windows, run:

Enter the password when prompted. The restore process will load the data into the dvdrental database.

To verify it, connect to the dvdrental database using psql:

List all tables to verify that the data has been loaded successfully:

You should see a list of tables such as actor, film, customer, etc., confirming that the sample database is ready to use.

Load the example DVD rental database using pgAdmin

1. Open pgAdmin and connect to your PostgreSQL server. Right-click on the Databases node in the Object Browser panel, and select Create, then Database.
2. Enter dvdrental as the database name, and click Save.
3. Right-click on the newly created dvdrental database and select Restore.
4. In the Restore dialog, enter the path to the dvdrental.tar file (e.g., c:\sampledb\dvdrental.tar), and click Restore.
5. Once the restore process completes, expand the dvdrental database in the Object Browser. You should see the tables and other objects within the public schema, confirming that the sample data has been successfully loaded.

Managed PostgreSQL with Instaclustr

Efficiency and Reliability Unleashed: The Benefits of Instaclustr for PostgreSQL Databases

Instaclustr offers a host of benefits specifically tailored for PostgreSQL databases, making it an ideal solution for organizations seeking efficient and reliable management of their PostgreSQL deployments. With its managed services approach, Instaclustr simplifies the deployment, configuration, and maintenance of PostgreSQL databases, freeing up valuable resources and enabling businesses to focus on their core applications and data-driven insights.

Some of the benefits of Instaclustr for PostgreSQL include:

• Comprehensive managed services infrastructure management, provisioning, configuration, and security, ensuring that organizations can leverage the power of this robust relational database management system without the complexities of managing it internally. By offloading these operational tasks to Instaclustr, organizations can reduce the burden on their internal teams and ensure that their PostgreSQL databases are managed effectively and securely.
• Automated scaling capabilities, enabling PostgreSQL to seamlessly handle increasing workloads by adding or removing resources as needed. This ensures that applications relying on PostgreSQL can accommodate spikes in traffic and scale to meet future growth demands. Instaclustr’s platform actively monitors the health of the database cluster, automatically handling scaling processes to ensure optimal resource utilization and cost efficiency.
• High availability and fault tolerance for PostgreSQL databases. By employing replication and failover mechanisms, Instaclustr ensures that data is stored redundantly across multiple nodes in the cluster, providing resilience against hardware failures and enabling continuous availability of data. Instaclustr’s platform actively monitors the health of the database cluster and automatically handles failover and recovery processes, minimizing downtime and maximizing data availability for PostgreSQL deployments.

Furthermore, Instaclustr’s expertise and support are invaluable for PostgreSQL databases. Our team of experts has deep knowledge and experience in managing and optimizing PostgreSQL deployments. We stay up-to-date with the latest advancements in PostgreSQL technologies, ensuring that the platform is compatible with the latest versions and providing customers with access to the latest features and improvements. Instaclustr’s 24/7 support ensures that organizations have the assistance they need to address any PostgreSQL-related challenges promptly.

For more information see:

• Instaclustr for PostgreSQL
• Fast Forking for PostgreSQL on Azure NetApp Files
• When You Need That Extra Power: Instaclustr Managed PostgreSQL on Azure NetApp Files