4.2.4 Using TablespacesIf the database cluster contains user-defined tablespaces (that is, tablespaces created with the CREATE TABLESPACE command), note the following:
• Either the tar (-F t) or plain text (-F p) backup file format may be used for full backups with the BACKUP subcommand. The plain text (-F p) backup file format must be used for incremental backups.
• Transaction log streaming (xlog_method = stream in the BART configuration file) for taking full backups with pg_basebackup can be used as long as the -F p option is specified with the BACKUP subcommand. Thus, transaction log streaming can be used on database clusters containing user-defined tablespaces.If the particular database cluster you plan to back up contains tablespaces created by the CREATE TABLESPACE command, be sure to set the tablespace_path parameter in the BART configuration file before you perform a BART RESTORE operation.The tablespace_path parameter specifies the directory paths to which you want the tablespaces to be restored.The tablespace_path parameter takes the following format:OID_1=tablespace_path_1;OID_2=tablespace_path_2 ...OID_1, OID_2, … are the Object Identifiers of the tablespaces. You can find the OIDs of the tablespaces and their corresponding soft links to the directories by listing the contents of the POSTGRES_INSTALL_HOME/data/pg_tblspc subdirectory as shown in the following example:The OIDs are 16644 and 16645 to directories /mnt/tablespace_1 and /mnt/tablespace_2, respectively. If you later wish to restore the tablespaces to the same locations as indicated in the preceding example, the BART configuration file must contain the following entry:If you wish to restore the tablespaces to different locations, specify the new directory locations in the tablespace_path parameter.In either case, the directories specified in the tablespace_path parameter must exist and be empty at the time you perform the BART RESTORE operation.If the database server is running on a remote host (in other words you are also using the remote_host configuration parameter or will specify the --remote-host option with the RESTORE subcommand), the specified tablespace directories must exist on the specified remote host.The directories must be owned by the user account with which you intend to start the database server (typically the Postgres user account) with no access by other users or groups as is required for the directory path to which the main full backup is to be restored.The following is an example of backing up and then restoring a database cluster on a remote host that includes tablespaces.Note: This example emphasizes the steps that are affected by tablespace usage. See Section 5 for the complete process required for backing up and restoring a database cluster.On an Advanced Server database running on a remote host, the following tablespaces are created and used by two tables:The following shows the OIDs assigned to the tablespaces and the symbolic links to the tablespace directories:The BART configuration file contains the following settings. Note that the tablespace_path parameter does not have to be set at this point.After the necessary configuration steps described in this section are performed so BART can manage the remote database server, a full backup is taken. See Section 5 for the preparation steps and action for taking the full backup.Note in the output from the preceding example that checksums are generated for the tablespaces as well as the full backup.Within the backup subdirectory 1447709811516 of the BART backup catalog, the tablespace data is stored with file names 16587.tar.gz and 16588.tar.gz as shown by the following:When you are ready to restore the backup, in addition to creating the directory to which the main database cluster is to be restored, prepare the directories to which the tablespaces are to be restored.On the remote host, directories /opt/restore_tblspc_1 and /opt/restore_tblspc_2 are created and assigned the proper ownership and permissions as shown by the following. The main database cluster is to be restored to /opt/restore.Set the tablespace_path parameter in the BART configuration file to specify the tablespace directories.Also note that the remote host user and IP address are specified by the remote_host configuration parameter.The following shows invocation of the RESTORE subcommand:The symbolic links in the pg_tblspc subdirectory point to the restored directory location:Queries within psql also show the restored tablespaces: