PostgreSQL: Client Applications

PostgreSQL: Client Applications

English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 2h 34m | 359 MB

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.

Table of Contents

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

1. Set Up Your PostgreSQL Toolset
4 What are PostgreSQL client applications
5 Locate client applications on Windows
6 Locate client applications on macOS
7 Set environment variables on Windows
8 Set path on macOS
9 Create a PostgreSQL server

2. Using the Psql Interactive Terminal
10 Solution Obtain database information
11 Connect to a PostgreSQL server
12 Create a database
13 Execute SQL commands
14 Backslash (meta) commands
15 Get help with SQL command keywords
16 Execute commands from a file
17 List database contents
18 Describe object configuration
19 Challenge 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

39 Next steps