Database Administration - PostgreSQL 9 Administration - page 148

The most sensible way to perform major administrative tasks is to write a script to do what
you think is required. If you're unsure, you can always run the script on a system test server,
and then run it again on the production server once you're happy. Manically typing commands
against production database servers isn't wise. Worse, using an admin tool can lead to
serious issues, if that tool doesn't show you the SQL you're about to execute. If you haven't
dropped your first live table yet, don't worry; you will. Perhaps you might want to read the
chapter on Backups first, eh? Back it up using scripts.
Scripts are great because you can automate common tasks. No need to sit there with a
mouse, working your way through a hundred changes.
If you're drawn to the discussion about Command line vs. GUI, then my thoughts and reasons
are completely orthogonal to that. I want to encourage you to avoid errors and save time by
repetitive and automatic execution of small administration programs or scripts. If it were safe or
easy to do the equivalent of mouse movements in a script, then that would be an option; but it's
definitely not. The only viable way to write a repeatable script is by writing text SQL commands.
Which scripting tool is a more interesting debate. We talk about psql here because, if you've
got PostgreSQL, then you've got psql. So, we're on solid ground to provide examples that way.
On to the recipes! First, we start by looking at some scripting techniques that are valuable
with PostgreSQL. This will make you more accurate, more repeatable, and free up time for
other cool things.
Writing a script that either all succeeds
or all fails
Database administration often involves applying a coordinated set of changes to the
database. One of PostgreSQL's great strengths is the transaction system, where almost all
actions can be executed inside a transaction. This allows us to build a script that will either all
succeed or all fail, which can be critically important on a production system.
Transactions definitely apply to DDL (Data Definition Language), which refers to the set of
SQL commands used to define, modify, and delete database objects. The term DDL goes back
many years, though it persists because that subset is a useful short name for the commands
that most administrators need to execute: CREATE , ALTER , DROP , and so on.
How to do it...
The basic way to ensure that we get all commands successful or none at all is to literally wrap
your script into a transaction like the following:
BEGIN;
command 1;
command 2;