Preface

Welcome to Instant PostgreSQL Backup and Restore How-to! These days, database-driven websites and applications are everywhere. Thanks to its free and open nature, and in no small part due to its full ACID guarantees, PostgreSQL is now a popular RDBMS used to fill the role of data storage in these next-generation apps. But with great power comes great responsibility. For a conscientious DBA, that means robust and sometimes numerous backups.

That's where we come in. PostgreSQL supplies a good toolset and documentation for securing your data from catastrophic loss. But this documentation is meant to be exhaustive, covering every known variant of using the backup tools. In a hurry? A new PostgreSQL DBA? Wading through all of that information might be overwhelming. It's there for long-term reference, not immediate or simple solutions.

This How-to fills the missing role of simple explanations for managing backups. We have a small recipe for each major variant of backup, and how to restore it. That's all. Just some commands you might type, and some exposition on how it works. No matter the size of your installation, something in this book should address your needs.

What this book covers

Getting a basic export gives an easy solution for small databases. All good things have a beginning. Export your entire database installation with a single command. Compress it and save it for later.

Partial database exports covers the tools that offer finer-grained control over what data you receive, making data migrations and copies possible. Larger databases are often backed up piece by piece.

Restoring a database export, combined with the previous recipe, has a complete solution for backup management. What to do with the backups you've made so far, and how to import them quickly is taken care of by backup management.

Obtaining a binary backup will cover the easiest way to get an exact copy of the database files for things such as warm or hot standby, or streaming replication, and open up several new possibilities. More complex systems need that copy of the database.

Stepping into TAR backups will cover parallel compression, so your backups become faster than what the default tools can provide.tar files are a Unix staple. They can also produce binary backups with the help of a couple of database commands.

Taking Snapshots will cover basic filesystem snapshots to freeze data files in place, and prevent partial or corrupt backups. Sometimes the filesystem itself can aid your backup efforts.

Synchronize backup servers will cover the basic tools to copy the data to a new server, and how to keep the data in sync afterwards. Larger installations should have multiple online copies of the database. The first step is to copy the data to a new server.

Restoring a binary backup will cover the methods of restoring the data files. With all those methods of backing up data files, we also need to restore them properly. It's not as easy as extracting the files and starting the database, but it's close.

Point in time recovery will tell you how to restore your data to a specific point in its history as if any problem never occurred. More complex restore situations may require stopping the restore before some catastrophic problem or data mismanagement.

Warm and hot standby will tell you how to make a backup of a complete online server. In an enterprise, having a complete online server can save millions in worst-case scenarios. It's the backup that can take over in case of emergencies.

Streaming replication covers how to use your backup(s) as secondary servers. The ultimate online backup is one that is never truly out of sync. Every transaction is copied when completed, and in some cases, guaranteed to exist on the remote server.

What you need for this book

This How-to is focused on Unix systems with a primary Linux focus. The LAMP (Linux, Apache, MySQL, PHP) stack of services is often modified to LAPP (Linux, Apache, PostgreSQL, PHP). This type of server makes up a large majority of hobbyist usage and several Enterprise-level systems. We highly recommend you to have a virtual machine or development system running a recent copy of Debian, Ubuntu, Red Hat Enterprise Linux, or a variant such as CentOS or Scientific Linux.

You will also need a copy of PostgreSQL. Though most Linux distributions include PostgreSQL packages, they are often one or more versions behind. So we recommend going to the PostgreSQL website, and downloading a copy that matches the architecture you have chosen. You can find these at the following URL:

http://www.postgresql.org/download/

Be sure to include the contrib packages in your installation. They include helpful utilities such as a benchmark suite, which we will be using in our recipes to generate sample data.

If you have a BSD system, you should still be able to follow along. Some commands may need to be altered to run properly on BSD, so be sure to understand the intent before executing them. All commands have been confirmed to work on BASH and recent GNU tools.

Who this book is for

This book is for anyone (and everyone) running PostgreSQL on a server. Your data is important, as losing it without proper backups can mean long nights, lost business, and in extreme cases, shutting down the company.

PostgreSQL DBAs are encouraged to use this resource for the advanced recipes.

Companies without a DBA may have a greater need, since the remaining system administrators and developers probably lack PostgreSQL expertise. If you are such a developer or system administrator, we highly recommend you to use the techniques we describe to prevent unnecessary risk. Who knows, you may even have a future as a PostgreSQL DBA!

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: "We can use the createdb utility to create an empty database for our backup experimentation."

A block of code is set as follows:

for f in $(find /db/pg_tblspc); do
    d=$(readlink -f $f)
    n=$(basename $f)
    tar -C $d . -c | pigz -p 8 > /backup/$n.tar.gz
done

Any command-line input or output is written as follows:

$> export PGUSER=postgres
$> createdb sample
$> pgbench -i -s 50 sample

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "Clicking the Next button moves you to the next screen".

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to <[e-mail]>, and mention the book title via the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at <[e-mail]> with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions

You can contact us at <[e-mail]> if you are having a problem with any aspect of the book, and we will do our best to address it.