PostgreSQL: Client Applications

Learn about the PostgreSQL command line utilities that provide database administrators with a powerful set of tools for managing remote database servers.

Introduction

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

1. 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

2. 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

3. Customize the Psql Environment

• Streamline password submission
• Change the prompt style
• Save custom environment settings

4. 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

5. 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