CODEWITHSUNDEEP
postgresqlpsql
Noitidart
Noitidart

Reputation: 37238

psql clean/reset database to just schema

I did an import to my database, but it came with content. I want to reset the database to just the schema. Meaning all content from tables dropped, but schema of tables left. And autoincrementing fields are reset to start from the beginning.

Is this possible with psql? I'm seeing commands that are dropping content with specific table commands, I just want a generic psql reset-to-schema.

Upvotes: 1

Views: 5049

Answers (1)

Islingre
Islingre

Reputation: 2349

Make a dump of your database (using pg_dump), drop and recreate the database (you now have an empty database) and restore all schema data (using pg_restore with option --schema-only). This will just restore the data definitions (tables, types, sequences, functions etc.) but not the data itself.

For more options, have a look at the manual. You might use these lines if all PG environment variables are set up as needed, otherwise you will at least need to specify some connection information:

pg_dump -F c -f <dump_file_name_here> <db_name_here>
pg_restore -c -C -F c -d <db_name_here> --schema-only <dump_file_name_here>

It is also possible to put the option --schema-only to the pg_dump. If you do not want to use the data anymore, this might give a huge speedup.

Upvotes: 4

Related Questions