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