postgres pgcopydb - ghdrako/doc_snipets GitHub Wiki

• https://pgcopydb.readthedocs.io/en/latest/

pgcopydb is a tool that automates copying a PostgreSQL database to another server. Main use case for pgcopydb is migration to a new Postgres system, either for new hardware, new architecture, or new Postgres major version.

The idea would be to run pg_dump -jN | pg_restore -jN between two running Postgres servers. To make a copy of a database to another server as quickly as possible, one would like to use the parallel options of pg_dump and still be able to stream the data to as many pg_restore jobs. Unfortunately, this approach cannot be implemented by using pg_dump and pg_restore directly, see Bypass intermediate files for the TABLE DATA.

When using pgcopydb it is possible to achieve both concurrency and streaming with this simple command line:

$ export PGCOPYDB_SOURCE_PGURI="postgres://[email protected]/dbname"
$ export PGCOPYDB_TARGET_PGURI="postgres://[email protected]/dbname"

$ pgcopydb clone --table-jobs 4 --index-jobs 4