PostgreSQL: Up and Running, 2nd Edition

Chapter 4. Using pgAdmin

pgAdmin III is the current rendition of the tried-and-true graphical administration tool for PostgreSQL. Although it has its shortcomings, we are always encouraged by not only how quickly bugs are fixed, but also how quickly new features are added. Since it’s positioned as the official graphical-administration tool for PostgreSQL and packaged with many binary distributions of PostgreSQL, pgAdmin has the responsibility to always be kept in sync with the latest PostgreSQL releases. If a new release of PostgreSQL introduce new features, you can count on the latest pgAdmin to let you manage it. If you’re new to PostgreSQL, you should definitely start with pgAdmin before exploring other tools.

Getting Started

Download pgAdmin from pgadmin.org. While on the site, you can opt to peruse one of the guides introducing pgAdmin. The tool is well-organized and, for the most part, guides itself quite well. For the adventurous, you can always try beta and alpha releases of pgAdmin. Your help in testing would be greatly appreciated by the PostgreSQL community.

Overview of Features

To whet your appetite, here’s a list of our favorite goodies in pgAdmin. More are listed in pgAdmin Features:

Graphical explain for your queries

This awesome feature offers pictorial insight into what the query planner is thinking. Gone are the days of trying to wade through the verbosity of text-based planner output.

SQL pane

pgAdmin ultimately interacts with PostgreSQL via SQL, and it’s not shy about letting you see the generated SQL. When you use the graphical interface to make changes to your database, pgAdmin automatically displays the underlying SQL in the SQL pane that will perform the tasks. For novices, studying the generated SQL is a superb learning opportunity. For pros, taking advantage of the generated SQL is a great time-saver.

GUI editor for configuration files such as postgresql.conf and pg_hba.conf

You no longer need to dig around for the files and use another editor.

Data export and import

pgAdmin can easily export query results as a CSV file or other delimited format and import such files as well. It can even export as HTML, providing you with a turn-key reporting engine, albeit a bit crude.

Backup and restore wizard

Can’t remember the myriad of commands and switches to perform a backup or restore using pg_restore and pg_dump? pgAdmin has a nice interface that lets you selectively back up and restore databases, schemas, single tables, and globals. You can view and copy the underlying pg_dump or pg_restore command that pgAdmin used in the Message tab.

Grant wizard

This time-saver allows you to change privileges on many database objects in one fell swoop.

pgScript engine

This is a quick-and-dirty way to run scripts that don’t have to complete as a transaction. With this you can execute loops that commit on each iteration, unlike functions that require all steps to be completed before the work is committed. Unfortunately, you cannot use this engine outside of pgAdmin.

Plug-in architecture

Access newly developed add-ons with a single mouse click. You can even install your own. We describe this feature in Change in pgAdmin Plug-Ins.

pgAgent

We’ll devote an entire section to this cross-platform job scheduling agent. pgAdmin provides a cool interface to it.

Connecting to a PostgreSQL Server

Connecting to a PostgreSQL server with pgAdmin is straightforward. The Properties and Advanced tabs are shown in Figure 4-1.

Figure 4-1. pgAdmin register server connection dialog

Navigating pgAdmin

The tree layout of pgAdmin is intuitive to follow but does start off showing you every esoteric object found in the database. You can pare down the tree display by going into the Options tab and deselecting objects that you would rather not have to stare at every time you use pgAdmin. To declutter the browse tree sections, go to Tools→Options→Browser. You will see the screen shown in Figure 4-2.

Figure 4-2. Hide or unhide database objects in pgAdmin browse tree

If you select Show System Objects in the tree view check box, you’ll see the guts of your server: internal functions, system tables, hidden columns in tables, and so forth. You will also see the metadata stored in the PostgreSQL system catalogs: information_schema catalog and the pg_catalog. information_schema is an ANSI SQL standard catalog found in other databases such as MySQL and SQL Server. You may recognize some of the tables and columns from working with other database products.

Warning

pgAdmin does not always keep the tree in sync with the current state of the database. For example, if one person alters a table, the tree viewed by a second person will not automatically refresh. There is a setting in recent versions that forces an automatic refresh if you select it, but you’ll have to contend with a slight wait time as pgAdmin repaints.

pgAdmin Features

pgAdmin is chock full of goodies. We don’t have the space to bring them all to light, so we’ll just highlight the features that we use on a regular basis.

Accessing psql from pgAdmin

Although pgAdmin is a great tool, psql does a better job in a few cases. One of them is the execution of very large SQL files, such as those created by pg_dump and other dump tools. You can easily jump to psql from pgAdmin. Click the plug-in menu, as shown in Figure 4-3, and then click PSQL Console. This opens a psql session connected to the database you are currently connected to in pgAdmin. You can then use \cd and \i commands to change directory and run the SQL file.

Figure 4-3. psql plug-in

Because this feature relies on a database connection, you’ll see it disabled until you’re connected to a database.

Editing postgresql.conf and pg_hba.conf from pgAdmin

You can edit configuration files directly from pgAdmin provided that you installed the adminpack extension on your server. PostgreSQL one-click installers generally create the adminpack extension. You should see the menu enabled, as shown in Figure 4-4.

Figure 4-4. PgAdmin configuration file editor

If the menu is grayed out and you are connected to a PostgreSQL server, either you don’t have the adminpack installed on that server or you are not logged in as a superuser. To install the adminpack on a server running PostgreSQL 9.0 or earlier, connect to the database named postgres as a superuser and run the file share/contrib/adminpack.sql. For PostgreSQL 9.1 or later, connect to the database named postgres and run the SQL statement CREATE EXTENSION adminpack; or use the graphical interface for installing extensions, as shown in Figure 4-5. Disconnect from the server and reconnect; you should see the menu enabled.

Figure 4-5. Installing extensions using pgAdmin

Creating Database Assets and Setting Privileges

pgAdmin lets you create all kinds of database assets and assign privileges.

Creating databases and other database assets

Creating a new database in pgAdmin is easy. Just right-click the database section of the tree and choose New Database, as shown in Figure 4-6. The definition tab provides a drop-down menu for you to select a template database, similar to what we did in Template Databases.

Figure 4-6. Creating a new database

Follow the same steps to create roles, schemas, and other objects. Each will have its own relevant set of tabs for you to specify additional attributes.

Privilege management

To manage privileges of database assets, nothing beats the pgAdmin Grant Wizard, which you access from the Tools→Grant Wizard menu of pgAdmin. As with many other features, this option is grayed out unless you are connected to a database. It’s also sensitive to the location in the tree you are on. For example, to set privileges for items in the census schema, select the schema and then choose the Grant Wizard. The Grant Wizard screen is shown in Figure 4-7. You can then select all or some of the items and switch to the Privileges tab to set the roles and privileges you want to grant.

Figure 4-7. Grant Wizard

More often than setting privileges on existing objects, you may want to set default privileges for new objects in a schema or database. To do so, right-click the schema or database, select Properties, and then go to the Default Privileges tab, as shown in Figure 4-8. Default privileges are available only for PostgreSQL 9.0 and later.

Figure 4-8. Granting default privileges

When setting privileges for a schema, make sure to also set the usage privilege on the schema to the groups you will be giving access to.

Import and Export

Like psql, pgAdmin allows you to import and export text files.

Importing files

The import feature is really a wrapper around the psql \copy command and requires the table that will receive the data to exist already. In order to import data, right-click the table you want to import data to, as shown in Figure 4-9.

Figure 4-9. Import menu

Exporting queries as a structured file or report

In addition to importing data, you can export your queries to delimited, HTML, or XML formats. To export with delimiters, perform the following:

1. Open the query window ().

2. Write the query.

3. Run the query.

4. Choose File→Export.

5. Fill out the settings as shown in Figure 4-10.

Figure 4-10. Export menu

Exporting as HTML or XML is much the same, except you use the File→Quick Report option (see Figure 4-11).

Figure 4-11. Export report options

Backup and Restore

pgAdmin offers a graphical interface to pg_dump and pg_restore, covered in Backup and Restore. In this section, we’ll repeat some of the same examples using pgAdmin instead of the command line.

If several versions of PostgreSQL or pgAdmin are installed on your computer, it’s a good idea to make sure that the pgAdmin version is using the versions of the utilities that you expect. Check what the bin setting in pgAdmin is pointing to in order to ensure it’s the latest available, as shown in Figure 4-12.

Figure 4-12. pgAdmin File→Options

Warning

If your server is remote or your databases are huge, we recommend using the command-line tools for backup and restore instead of pgAdmin to avoid adding another layer of complexity to what could already be a pretty lengthy process. Also keep in mind that if you do a compressed/TAR/directory backup with a newer version of pg_dump, you need to use the same or later version of pg_restore.

Backing Up an Entire Database

In Selective Backup Using pg_dump, we demonstrated how to back up a database. To repeat the same steps using the pgAdmin interface, right-click the database you want to back up and choose Custom for format, as shown in Figure 4-13.

Figure 4-13. Backup database

Backing up system-wide objects

pgAdmin provides a graphical interface to pg_dumpall for backing up system objects. To use the interface, first connect to the server you want to back up. Then, from the top menu, choose Tools→Backup Globals.

pgAdmin doesn’t give you control over which global objects to back up, as the command-line interface does. pgAdmin backs up all tablespaces and roles.

If you ever want to back up the entire server, perform a pg_dumpall by going to the top menu and choosing Tools→Backup Server.

Selective backup of database assets

pgAdmin provides a graphical interface to pg_dump for selective backup. Right-click the asset you want to back up and select Backup (see Figure 4-14). You can back up an entire database, a particular schema, a table, or anything else.

Figure 4-14. pgAdmin schema backup

To back up the selected asset, you can forgo the other tabs (seeFigure 4-13). However, you can selectively drill down to more items by clicking the Objects tab, as shown in Figure 4-15.

Figure 4-15. pgAdmin selective backup Objects tab

Tip

Behind the scenes, pgAdmin simply runs pg_dump to perform backups. If you ever want to know the actual commands pgAdmin is using, say for scripting, look at the Messages tab after you click the Backup button. You’ll see the exact call with arguments to pg_dump.

pgScript

pgScript is a built-in scripting tool in pgAdmin. It’s most useful for running repetitive SQL tasks. pgScript can make better use of memory, and thus be more efficient, than equivalent PostgreSQL functions. This is because stored functions maintain all their work in memory and commit all the results of a function in a single batch. In contrast, pgScript commits each SQL insert or update statement as it runs through the script. This makes pgScript particularly handy for memory-hungry processes that you don’t need completed as a single transaction. Once a particular transaction commits, memory is available for the next one. You can see an example of where we use it for batch geocoding at a Using pgScript for Geocoding.

The pgScript language is lazily typed and supports conditionals, loops, data generators, basic print statements, and record variables. The general syntax is similar to that of Transact SQL, the stored procedure language of Microsoft SQL Server. Variables, prepended with @, can hold scalars or arrays, including the results of SQL commands. Commands such as DECLARE and SET, and control constructs such as IF-ELSE and WHILE loops, are part of the pgScript language.

Launch pgScript by opening a regular SQL query window. After typing in your script, execute it by clicking the pgScript icon ().

We’ll now show you some examples of pgScripts. Example 4-1 demonstrates how to use pgScript record variables and loops to build a cross-tab table, using the lu_fact_types table we create in Example 7-18. The pgScript creates an empty table called census.hisp_pop with numeric columns: hispanic_or_latino, white_alone, black_or_african_american_alone, and so on.

Example 4-1. Create table using record variables in pgScript

DECLARE @I, @labels, @tdef;
SET @I = 0;

Labels will hold records.
SET @labels =
    SELECT
        quote_ident(
            replace(
                replace(lower(COALESCE(fact_subcats[4], fact_subcats[3])), ' ', '_'),':',''
            )
        ) As col_name,
    fact_type_id
    FROM census.lu_fact_types
    WHERE category = 'Population' AND fact_subcats[3] ILIKE 'Hispanic or Latino%'
    ORDER BY short_name;

SET @tdef = 'census.hisp_pop(tract_id varchar(11) PRIMARY KEY ';

Loop through records using LINES function.
WHILE @I < LINES(@labels)
BEGIN
    SET @tdef = @tdef + ', ' + @labels[@I][0] + ' numeric(12,3) ';
    SET @I = @I + 1;
END

SET @tdef = @tdef + ')';

Print out table def.
PRINT @tdef;

create the table.
CREATE TABLE @tdef;

Although pgScript does not have an execute command that allows you to run dynamically generated SQL, we accomplished the same in Example 4-1 by assigning a SQL string to a variable. Example 4-2 pushes the envelope a bit further by populating the census.hisp_pop table we just created.

Example 4-2. Populating tables with pgScript loop

DECLARE @I, @labels, @tload, @tcols, @fact_types;
SET @I = 0;
SET @labels =
    SELECT
        quote_ident(
            replace(
                replace(
                    lower(COALESCE(fact_subcats[4], fact_subcats[3])), ' ', '_'),':',''
            )
        ) As col_name,
    fact_type_id
    FROM census.lu_fact_types
    WHERE category = 'Population' AND fact_subcats[3] ILIKE 'Hispanic or Latino%'
    ORDER BY short_name;

SET @tload = 'tract_id';
SET @tcols = 'tract_id';
SET @fact_types = '-1';

WHILE @I < LINES(@labels)
BEGIN
    SET @tcols = @tcols + ', ' + @labels[@I][0] ;
    SET @tload = @tload +
        ', MAX(CASE WHEN fact_type_id = ' +
        CAST(@labels[@I][1] AS STRING) +
        ' THEN val ELSE NULL END)';
    SET @fact_types = @fact_types + ', ' + CAST(@labels[@I][1] As STRING);
    SET @I = @I + 1;
END

INSERT INTO census.hisp_pop(@tcols)
SELECT @tload FROM census.facts
WHERE fact_type_id IN(@fact_types) AND yr=2010
GROUP BY tract_id;

The lesson to take away from Example 4-2 is that you can dynamically append SQL fragments into a variable.

Graphical Explain

One of the great gems in pgAdmin is its at-a-glance graphical explain of the query plan. You can access the graphical explain plan by opening up an SQL query window, writing a query, and clicking the explain icon ().

If we run the query:

SELECT left(tract_id, 5) As county_code, SUM(hispanic_or_latino) As tot,
    SUM(white_alone) As tot_white,
    SUM(COALESCE(hispanic_or_latino,0) - COALESCE(white_alone,0)) AS non_white
FROM census.hisp_pop
GROUP BY county_code
ORDER BY county_code;

we will get the graphical explain shown in Figure 4-16. Here’s a quick tip for reading the graphical explain: trim the fat! The fatter the arrow, the longer a step takes to complete.

Figure 4-16. Graphical explain example

Graphical explain is disabled if Query→Explain→Buffers is enabled. So make sure to uncheck buffers before trying a graphical explain. In addition to the graphical explain, the Data Output tab shows the textual explain plan, which for this example looks like:

GroupAggregate  (cost=111.29..151.93 rows=1478 width=20)
  Output: ("left"((tract_id)::text, 5)), sum(hispanic_or_latino), sum(white_alone), ...
  ->  Sort  (cost=111.29..114.98 rows=1478 width=20)
     Output: tract_id, hispanic_or_latino, white_alone,
     ("left"((tract_id)::text, 5))
	Sort Key: ("left"((tract_id)::text, 5))
	->  Seq Scan on census.hisp_pop  (cost=0.00..33.48 rows=1478 width=20)
		  Output: tract_id, hispanic_or_latino
		   , white_alone, "left"((tract_id)::text, 5)

Job Scheduling with pgAgent

pgAgent is a handy utility for scheduling PostgreSQL jobs. But it can also execute batch scripts in the OS, replacing crontab on Linux/Unix and the task scheduler on Windows. pgAgent goes even further: you can schedule jobs to run on any other host regardless of OS. All you have to do is install the pgAgent service on the host and point it to use a specific PostgreSQL database with pgAgent tables and functions installed. The PostgreSQL server itself is not required, but the client connection libraries are. Because pgAgent is built atop PostgreSQL, you are blessed with the added advantage of having access to all the tables controlling the agent. If you ever need to replicate a complicated job multiple times, you can go straight into the database tables directly and insert the records for new jobs, skipping the pgAdmin interface.

We’ll get you started with pgAgent in this section. Visit Setting Up pgAgent and Doing Scheduled Backups to see more working examples and details of how to set it up.

Installing pgAgent

You can download pgAgent from pgAgent Download. It is also available via the EDB Application Stackbuilder commonly used to install PostgreSQL on Windows. The packaged SQL installation script creates a new schema named pgAgent in the postgres database. When you connect to your server via pgAdmin, you will see a new section called Jobs, as shown in Figure 4-17.

Figure 4-17. pgAdmin with pgAgent installed

If you want pgAgent to run batch jobs on additional servers, follow the same steps, except you don’t have to reinstall the SQL script packaged with pgAgent. Pay particular attention to the OS permission settings of the pgAgent service/daemon account. Make sure each agent has sufficient privileges to execute the batch jobs that you will be scheduling.

Warning

Batch jobs often fail in pgAgent even when they might run fine from the command line. This is often due to permission issues. pgAgent always runs under the same account as the pgAgent service/daemon. If this account doesn’t have sufficient privileges or the necessary network path mappings, jobs fail.

Scheduling Jobs

Each scheduled job has two parts: the execution steps and the schedule. When creating a new job, start by adding one or more job steps. Figure 4-18 shows what the step add/edit screen looks like.

Figure 4-18. pgAdmin step edit screen

For each step, you can enter an SQL statement to run, point to a shell script on the OS, or even cut and paste in a full shell script as we commonly do.

If you choose SQL, the connection type option becomes enabled and defaults to local. With a local connection, the job step runs on the same server as the pgAgent and uses the same authentication username and password. You need to additionally specify the database that pgAgent should connect to to run the jobs. The screen offers you a drop-down list of databases to choose from. If you choose a remote connection type, the text box for entering a connection string becomes enabled. Type in the full connection string, including credentials, and database. When you connect to a remote PostgreSQL server with an earlier version of PostgreSQL, make sure that you don’t use SQL constructs that are not supported.

If you choose to run batch jobs, the syntax must be specific to the OS running the job. For example, if your pgAgent is running on Windows, your batch jobs should have valid DOS commands. If you are on Linux, your batch jobs should have valid shell or Bash commands.

Steps run in alphabetical order, and you can decide what kinds of actions you want to take upon success or failure of each. You have the option of disabling steps that should remain dormant but that you don’t want to delete because you might reactivate them later.

Once you have the steps ready, go ahead and set up a schedule to run them. You can set up intricate schedules with the scheduling screen. You can even set up multiple schedules.

If you installed pgAgent on multiple servers and have them all pointing to the same pgAgent database, all these agents by default will execute all jobs.

If you want to run the job on just one specific machine, fill in the host agent field when creating the job. Agents running on other servers will skip the job if it doesn’t match their host name.

Tip

pgAgent consists of two parts: the data defining the jobs and the logging of the job. Log information resides in the pgAgent schema, usually in postgres database; the job agents query the jobs for the next job to run and then insert relevant logging information in the database. Generally, both the PostgreSQL server holding the data and the job agent executing the jobs reside on the same server, but they are not required to. Additionally, a single PostgreSQL server can service many job agents residing on different servers.

A fully formed job is shown in Figure 4-19.

Figure 4-19. pgAgent jobs in pgAdmin

Helpful pgAgent Queries

With your finely honed SQL skills, you can easily replicate jobs, delete jobs, and edit jobs directly by messing with pgAgent metatables. Just be careful! For example, to get a glimpse inside the tables controlling all of your agents and jobs, connect to the postgres database and execute the query in Example 4-3.

Example 4-3. Description of pgAgent tables

SELECT c.relname As table_name, d.description
FROM
    pg_class As c INNER JOIN
    pg_namespace n ON n.oid = c.relnamespace INNER JOIN
    pg_description As d ON d.objoid = c.oid AND d.objsubid = 0
WHERE n.nspname = 'pgagent'
ORDER BY c.relname;

table_name     |       description
---------------+-------------------------
pga_job        | Job main entry
pga_jobagent   | Active job agents
pga_jobclass   | Job classification
pga_joblog     | Job run logs.
pga_jobstep    | Job step to be executed
pga_jobsteplog | Job step run logs.
pga_schedule   | Job schedule exceptions

Although pgAdmin already provides an intuitive interface to pgAgent scheduling and logging, you may find the need to generate your own jobs reports. This is especially true if you have many jobs or you want to compile stats from your job results. Example 4-4 demonstrates the one query we use often.

Example 4-4. List log step results from today

SELECT j.jobname, s.jstname, l.jslstart,l.jslduration, l.jsloutput
FROM
    pgagent.pga_jobsteplog As l INNER JOIN
    pgagent.pga_jobstep As s ON s.jstid = l.jsljstid INNER JOIN
    pgagent.pga_job As j ON j.jobid = s.jstjobid
WHERE jslstart > CURRENT_DATE
ORDER BY j.jobname, s.jstname, l.jslstart DESC;

We find this query essential for monitoring batch jobs because sometimes a job will report success even though it failed. pgAgent can’t always discern the success or failure of a shell script on the OS. The jsloutput field in the logs provides the shell output, which usually details what went wrong.

Warning

In some versions of pgAgent running on Windows, shell scripts often default to failed when they succeeded. If this happens, you should set the step status to ignore. This is a known bug that we hope will be fixed in a future release.