PGCOPYDB DUMP(1)

📦
version 0.18 · 6 versions
, in ubuntu stonking

💅 NAME 💅

okay so pgcopydb dump — that's the command, and this whole page lives in section 1 of the pgcopydb manual ✨

pgcopydb dump — dump database objects from a Postgres instance 🫶

This command is literally the group chat that the following sub-commands hang out in:

pgcopydb dump: Dump database objects from a Postgres instance

Available commands:
  pgcopydb dump
    schema  Dump source database schema as custom files in work directory
    roles   Dump source database roles as custome file in work directory

(not the typo bestie, that's genuinely how it's spelled up there 💀 copying it char for char, no cap)

🌸 PGCOPYDB DUMP SCHEMA 🌸

pgcopydb dump schema — dumps the source database schema as custom files in the target directory 💖

So the way that pgcopydb dump schema works is it calls on pg_dump to export the SQL schema definitions from the source Postgres instance you hand it. iconic behavior honestly 👑

pgcopydb dump schema: Dump source database schema as custom files in work directory
usage: pgcopydb dump schema  --source <URI>

  --source             Postgres URI to the source database
  --target             Directory where to save the dump files
  --dir                Work directory to use
  --skip-extensions    Skip restoring extensions
  --filters <filename> Use the filters defined in <filename>
  --snapshot           Use snapshot obtained with pg_export_snapshot

🦋 PGCOPYDB DUMP ROLES 🦋

pgcopydb dump roles — dumps the source database roles as a custome file in the work directory 💜

pgcopydb dump roles uses pg_dumpall --roles-only to export the SQL definitions of all the roles it finds hanging out on the source Postgres instance. so slay 🌈

pgcopydb dump roles: Dump source database roles as custome file in work directory
usage: pgcopydb dump roles  --source <URI>

  --source            Postgres URI to the source database
  --target            Directory where to save the dump files
  --dir               Work directory to use
  --no-role-passwords Do not dump passwords for roles

Because the pg_dumpall --roles-only call is what fetches the role list from the source database, and this command includes support for passwords, that means this whole operation requires superuser privileges. periodt, no negotiating 😭

Good news tho: you can use the option --no-role-passwords to do the thing without superuser privileges 🫶 But heads up — in that case the passwords are just not part of the dump, so authentication might fail until passwords have been set up properly. fair warning bestie ✨

🎀 DESCRIPTION 🎀

The pgcopydb dump schema command is doing the very first step of the full database migration — it fetches the schema definitions from the source database 👑

When the command runs, it calls pg_dump to get the pre-data schema and the post-data schema output into a Postgres custom file called schema.dump 💾

The output files are written to the schema sub-directory of the --target directory 🗂️

✨ OPTIONS ✨

The following options are available to the pgcopydb dump schema subcommand 💖

  • --source — connection string to the source Postgres instance. See the Postgres documentation for connection strings <https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING> if you want the deep lore. tl;dr: both the quoted form "host=... dbname=..." and the URI form postgres://user@host:5432/dbname are supported 🫶
  • --target — connection string to the target Postgres instance.
  • --dir — during its normal operations pgcopydb creates a lot of temporary files to track sub-processes progress (so many files, it's giving chaos 🌪️). Those temporary files are created in the directory specified by this option, or they default to ${TMPDIR}/pgcopydb when the environment variable is set, or otherwise to /tmp/pgcopydb.
  • --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 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 — so it's lowkey a workaround too 💅
  • --snapshot — instead of exporting its own snapshot by calling the PostgreSQL function pg_export_snapshot() it is possible for pgcopydb to re-use an already exported snapshot 📸
  • --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 🗣️
  • --debug — set current verbosity to DEBUG level 🐛
  • --trace — set current verbosity to TRACE level 🔍
  • --quiet — set current verbosity to ERROR level 🤫

🌈 ENVIRONMENT 🌈

  • PGCOPYDB_SOURCE_PGURI — connection string to the source Postgres instance. When --source is ommitted from the command line, then this environment variable is used 💫

🔥 EXAMPLES 🔥

First, using pgcopydb dump schema:

$ pgcopydb dump schema --source "port=5501 dbname=demo" --target /tmp/target
09:35:21 3926 INFO  Dumping database from "port=5501 dbname=demo"
09:35:21 3926 INFO  Dumping database into directory "/tmp/target"
09:35:21 3926 INFO  Found a stale pidfile at "/tmp/target/pgcopydb.pid"
09:35:21 3926 WARN  Removing the stale pid file "/tmp/target/pgcopydb.pid"
09:35:21 3926 INFO  Using pg_dump for Postgres "12.9" at "/Applications/Postgres.app/Contents/Versions/12/bin/pg_dump"
09:35:21 3926 INFO   /Applications/Postgres.app/Contents/Versions/12/bin/pg_dump -Fc --section pre-data --section post-data --file /tmp/target/schema/schema.dump 'port=5501 dbname=demo'

Once the previous command is finished, the pg_dump output file can be found in /tmp/target/schema and is named schema.dump 🎉 Additionally, other files and directories have been created.

$ find /tmp/target
/tmp/target
/tmp/target/pgcopydb.pid
/tmp/target/schema
/tmp/target/schema/schema.dump
/tmp/target/run
/tmp/target/run/tables
/tmp/target/run/indexes

look at all those little files, they're thriving 🌱

💖 Author 💖

Dimitri Fontaine 👑

2022-2024, Dimitri Fontaine

and that's the whole page bestie, you ate 💅✨ this beauty ships as version 0.18 from the pgcopydb source, dated Jun 28, 2026, and it's filed under PGCOPYDB DUMP(1) — go forth and dump, main character 🎀🦋

📦 where this page comes from