Thread: dumping the schema
How to DUMP the Database schema, (so later can get it back i.e recreate everything). I need only the schema (tables, users, groups etc..) now (not the data, is it any different if i want the data too). the dump should be in order so that i can just execute it... One more thing I created db and tables with default config (with pgadmin3) i.e. owner postgres, Now I want to switch all to other user I just created, can I do it "at once" or I have to go and edit permission of every table... Can I remove any access to the DB for "postgres" and "public" and leave such access to my new user OR they should stay for some db-functionality.. tia
raptor@tvskat.net wrote: > How to DUMP the Database schema, (so later can get it back i.e recreate everything). > I need only the schema (tables, users, groups etc..) now (not the data, is it any different if i want the data too). http://www.postgresql.org/docs/7.4/static/app-pgdump.html Check --schema-only/--data-only options. > the dump should be in order so that i can just execute it... psql dbname < dump.file This is the simplest way of restoring a dump. > One more thing I created db and tables with default config (with pgadmin3) i.e. owner postgres, > Now I want to switch all to other user I just created, can I do it "at once" or I > have to go and edit permission of every table... Dump the schema and hand-edit it to change ownership. > Can I remove any access to the DB for "postgres" and "public" and leave such access to > my new user OR they should stay for some db-functionality.. Well, you better leave those users and accesses in place on system objects at least. For use defined table, you can fiddle around as you please. HTH Shridhar