Replaying backups
Having a backup is pointless unless you have tried to actually replay it. Fortunately, this is easy to do. If you have created a plaintext backup, simply take the SQL file and execute it. The following example shows how that can be done:
psql your_db < your_file.sql
A plaintext backup is simply a text file containing everything. We can always simply replay a text file.
If you have decided on a custom format or directory format, you can use pg_restore to replay the backup. Additionally, pg_restore allows you to do all kinds of fancy things, such as replaying just part of a database. In most cases, however, you will simply replay the entire database. In this example, we will create an empty database and just replay a custom format dump:
[hs@linuxpc backup]$ createdb new_db [hs@linuxpc backup]$ pg_restore -d new_db -j 4 /tmp/dump.fc
Note that pg_restore will add data to an existing database. If your database is not empty, pg_restore might error out but continue.
Again, -j is used to throw more than one process. In this example, four cores are used to replay the data; however, this only works when more than one table is being replayed.
Important note
If you are using a directory format, you can simply pass the name of the directory instead of the file.
As far as performance is concerned, dumps are a good solution if you are working with small or medium amounts of data. There are two major downsides:
- We will get a snapshot, so everything since the last snapshot will be lost
- Rebuilding a dump from scratch is comparatively slow compared to binary copies because all of the indexes have to be rebuilt
We will take a look at binary backups in Chapter 10, Making Sense of Backups and Replication. Replaying backups is easy, but there is more to it than meets the eye. The following section handles global data. What does that mean?
