## F.46. pg_transfer

The `pg_transfer` extension enables quick transfer of tables between Postgres Pro Enterprise instances.

### F.46.1. Description

Some applications experience problems loading huge amount of data into a database, for example when consolidating data from regional servers into a central one. Usual method is to copy schema and data by using pg_dump and pg_restore. In this case the receiving server's workload is higher then that of sending server. Data are loaded by `INSERT` or `COPY` commands, which creates significant impact on a disk subsystem. Creation of indexes and analysis of tables are performed in a target database after data are loaded.

The `pg_transfer` extension allows to prepare a table (i. e. creation of indexes and analysis) out of target server and ensures fast load of read-only data. The extension contains additional functions for pg_dump and pg_restore utilities.

### F.46.2. Installation

To install the extension execute the following SQL command:

        CREATE EXTENSION pg_transfer;

### F.46.3. Usage

Before table can be transferred, it must be marked as read-only.

        ALTER TABLE table_name SET CONSTANT;

After that `VACUUM (ANALYZE)` should be executed to get rid of dead tuples and refresh statistics.

        VACUUM (ANALYZE) table_name;

Data transfer is performed in two stages. First, logical dump of data schema is taken on auxiliary database server and restored on target server. Second, data on auxiliary server are prepared for transfer, using some information about the restored schema, and the transfer itself is performed.

When source and target databases are located in the same file system, `--copy-mode-transfer` option must be specified at least once (for either pg_dump or pg_restore) to get an independent copy of data. When restoring data on primary server, `--generate-wal` option must be specified for pg_restore for changes to be replicated to standby server.

### Note

Architecture of both servers and configuration of Postgres Pro Enterprise must guarantee binary-compatible file formats. Checks are performed during data restoring for coincidence of alignment, page and segment sizes etc.

#### F.46.3.1. Stage 1

    pg_dump old_database -Fc -t table_name --schema-only -f transfer_dir/archive.out
    pg_restore -d new_database --schema-only transfer_dir/archive.out

After the schema is restored, TOAST table's identifier must be determined.

    psql new_database -c "SELECT reltoastrelid FROM pg_class WHERE relname='table_name';"

#### F.46.3.2. Stage 2

The `pg_transfer` extension must be installed in both databases.

Using the TOAST table's identifier from the previous stage (`reltoastid`), prepare the table for transfer and force data flush to disk.

    psql -d old_database -c "SELECT pg_transfer_freeze('table_name'::regclass::oid, reltoastrelid::oid);"

#### F.46.3.3. Data transfer

Preparation is completed. Now the data can be transferred into separate directory using pg_dump utility.

    pg_dump old_database -Fc -t table_name --transfer-dir transfer_dir/ -f transfer_dir/archive.out

And finally data can be restored in target database.

    pg_restore -d new_database --data-only --transfer-dir transfer_dir/ transfer_dir/archive.out --copy-mode-transfer

### F.46.4. Compatibility

The extension is compatible with Postgres Pro 9.6 or newer on Unix-like systems.