Recovering From a Backup

It is relatively easy to create a backup strategy for your PostgreSQL database. It is much more difficult to actually use those backups. Every database administrator should take the time to restore those backups and test them to make sure they will work as planned. Here are some considerations to take into account as you create your backup strategy:

Your primary database is gone and you have to restore to your hot standby.

Streaming replication is wonderful and it can really save your data. What is your strategy though? Do you trigger your hot standby slave to become your master database? If so, what does that mean for your applications? Will you have to go through a number of servers and configure them to talk to the slave? This seems an ideal situation for PgPool-II but many have discovered that it triggers too many false positives.

The best course of action depends upon your environment. Some may opt to never trigger the hot standby but instead just create a database dump and reload it into the original master once it is rebuilt. It will require setting up your log shipping again, but is quick and simple to do. The only downside is that your database will be offline while you rebuild things. In some environments, that won't work while others allow for a certain amount of down time.

There are a number of ways you can use your hot standby and it is important to have a plan long before you need to use it. Trying to decide the best course of action during the emergency is the worst thing you can do. Practice using your hot standby servers so you know how to trigger them into becoming the master and that your application can use them.

You are using pg_dump and need to reload an entire database.

The pg_dump utility is great for recreating data when a user accidentally deletes a table. What happens when someone deletes an entire database? It is important to remember that pg_dump does not store the CREATE DATABASE commands in the dump file and so you will have to have them handy in the event you need to restore an entire database. Furthermore, when you run the .SQL file back through the psql utility, you will want to make sure you are connected to the right database and not the default one. Otherwise your application may be looking in the wrong place for its data.

Performance Tuning

The Oracle relational database management system (RDBMS) has been around for a very long time. In the early days it came with a number of manuals. Version 4 had three small binders that fit in a nice box that was popular with software back in the 1980's. Version 5 replaced the binders with books that came with their own boxes. Version 6 seemed to come with its own bookshelf. I would have to say that my favorite version of the documentation came with the beta release of version 7 as there was an entire book devoted to performance tuning. All of the tips and tricks to tune the seemingly complex database software was in one volume. When the production documentation was released, that very important volume was embedded into one of the three larger books on database administration. I was sad to see it disappear and kept copious notes in my beta manual that I used with a fair number of production systems.

Performance tuning is a topic that applies to all database management system and PostgreSQL is no exception. There are a number of topics related to performance tuning that include:

• Performance Monitoring
• SQL Statement Optimization
• Proper Indexes
• Server Configuration
• Hardware Upgrades
• Data Partitioning

Amazingly, if you were to go back and look at the 5 major topics covered in that old Oracle7 Performance Tuning manual, the topics would be relatively similar to the first 5 here even though we are now dealing with PostgreSQL. Here is a quick summary of each topic.

Performance Monitoring

I have already covered monitoring PostgreSQL, but there are more things you can do when you want to monitor for performance.  PostgreSQL allows you to turn on monitoring statistics to collect information about your database cluster. Normally statistics are left turned off so as not to impede performance. Turning them on adds some overhead and so the best course of action is to turn them on during the testing phase of your application development, run real-world tests, analyze the information they provide, make appropriate changes, and turn statistics off before putting your application in production. Understanding what information is collected and how to use it is a critical tool in helping you optimize PostgreSQL for your environment.

SQL Statement Optimization

PostgreSQL uses a cost-based optimizer to come up with the fastest way to retrieve data for a query. Understanding how the optimizer works and how to influence it will help performance. You can also see how PostgreSQL will execute your query by prefacing it with EXPLAIN or EXPLAIN ANALYZE. Generally you will not need to tune your SQL queries but there are some exceptions.

Proper Indexes

Indexes provide a shortcut to the data stored in a table. A query can use an index that is generally much faster than doing a full-table scan provided an index exists for a specific column and that column is used without modification in the query's WHERE clause. There are situations where an index will slow a query down. They also increase the time it takes to add data. Making sure right mix indexes is important for performance.

Server Configuration

PostgreSQL provides the postgresql.conf file that allows you to configure it for your environment. There are a number of internal buffers and caches that can be made larger and increase performance so long as you have the hardware memory for them. Making them too large can decrease performance and limit the number of connections to your database.  As PostgreSQL generally comes configured to run on the smallest of servers, there are a lot of tweaks that can be made to the postgresql.conf file to help increase performance.

Hardware Upgrades

If you are planning a new database server installation, then you may want to start with hardware. Using more than one disk, increasing RAM, or multiple CPU's can all help increase performance. However it is listed last as you will best understand what upgrades are most beneficial after seeing how your application works with PostgreSQL.

Data Partitioning

One of the reasons that Oracle 7's beta documentation didn't include the topic of data partitioning is that it wasn't made available until version 8. It is something that has been available in PostgreSQL for a while and allows you to break your table into smaller chunks based on the values in a particular column or group of columns. The idea is that certain queries that leverage natural data boundaries will run faster because you only need to look at a small set of data. The database only needs to look in a specific partition instead of the entire table.

An Artificial Benchmark

PostgreSQL comes with an artificial benchmark tool called pgbench and it can be used to help you fine tune your database installation while you develop your application. It is most helpful as you modify your server configuration or make hardware changes but can be used with any aspect of performance tuning. Spend some time learning how to use it and feel free to play around with it so it mimics your own application. Those hours you spend tuning at the beginning will be well worth it.

The Power of UNION

The UNION clause is used to join two SELECT statements to create a single output of data. Now why would you want to do that? If you are pulling data from a single table, you can always just adjust the WHERE clause to include both conditions. What about when you are pulling data from more than one table? The UNION clause can help.

My first use of the UNION clause came as I was creating a cross-tab report. I was pulling data from multiple database tables and was able to massage the data so that I could use it with the UNION to create one set of output that could then be displayed in a dynamic spreadsheet-like report. When I was done, I had a 650-line SQL query with over 100 UNION clauses. Here is a simple example of how it works:

Suppose you have two tables: favorite_sports and favorite_hobbies. Never mind that nobody would ever create tables this way, but it helps make the example clear. The favorite_sports table has the following contents:

 id | name |     sport    
----+------+---------------
  1 | Matt | Skiing
  2 | Nate | Rock Climbing
  3 | Dave | Skiing

And the favorite_hobbies table has the following contents:

 id | name |   hobby  
----+------+-----------
  1 | Matt | Ham Radio

The table definitions don't have to match exactly to use the UNION operator, they just need to be somewhat similar. In this example, I used a varchar(20) for the sport column and varchar(25) for the hobby column. That works fine, just don't try to match up an integer with a string.

A simple example of the UNION clause would be:

SELECT * FROM favorite_sports
UNION
SELECT * FROM favorite_hobbies;

And would give the following results:

 id | name |     sport    
----+------+---------------
  1 | Matt | Ham Radio
  2 | Nate | Rock Climbing
  3 | Dave | Skiing
  1 | Matt | Skiing

Notice how we get all of the rows out of both tables. Unfortunately the column heading for the sport or hobby is "sport". We can fix that by using an alias as in the following query:

SELECT id, name, sport AS "sport/hobby" 
  FROM favorite_sports
UNION
SELECT * 
  FROM favorite_hobbies;

And gives us the following results:

 id | name |  sport/hobby 
----+------+---------------
  1 | Matt | Ham Radio
  2 | Nate | Rock Climbing
  3 | Dave | Skiing
  1 | Matt | Skiing

Notice the use of double quotes for the alias name. We generally use single quotes in SQL but that will generate a syntax error here. Now if we want to add a type column, it is as simple as running the following query:

SELECT id, name, sport AS "sport/hobby", 'sport' AS type
  FROM favorite_sports
UNION
SELECT *, 'hobby'
  FROM favorite_hobbies;

Which gives us the following results:

 id | name |  sport/hobby  | type 
----+------+---------------+-------
  1 | Matt | Ham Radio     | hobby
  1 | Matt | Skiing        | sport
  2 | Nate | Rock Climbing | sport
  3 | Dave | Skiing        | sport

Hopefully this gives you a concrete example of the UNION clause and helps you understand when to use it.

Monitoring PostgreSQL

PostgreSQL often does not require a lot of monitoring. For those developers creating applications or trying things out on their own system, it isn't required at all. Once you move your application into production, the database may or may not need to be monitored depending on how much traffic it sees. Production environments that see a lot of traffic and run close to system capacity need a bit more care.

PostgreSQL includes a number of system catalog tables that you can use to find out information about your database cluster.  I've always referred to them as Data Dictionary tables because that's what they would be called in Oracle and Oracle is the first database system I learned. PostgreSQL also includes a number of stored procedures or functions that combine the information in some of these tables and present the results in a more easily readable form. Monitoring PostgreSQL is done by querying these tables or running these functions. Here are some of the common ones that I have used to help monitor PostgreSQL:

Monitoring Current Database Commands

When things start slowing down and traffic backs up it is good to be able to see what queries or commands are currently running. Perhaps there is a query that really slows everything down or a large number of statements are being executed all at once. Looking at the pg_stat_activity view will show you what is running along with some important information about the statements running. If you describe the pg_stat_activity view using the \d command in psql, you get the following output:

           View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Modifiers
------------------+--------------------------+-----------
 datid            | oid                      |
 datname          | name                     |
 pid              | integer                  |
 usesysid         | oid                      |
 usename          | name                     |
 application_name | text                     |
 client_addr      | inet                     |
 client_hostname  | text                     |
 client_port      | integer                  |
 backend_start    | timestamp with time zone |
 xact_start       | timestamp with time zone |
 query_start      | timestamp with time zone |
 state_change     | timestamp with time zone |
 waiting          | boolean                  |
 state            | text                     |
 query            | text                     |
The last column in the view is named query but will also show other running SQL statements like INSERTS or DELETES. Other columns of note include the pid column and is the process id of the statement. This allows you to go out to the operating system and use tools like ps to find more information about a specific statement that is running.

Running the command on a development server with no current traffic gives the following results:

 datid | datname  | pid  | usesysid | usename  | application_name | client_addr | client_hostname | clie
nt_port |         backend_start         |          xact_start           |          query_start         
|         state_change         | waiting | state  |              query             
-------+----------+------+----------+----------+------------------+-------------+-----------------+-----
--------+-------------------------------+-------------------------------+-------------------------------
+------------------------------+---------+--------+---------------------------------
 12914 | postgres | 3389 |    16391 | mbennett | psql             |             |                 |    
     -1 | 2014-02-19 09:05:51.377371-07 | 2014-02-19 09:10:28.021888-07 | 2014-02-19 09:10:28.021888-07
| 2014-02-19 09:10:28.02189-07 | f       | active | select * from pg_stat_activity;
(1 row)

Listing everything in a single line makes it rather difficult to read but this gives you an idea of what your output will look like. For a larger server running in a high-traffic production environment, you will get a number of rows output and so you may only want to see those columns to help you diagnose problems. They may include pid, application_name, query_start, and query.

Calculating the Size of Your Database

Unix and Linux do not behave very well when you fill the disk. Therefore it is very important to make sure you keep a close eye on how large your database is to ensure you don't run out of space. There is a database function named pg_database_size to help see the size of your database. You just need to run the following query:

SELECT pg_database_size('database_name');

PostgreSQL can have multiple  databases running on the same server. You can get a list of all database by doing a \l in psql. Be sure to replace the string database_name with the name of your actual database. The result is output in bytes.

Calculating the Size of a Specific Table

Knowing the size of your database is helpful to keep from running out of disk space. Knowing the size of a specific table will help with increasing performance. Once a table grows above a specific size, indexes will help reduce query times. You may also want to consider partitioning for larger tables. Finding the table size can be done using the pg_total_relation_size function and can be done using the follow command:

SELECT pg_total_relation_size('table_name');

Be sure to replace table_name with the actual name of the table for which you are interested. Once again the result is in total bytes.

Finding the Largest Tables in Your Database

The pg_total_relation_size function is good if you know which of your tables are large. What do you do if you are new to a project and have not had time to have someone explain which tables are your largest? How do you find that out? There is a view called pg_class that contains that information and finding out the top 10 largest tables is as simple as using the following query:

SELECT relname, relpages
  FROM pg_class
 ORDER BY relpages DESC
 LIMIT 10;

Changing the LIMIT clause will allow you to see more or fewer tables. Leaving it off completely shows all of the tables in your database including the system catalog or data dictionary tables and views. The relpages column shows how many database pages are used by each of the tables and so it is a relative size as opposed to the number of bytes. You can use the pg_total_relation_size function to see the number of bytes used by the table, as shown above.

Checking for Uncommitted Transaction Locks

In high-volume transaction environments you there is a chance that you will run into deadlocks. Being able to look at the pg_locks view will help you figure out locking issues. Describing the view with the \d command in psql gives the following:

        View "pg_catalog.pg_locks"
       Column       |   Type   | Modifiers
--------------------+----------+-----------
 locktype           | text     |
 database           | oid      |
 relation           | oid      |
 page               | integer  |
 tuple              | smallint |
 virtualxid         | text     |
 transactionid      | xid      |
 classid            | oid      |
 objid              | oid      |
 objsubid           | smallint |
 virtualtransaction | text     |
 pid                | integer  |
 mode               | text     |
 granted            | boolean  |
 fastpath           | boolean  |

The column we are most interested in viewing is granted. If the value of granted is true, the lock has been granted. If the value is false, the query is waiting for a lock and provides a clue as to what might be causing a problem.

Using pg_dump and pg_dumpall for Database Backups

The pg_dump and pg_dumpall utilities are great for moving data between databases and for making quick backups. While log shipping may have you back in business after a database failure, it doesn't protect you against a user error where pg_dump and pg_dumpall will. I actually like to set up both nightly pg_dump runs and log shipping on production databases.

There really isn't a need to keep pg_dump files around for a long time and so it is helpful to implement some kind of automatic backup rotation. Otherwise you will constantly have to monitor space on the filesystem where you keep your backups. I like to implement the following strategy for my database dumps:

1. Perform a nightly backup and save them for a week
2. Keep a copy of each Monday backup for a month
3. Keep a copy of each first day of the month backup for a year

I figure that if you don't need your backup in a year's time, there is no need to keep a copy of lying around. As I use Linux for all of my production servers, I use cron to schedule all of my backups. My crontab has the following 3 entries for the postgres user:

15 1 * * * <PATH>/nightly_backup.sh >> <PATH>/backup.log 2>&1
15 2 * * 1 <PATH>/weeksave.sh >> <PATH>/backup.log 2>&1
15 3 1 * * <PATH>/monthsave.sh >> <PATH>/backup.log 2>&1

This runs the nightly backup script at 1:15 every day, so it is more of a morning backup instead of a nightly one. You can modify the hour of the backup to meet your needs. I also allow an hour for the backup to complete before I try saving a copy for the weekly backup. You may need to adjust your timing accordingly.

I then have three simple scripts: nightly_backup.sh, weeksave.sh, and monthsave.sh. Here are the contents of each:

nightly_backup.sh
#!/bin/sh
/usr/bin/pg_dumpall > <PATH>/db_backup_$(date +%A).sql

weeksave.sh
#!/bin/sh
let WEEK=($(date +%d)-1)/7+1
/bin/cp <PATH>/db_backup_$(date +%A).sql 
        <PATH>/db_backup_week$WEEK.sql
# the previous 2 lines should all be on 1 line

monthsave.sh
#!/bin/sh
/bin/cp <PATH>/db_backup_$(date +%A).sql
        <PATH>/db_backup_$(date +%B).sql
# the previous 2 lines should all be on 1 line
# Change %B to %B%Y if you want to keep monthly backups
#   forever

You will want to replace all <PATH> references to the paths used in your environment.

The key to all of the scripts is to use dates as part of the file names for each of the backup files. The nightly backup includes the day of the week so that when Monday rolls around again, it overwrites the previous Monday's backup. The weekly backup uses Monday's backup and just copies the file so that it has week1, week2, week3, week4, or week5 in the name.

Notice the comment in the monthsave.sh script that shows how to keep your monthly backups forever. It just adds the year to the month used in the file name.

Creating a Filesystem Backup

One simple way of backing up your PostgreSQL database is to take a filesystem backup. Perhaps you have a system that backs up all of your servers on a nightly basis and you just want to include your PostgreSQL database. Unfortunately there is one condition that must be met before you can use this method safely: The database must be shut down. Some environments allow you to shut the database down on a nightly basis and that will allow you make a filesystem backup.

Doing so is as simple as using a utility like tar to backup the data directory as in the following command:

tar -cvzf my_backup.tgz ~/data

The "z" option tells tar to compress the resulting file so it takes less space.

If you are running on hardware that allows you to take a consistent snapshot snapshot of the filesystem, you can do so with a running PostgreSQL database. Should you need to restore from the backup, PostgreSQL will think that it was shutdown improperly and  rerun the WAL files, which is not a problem.

If you implement streaming replication or log shipping, you can set the database into a backup mode that works with write-ahead logging (WAL) to ensure your database files are kept in sync. If you try to run the SELECT pg_start_backup('x'); command on a database that does not have log shipping enabled, you will get an error indicating that WAL archiving is not active.

Configuring a Hot Standby Server Using Log Shipping

Log Shipping has been around with PostgreSQL since before version 8.4. Originally the secondary or slave servers could not be used for anything unless something went wrong with the master and then one could be triggered to become the master or primary. This is what is referred to as a warm standby. It is almost ready to use and doesn't require time to recover a database backup. Version 9.0 introduced hot-standby servers with log shipping in that the slave servers can now be used as read-only copies of the data. This allows them to be used as reporting servers which can offload some processing of the primary database server.

Log shipping or streaming replication in PostgreSQL 9.x is near real-time, so it takes less than a second for a change made in the master database to propagate to the replicated database. Here are the steps necessary to configure streaming replication on Linux:

1. Set up two identical database server machines. Make sure to disable the firewall on both machines. For the purposes of this tutorial, one of the machines is named PG9master and the other PG9slave. Their names should indicate which one is which.

2. Install the proper PostgreSQL packages for your given distribution on both servers. RedHat, Fedora, and CentOS require postgresql-libs, postgresql-server, and postgresql-contrib. Ubuntu and Debian require postgresql-9.x and postgresql-contrib-9.x, where the x can be 0, 1, 2, or 3 (9.3 is the latest version of PostgreSQL at the time of this writing).

3. Set up some form of file transfer from PG9master to PG9slave. The example in this tutorial assumes that scp will be used. You will need to generate public and private keys that allow scp to transfer files without the need for password exchanges. As the postgres user on PG9master, run the following:

ssh-keygen -t rsa

Don't enter a passphrase. As it asks for it twice, be sure to just hit return twice. Your key files will be saved in the .ssh directory. You will need to copy id_rsa.pub to the PG9slave server using the following command from PG9master:

scp ~/.ssh/id_rsa.pub PG9slave:.ssh/authorized_keys

You will be asked to enter a password for the postgres user on PG9slave. Once the scp is finished, you can test not needing the password by using ssh to log into PG9slave from PG9master using the following:

ssh PG9slave

You should not need to enter the password.

4. You will need to create a directory where log files can be copied. On PG9slave, create a directory called:

~/master_logs

5. You are now ready to edit the postgresql.conf file on PG9master. If you are running one of the RedHat flavors of Linux, it will be located underneath the postgres user's home directory. If you are running Ubuntu, it will be located under the /etc/postgresql directory. Adjust the following parameters:

wal_level = hot_standby
archive_mode = on
archive_command = 'scp %p PG9slave:master_logs/%f'
listen_addresses = '*'
archive_timeout = '120     #2 minute window if streaming fails
max_wal_senders = 1        #assume a single slave

6. Now edit the pg_hba.conf file on PG9master. It will be found in the same directory as the postgresql.conf file. Add the following being sure to replace <IP of PG9slave> with the actual IP address of PG9slave:

# TYPE   DATABASE     USER   CIDR-ADDRESS         METHOD
host     replication  pgrep  <IP of PG9slave>/32  md5

7. If you are running the RedHat flavors of Linux, start the PostgreSQL server on PG9master using the following command as the root user:

/etc/init.d/postgresql-9.x start

Make sure to replace the .x with the correct dot release of PostgreSQL that you are using. If you are using the Ubuntu flavor of Linux, you will need to reload PostgreSQL's postgresql.conf file by running the following command as the root user:

service postgresql restart

8. Create a database user on PG9master named pgrep by going into psql and running the following command:

CREATE USER pgrep WITH superuser login PASSWORD 'pgrep_pass';

9. You are now ready to make a filesystem backup of the master database and copy it to the slave. On PG9master, run psql and issue the following command:

SELECT pg_start_backup('backup');

Exit psql using \q and make a copy of the data directory using the following:

cd 9.x
tar -cvf db_backup.tar data

Be sure to replace the .x with the dot version of PostgreSQL you are using. When the backup completes, go back into psql and issue the following command:

SELECT pg_stop_backup();

Copy the tar file to the slave machine using the following scp command:

scp db_backup.tar PG9slave:

10. Restore the master database on the slave. Log into PG9slave as the postgres user and issue the following commands:

cd 9.x
mv ../db_backup.tar .
tar -xvf db_backup.tar
cd data
rm postmaster.pid
rm -rf pg_xlog/*

Being sure to replace .x with the dot version of PostgreSQL you are using.

11. Edit the postgresql.conf file on PG9slave and set the following parameter:

hot_standby = on

If you are running the RedHat flavor of Linux, you will also need to comment out the following parameters that you brought over when you moved the postgresql.conf file from PG9master:

archive_mode
archive_command
archive_timeout

12. Create the recovery.conf file on PG9slave. If you are running the RedHat flavor of Linux, it will be in the same directory as the postgresql.conf file. If you are running Ubuntu, it will need to go in the data directory. It is a new file that should contain the following information and assumes you are running the RedHat flavor of Linux:

standby_mode = 'on'
primary_conninfo = 'host=PG9master port=5432 user=pgrep password = 'pgrep_pass'
restore_command = 'cp -i /var/lib/pgsql/master_logs/%f %p'
archive_cleanup_command = 'pg_archivecleanup /var/lib/pgsql/master_logs %r'
trigger_file = '/tmp/stop_pg_streaming.tmp'

If you are running Ubuntu Linux, you will need to replace the paths in the restore and archive cleanup commands with those used by Ubuntu.

Be sure to make sure that the program pg_archivecleanup is in the postgres user's path. If not, you can symbolically link it. On RedHat you would use:

ln -s /usr/pgsql-9.x/bin/pg_archivecleanup /usr/bin/pg_archivecleanup

On Ubuntu, you would use:

ln -s /usr/lib/postgresql/9.x/bin/pg_archivecleanup /usr/bin/pg_archivecleanup

Again, be sure to replace the .x with the appropriate dot release of Postgresql that you are using.

13. Start the database on PG9slave as root using the following commands. On RedHat use:

/etc/init.d/postgresql-9.x start

Be sure to replace the .x with the point release of PostgreSQL you are using. On Ubuntu, use:

service postgresql start

14. Test everything out. The psql program will work on both systems. You shold be able to create a table on PG9master and have it immediately show up on PG9slave. If there is a delay of 2 minutes, then something is amiss with streaming replication.

Troubleshooting

Always check the log files. As usual RedHat flavors of Linux will store them in different places than Ubuntu. If there is a problem though, you should get some hint of what has gone wrong from the log file.

Try logging into the PG9master database from PG9slave using:

psql -h PG9master -U gprep postgres

If you get an error, then streaming replication is not working either.

Make sure that the "wal sender process" is runnning on PG9master by doing the following:

ps aux | grep post

Also make sure there is a "wal receiver process" running on PG9slave.