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

0. Introduction

01 - Work with PostgreSQL on the command line
02 - What you should know
03 - Use the exercise files

1. Set Up Your PostgreSQL Toolset

04 - What are PostgreSQL client applications
05 - Locate client applications on Windows
06 - Locate client applications on macOS
07 - Set environment variables on Windows
08 - Set path on macOS
09 - Create a PostgreSQL server

2. Using the Psql Interactive Terminal

10 - Connect to a PostgreSQL server
11 - Create a database
12 - Execute SQL commands
13 - Backslash (meta) commands
14 - Get help with SQL command keywords
15 - Execute commands from a file
16 - List database contents
17 - Describe object configuration
18 - Challenge - Obtain database information
19 - Solution - Obtain database information

3. Customize the Psql Environment

20 - Streamline password submission
21 - Change the prompt style
22 - Save custom environment settings

4. Input and Output Options

23 - Input queries from the command line
24 - Format aligned text output
25 - Output results to external files
26 - Save query results to .csv
27 - Output HTML and LaTeX tables
28 - Export files from the command line
29 - Challenge - Export a data table
30 - Solution - Export a data table

5. Backup and Restore a PostgreSQL Database

31 - Create backups with pg dump
32 - Configure backup options
33 - Data-only and schema-only backups
34 - Create a backup of a single table
35 - Restoring plain-text backups
36 - Custom archive backup files
37 - Restoring database archives with pg restore
38 - Partial database restores

Conclusion

39 - Next steps

PostgreSQL: Client Applications

Authors

Adam Wilbert