PostgreSQL: Client Applications

Course details

PostgreSQL is one of the most popular open-source database management systems. In this course, Adam Wilbert introduces you to the command line tools used in professional PostgreSQL database administration. These tools are used for remote management of the server and provide detailed insight into the configuration and operation of the server. Adam starts by locating or installing the tools, then explores the operation of the main utility, psql. Psql provides a wide array of configurable options that can be combined to create flexible workflows for sending SQL commands to the server, or for exporting query results to external files. Adam finishes the course with a detailed look at the command line utilities used to create and restore database backups.

Concepts

Introduction

Work with PostgreSQL on the command line
What you should know
Use the exercise files

Set Up Your PostgreSQL Toolset

What are PostgreSQL client applications
Locate client applications on Windows
Locate client applications on macOS
Set environment variables on Windows
Set path on macOS
Create a PostgreSQL server

Using the Psql Interactive Terminal

Connect to a PostgreSQL server
Create a database
Execute SQL commands
Backslash meta commands
Get help with SQL command keywords
Execute commands from a file
List database contents
Describe object configuration
Challenge Obtain database information
Solution Obtain database information

Customize the Psql Environment

Streamline password submission
Change the prompt style
Save custom environment settings

Input and Output Options

Input queries from the command line
Format aligned text output
Output results to external files
Save query results to .csv
Output HTML and LaTeX tables
Export files from the command line
Challenge Export a data table
Solution Export a data table

Backup and Restore a PostgreSQL Database

Create backups with pg dump
Configure backup options
Data-only and schema-only backups
Create a backup of a single table
Restoring plain-text backups
Custom archive backup files
Restoring database archives with pg restore
Partial database restores

Conclusion

Next steps