Search icon CANCEL
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
PostgreSQL 10 Administration Cookbook

You're reading from   PostgreSQL 10 Administration Cookbook Over 165 effective recipes for database management and maintenance in PostgreSQL 10

Arrow left icon
Product type Paperback
Published in May 2018
Publisher Packt
ISBN-13 9781788474924
Length 576 pages
Edition 4th Edition
Languages
Arrow right icon
Authors (2):
Arrow left icon
Gianni Ciolli Gianni Ciolli
Author Profile Icon Gianni Ciolli
Gianni Ciolli
Simon Riggs Simon Riggs
Author Profile Icon Simon Riggs
Simon Riggs
Arrow right icon
View More author details
Toc

Table of Contents (14) Chapters Close

Preface 1. First Steps FREE CHAPTER 2. Exploring the Database 3. Configuration 4. Server Control 5. Tables and Data 6. Security 7. Database Administration 8. Monitoring and Diagnosis 9. Regular Maintenance 10. Performance and Concurrency 11. Backup and Recovery 12. Replication and Upgrades 13. Other Books You May Enjoy

Using the psql query and scripting tool

psql is the query tool supplied as a part of the core distribution of PostgreSQL, so it is available in all environments, and works similarly in them all. This makes it an ideal choice for developing portable applications and techniques.

psql provides features for use as both an interactive query tool and a a scripting tool.

Getting ready

From here on, we will assume that the psql command is enough to allow you access to the PostgreSQL server. This assumes that all your connection parameters are defaults, which may not be true.

Written in full, the connection parameters would be either of these options:

psql -h myhost -p 5432 -d mydb -U myuser 
psql postgresql://myuser@myhost:5432/mydb

The default value for the port (-p) is 5432. By default, mydb and myuser are both identical to the operating system's username. The default myhost on Windows is localhost, while on Unix, we use the default directory for Unix socket connections. The location of such directories varies across distributions and is set at compile time. However, note that you don't actually need to know its value, because on local connections both the server and the client are normally compiled together, so they use the same default.

How to do it...

The command that executes a single SQL command and prints the output is the easiest, as shown here:

$ psql -c "SELECT current_time"
timetz
-----------------
18:48:32.484+01
(1 row)

The -c command is non-interactive. If we want to execute multiple commands, we can write those commands in a text file and then execute them using the -f option. This command loads a very small and simple set of examples:

$ psql -f examples.sql

It produces the following output when successful:

SET
SET
SET
SET
SET
SET
DROP SCHEMA
CREATE SCHEMA
SET
SET
SET
CREATE TABLE
CREATE TABLE
COPY 5
COPY 3

The examples.sql script is very similar to a dump file produced by PostgreSQL backup tools, so this type of file and the output it produces are very common. When a command is executed successfully, PostgreSQL outputs a command tag equal to the name of that command; this is how the preceding output was produced.

The psql tool can also be used with both the -c and -f modes together; each one can be used multiple times. In this case, it will execute all the commands consecutively:

$ psql -c "SELECT current_time" –f examples.sql -c "SELECT current_time"
timetz
-----------------
18:52:15.287+01
(1 row)
...output removed for clarity...
timetz
-----------------
18:58:23.554+01
(1 row)

The psql tool can also be used in interactive mode, which is the default, so it requires no option:

$ psql
postgres=#

The first interactive command you'll need is the following:

postgres=# help

You can then enter SQL or other commands. The following is the last interactive command you'll need:

postgres=# \quit

Unfortunately, you cannot type quit on its own, nor can you type \exit, or other options. Sorry, just \quit, or \q for short!

How it works...

In psql, you can enter the following two types of commands:

  • psql meta-commands
  • SQL

A meta-command is a command for the psql client, whereas SQL is sent to the database server. An example of a meta-command is \q, which tells the client to disconnect. All lines that begin with \ (backslash) as the first nonblank character are presumed to be meta-commands of some kind.

If it isn't a meta-command, then it's SQL. We keep reading SQL until we find a semicolon, so we can spread SQL across many lines and format it any way we find convenient.

The help command is the only exception. We provide this for people who are completely lost, which is a good thought; so let's start from there ourselves.

There are two types of help commands, which are as follows:

  • \?: This provides help on psql meta-commands
  • \h: This provides help on specific SQL commands

Consider the following snippet as an example:

postgres=# \h DELETE
Command: DELETE
Description: delete rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table [ [ AS ] alias ]
[ USING usinglist ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ AS output_name ] [,]]

I find this a great way to discover and remember options and syntax. You'll also like the ability to scroll back through the previous command history.

You'll get a lot of benefit from tab completion, which will fill in the next part of the syntax when you press the Tab key. This also works for object names, so you can type in just the first few letters and then press Tab; all the options will be displayed. Thus, you can type in just enough letters to make the object name unique, and then hit Tab to get the rest of the name.

One-line comments begin with two dashes, as follows:

-- This is a single-line comment

Multi-line comments are similar to those in C and Java:

/*
* Multi-line comment
*/

You'll probably agree that psql looks a little daunting at first, with strange backslash commands. I do hope you'll take a few moments to understand the interface and keep digging for more information. The psql tool is one of the most surprising parts of PostgreSQL, and it is incredibly useful for database administration tasks when used alongside other tools.

There's more...

psql works across releases and works well with older versions. It may not work at all with newer server versions, so use the latest client whatever level of server you are accessing.

See also

Check out some other useful features of psql, which are as follows:

  • Information functions
  • Output formatting
  • Execution timing using the \timing command
  • Input/output and editing commands
  • Automatic start up files, such as .psqlrc
  • Substitutable parameters (variables)
  • Access to the OS command line
You have been reading a chapter from
PostgreSQL 10 Administration Cookbook - Fourth Edition
Published in: May 2018
Publisher: Packt
ISBN-13: 9781788474924
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at €18.99/month. Cancel anytime