PGCOPYDB DUMP(1)

NAME
pgcopydb dump - pgcopydb dump

pgcopydb dump - Dump database objects from a Postgres instance

This command prefixes the following sub-commands:

            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

PGCOPYDB DUMP SCHEMA

pgcopydb dump schema - Dump source database schema as custom files in target directory

The command pgcopydb dump schema uses pg_dump to export SQL schema definitions from the given source Postgres instance.

            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 - Dump source database roles as custome file in work directory

The command pgcopydb dump roles uses pg_dumpall --roles-only to export SQL definitions of the roles found on the source Postgres instance.

            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

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.

It is possible to use the option --no-role-passwords to operate without superuser privileges. In that case though, the passwords are not part of the dump and authentication might fail until passwords have been setup properly.

DESCRIPTION

The pgcopydb dump schema command implements the first step of the full database migration and 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 in 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 pgcopydb dump schema subcommand:

    --source
        Connection string to the source Postgres instance. See the Postgres documentation for connection strings for the details. In short 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. Temporary files are created in the directory specified by this option, or defaults 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.

    --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

AUTHOR
Dimitri Fontaine

COPYRIGHT
2022-2024, Dimitri Fontaine