I'm used to working with SQL Server and the SQL Server Management Studio has the option to automatically generate a script to drop and recreate everything in a database (tables/views/procedures/etc). I find that when developing a new application and writing a bunch of junk in a local database for basic testing it's very helpful to have the options to just nuke the whole thing and recreate it in a clean slate, so I'm looking for a similar functionality within postgres/pgadmin.

PGAdmin has an option to generate a create script for a specific table but right clicking each table would be very tedious and I'm wondering if there's another way to do it.

CodePudding user response：

To recreate a clean schema only database you can use the pg_dump client included with a Postgres server install. The options to use are:

-c

--clean

Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.)

This option is ignored when emitting an archive (non-text) output file. For the archive formats, you can specify the option when you call pg_restore.

and:

-s

--schema-only

Dump only the object definitions (schema), not data.

This option is the inverse of --data-only. It is similar to, but for historical reasons not identical to, specifying --section=pre-data --section=post-data.

(Do not confuse this with the --schema option, which uses the word “schema” in a different meaning.)

To exclude table data for only a subset of tables in the database, see --exclude-table-data.

CodePudding user response：

clean in Flyway

The database migration tool Flyway offers a clean command that drops all objects in the configured schemas.

To quote the documentation:

Clean is a great help in development and test. It will effectively give you a fresh start, by wiping your configured schemas completely clean. All objects (tables, views, procedures, …) will be dropped.

Needless to say: do not use against your production DB!