.\" Man page generated from reStructuredText. . . .nr rst2man-indent-level 0 . .de1 rstReportMargin \\$1 \\n[an-margin] level \\n[rst2man-indent-level] level margin: \\n[rst2man-indent\\n[rst2man-indent-level]] - \\n[rst2man-indent0] \\n[rst2man-indent1] \\n[rst2man-indent2] .. .de1 INDENT .\" .rstReportMargin pre: . RS \\$1 . nr rst2man-indent\\n[rst2man-indent-level] \\n[an-margin] . nr rst2man-indent-level +1 .\" .rstReportMargin post: .. .de UNINDENT . RE .\" indent \\n[an-margin] .\" old: \\n[rst2man-indent\\n[rst2man-indent-level]] .nr rst2man-indent-level -1 .\" new: \\n[rst2man-indent\\n[rst2man-indent-level]] .in \\n[rst2man-indent\\n[rst2man-indent-level]]u .. .TH "PGCOPYDB COPY" "1" "Mar 15, 2023" "0.11" "pgcopydb" .SH NAME pgcopydb copy \- pgcopydb copy .sp pgcopydb copy \- Implement the data section of the database copy .sp This command prefixes the following sub\-commands: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C pgcopydb copy db Copy an entire database from source to target roles Copy the roles from the source instance to the target instance extensions Copy the extensions from the source instance to the target instance schema Copy the database schema from source to target data Copy the data section from source to target table\-data Copy the data from all tables in database from source to target blobs Copy the blob data from ther source database to the target sequences Copy the current value from all sequences in database from source to target indexes Create all the indexes found in the source database in the target constraints Create all the constraints found in the source database in the target .ft P .fi .UNINDENT .UNINDENT .sp Those commands implement a part of the whole database copy operation as detailed in section \fI\%pgcopydb clone\fP\&. Only use those commands to debug a specific part, or because you know that you just want to implement that step. .sp \fBWARNING:\fP .INDENT 0.0 .INDENT 3.5 Using the \fBpgcopydb clone\fP command is strongly advised. .sp This mode of operations is useful for debugging and advanced use cases only. .UNINDENT .UNINDENT .SH PGCOPYDB COPY DB .sp pgcopydb copy db \- Copy an entire database from source to target .sp The command \fBpgcopydb copy db\fP is an alias for \fBpgcopydb clone\fP\&. See also \fI\%pgcopydb clone\fP\&. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C pgcopydb copy db: Copy an entire database from source to target usage: pgcopydb copy db \-\-source ... \-\-target ... [ \-\-table\-jobs ... \-\-index\-jobs ... ] \-\-source Postgres URI to the source database \-\-target Postgres URI to the target database \-\-dir Work directory to use \-\-table\-jobs Number of concurrent COPY jobs to run \-\-index\-jobs Number of concurrent CREATE INDEX jobs to run \-\-drop\-if\-exists On the target database, clean\-up from a previous run first \-\-roles Also copy roles found on source to target \-\-no\-owner Do not set ownership of objects to match the original database \-\-no\-acl Prevent restoration of access privileges (grant/revoke commands). \-\-no\-comments Do not output commands to restore comments \-\-skip\-large\-objects Skip copying large objects (blobs) \-\-filters Use the filters defined in \-\-restart Allow restarting when temp files exist already \-\-resume Allow resuming operations after a failure \-\-not\-consistent Allow taking a new snapshot on the source database \-\-snapshot Use snapshot obtained with pg_export_snapshot .ft P .fi .UNINDENT .UNINDENT .SH PGCOPYDB COPY ROLES .sp pgcopydb copy roles \- Copy the roles from the source instance to the target instance .sp The command \fBpgcopydb copy roles\fP implements both \fI\%pgcopydb dump roles\fP and then \fI\%pgcopydb restore roles\fP\&. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C pgcopydb copy roles: Copy the roles from the source instance to the target instance usage: pgcopydb copy roles \-\-source ... \-\-target ... \-\-source Postgres URI to the source database \-\-target Postgres URI to the target database \-\-dir Work directory to use \-\-no\-role\-passwords Do not dump passwords for roles .ft P .fi .UNINDENT .UNINDENT .sp \fBNOTE:\fP .INDENT 0.0 .INDENT 3.5 In Postgres, roles are a global object. This means roles do not belong to any specific database, and as a result, even when the \fBpgcopydb\fP tool otherwise works only in the context of a specific database, this command is not limited to roles that are used within a single database. .UNINDENT .UNINDENT .sp When a role already exists on the target database, its restoring is entirely skipped, which includes skipping both the \fBCREATE ROLE\fP and the \fBALTER ROLE\fP commands produced by \fBpg_dumpall \-\-roles\-only\fP\&. .sp The \fBpg_dumpall \-\-roles\-only\fP is used to fetch the list of roles from the source database, and this command includes support for passwords. As a result, this operation requires the superuser privileges. .SH PGCOPYDB COPY EXTENSIONS .sp pgcopydb copy extensions \- Copy the extensions from the source instance to the target instance .sp The command \fBpgcopydb copy extensions\fP gets a list of the extensions installed on the source database, and for each of them run the SQL command CREATE EXTENSION IF NOT EXISTS. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C pgcopydb copy extensions: Copy the extensions from the source instance to the target instance usage: pgcopydb copy extensions \-\-source ... \-\-target ... \-\-source Postgres URI to the source database \-\-target Postgres URI to the target database \-\-dir Work directory to use .ft P .fi .UNINDENT .UNINDENT .sp When copying extensions, this command also takes care of copying any \fI\%Extension Configuration Tables\fP user\-data to the target database. .SH PGCOPYDB COPY SCHEMA .sp pgcopydb copy schema \- Copy the database schema from source to target .sp The command \fBpgcopydb copy schema\fP implements the schema only section of the clone steps. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C pgcopydb copy schema: Copy the database schema from source to target usage: pgcopydb copy schema \-\-source ... \-\-target ... [ \-\-table\-jobs ... \-\-index\-jobs ... ] \-\-source Postgres URI to the source database \-\-target Postgres URI to the target database \-\-dir Work directory to use \-\-filters Use the filters defined in \-\-restart Allow restarting when temp files exist already \-\-resume Allow resuming operations after a failure \-\-not\-consistent Allow taking a new snapshot on the source database \-\-snapshot Use snapshot obtained with pg_export_snapshot .ft P .fi .UNINDENT .UNINDENT .SH PGCOPYDB COPY DATA .sp pgcopydb copy data \- Copy the data section from source to target .sp The command \fBpgcopydb copy data\fP implements the data section of the clone steps. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C pgcopydb copy data: Copy the data section from source to target usage: pgcopydb copy data \-\-source ... \-\-target ... [ \-\-table\-jobs ... \-\-index\-jobs ... ] \-\-source Postgres URI to the source database \-\-target Postgres URI to the target database \-\-dir Work directory to use \-\-table\-jobs Number of concurrent COPY jobs to run \-\-index\-jobs Number of concurrent CREATE INDEX jobs to run \-\-drop\-if\-exists On the target database, clean\-up from a previous run first \-\-no\-owner Do not set ownership of objects to match the original database \-\-skip\-large\-objects Skip copying large objects (blobs) \-\-restart Allow restarting when temp files exist already \-\-resume Allow resuming operations after a failure \-\-not\-consistent Allow taking a new snapshot on the source database \-\-snapshot Use snapshot obtained with pg_export_snapshot .ft P .fi .UNINDENT .UNINDENT .sp \fBNOTE:\fP .INDENT 0.0 .INDENT 3.5 The current command line has both the commands \fBpgcopydb copy table\-data\fP and \fBpgcopydb copy data\fP, which are looking quite similar but implement different steps. Be careful for now. This will change later. .UNINDENT .UNINDENT .sp The \fBpgcopydb copy data\fP command implements the following steps: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ pgcopydb copy table\-data $ pgcopydb copy blobs $ pgcopydb copy indexes $ pgcopydb copy constraints $ pgcopydb copy sequences $ vacuumdb \-z .ft P .fi .UNINDENT .UNINDENT .sp Those steps are actually done concurrently to one another when that\(aqs possible, in the same way as the main command \fBpgcopydb clone\fP would. The only difference is that the \fBpgcopydb clone\fP command also prepares and finishes the schema parts of the operations (pre\-data, then post\-data), which the \fBpgcopydb copy data\fP command ignores. .SH PGCOPYDB COPY TABLE-DATA .sp pgcopydb copy table\-data \- Copy the data from all tables in database from source to target .sp The command \fBpgcopydb copy table\-data\fP fetches the list of tables from the source database and runs a COPY TO command on the source database and sends the result to the target database using a COPY FROM command directly, avoiding disks entirely. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C pgcopydb copy table\-data: Copy the data from all tables in database from source to target usage: pgcopydb copy table\-data \-\-source ... \-\-target ... [ \-\-table\-jobs ... \-\-index\-jobs ... ] \-\-source Postgres URI to the source database \-\-target Postgres URI to the target database \-\-dir Work directory to use \-\-table\-jobs Number of concurrent COPY jobs to run \-\-restart Allow restarting when temp files exist already \-\-resume Allow resuming operations after a failure \-\-not\-consistent Allow taking a new snapshot on the source database \-\-snapshot Use snapshot obtained with pg_export_snapshot .ft P .fi .UNINDENT .UNINDENT .SH PGCOPYDB COPY BLOBS .sp pgcopydb copy blobs \- Copy the blob data from ther source database to the target .sp The command \fBpgcopydb copy blobs\fP fetches list of large objects (aka blobs) from the source database and copies their data parts to the target database. By default the command assumes that the large objects metadata have already been taken care of, because of the behaviour of \fBpg_dump \-\-section=pre\-data\fP\&. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C pgcopydb copy blobs: Copy the blob data from ther source database to the target usage: pgcopydb copy blobs \-\-source ... \-\-target ... \-\-source Postgres URI to the source database \-\-target Postgres URI to the target database \-\-dir Work directory to use \-\-restart Allow restarting when temp files exist already \-\-resume Allow resuming operations after a failure \-\-not\-consistent Allow taking a new snapshot on the source database \-\-snapshot Use snapshot obtained with pg_export_snapshot \-\-drop\-if\-exists On the target database, drop and create large objects .ft P .fi .UNINDENT .UNINDENT .SH PGCOPYDB COPY SEQUENCES .sp pgcopydb copy sequences \- Copy the current value from all sequences in database from source to target .sp The command \fBpgcopydb copy sequences\fP fetches the list of sequences from the source database, then for each sequence fetches the \fBlast_value\fP and \fBis_called\fP properties the same way pg_dump would on the source database, and then for each sequence call \fBpg_catalog.setval()\fP on the target database. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C pgcopydb copy sequences: Copy the current value from all sequences in database from source to target usage: pgcopydb copy sequences \-\-source ... \-\-target ... [ \-\-table\-jobs ... \-\-index\-jobs ... ] \-\-source Postgres URI to the source database \-\-target Postgres URI to the target database \-\-dir Work directory to use \-\-restart Allow restarting when temp files exist already \-\-resume Allow resuming operations after a failure \-\-not\-consistent Allow taking a new snapshot on the source database .ft P .fi .UNINDENT .UNINDENT .SH PGCOPYDB COPY INDEXES .sp pgcopydb copy indexes \- Create all the indexes found in the source database in the target .sp The command \fBpgcopydb copy indexes\fP fetches the list of indexes from the source database and runs each index CREATE INDEX statement on the target database. The statements for the index definitions are modified to include IF NOT EXISTS and allow for skipping indexes that already exist on the target database. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C pgcopydb copy indexes: Create all the indexes found in the source database in the target usage: pgcopydb copy indexes \-\-source ... \-\-target ... [ \-\-table\-jobs ... \-\-index\-jobs ... ] \-\-source Postgres URI to the source database \-\-target Postgres URI to the target database \-\-dir Work directory to use \-\-index\-jobs Number of concurrent CREATE INDEX jobs to run \-\-restart Allow restarting when temp files exist already \-\-resume Allow resuming operations after a failure \-\-not\-consistent Allow taking a new snapshot on the source database .ft P .fi .UNINDENT .UNINDENT .SH PGCOPYDB COPY CONSTRAINTS .sp pgcopydb copy constraints \- Create all the constraints found in the source database in the target .sp The command \fBpgcopydb copy constraints\fP fetches the list of indexes from the source database and runs each index ALTER TABLE ... ADD CONSTRAINT ... USING INDEX statement on the target database. .sp The indexes must already exist, and the command will fail if any constraint is found existing already on the target database. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C pgcopydb copy indexes: Create all the indexes found in the source database in the target usage: pgcopydb copy indexes \-\-source ... \-\-target ... [ \-\-table\-jobs ... \-\-index\-jobs ... ] \-\-source Postgres URI to the source database \-\-target Postgres URI to the target database \-\-dir Work directory to use \-\-restart Allow restarting when temp files exist already \-\-resume Allow resuming operations after a failure \-\-not\-consistent Allow taking a new snapshot on the source data .ft P .fi .UNINDENT .UNINDENT .SH DESCRIPTION .sp These commands allow implementing a specific step of the pgcopydb operations at a time. It\(aqs useful mainly for debugging purposes, though some advanced and creative usage can be made from the commands. .sp The target schema is not created, so it needs to have been taken care of first. It is possible to use the commands \fI\%pgcopydb dump schema\fP and then \fI\%pgcopydb restore pre\-data\fP to prepare your target database. .sp To implement the same operations as a \fBpgcopydb clone\fP command would, use the following recipe: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ export PGCOPYDB_SOURCE_PGURI=\(dqpostgres://user@source/dbname\(dq $ export PGCOPYDB_TARGET_PGURI=\(dqpostgres://user@target/dbname\(dq $ pgcopydb dump schema $ pgcopydb restore pre\-data \-\-resume \-\-not\-consistent $ pgcopydb copy table\-data \-\-resume \-\-not\-consistent $ pgcopydb copy sequences \-\-resume \-\-not\-consistent $ pgcopydb copy indexes \-\-resume \-\-not\-consistent $ pgcopydb copy constraints \-\-resume \-\-not\-consistent $ vacuumdb \-z $ pgcopydb restore post\-data \-\-resume \-\-not\-consistent .ft P .fi .UNINDENT .UNINDENT .sp The main \fBpgcopydb clone\fP is still better at concurrency than doing those steps manually, as it will create the indexes for any given table as soon as the table\-data section is finished, without having to wait until the last table\-data has been copied over. Same applies to constraints, and then vacuum analyze. .SH OPTIONS .sp The following options are available to \fBpgcopydb copy\fP sub\-commands: .INDENT 0.0 .TP .B \-\-source Connection string to the source Postgres instance. See the Postgres documentation for \fI\%connection strings\fP for the details. In short both the quoted form \fB\(dqhost=... dbname=...\(dq\fP and the URI form \fBpostgres://user@host:5432/dbname\fP are supported. .TP .B \-\-target Connection string to the target Postgres instance. .TP .B \-\-dir During its normal operations pgcopydb creates a lot of temporary files to track sub\-processes progress. Temporary files are created in the directory location given by this option, or defaults to \fB${TMPDIR}/pgcopydb\fP when the environment variable is set, or then to \fB/tmp/pgcopydb\fP\&. .TP .B \-\-no\-role\-passwords Do not dump passwords for roles. When restored, roles will have a null password, and password authentication will always fail until the password is set. Since password values aren\(aqt needed when this option is specified, the role information is read from the catalog view pg_roles instead of pg_authid. Therefore, this option also helps if access to pg_authid is restricted by some security policy. .TP .B \-\-table\-jobs How many tables can be processed in parallel. .sp This limit only applies to the COPY operations, more sub\-processes will be running at the same time that this limit while the CREATE INDEX operations are in progress, though then the processes are only waiting for the target Postgres instance to do all the work. .TP .B \-\-index\-jobs How many indexes can be built in parallel, globally. A good option is to set this option to the count of CPU cores that are available on the Postgres target system, minus some cores that are going to be used for handling the COPY operations. .TP .B \-\-split\-tables\-larger\-than Allow \fI\%Same\-table Concurrency\fP when processing the source database. This environment variable value is expected to be a byte size, and bytes units B, kB, MB, GB, TB, PB, and EB are known. .TP .B \-\-skip\-large\-objects Skip copying large objects, also known as blobs, when copying the data from the source database to the target database. .TP .B \-\-restart When running the pgcopydb command again, if the work directory already contains information from a previous run, then the command refuses to proceed and delete information that might be used for diagnostics and forensics. .sp In that case, the \fB\-\-restart\fP option can be used to allow pgcopydb to delete traces from a previous run. .TP .B \-\-resume When the pgcopydb command was terminated before completion, either by an interrupt signal (such as C\-c or SIGTERM) or because it crashed, it is possible to resume the database migration. .sp When resuming activity from a previous run, table data that was fully copied over to the target server is not sent again. Table data that was interrupted during the COPY has to be started from scratch even when using \fB\-\-resume\fP: the COPY command in Postgres is transactional and was rolled back. .sp Same reasonning applies to the CREATE INDEX commands and ALTER TABLE commands that pgcopydb issues, those commands are skipped on a \fB\-\-resume\fP run only if known to have run through to completion on the previous one. .sp Finally, using \fB\-\-resume\fP requires the use of \fB\-\-not\-consistent\fP\&. .TP .B \-\-not\-consistent In order to be consistent, pgcopydb exports a Postgres snapshot by calling the \fI\%pg_export_snapshot()\fP function on the source database server. The snapshot is then re\-used in all the connections to the source database server by using the \fBSET TRANSACTION SNAPSHOT\fP command. .sp Per the Postgres documentation about \fBpg_export_snapshot\fP: .INDENT 7.0 .INDENT 3.5 Saves the transaction\(aqs current snapshot and returns a text string identifying the snapshot. This string must be passed (outside the database) to clients that want to import the snapshot. The snapshot is available for import only until the end of the transaction that exported it. .UNINDENT .UNINDENT .sp Now, when the pgcopydb process was interrupted (or crashed) on a previous run, it is possible to resume operations, but the snapshot that was exported does not exists anymore. The pgcopydb command can only resume operations with a new snapshot, and thus can not ensure consistency of the whole data set, because each run is now using their own snapshot. .TP .B \-\-snapshot Instead of exporting its own snapshot by calling the PostgreSQL function \fBpg_export_snapshot()\fP it is possible for pgcopydb to re\-use an already exported snapshot. .TP .B \-\-verbose Increase current verbosity. The default level of verbosity is INFO. In ascending order pgcopydb knows about the following verbosity levels: FATAL, ERROR, WARN, INFO, NOTICE, DEBUG, TRACE. .TP .B \-\-debug Set current verbosity to DEBUG level. .TP .B \-\-trace Set current verbosity to TRACE level. .TP .B \-\-quiet Set current verbosity to ERROR level. .UNINDENT .SH ENVIRONMENT .sp PGCOPYDB_SOURCE_PGURI .INDENT 0.0 .INDENT 3.5 Connection string to the source Postgres instance. When \fB\-\-source\fP is ommitted from the command line, then this environment variable is used. .UNINDENT .UNINDENT .sp PGCOPYDB_TARGET_PGURI .INDENT 0.0 .INDENT 3.5 Connection string to the target Postgres instance. When \fB\-\-target\fP is ommitted from the command line, then this environment variable is used. .UNINDENT .UNINDENT .sp PGCOPYDB_TABLE_JOBS .INDENT 0.0 .INDENT 3.5 Number of concurrent jobs allowed to run COPY operations in parallel. When \fB\-\-table\-jobs\fP is ommitted from the command line, then this environment variable is used. .UNINDENT .UNINDENT .sp PGCOPYDB_INDEX_JOBS .INDENT 0.0 .INDENT 3.5 Number of concurrent jobs allowed to run CREATE INDEX operations in parallel. When \fB\-\-index\-jobs\fP is ommitted from the command line, then this environment variable is used. .UNINDENT .UNINDENT .sp PGCOPYDB_SPLIT_TABLES_LARGER_THAN .INDENT 0.0 .INDENT 3.5 Allow \fI\%Same\-table Concurrency\fP when processing the source database. This environment variable value is expected to be a byte size, and bytes units B, kB, MB, GB, TB, PB, and EB are known. .sp When \fB\-\-split\-tables\-larger\-than\fP is ommitted from the command line, then this environment variable is used. .UNINDENT .UNINDENT .sp PGCOPYDB_DROP_IF_EXISTS .INDENT 0.0 .INDENT 3.5 When true (or \fIyes\fP, or \fIon\fP, or 1, same input as a Postgres boolean) then pgcopydb uses the pg_restore options \fB\-\-clean \-\-if\-exists\fP when creating the schema on the target Postgres instance. .UNINDENT .UNINDENT .sp PGCOPYDB_SNAPSHOT .INDENT 0.0 .INDENT 3.5 Postgres snapshot identifier to re\-use, see also \fB\-\-snapshot\fP\&. .UNINDENT .UNINDENT .sp TMPDIR .INDENT 0.0 .INDENT 3.5 The pgcopydb command creates all its work files and directories in \fB${TMPDIR}/pgcopydb\fP, and defaults to \fB/tmp/pgcopydb\fP\&. .UNINDENT .UNINDENT .SH EXAMPLES .sp Let\(aqs export the Postgres databases connection strings to make it easy to re\-use them all along: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ export PGCOPYDB_SOURCE_PGURI=\(dqport=54311 host=localhost dbname=pgloader\(dq $ export PGCOPYDB_TARGET_PGURI=\(dqport=54311 dbname=plop\(dq .ft P .fi .UNINDENT .UNINDENT .sp Now, first dump the schema: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ pgcopydb dump schema 15:24:24 75511 INFO Removing the stale pid file \(dq/tmp/pgcopydb/pgcopydb.pid\(dq 15:24:24 75511 WARN Directory \(dq/tmp/pgcopydb\(dq already exists: removing it entirely 15:24:24 75511 INFO Dumping database from \(dqport=54311 host=localhost dbname=pgloader\(dq 15:24:24 75511 INFO Dumping database into directory \(dq/tmp/pgcopydb\(dq 15:24:24 75511 INFO Using pg_dump for Postgres \(dq12.9\(dq at \(dq/Applications/Postgres.app/Contents/Versions/12/bin/pg_dump\(dq 15:24:24 75511 INFO /Applications/Postgres.app/Contents/Versions/12/bin/pg_dump \-Fc \-\-section pre\-data \-\-file /tmp/pgcopydb/schema/pre.dump \(aqport=54311 host=localhost dbname=pgloader\(aq 15:24:25 75511 INFO /Applications/Postgres.app/Contents/Versions/12/bin/pg_dump \-Fc \-\-section post\-data \-\-file /tmp/pgcopydb/schema/post.dump \(aqport=54311 host=localhost dbname=pgloader\(aq .ft P .fi .UNINDENT .UNINDENT .sp Now restore the pre\-data schema on the target database, cleaning up the already existing objects if any, which allows running this test scenario again and again. It might not be what you want to do in your production target instance though! .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C PGCOPYDB_DROP_IF_EXISTS=on pgcopydb restore pre\-data \-\-no\-owner 15:24:29 75591 INFO Removing the stale pid file \(dq/tmp/pgcopydb/pgcopydb.pid\(dq 15:24:29 75591 INFO Restoring database from \(dq/tmp/pgcopydb\(dq 15:24:29 75591 INFO Restoring database into \(dqport=54311 dbname=plop\(dq 15:24:29 75591 INFO Using pg_restore for Postgres \(dq12.9\(dq at \(dq/Applications/Postgres.app/Contents/Versions/12/bin/pg_restore\(dq 15:24:29 75591 INFO /Applications/Postgres.app/Contents/Versions/12/bin/pg_restore \-\-dbname \(aqport=54311 dbname=plop\(aq \-\-clean \-\-if\-exists \-\-no\-owner /tmp/pgcopydb/schema/pre.dump .ft P .fi .UNINDENT .UNINDENT .sp Then copy the data over: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ pgcopydb copy table\-data \-\-resume \-\-not\-consistent 15:24:36 75688 INFO [SOURCE] Copying database from \(dqport=54311 host=localhost dbname=pgloader\(dq 15:24:36 75688 INFO [TARGET] Copying database into \(dqport=54311 dbname=plop\(dq 15:24:36 75688 INFO Removing the stale pid file \(dq/tmp/pgcopydb/pgcopydb.pid\(dq 15:24:36 75688 INFO STEP 3: copy data from source to target in sub\-processes 15:24:36 75688 INFO Listing ordinary tables in \(dqport=54311 host=localhost dbname=pgloader\(dq 15:24:36 75688 INFO Fetched information for 56 tables \&... Step Connection Duration Concurrency \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\-\-\- Dump Schema source 0ms 1 Prepare Schema target 0ms 1 COPY, INDEX, CONSTRAINTS, VACUUM (wall clock) both 0ms 4 + 4 COPY (cumulative) both 1s140 4 CREATE INDEX (cumulative) target 0ms 4 Finalize Schema target 0ms 1 \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\-\-\- Total Wall Clock Duration both 2s143 4 + 4 \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\-\-\- .ft P .fi .UNINDENT .UNINDENT .sp And now create the indexes on the target database, using the index definitions from the source database: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ pgcopydb copy indexes \-\-resume \-\-not\-consistent 15:24:40 75918 INFO [SOURCE] Copying database from \(dqport=54311 host=localhost dbname=pgloader\(dq 15:24:40 75918 INFO [TARGET] Copying database into \(dqport=54311 dbname=plop\(dq 15:24:40 75918 INFO Removing the stale pid file \(dq/tmp/pgcopydb/pgcopydb.pid\(dq 15:24:40 75918 INFO STEP 4: create indexes in parallel 15:24:40 75918 INFO Listing ordinary tables in \(dqport=54311 host=localhost dbname=pgloader\(dq 15:24:40 75918 INFO Fetched information for 56 tables 15:24:40 75930 INFO Creating 2 indexes for table \(dqcsv\(dq.\(dqpartial\(dq 15:24:40 75922 INFO Creating 1 index for table \(dqcsv\(dq.\(dqtrack\(dq 15:24:40 75931 INFO Creating 1 index for table \(dqerr\(dq.\(dqerrors\(dq 15:24:40 75928 INFO Creating 1 index for table \(dqcsv\(dq.\(dqblocks\(dq 15:24:40 75925 INFO Creating 1 index for table \(dqpublic\(dq.\(dqtrack_full\(dq 15:24:40 76037 INFO CREATE INDEX IF NOT EXISTS partial_b_idx ON csv.partial USING btree (b); 15:24:40 76036 INFO CREATE UNIQUE INDEX IF NOT EXISTS track_pkey ON csv.track USING btree (trackid); 15:24:40 76035 INFO CREATE UNIQUE INDEX IF NOT EXISTS partial_a_key ON csv.partial USING btree (a); 15:24:40 76038 INFO CREATE UNIQUE INDEX IF NOT EXISTS errors_pkey ON err.errors USING btree (a); 15:24:40 75987 INFO Creating 1 index for table \(dqpublic\(dq.\(dqxzero\(dq 15:24:40 75969 INFO Creating 1 index for table \(dqpublic\(dq.\(dqcsv_escape_mode\(dq 15:24:40 75985 INFO Creating 1 index for table \(dqpublic\(dq.\(dqudc\(dq 15:24:40 75965 INFO Creating 1 index for table \(dqpublic\(dq.\(dqallcols\(dq 15:24:40 75981 INFO Creating 1 index for table \(dqpublic\(dq.\(dqserial\(dq 15:24:40 76039 INFO CREATE INDEX IF NOT EXISTS blocks_ip4r_idx ON csv.blocks USING gist (iprange); 15:24:40 76040 INFO CREATE UNIQUE INDEX IF NOT EXISTS track_full_pkey ON public.track_full USING btree (trackid); 15:24:40 75975 INFO Creating 1 index for table \(dqpublic\(dq.\(dqnullif\(dq 15:24:40 76046 INFO CREATE UNIQUE INDEX IF NOT EXISTS xzero_pkey ON public.xzero USING btree (a); 15:24:40 76048 INFO CREATE UNIQUE INDEX IF NOT EXISTS udc_pkey ON public.udc USING btree (b); 15:24:40 76047 INFO CREATE UNIQUE INDEX IF NOT EXISTS csv_escape_mode_pkey ON public.csv_escape_mode USING btree (id); 15:24:40 76049 INFO CREATE UNIQUE INDEX IF NOT EXISTS allcols_pkey ON public.allcols USING btree (a); 15:24:40 76052 INFO CREATE UNIQUE INDEX IF NOT EXISTS nullif_pkey ON public.\(dqnullif\(dq USING btree (id); 15:24:40 76050 INFO CREATE UNIQUE INDEX IF NOT EXISTS serial_pkey ON public.serial USING btree (a); Step Connection Duration Concurrency \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\-\-\- Dump Schema source 0ms 1 Prepare Schema target 0ms 1 COPY, INDEX, CONSTRAINTS, VACUUM (wall clock) both 0ms 4 + 4 COPY (cumulative) both 619ms 4 CREATE INDEX (cumulative) target 1s023 4 Finalize Schema target 0ms 1 \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\-\-\- Total Wall Clock Duration both 400ms 4 + 4 \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\-\-\- .ft P .fi .UNINDENT .UNINDENT .sp Now re\-create the constraints (primary key, unique constraints) from the source database schema into the target database: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ pgcopydb copy constraints \-\-resume \-\-not\-consistent 15:24:43 76095 INFO [SOURCE] Copying database from \(dqport=54311 host=localhost dbname=pgloader\(dq 15:24:43 76095 INFO [TARGET] Copying database into \(dqport=54311 dbname=plop\(dq 15:24:43 76095 INFO Removing the stale pid file \(dq/tmp/pgcopydb/pgcopydb.pid\(dq 15:24:43 76095 INFO STEP 4: create constraints 15:24:43 76095 INFO Listing ordinary tables in \(dqport=54311 host=localhost dbname=pgloader\(dq 15:24:43 76095 INFO Fetched information for 56 tables 15:24:43 76099 INFO ALTER TABLE \(dqcsv\(dq.\(dqtrack\(dq ADD CONSTRAINT \(dqtrack_pkey\(dq PRIMARY KEY USING INDEX \(dqtrack_pkey\(dq; 15:24:43 76107 INFO ALTER TABLE \(dqcsv\(dq.\(dqpartial\(dq ADD CONSTRAINT \(dqpartial_a_key\(dq UNIQUE USING INDEX \(dqpartial_a_key\(dq; 15:24:43 76102 INFO ALTER TABLE \(dqpublic\(dq.\(dqtrack_full\(dq ADD CONSTRAINT \(dqtrack_full_pkey\(dq PRIMARY KEY USING INDEX \(dqtrack_full_pkey\(dq; 15:24:43 76142 INFO ALTER TABLE \(dqpublic\(dq.\(dqallcols\(dq ADD CONSTRAINT \(dqallcols_pkey\(dq PRIMARY KEY USING INDEX \(dqallcols_pkey\(dq; 15:24:43 76157 INFO ALTER TABLE \(dqpublic\(dq.\(dqserial\(dq ADD CONSTRAINT \(dqserial_pkey\(dq PRIMARY KEY USING INDEX \(dqserial_pkey\(dq; 15:24:43 76161 INFO ALTER TABLE \(dqpublic\(dq.\(dqxzero\(dq ADD CONSTRAINT \(dqxzero_pkey\(dq PRIMARY KEY USING INDEX \(dqxzero_pkey\(dq; 15:24:43 76146 INFO ALTER TABLE \(dqpublic\(dq.\(dqcsv_escape_mode\(dq ADD CONSTRAINT \(dqcsv_escape_mode_pkey\(dq PRIMARY KEY USING INDEX \(dqcsv_escape_mode_pkey\(dq; 15:24:43 76154 INFO ALTER TABLE \(dqpublic\(dq.\(dqnullif\(dq ADD CONSTRAINT \(dqnullif_pkey\(dq PRIMARY KEY USING INDEX \(dqnullif_pkey\(dq; 15:24:43 76159 INFO ALTER TABLE \(dqpublic\(dq.\(dqudc\(dq ADD CONSTRAINT \(dqudc_pkey\(dq PRIMARY KEY USING INDEX \(dqudc_pkey\(dq; 15:24:43 76108 INFO ALTER TABLE \(dqerr\(dq.\(dqerrors\(dq ADD CONSTRAINT \(dqerrors_pkey\(dq PRIMARY KEY USING INDEX \(dqerrors_pkey\(dq; Step Connection Duration Concurrency \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\-\-\- Dump Schema source 0ms 1 Prepare Schema target 0ms 1 COPY, INDEX, CONSTRAINTS, VACUUM (wall clock) both 0ms 4 + 4 COPY (cumulative) both 605ms 4 CREATE INDEX (cumulative) target 1s023 4 Finalize Schema target 0ms 1 \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\-\-\- Total Wall Clock Duration both 415ms 4 + 4 \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\- \-\-\-\-\-\-\-\-\-\-\-\- .ft P .fi .UNINDENT .UNINDENT .sp The next step is a VACUUM ANALYZE on each table that\(aqs been just filled\-in with the data, and for that we can just use the \fI\%vacuumdb\fP command from Postgres: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ vacuumdb \-\-analyze \-\-dbname \(dq$PGCOPYDB_TARGET_PGURI\(dq \-\-jobs 4 vacuumdb: vacuuming database \(dqplop\(dq .ft P .fi .UNINDENT .UNINDENT .sp Finally we can restore the post\-data section of the schema: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ pgcopydb restore post\-data \-\-resume \-\-not\-consistent 15:24:50 76328 INFO Removing the stale pid file \(dq/tmp/pgcopydb/pgcopydb.pid\(dq 15:24:50 76328 INFO Restoring database from \(dq/tmp/pgcopydb\(dq 15:24:50 76328 INFO Restoring database into \(dqport=54311 dbname=plop\(dq 15:24:50 76328 INFO Using pg_restore for Postgres \(dq12.9\(dq at \(dq/Applications/Postgres.app/Contents/Versions/12/bin/pg_restore\(dq 15:24:50 76328 INFO /Applications/Postgres.app/Contents/Versions/12/bin/pg_restore \-\-dbname \(aqport=54311 dbname=plop\(aq \-\-use\-list /tmp/pgcopydb/schema/post.list /tmp/pgcopydb/schema/post.dump .ft P .fi .UNINDENT .UNINDENT .SH AUTHOR Dimitri Fontaine .SH COPYRIGHT 2023, Dimitri Fontaine .\" Generated by docutils manpage writer. .