PGCOPYDB COPY(1)
✨ NAME ✨
okay so bestie, pull up a chair, because this is pgcopydb copy — straight out of the PGCOPYDB COPY(1) manual 💖
pgcopydb copy — she implements the data section of the database copy. that's it, that's the whole job. but wait, there's a whole girlband of sub-commands hanging off this thing, and lowkey they're all iconic in their own way 👑
This command prefixes the following sub-commands:
pgcopydb copy: Implement the data section of the database copy
Available commands:
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 the 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 targetNot me about to explain every single one of these like they're my children 🥹 Those commands implement a part of the whole database copy operation, as detailed in section pgcopydb clone <#id1>. So here's the tea: only reach for these bad boys when you're debugging a specific part, or because you already know you just want to implement that one step. not a vibe, not a guess — you gotta know.
⚠️ Warning: Using thepgcopydb clonecommand is strongly advised. This mode of operations is useful for debugging and advanced use cases only. fr fr, if you're just here for a normal copy, go runpgcopydb cloneand let it cook 🍓
🎀 PGCOPYDB COPY DB 🎀
pgcopydb copy db — copy an entire database from source to target 🌈
okay so plot twist: pgcopydb copy db is literally just an alias for pgcopydb clone. See also pgcopydb clone <#id1>. same girl, different name, main character energy 💅
pgcopydb copy db: Copy an entire database from source to target
usage: pgcopydb copy db --source ... --target ... [ --table-jobs ... --index-jobs ... ]--source— the Postgres URI that points at your source database 🫶--target— the Postgres URI that points at your target database ✨--dir— the work directory to use--table-jobs— how many concurrent COPY jobs to run--index-jobs— how many concurrentCREATE INDEXjobs to run--restore-jobs— how many concurrent jobs to run forpg_restore--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--no-tablespaces— do not output commands to select tablespaces--skip-large-objects— skip copying large objects (blobs) 🙈--filters <filename>— use the filters defined in <filename>--fail-fast— abort early in case of error 💀--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 withpg_export_snapshot--use-copy-binary— use theCOPY BINARYformat forCOPYoperations
🌸 PGCOPYDB COPY ROLES 🌸
pgcopydb copy roles — copy the roles from the source instance to the target instance 👑
so this command is giving boss energy, because pgcopydb copy roles implements both pgcopydb dump roles <#pgcopydb-dump-roles> and then pgcopydb restore roles <#pgcopydb-restore-roles>. two for one, we love a multitasker 💅
pgcopydb copy roles: Copy the roles from the source instance to the target instance
usage: pgcopydb copy roles --source ... --target ...--source— the Postgres URI that points at your source database--target— the Postgres URI that points at your target database--dir— the work directory to use--no-role-passwords— do not dump passwords for roles 🔒
📌 Note: In Postgres, roles are a global object. That means roles don't belong to any specific database, and as a result — even when the pgcopydb 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. she's global, periodt 🌍
When a role already exists on the target database, its restoring is entirely skipped, which includes skipping both the CREATE ROLE and the ALTER ROLE commands produced by pg_dumpall --roles-only. no double-dipping ✋
The pg_dumpall --roles-only 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. so like, bring your keys 🔑
🦋 PGCOPYDB COPY EXTENSIONS 🦋
pgcopydb copy extensions — copy the extensions from the source instance to the target instance ✨
The way this one works: pgcopydb copy extensions gets a list of the extensions installed on the source database, and for each of them it runs the SQL command CREATE EXTENSION IF NOT EXISTS. cute, efficient, no notes 💖
pgcopydb copy extensions: Copy the extensions from the source instance to the target instance
usage: pgcopydb copy extensions --source ... --target ...--source— the Postgres URI that points at your source database--target— the Postgres URI that points at your target database--dir— the work directory to use--requirements— list extensions requirements 📋
When copying extensions, this command also takes care of copying any [Extension Configuration Tables](https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES) user-data to the target database. yep, the user-data comes too, she brings her whole entourage 🎀
🌈 PGCOPYDB COPY SCHEMA 🌈
pgcopydb copy schema — copy the database schema from source to target 🏗️
pgcopydb copy schema implements the schema only section of the clone steps. just the bones, bestie, no data attached 💀
pgcopydb copy schema: Copy the database schema from source to target
usage: pgcopydb copy schema --source ... --target ... [ --table-jobs ... --index-jobs ... ]--source— the Postgres URI that points at your source database--target— the Postgres URI that points at your target database--dir— the work directory to use--filters <filename>— use the filters defined in <filename>--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 withpg_export_snapshot
💜 PGCOPYDB COPY DATA 💜
pgcopydb copy data — copy the data section from source to target 🍓
pgcopydb copy data implements the data section of the clone steps. the main event, the moment, the slay 💅
pgcopydb copy data: Copy the data section from source to target
usage: pgcopydb copy data --source ... --target ... [ --table-jobs ... --index-jobs ... ]--source— the Postgres URI that points at your source database--target— the Postgres URI that points at your target database--dir— the work directory to use--table-jobs— how many concurrent COPY jobs to run--index-jobs— how many concurrentCREATE INDEXjobs to run--restore-jobs— how many concurrent jobs to run forpg_restore--skip-large-objects— skip copying large objects (blobs) 🙈--filters <filename>— use the filters defined in <filename>--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 withpg_export_snapshot
📌 Note: The current command line has both the commandspgcopydb copy table-dataandpgcopydb copy data, which are looking quite similar but implement different steps. Be careful for now. This will change later. lowkey confusing, I know, but stay focused bestie 😭
The pgcopydb copy data command implements the following steps:
$ pgcopydb copy table-data
$ pgcopydb copy blobs
$ pgcopydb copy indexes
$ pgcopydb copy constraints
$ pgcopydb copy sequences
$ vacuumdb -zThose steps are actually done concurrently to one another when that's possible, in the same way as the main command pgcopydb clone would. The only difference is that the pgcopydb clone command also prepares and finishes the schema parts of the operations (pre-data, then post-data), which the pgcopydb copy data command ignores. so she skips the pregame and the afterparty and just does the dancing 💃
🧚 PGCOPYDB COPY TABLE-DATA 🧚
pgcopydb copy table-data — copy the data from all tables in database from source to target 📦
So pgcopydb copy table-data fetches the list of tables from the source database and runs a COPY TO command on the source database, then sends the result to the target database using a COPY FROM command directly, avoiding disks entirely. no middleman, no temp files, just vibes ✨
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— the Postgres URI that points at your source database--target— the Postgres URI that points at your target database--dir— the work directory to use--table-jobs— how many concurrent COPY jobs to run--split-tables-larger-than— same-table concurrency size threshold 📏--split-max-parts— maximum number of jobs for Same-table concurrency--filters <filename>— use the filters defined in <filename>--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 withpg_export_snapshot
🫧 PGCOPYDB COPY BLOBS 🫧
pgcopydb copy blobs — copy all the blob data from the source database over to the target, bestie 🦋
okay so this one fetches the whole list of large objects (aka blobs, the big chonky binary friends) from the source database and then copies their data parts over to the target database. And here's the lil detail that matters: by default it assumes the large objects metadata has already been handled — because that's literally what pg_dump --section=pre-data does and leaves behind. So metadata = already done, data = this command's whole job. 💅
pgcopydb copy blobs: Copy the blob data from the source database to the target
usage: pgcopydb copy blobs --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
--large-objects-jobs Number of concurrent Large Objects jobs to run
--drop-if-exists On the target database, drop and create large objects
--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_snapshotthe options, decoded for u 🎀
--source— the Postgres URI pointing at your source database--target— the Postgres URI pointing at your target database--dir— the work directory it should use--large-objects-jobs— how many concurrent Large Objects jobs to run at once--drop-if-exists— on the target database, drop and then create the large objects--restart— lets you restart even when temp files are already sitting there--resume— lets you resume operations after a failure--not-consistent— allows taking a brand new snapshot on the source database--snapshot— use the snapshot you got frompg_export_snapshot
🌸 PGCOPYDB COPY SEQUENCES 🌸
pgcopydb copy sequences — copy the current value of every sequence in the database, source ➡️ target ✨
the way this works is so clean honestly. it grabs the list of sequences from the source database, and then for each and every sequence it fetches the last_value and is_called properties — doing it exactly the way pg_dump would on the source — and then for each sequence it calls pg_catalog.setval() on the target database. so your counters stay in sync, no cap 🫶
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
--filters <filename> Use the filters defined in <filename>
--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_snapshotthe lineup 💜
--source— Postgres URI to the source database--target— Postgres URI to the target database--dir— work directory to use--filters<filename>— use the filters defined in<filename>--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 withpg_export_snapshot
👑 PGCOPYDB COPY INDEXES 👑
pgcopydb copy indexes — build every index the source has, over on the target 🔥
so it fetches the list of indexes from the source database and runs each CREATE INDEX statement on the target database. and here's the lil glow-up: those index definition statements get modified to include IF NOT EXISTS, so it can just skip any index that already exists on the target. iconic behaviour, honestly. 💅
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
--restore-jobs Number of concurrent jobs for pg_restore
--filters <filename> Use the filters defined in <filename>
--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 databaseyour options, gorgeous 🌈
--source— Postgres URI to the source database--target— Postgres URI to the target database--dir— work directory to use--index-jobs— number of concurrentCREATE INDEXjobs to run--restore-jobs— number of concurrent jobs forpg_restore--filters<filename>— use the filters defined in<filename>--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
🎀 PGCOPYDB COPY CONSTRAINTS 🎀
pgcopydb copy constraints — add every constraint the source has onto the target 🦋
it fetches the list of indexes from the source database and runs each and every ALTER TABLE ... ADD CONSTRAINT ... USING INDEX statement on the target database.
⚠️ ok so listen — the indexes must already exist for this to work, bestie. and if any constraint is found already existing on the target database, the command will fail. no quiet skipping here, it just says no. 🙈
pgcopydb copy constraints: Create all the constraints found in the source database in the target
usage: pgcopydb copy constraints --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 <filename> Use the filters defined in <filename>
--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 databasethe flags, all of em ✨
--source— Postgres URI to the source database--target— Postgres URI to the target database--dir— work directory to use--filters<filename>— use the filters defined in<filename>--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
💖 DESCRIPTION 💖
so like, what are all these copy commands even for? they let you run one specific step of the pgcopydb operations at a time. it's mainly a debugging thing, though honestly some advanced and creative usage can absolutely be made from the commands. main character energy. 🌟
one thing to know: the target schema is not created by any of this, so you need to have taken care of that first. to prep your target database you can use pgcopydb dump schema <# pgcopydb-dump-schema> and then pgcopydb restore pre-data <# pgcopydb-restore-pre-data>. 🧚
and if you wanna do the exact same operations that a pgcopydb clone command would do, here's the recipe — chef's kiss, fr fr 👩🍳
$ export PGCOPYDB_SOURCE_PGURI="postgres://user@source/dbname"
$ export PGCOPYDB_TARGET_PGURI="postgres://user@target/dbname"
$ 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-consistentreal talk though: the main pgcopydb clone is still better at concurrency than doing all those steps manually 💀 because it creates the indexes for any given table as soon as that table's data section is finished — it does not have to wait until the last table-data has been copied over. the exact same thing applies to constraints, and then vacuum analyze. so like, the manual recipe works but it's not the speedrun version. 🏃♀️💨
✨ OPTIONS ✨
okay bestie, here's the full menu of flags you can sprinkle on your pgcopydb copy sub-commands 💅 every single one matters, no skipping.
--source— the connection string to your source Postgres instance. like, where are we copying from? check the Postgres docs for connection strings if you need the deets. short version: both the quoted form"host=... dbname=..."and the URI formpostgres://user@host:5432/dbnameare supported. periodt.--target— connection string to the target Postgres instance. where we're copying to. obviously.--dir— during normal operationspgcopydbcreates a lot of temporary files to track sub-processes progress. those temp files go in the directory you set here, or default to${TMPDIR}/pgcopydbwhen the environment variable is set, or otherwise to/tmp/pgcopydb. so basically your work-dir vibe. 📁--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't needed when you use this, the role info is read from the catalog viewpg_rolesinstead ofpg_authid. so this also helps if access topg_authidis restricted by some security policy. lowkey a security flex 🔐--table-jobs— how many tables can be processed in parallel. this limit only applies to theCOPYoperations, more sub-processes will be running at the same time that this limit while theCREATE INDEXoperations are in progress, though then the processes are only waiting for the target Postgres instance to do all the work. so it's a soft cap, not a hard one, iykyk.--index-jobs— how many indexes can be built in parallel, globally. a good option is to set this 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 theCOPYoperations. like, don't starve your copy jobs for the sake of indexes, bestie.--large-object-jobs— how many worker processes to start to copy Large Objects concurrently. blobs need love too 💖--split-tables-larger-than— allow Same-table Concurrency <#same-table-concurrency> when processing the source database. the value is expected to be a byte size, and bytes unitsB,kB,MB,GB,TB,PB, andEBare known. so you can say--split-tables-larger-than=1GBand it just gets it.--skip-large-objects— skip copying large objects, also known as blobs, when copying the data from the source database to the target database. not today, blobs. 🙈--restart— when running thepgcopydbcommand 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. in that case, the--restartoption can be used to allowpgcopydbto delete traces from a previous run. so it's the "okay fine, wipe it, we ball" button 🔥--resume— when thepgcopydbcommand was terminated before completion, either by an interrupt signal (such as C-c orSIGTERM) or because it crashed, it is possible to resume the database migration. 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 theCOPYhas to be started from scratch even when using--resume: theCOPYcommand in Postgres is transactional and was rolled back. same reasonning applies to theCREATE INDEXcommands andALTER TABLEcommands thatpgcopydbissues, those commands are skipped on a--resumerun only if known to have run through to completion on the previous one. finally, using--resumerequires the use of--not-consistent. no cap, that's the deal. 😭--not-consistent— in order to be consistent,pgcopydbexports a Postgres snapshot by calling thepg_export_snapshot()<https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION-TABLE> function on the source database server. the snapshot is then re-used in all the connections to the source database server by using theSET TRANSACTION SNAPSHOTcommand.
per the Postgres documentation about pg_export_snapshot:
Saves the transaction's 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.
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. so like, it's giving "we tried our best but the vibe shifted" 💀
--snapshot— instead of exporting its own snapshot by calling the PostgreSQL functionpg_export_snapshot()it is possible forpgcopydbto re-use an already exported snapshot. reusing is caring 🫶--use-copy-binary— use theCOPY WITH (FORMAT BINARY)instead of theCOPYcommand. see also documentation forCOPY<https://www.postgresql.org/docs/current/sql-copy.html>. binary is the main character here 🔥--verbose— increase current verbosity. the default level of verbosity isINFO. in ascending orderpgcopydbknows about the following verbosity levels:FATAL,ERROR,WARN,INFO,NOTICE,DEBUG,TRACE. so you can go from "just the tea" to "literally everything" ☕--debug— set current verbosity toDEBUGlevel. for when you need the receipts.--trace— set current verbosity toTRACElevel. maximum overdrive, no filter, we're seeing it all 🌈--quiet— set current verbosity toERRORlevel. only the messy stuff shows up. shhh 🤫
🌸 ENVIRONMENT 🌸
bestie, you don't always have to type the flags. the environment can hold your hand too. here's every variable that matters.
PGCOPYDB_SOURCE_PGURI
connection string to the source Postgres instance. when --source is ommitted from the command line, then this environment variable is used. so set it once and forget it 💅
PGCOPYDB_TARGET_PGURI
connection string to the target Postgres instance. when --target is ommitted from the command line, then this environment variable is used. same energy, different destination.
PGCOPYDB_TABLE_JOBS
number of concurrent jobs allowed to run COPY operations in parallel. when --table-jobs is ommitted from the command line, then this environment variable is used. set it and let it cook.
PGCOPYDB_INDEX_JOBS
number of concurrent jobs allowed to run CREATE INDEX operations in parallel. when --index-jobs is ommitted from the command line, then this environment variable is used. your indexes are gonna slay ✨
PGCOPYDB_RESTORE_JOBS
number of concurrent jobs allowed to run pg_restore operations in parallel. when --restore-jobs is ommitted from the command line, then this environment variable is used. restore, but make it parallel.
PGCOPYDB_LARGE_OBJECTS_JOBS
number of concurrent jobs allowed to copy Large Objects data in parallel. when --large-objects-jobs is ommitted from the command line, then this environment variable is used. big blobs need big energy 🦋
PGCOPYDB_SPLIT_TABLES_LARGER_THAN
allow Same-table Concurrency <#same-table-concurrency> 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. when --split-tables-larger-than is ommitted from the command line, then this environment variable is used. so yeah, same as the flag but in env form.
PGCOPYDB_SPLIT_MAX_PARTS
limit the maximum number of parts when Same-table Concurrency <#same-table-concurrency> is used. when --split-max-parts is ommitted from the command line, then this environment variable is used. don't let the split go feral.
PGCOPYDB_ESTIMATE_TABLE_SIZES
when true (or yes, or on, or 1, same input as a Postgres boolean) then pgcopydb estimates the size of tables to determine whether or not to split tables. this option is only useful when querying the relation sizes on source database is costly. when --estimate-table-sizes is ommitted from the command line, then this environment variable is used. estimate, don't calculate. big brain move 🧠
PGCOPYDB_DROP_IF_EXISTS
when true (or yes, or on, or 1, same input as a Postgres boolean) then pgcopydb uses the pg_restore options --clean --if-exists when creating the schema on the target Postgres instance. so if it exists, drop it, no drama. 💅
PGCOPYDB_SNAPSHOT
Postgres snapshot identifier to re-use, see also --snapshot. reuse the snapshot, save the vibe.
PGCOPYDB_USE_COPY_BINARY
when true (or yes, or on, or 1, same input as a Postgres boolean) then pgcopydb uses the COPY WITH (FORMAT BINARY) instead of the COPY command, same as when using the --use-copy-binary option. binary mode go brrr 🔥
TMPDIR
the pgcopydb command creates all its work files and directories in ${TMPDIR}/pgcopydb, and defaults to /tmp/pgcopydb. so if you want your temp files somewhere else, set TMPDIR and it just works. 🎀
💅 EXAMPLES 💅
ok bestie, buckle up, here's the whole glow-up from source db to target db, step by step, no skips, we're not gatekeeping 💖
First, let's export the Postgres database connection strings so we can just re-use them the entire way through instead of retyping them like a clown:
$ export PGCOPYDB_SOURCE_PGURI=postgres://pagila:0wn3d@source/pagila
$ export PGCOPYDB_TARGET_PGURI=postgres://pagila:0wn3d@target/pagilaNow, first up in the lineup — dump that schema ✨
$ pgcopydb dump schema
08:27:48.633 44371 INFO Using work dir "/tmp/pgcopydb"
08:27:48.634 44371 INFO Dumping database from "postgres://pagila:0wn3d@source/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
08:27:48.634 44371 INFO Dumping database into directory "/tmp/pgcopydb"
08:27:48.634 44371 INFO Using pg_dump for Postgres "16.2" at "/usr/bin/pg_dump"
08:27:48.971 44371 INFO Fetched information for 5 tables (including 0 tables split in 0 partitions total), with an estimated total of 1000 thousands tuples and 128 MB on-disk
08:27:48.978 44371 INFO Fetched information for 4 indexes (supporting 4 constraints)
08:27:48.983 44371 INFO Fetching information for 1 sequences
08:27:48.996 44371 INFO Fetched information for 1 extensions
08:27:49.072 44371 INFO Found 5 indexes (supporting 5 constraints) in the target database
08:27:49.078 44371 INFO /usr/bin/pg_dump -Fc --section=pre-data --section=post-data --file /tmp/pgcopydb/schema/schema.dump 'postgres://pagila:0wn3d@source/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60'Next: we restore the pre-data schema onto the target database, and because we pass the env var, it cleans up any objects that are already sitting there. That's what lets you run this whole test scenario again and again and again 🌀 It might not be what you want to do in your production target instance though!! 🚨 like, read that again, please.
$ PGCOPYDB_DROP_IF_EXISTS=on pgcopydb restore pre-data --no-owner --resume --not-consistent
08:30:13.621 44597 INFO Using work dir "/tmp/pgcopydb"
08:30:13.621 44597 INFO Restoring database from existing files at "/tmp/pgcopydb"
08:30:13.706 44597 INFO Found 5 indexes (supporting 5 constraints) in the target database
08:30:13.710 44597 INFO Using pg_restore for Postgres "16.2" at "/usr/bin/pg_restore"
08:30:13.711 44597 INFO [TARGET] Restoring database into "postgres://pagila:0wn3d@target/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
08:30:13.730 44597 INFO Drop tables on the target database, per --drop-if-exists
08:30:13.774 44597 INFO /usr/bin/pg_restore --dbname 'postgres://pagila:0wn3d@target/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60' --section pre-data --jobs 4 --clean --if-exists --no-owner --use-list /tmp/pgcopydb/schema/pre-filtered.list /tmp/pgcopydb/schema/schema.dumpThen we copy the data over 🦋
$ pgcopydb copy table-data --resume --not-consistent
08:34:02.813 44834 INFO [SOURCE] Copying database from "postgres://pagila:0wn3d@source/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
08:34:02.813 44834 INFO [TARGET] Copying database into "postgres://pagila:0wn3d@target/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
08:34:02.861 44834 INFO Using work dir "/tmp/pgcopydb"
08:34:02.862 44834 INFO Copy data from source to target in sub-processes
08:34:02.863 44834 INFO Re-using catalog caches
08:34:02.863 44834 INFO STEP 4: starting 4 table-data COPY processesAnd now we create the indexes on the target database, using the index definitions from the source database:
$ pgcopydb copy indexes --resume --not-consistent
14:28:53 47 INFO Running pgcopydb version 0.13.38.g22e6544.dirty from "/usr/local/bin/pgcopydb"
14:28:53 47 INFO [SOURCE] Copying database from "postgres://pagila@source/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
14:28:53 47 INFO [TARGET] Copying database into "postgres://pagila@target/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
14:28:53 47 INFO Schema dump for pre-data and post-data section have been done
14:28:53 47 INFO Pre-data schema has been restored on the target instance
14:28:53 47 INFO All the table data has been copied to the target instance
14:28:53 47 INFO All the indexes have been copied to the target instance
14:28:53 47 INFO Fetched information for 54 indexes
14:28:53 47 INFO Creating 54 indexes in the target database using 4 processes
Step Connection Duration Transfer Concurrency
-------------------------------------------------- ---------- ---------- ---------- ------------
Dump Schema source 0ms 1
Catalog Queries (table ordering, filtering, etc) source 0ms 1
Prepare Schema target 0ms 1
COPY, INDEX, CONSTRAINTS, VACUUM (wall clock) both 0ms 4 + 8
COPY (cumulative) both 0ms 0 B 4
Large Objects (cumulative) both 4
CREATE INDEX, CONSTRAINTS (cumulative) target 0ms 4
Finalize Schema target 0ms 1
-------------------------------------------------- ---------- ---------- ---------- ------------
Total Wall Clock Duration both 696ms 4 + 8
-------------------------------------------------- ---------- ---------- ---------- ------------Now we re-create the constraints (primary key, unique constraints) from the source database schema into the target database 👑
$ pgcopydb copy constraints --resume --not-consistent
14:28:54 53 INFO Running pgcopydb version 0.13.38.g22e6544.dirty from "/usr/local/bin/pgcopydb"
14:28:54 53 INFO [SOURCE] Copying database from "postgres://pagila@source/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
14:28:54 53 INFO [TARGET] Copying database into "postgres://pagila@target/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
14:28:54 53 INFO Schema dump for pre-data and post-data section have been done
14:28:54 53 INFO Pre-data schema has been restored on the target instance
14:28:54 53 INFO All the table data has been copied to the target instance
14:28:54 53 INFO All the indexes have been copied to the target instance
14:28:54 53 INFO Create constraints
14:28:54 53 INFO Fetched information for 54 indexes
14:28:54 53 INFO Creating 54 indexes in the target database using 4 processes
Step Connection Duration Transfer Concurrency
-------------------------------------------------- ---------- ---------- ---------- ------------
Dump Schema source 0ms 1
Catalog Queries (table ordering, filtering, etc) source 0ms 1
Prepare Schema target 0ms 1
COPY, INDEX, CONSTRAINTS, VACUUM (wall clock) both 0ms 4 + 8
COPY (cumulative) both 0ms 0 B 4
Large Objects (cumulative) both 4
CREATE INDEX, CONSTRAINTS (cumulative) target 0ms 4
Finalize Schema target 0ms 1
-------------------------------------------------- ---------- ---------- ---------- ------------
Total Wall Clock Duration both 283ms 4 + 8
-------------------------------------------------- ---------- ---------- ---------- ------------The next step is a VACUUM ANALYZE on every table that we just filled in with data, and for that we can literally just use the vacuumdb command from Postgres (https://www.postgresql.org/docs/current/app-vacuumdb.html) and let it cook:
$ vacuumdb --analyze --dbname "$PGCOPYDB_TARGET_PGURI" --jobs 4
vacuumdb: vacuuming database "pagila"Finally, finally, we restore the post-data section of the schema 🎀
$ pgcopydb restore post-data --resume --not-consistent
14:28:54 60 INFO Running pgcopydb version 0.13.38.g22e6544.dirty from "/usr/local/bin/pgcopydb"
14:28:54 60 INFO Schema dump for pre-data and post-data section have been done
14:28:54 60 INFO Pre-data schema has been restored on the target instance
14:28:54 60 INFO All the table data has been copied to the target instance
14:28:54 60 INFO All the indexes have been copied to the target instance
14:28:54 60 INFO Restoring database from existing files at "/tmp/pgcopydb"
14:28:54 60 INFO Using pg_restore for Postgres "16.1" at "/usr/bin/pg_restore"
14:28:54 60 INFO [TARGET] Restoring database into "postgres://pagila@target/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
14:28:55 60 INFO /usr/bin/pg_restore --dbname 'postgres://pagila@target/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60' --single-transaction --use-list /tmp/pgcopydb/schema/post-filtered.list /tmp/pgcopydb/schema/schema.dump🌟 Author 🌟
Dimitri Fontaine — the icon behind all of this, no cap 💖
🎀 Copyright 🎀
2022-2024, Dimitri Fontaine
and that's the whole thing bestie, we ate and left zero crumbs 🫶 this was PGCOPYDB COPY(1), version 0.18, dated Jun 28, 2026 — go forth and copy, you've got this 💅✨
📦 where this page comes from
- ubuntu stonking: pgcopydb 0.18-1 · PostgreSQL
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms