Backup Strategies and Tools for PostgreSQL
| There are several backup strategies that can be used in combination with PostgreSQL. Let's take a look at some of them. pg_dump and pg_dumploThe most important tool when you have to back up your PostgreSQL server is pg_dump . If you want to get a description of pg_dump , you can use pg_dump --help : bash-2.04$ pg_dump --help pg_dump dumps a database as a text file. Usage: pg_dump [options] dbname Options: -a, --data-only dump out only the data, not the schema -c, --clean clean (drop) schema prior to create -d, --inserts dump data as INSERT, rather than COPY, commands -D, --attribute-inserts dump data as INSERT commands with attribute names -h, --host <hostname> server host name -i, --ignore-version proceed when database version != pg_dump version -n, --no-quotes suppress most quotes around identifiers -N, --quotes enable most quotes around identifiers -o, --oids dump object ids (oids) -p, --port <port> server port number -s, --schema-only dump out only the schema, no data -t, --table <table> dump for this table only -u, --password use password authentication -v, --verbose verbose -x, --no-acl do not dump ACL's (grant/revoke) If no database name is not supplied, then the PGDATABASE environment variable value is used. Report bugs to <pgsql-bugs@postgresql.org>. As you can see in the description, pg_dump is a powerful tool. Remember that pg_dump writes data to standard output. You have to take this into consideration when backing up data. Here is an example of how you can use pg_dump pg_dump person > backupfile.sql We have dumped the database called person and redirected the output to backupfile.sql . Let's have a look at what backfile.sql contains: \connect - postgres CREATE TABLE "programmers" ( "name" character varying(50) ); CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/local/postgres-7.0.3/lib//plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; COPY "programmers" FROM stdin; Donald Becker Alexandre Julliard \. The first line tells the database that the following operations will be done as user postgres . Then a table is created. The next two lines tell us that the database supports PL/pgSQL; the database creates a call handler function. After the data structure and the call handlers have been created, the actual data is listed in the COPY command. Recovering a dumped database is an easy task. Assume that we want to insert the data and the data structure in file backupfile.sql into a database called people: bash-2.04$ createdb people CREATE DATABASE bash-2.04$ psql people < backupfile.sql You are now connected as new user postgres. CREATE CREATE CREATE First we use the createdb command to create the database. Then we start psql in batch mode and insert the file into the database. The < operator of the shell is used to transfer the data in backupfile.sql to psql . We can also achieve the same result with the help of the cat command and a pipe ( ): cat backupfile.sql psql people Some Unix gurus say that the cat command is absolutely useless. It is certainly true that a Unix shell without the cat command provides the same power as a system featuring cat . You decide how you want to insert data. pg_dump is sometimes used to dump the data structure only, which is useful when you want to set up multiple databases with the same tables, sequences, views, and other components . You can dump the structure of a database without data to a file. This can easily be done with the -s flag: pg_dump -s people > /tmp/structure.sql structure.sql contains data now: \connect - postgres CREATE TABLE "programmers" ( "name" character varying(50) ); CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/local/postgres-7.0.3/lib//plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; If you dump large databases, the output of pg_dump will be a very large file. On Unix systems, backups are very often stored as compressed tar archives. A tar archive is a comfortable way of storing data and can easily be compressed with tools such as gzip or compress . People use tar archives around the world, so it is easy to exchange files. When surfing through software archives on an FTP server, you might have noticed that nearly all Unix programs available for download have the extension .tar.gz. The problem with tar archives is that you cannot easily dump a database directly into a tar archive without dumping the data to a file first, which takes a lot of space. The best thing to get around the problem is to use gzip without tar : pg_dump people gzip -cv > people.gz -c makes gzip write the compressed output to standard output. -v is not necessary, but gives you a little information about the compression process. The whole result (standard output) is now redirected to a file. If you want to check whether people.gz has been written correctly, you can use zcat : bash-2.04$ zcat people.gz \connect - postgres CREATE TABLE "programmers" ( "name" character varying(50) ); CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/local/postgres-7.0.3/lib//plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; COPY "programmers" FROM stdin; Donald Becker Alexandre Julliard \. zcat uncompresses the file on-the-fly and writes the output to standard output. You can also use gunzip"" : zcat people.gz psql people gunzip -c people.gz psql people Many PostgreSQL environments consist of many databases stored on more than just one server. In such environments, you can manage the backup strategy of all servers on one machine by using pg_dump network functions. Simply define the host you want to connect to ( -h ) and the port the remote server is listening to ( -p ) in your pg_dump command. If you decide to back up your server remotely this way, keep in mind that all data you want to back up has to be transferred over the network. The use of network connections is most likely slower than dumping data to a hard disk on the machine that is backed up. pg_dump is useful and easy to use, although some important features are still missing. Many commercial database developers should consider implementing a similar tool. The output of pg_dump is, by default, SQL, and can therefore be converted easily into every other data format. Dumping binary data is not possible with pg_dump . This is a weak spot of pg_dump , because it is impossible to back up binary data comfortably. Thanks to Karel Zak (Czech Republic), a powerful tool can be found in the contributed directory of PostgreSQL's source code. The tool is called pg_dumplo and can be used to back up binary data. The following is an overview of pg_dumplo : [hs@duron pg_dumplo]$ ./pg_dumplo --help pg_dumplo 7.1.0 - PostgreSQL large objects dump pg_dumplo [option] -h --help this help -u --user=<username> username for connection to server -p --password=<password> password for connection to server -d --db=<database> database name -t --host=<hostname> server hostname -s --space=<dir> directory with dump tree (for export/import) -i --import import large obj dump tree to DB -e --export export (dump) large obj to dump tree -l <table.attr ...> dump attribute (columns) with LO to dump tree -a --all dump all LO in DB (default) -r --remove if is set '-i' try remove old LO -q --quiet run quietly -w --show not dump, but show all LO in DB Example (dump): pg_dumplo -d my_db -s /my_dump/dir -l t1.a t1.b t2.a pg_dumplo -a -d my_db -s /my_dump/dir Example (import): pg_dumplo -i -d my_db -s /my_dump/dir Example (show): pg_dumplo -w -d my_db - Note: * option '-l' must be last option! * option '-i' without option '-r' make new large obj in DB not rewrite old, the '-i' UPDATE oid numbers in table.attr only! * if is not set option -s, the pg_dumplo use $PWD You can see that this tool is easy to use. If you want to find out more about this software, check out the readme file in contrib /pg_dumplo. For backing up an entire database cluster, PostgreSQL offers a program called pg_dumpall. Using pg_dumpall is easier than backing up individual databases, because everything can be backed up in one file. Because pg_dumpall produces ASCII files, the data is portable. Let's look at the syntax overview of pg_dumpall: [hs@duron pg_dumplo]$ pg_dumpall --help pg_dumpall extracts a PostgreSQL database cluster into an SQL script file. Usage: pg_dumpall [ options... ] Options: -c, --clean Clean (drop) schema prior to create -g, --globals-only Only dump global objects, no databases -h, --host=HOSTNAME Server host name -p, --port=PORT Server port number -U, --username=NAME Connect as specified database user -W, --password Force password prompts (should happen automatically) Any extra options will be passed to pg_dump. The dump will be written to the standard output. Report bugs to <pgsql-bugs@postgresql.org>. The list of options provided by pg_dumpall is not as long as the list of options provided by pg_dump, because it pg_dump _all cannot dump individual tables. pg_dump all is used only for dumping entire database clusters. Backup ServerOne way of storing your backup files is to use a backup server. Backup servers are easy to handle, especially when dealing with large amounts of data. The backup server and the production server can be connected using a network file system ”such as NFS, SMB, or Coda ”but FTP or SCP can also be used. In the case of a file system (NFS, SMB, or Coda), dump your database to a file that is physically stored on the remote machine. Make sure that the remote file system is mounted correctly. If you back up your database, we recommend that the name of the backup files contains a timestamp. This can be achieved with a shell script like the following: #!/bin/sh TIMESTAMP=`date +%Y%m%d"_"%H%M` pg_dump people gzip > backup${ TIMESTAMP} .sql.gz The dump of the database will be compressed using gzip , and the name of the backup file will contain the word backup plus the date in ISO format ( YYYYMMDD ). The ISO format is, in this case, the best one because sorting by date can easily be done with a sort command. Europeans sometimes write dates in this format: DD.MM.YYYY. If you have multiple files using that format, it is rather difficult to find the most recent or the oldest file. If you write the date in the ISO format, the result of a simple sort command (Unix shell) is a result that is sorted by date (old to new). Here is an example of a filename using ISO date format: backup20010417_1915.sql.gz As you can see, the file was created on April 17, 2001. Some of you might think that storing the date within the filename is redundant since the file system already stores the date. This is true, but what if someone destroys the system timestamp by performing operations on the file? It is more secure to have the date in the filename ”it can save you a lot of time. In many network environments, the machines are not connected via a network file system. If you have to back up your machine over the Internet it is useful to encrypt the data before you transfer it. Secure network transfers can be achieved in many ways. You can copy your data using an SSH tunnel or you can use the secure copy command ( scp ). scp copies files between hosts on a network. Normally scp asks for a password before transmitting the data. Because backups should work automatically, inserting the password is not a suitable solution. Check out the man pages of ssh, ssh-agent, and ssh-add to learn how you can get around typing the password every time (www.openssh.org). Network file systems, such as NFS, are another way of connecting your backup server to the production server. Remote file systems can easily be mounted and used like local disc space. Check out the documentation about the network file system you want to use to find out more. Jukeboxes and Basic Tape OperationsAs mentioned earlier, jukeboxes are a useful hardware component. In general, jukeboxes consist of an array of tapes (usually 6 or more) that can be loaded and unloaded with the help of software. The advantage of a jukebox is that changing the tapes can be done by the jukebox itself and therefore no user is needed. In this section, you explore basic tape operations on Unix systems and a backup solution using tapes and PostgreSQL. We will also share a brief insight of how tapes can be handled on a Unix system, using some powerful Unix commands. In the early days, tapes were the most important medium for storing data and performing backups. Although times have changed, tapes still play an important role. People still rely on streamers, especially in mainframe environments. PostgreSQL is no mainframe database, but if the amount of data that has to be saved increases , the importance of tapes also grows because it is a rather cheap and portable method to back up huge amounts of data. Jukeboxes are not common on Windows machines, although some programs, such as Backup Exec, are capable of handling tapes. In general, tapes don't need a file system on Unix systems. They are simply treated as a "special" file. A tape drive appears as a list of devices. We will explain this in detail a little later. The most important Unix commands to work with these devices are
On AIX 3 systems, the device is called "/dev/rmt0". The device can be accessed like a file. If you want to create a tar- archive on the tape containing a dumped PostgreSQL database you can simply use the following: tar cvfz /dev/st0 datadump* The system creates a tar archive that contains all files starting with datadump . If the tape seems to work slowly, you can set the block size to 1024 using the mt command: mt -f /dev/st0 setblk 1024 This should significantly speed up the process. In the example, we wrote the tar archive to /dev/st0 . Because devices are treated like files, the system starts to write at the beginning of the tape. This seems very obvious, because tar also starts to write at the beginning of the file that has to be created. If you want to write a second file on the tape, you will face the following situation: After you have written the first tar archive on the tape, the tape is rewound automatically so that the next call of tar can start at the beginning of the file (in this case at the beginning of the tape). The first tar archive would be destroyed immediately by the second tar command. This is a very common error and hardly anyone would think of the problem the first time using tapes. Remember that you have to check whether your backup has been created successfully ”if you do, you will find an error before facing a critical situation. If you want to add a second archive to the data on the tape, you have to use the mt command to find the correct position on the tape to start writing. In the following example, we use the second face of /dev/st0 called /dev/nst0. /dev/nst0 is the same as /dev/st0 but does not rewind (/dev/rmt0.1 on AIX). The correct position to start writing on the table is immediately after the first file: mt -f /dev/nst0 fsf 1 fsf (forward space count files) brings us to file number two. The tape is now positioned on the first block of the next file and we can start writing using a tar command, for instance: tar cvfz /dev/nst0 backup* Restoring data from a tape works nearly the same way as writing. Go to the directory where you want the data to be restored, find the correct position on the tape (use mt ), and use the following: tar xvfz /dev/st0 Note The z flag in every tar command we use in this section is used to perform compression and decompression using gzip . The z flag is mostly used on Linux systems. If you are using a version of Unix that does not support gzip in combination with tar , you can either install the required GNU tools or simply use a workaround with the help of pipes. Systems using the GNU version of tar support the z flag. Let's look at a backup system that creates backups with the help of a jukebox. Assume that the amount of data you have to store is small enough to fit on one tape. Here is a short shell script you can use to save your data: #!/bin/sh # Load the next tape mtx -f /dev/st0 next # Dump data, compress it and write it to /dev/st0 pg_dump yourdb gzip > /dev/st0 Simply start the script with the help of cron. First we unload the drive and load the next tape in sequence. If the drive is empty, we load the first tape into the drive (this is done by the next command). Finally, we dump the data and compress it with gzip before writing it to the tape. Saving Large Amounts of Data on Multiple TapesThe backup strategy described in the last section works pretty well with data that fits on one tape. If the amount of data increases, you have to find a different solution for the problem if you still want to perform full backups. One way is to use more than one tape drive. The most important point when performing full backups is to find a way to perform just one dump. If you perform multiple (smaller) dumps (for example, one table after the other) to save a database, you might find yourself in deep trouble. What happens if you use foreign keys and people change the data during the dumps? This might lead to problems with data integrity. A foreign key used in one table might already be deleted in another table. Combining your dumps to one correct database might become extremely difficult if not impractical . A dump of your database must therefore be done at once ”but how can we store the data? One easy solution would be to dump the database to disk and use a simple split command to create many smaller files that can easily be stored on a tape: pg_dump person split -l 3 - prefix_ This creates a set of files: bash-2.04$ ls -l insgesamt 16 -rw-rw-r-- 1 postgres postgres 79 Apr 18 21:50 prefix_aa -rw-rw-r-- 1 postgres postgres 222 Apr 18 21:50 prefix_ab -rw-rw-r-- 1 postgres postgres 64 Apr 18 21:50 prefix_ac -rw-rw-r-- 1 postgres postgres 3 Apr 18 21:50 prefix_ad This command stores three lines per file and uses prefix_ as the prefix for the files created by split . Lines might not be a good choice for splitting a file because lines can have variable length. Bytes can, in some cases, be better (see man split for further information). Usually, split is not used for splitting a file by lines. For backup, it is more useful to tell split to start a new file after a predefined number of bytes. This makes sure that all backup files have the same size. Defining the size in bytes can be done by using the -b flag. We can save the files to tape by using a simple tar command now, but how can we restore the files? We can write a one-line shell script that restores the data and inserts the values into the database called person : for i in `ls sort`; do cat $i; done psql person We process every file returned by the ls command (the sort command is redundant; it is just for better understanding) in ascending order. Note Mind the order of the files. We perform a cat on each of the files and pipe the whole result to psql, which inserts the data into the database. We simply stick the files together into one stream of data again. In many cases, you won't have the disk space to perform an operation like that. How can you use all tapes, one after the other, to back up the data? If you take a closer look at the filenames created by the split command, you can see that the files are consecutively numbered using letters . If you have multiple tape drives available, the devices assigned to the driver are also consecutively numbered ”but not the same way. SCSI tapes usually start with st0 and continue with st1, st2, st3, and so on. split starts with aa and continues with ab , ac , and so on (if the number of files is higher, split uses more than two letters). It would be nice to have devices and files numbered the same way. Well, that's easy. Simply create hard links for your devices that have the required name. Here is an example: ln /dev/st0 /dev/st0aa st0aa is now an alias for st0. You can create a link for every tape drive now. Saving for data on the array of tape drives is easy: pg_dump person split -l 3 - /dev/st0 Dump the data, split it, and use the devices as the files for the result. Note The - specifies that we want to take input from standard input. If we omitted the - , the next parameter we provide ( /dev/st0 ) would be used for input. Many of you might find the algorithm just described is not an everyday solution. It is not supposed to be. This example is taken from a real-world scenario, the solution we implemented to back up the PostgreSQL server in our company (see http://postgres.cybertec.at). It works well for us and recovery does also. We have tested this several times, of course. Thanks to PostgreSQL, Debian, and RedHat, we have never had to recover the database because of problems with the operating system or the database. JournalingJournaling and logging are two useful steps when dealing with any kind of system or application. An application can be fully debugged only when you know perfectly well what is going on inside the software. Logging is not only useful for debugging purposes; it can be very handy for analyzing and backup. Assume a situation where PostgreSQL is used as the database for an online shop. The shopping cart of a user is stored in the database and updated by the application every time the customer adds or removes a product or leaves the shop. It might be interesting for you to find out which products are added or removed from the shopping cart while the user is shopping. A logging table can offer significant advantage over log files because data in a database can be processed much easier. Logging can also be used for backup purposes. A backup application built on logging will be something like incremental backup, and that's the way you can use it: snapshot + journal = current status Create a database on a separate machine that is used for journaling only, and create an entry for every transaction that has been completed on the production system and assign a unique id to it. Every time you dump the production database, you have to make sure that you save the id of the last transaction completed on the server for recovery purposes. When designing a solution such as we have described, you have to keep PostgreSQL's transaction code in mind. This might be tricky because a few changes in the database might be forgotten "near" the pg_dump . This is a very crucial point. If the application that uses the database is designed and implemented properly, a journaling server can provide you a lot of information about the processes happening on your system. You can perform any kind of data mining that will be of use to your company. Another advantage of journaling over other backup methods is that you always have the most recent backup of your database ”at least you can restore the most recent snapshot of the data. In database environments where journaling is used, applications have to be designed to satisfy the demands of the backup system. On the other hand, the backup system is designed to satisfy the demands of your application.  |
- ERP System Acquisition: A Process Model and Results From an Austrian Survey
- The Effects of an Enterprise Resource Planning System (ERP) Implementation on Job Characteristics – A Study using the Hackman and Oldham Job Characteristics Model
- Distributed Data Warehouse for Geo-spatial Services
- A Hybrid Clustering Technique to Improve Patient Data Quality
- Relevance and Micro-Relevance for the Professional as Determinants of IT-Diffusion and IT-Use in Healthcare