Truncate tables and PostgreSQL before running snapshot.

[smozely]

Wondering if it is possible for the snapshotting process to truncate the data in the target tables before doing insertion?

It's part of the process of doing overnight snapshots from our production environment to our lower environments, wanting to make it a clone, but it doesn't seem possible to have it truncate the data first?

[eminano]

Hi @smozely !

You can configure the snapshot to clean the target database. This will drop and recreate the relevant schema objects, which will give you the same effect as the truncate.

You can enable it by:

• Setting the config field to true in the yaml configuration (source.postgres.snapshot.schema.pgdump_pgrestore.clean_target_db)

source:
  postgres:
   [...]
    snapshot: 
      schema: 
       [...]
        pgdump_pgrestore:
          clean_target_db: true # whether to clean the target database before restoring. Defaults to false

• Setting the environment variable to true (PGSTREAM_POSTGRES_SNAPSHOT_CLEAN_TARGET_DB)
• Passing the --reset flag to the pgstream snapshot command

You can find more details and examples for the configuration options in the docs here, as well as the CLI flag options.

Let us know if you have any other questions!