PostgreSQL pg_copy Utility Summary This command is part of a set of options and commands that together provide for database recovery in the event of loss or corruption of one or more PostgreSQL database files. This includes an on line backup facility pg_copy, and a set of configuration options that control PostgreSQL DBMS recovery log reuse. Note that in this document database is used synonymously with database cluster, where a database cluster is the set of databases served by a particular instance of the Postmaster. pg_copy The pg_copy utility is a command-line executable that produces a "physical" copy of a database cluster for use with the roll forward recovery (RFR) mechanism. This is in contrast to pg_dump which produces a "logical" copy of the database, which is not usable with RFR. Also, note that operating system facilities also produce "physical" copies, but can only be used when the database system is shutdown. Therefore, a utility such as pg_copy is required for online backup and RFR. Syntax pg_copy [--archive_wal_files_dir= [-h host] [-p port] [-U username] [–W] The pg_copy command safely recursively copies all files and directories for the database cluster to the location specified by . If the backup_directory is not empty the copy command aborts. The copy is performed in such a way that if you then start a postmaster with –D , the database will appear as though it crashed at the instant that the pg_copy command completed. Crash recovery will then generate a consistent database. pg_copy requires: - the database cluster to be online (i.e., have a Postmaster running). - the specified user to have superuser rights. - template1 to exist and be connectable. --archive_wal_files_dir= Specifies a location to copy recovery log files that were used prior to the current checkpoint interval and so therefore not needed for this copy, otherwise these files will just be deleted. A likely candidate for the directory path is the pg_xlog directory of the previous pg_copy command . This allows recovery of a database cluster in which the most recent archive copy is found to be corrupt. The RFR would just be started on the previous archive copy which would process the complete set of logs during recovery. Note that this scheme depends on log file not being reused. Therefore, if an archive directory is specified with wal_file_reuse being true, the command will abort. pg_copy also accepts the following command line arguments for connection parameters: -h host --host=host Specifies the host name of the machine on which the server is running. If host begins with a slash, it is used as the directory for the Unix domain socket. -p port --port=port Specifies the Internet TCP/IP port or local Unix domain socket file extension on which the server is listening for connections. The port number defaults to 5432, or the value of the PGPORT environment variable (if set). -U username Connect as the given user. -W Force a password prompt. This should happen automatically if the server requires password authentication. Design The pg_copy utility first terminates any checkpoints, lazy or otherwise, that are in progress and prevents any new checkpoints from being initiated. It then recursively copies all the files in the database cluster (except for postmaster.pid) leaving the xlog directory for last. Since the OS page size (on Linux at least) is 4K, while the DB system page size is 8K by default, care must be taken to avoid reads of partially written DB files. Therefore, the contents of the data files must be read via the shared buffer pool. (Strictly speaking, reads via the shared buffer pool are only necessary if the enable_atomic_write configuration parameter (see CoreFeatures.doc) has been set to false; otherwise just copying the files would be safe.) The pg_xlog directory requires some special handling. Pg_copy copies the xlog files from the xlog file containing the last redo position in the log file up to and including the last xlog file. Before copying the last xlog file the database manager is instructed to stop all logging activity and flush all its xlog buffers. Then the pg_copy utility copies the last xlog file. Once this copy is complete, the database manager is notified that logging can resume. If an archive directory has been specified then copy all of the xlog files up to and including the xlog file containing the log redo position (checkpoint) at the time the pg_copy started. The pg_copy facility will be implemented as an executable to provide flexibility and security for inter-process communications. The copy process requires checkpointing be stopped and various alter system commands to be executed. The pg_copy process will fork a backend to execute alter system commands on its behalf. If anything fails during any step of this process, there needs to be a mechanism to detect the failure and restore the system to a sane state. This is most easily realized by implementing the appropriate signal handlers and shared memory communication mechanisms. Backend Commands The pg_copy utility is implemented using a set of command primitives. Only the database super-user can issue these commands. Pg_copy issues the ALTER SYSTEM BEGIN BACKUP command over a connection to the template1 database. The pg_database table will be read and each connectable database will be issued an ALTER SYSTEM BACKUP DATABASE command. Finally, the ALTER SYSTEM FINISH BACKUP command is issued over the connection to the template1 database. The connection to the template1 database must be maintained for the entire duration of the backup. The BEGIN BACKUP command will set the online backup in progress flag in shared memory and, along with setting this flag, will copy its process id into a shared memory variable. The CreateCheckpoint procedure will check if the backup process is still alive and if not will clear the online backup flag. While a pg_copy is running, "checkpoint" sql commands and checkpoint interval timeouts will be queued. ALTER SYSTEM BEGIN BACKUP Checks if the is empty and if not returns an error. Tells the database manager to stop database checkpoints. This command is needed only once for the entire cluster. ALTER SYSTEM BACKUP TO Backs up all of the data files for the connected database to the specified . This command is needed once for each connectable database in the cluster. ALTER SYSTEM FINISH BACKUP [ARCHIVE_WAL_FILES_DIR=] Backs up any non-connectable databases (template0, etc.), configuration files, the global directory, the pg_clog directory (commit logs) and the pg_xlog directory. If the ARCHIVE_WAL_FILES_DIR directory is specified, then all of the xlog files up to and including the xlog file containing the log redo position (checkpoint) are copied to the specified directory, otherwise they are deleted. At this point, the online backup is complete and database checkpointing resumes. ALTER SYSTEM ABORT BACKUP This command is only needed if any step of the backup fails (disk full, etc), dies or is interrupted and needs to terminate. In this case, any in-progress online backup needs to be erased. The backup_directory needs to be emptied and removed, and anything added to the archive_wal_files_dir needs to be removed. 4