[NEON][BACKUP] Creating a dump of a Postgres db does not delete existing tables

[zaeema-n]

Add the --clean command when taking a dump of the postgres database.

When you run pg_dump with --clean, the dump file will include DROP statements before each object (TABLE, SEQUENCE, VIEW, etc.).

If you restore a dump with --clean, you don’t need to manually drop tables first.

Restores become idempotent — safe to run even if the DB already has some objects.

[zaeema-n]

Description

The current backup script does not delete existing tables in the Postgres table before restoring the database. This can cause issues where if you attempt to restore a dump to a postgres database without first clearing existing tables in the database, old data will be retained in the database.

Steps to reproduce

Refer to OpenGIN/deployment/development/backup_and_restore_workflow.md for instructions on how to take a backup and restore a backup. Attempt to restore a postgres backup twice, the database will not be cleared before the second restore.

Possible Solution

Note this solution has not been tested yet, feel free to experiment with other solutions as well:

Add the --clean command when taking a dump of the postgres database.

When you run pg_dump with --clean, the dump file will include DROP statements before each object (TABLE, SEQUENCE, VIEW, etc.).

If you restore a dump with --clean, you don’t need to manually drop tables first.

Restores become idempotent — safe to run even if the DB already has some objects.

Additional Context

The relevant script can be found at OpenGIN/deployment/development/init.sh, relevant documentation can be found at OpenGIN/deployment/development/backup_and_restore_workflow.md.

Once the fix is done, ensure it is tested thoroughly and any relevant documentation is updated if necessary.

[KavindaRajapaksha]

@zaeema-n create a PR for this issue:KavindaRajapaksha:fix/postgres-pgdump-clean-restore

[zaeema-n]

Hi Kavinda. Thank you for your PR. We will review it and get back to you.