The pg_transfer extension enables quick transfer of
tables between Postgres Pro Enterprise instances.
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.
To install the extension execute the following SQL command:
CREATE EXTENSION pg_transfer;
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.
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.
pg_dump database -t table_name --schema-only -f transfer_dir/archive.out
pg_restore -d database --schema-only transfer_dir/archive.out
After the schema is restored, TOAST table's identifier must be determined.
psql target_database -c select reltoastrelid from pg_class where relname='table_name'
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 database -c select pg_transfer_freeze('table_name'::regclass::oid, reltoastrelid::oid);
Preparation is completed. Now the data can be transferred into separate directory using pg_dump utility.
pg_dump database -Fc -t table_name --copy-mode-transfer --transfer-dir transfer_dir/ -f transfer_dir/archive.out
And finally data can be restored in target database.
pg_restore -d target_database --data-only --transfer-dir transfer_dir/ transfer_dir/archive.out
The extension is compatible with Postgres Pro 9.6 or newer on Unix-like systems.