Thread

1. restoring a database to its initial state

   Manlio Perillo <manlio.perillo@gmail.com> — 2010-03-13T12:48:14Z

   -----BEGIN PGP SIGNED MESSAGE-----
   Hash: SHA1
   
   Hi.
   
   Usually when I need to restore a database to its initial state, what I
   do is to simply drop it, and then re-create it.
   
   However on a shared hosting this is not possible.
   
   By initial state I mean:
     The content of template1 database
   or
     The database without all objects owned by role X, and that can be
     re-created by X
   
   where X is a role with normal privileges.
   
   
   I searched on pgFoundry, without success.
   
   It should not be hard to implement, but I would like to know if
   something similar has already been implemented.
   
   
   
   Thanks  Manlio
   -----BEGIN PGP SIGNATURE-----
   Version: GnuPG v1.4.10 (GNU/Linux)
   Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
   
   iEYEARECAAYFAkubiY4ACgkQscQJ24LbaURhBgCdHHSop6sxE1iGhGScLgQxTs0y
   F54An3uYbIA5NqXIb79n1IQZA4cKb7XQ
   =sooF
   -----END PGP SIGNATURE-----
   
   

2. Re: restoring a database to its initial state

   Pavel Stehule <pavel.stehule@gmail.com> — 2010-03-13T13:26:28Z

   Hello
   
   2010/3/13 Manlio Perillo <manlio.perillo@gmail.com>:
   > -----BEGIN PGP SIGNED MESSAGE-----
   > Hash: SHA1
   >
   > Hi.
   >
   > Usually when I need to restore a database to its initial state, what I
   > do is to simply drop it, and then re-create it.
   >
   > However on a shared hosting this is not possible.
   >
   > By initial state I mean:
   >  The content of template1 database
   > or
   >  The database without all objects owned by role X, and that can be
   >  re-created by X
   >
   > where X is a role with normal privileges.
   >
   >
   > I searched on pgFoundry, without success.
   >
   > It should not be hard to implement, but I would like to know if
   > something similar has already been implemented.
   >
   >
   
   Usually people use a install and a uninstall scripts. First creates
   dbobjects, second removes objects.
   
   Regards
   Pavel Stehule
   
   >
   > Thanks  Manlio
   > -----BEGIN PGP SIGNATURE-----
   > Version: GnuPG v1.4.10 (GNU/Linux)
   > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
   >
   > iEYEARECAAYFAkubiY4ACgkQscQJ24LbaURhBgCdHHSop6sxE1iGhGScLgQxTs0y
   > F54An3uYbIA5NqXIb79n1IQZA4cKb7XQ
   > =sooF
   > -----END PGP SIGNATURE-----
   >
   > --
   > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/mailpref/pgsql-general
   >
   
   

3. Re: restoring a database to its initial state

   Adrian von Bidder <avbidder@fortytwo.ch> — 2010-03-13T15:06:02Z

   Hyho!
   
   On Saturday 13 March 2010 13.48:14 Manlio Perillo wrote:
   
   > Usually when I need to restore a database to its initial state, what I
   > do is to simply drop it, and then re-create it.
   > 
   > However on a shared hosting this is not possible.
   
   Create a schema, modify your default search path so that you're always 
   working in your new schema, then you can just drop schema foo cascade;
   
   cheers
   -- vbi
   
   
   -- 
   Today is Boomtime, the 72nd day of Chaos in the YOLD 3176
   

4. Re: restoring a database to its initial state

   Tom Lane <tgl@sss.pgh.pa.us> — 2010-03-13T16:20:54Z

   Pavel Stehule <pavel.stehule@gmail.com> writes:
   > 2010/3/13 Manlio Perillo <manlio.perillo@gmail.com>:
   >> Usually when I need to restore a database to its initial state, what I
   >> do is to simply drop it, and then re-create it.
   >> However on a shared hosting this is not possible.
   
   > Usually people use a install and a uninstall scripts. First creates
   > dbobjects, second removes objects.
   
   "pg_dump --clean" can help with creating an uninstall script.
   
   			regards, tom lane
   
   

5. Re: restoring a database to its initial state

   Manlio Perillo <manlio.perillo@gmail.com> — 2010-03-16T15:14:43Z

   -----BEGIN PGP SIGNED MESSAGE-----
   Hash: SHA1
   
   Tom Lane ha scritto:
   > Pavel Stehule <pavel.stehule@gmail.com> writes:
   >> 2010/3/13 Manlio Perillo <manlio.perillo@gmail.com>:
   >>> Usually when I need to restore a database to its initial state, what I
   >>> do is to simply drop it, and then re-create it.
   >>> However on a shared hosting this is not possible.
   > 
   >> Usually people use a install and a uninstall scripts. First creates
   >> dbobjects, second removes objects.
   > 
   > "pg_dump --clean" can help with creating an uninstall script.
   > 
   
   The problem is that it is not possible to *just* having pg_dump generate
   the SQL statement for database cleanup.
   
   I was thinking to write a simple Python script for the job.
   It will read objects from the pg_catalog, having an associated owner:
   
    pg_class, pg_conversion, pg_database, pg_language, pg_namespace,
    pg_opclass, pg_operator, pg_opfamily, pg_proc, pg_tablespace,
    pg_ts_config, pg_ts_dict, pg_type
   
   For each object the script will issue an appropriate DROP CASCADE statement.
   
   Is this correct?
   
   
   Thanks  Manlio
   -----BEGIN PGP SIGNATURE-----
   Version: GnuPG v1.4.10 (GNU/Linux)
   Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
   
   iEYEARECAAYFAkufoGIACgkQscQJ24LbaUT3EgCghRQy4BdYtaGAXNE0Mx1gj0LT
   mdgAoIiRPvgEdUy+STDhfoppgz9MRQVZ
   =3uye
   -----END PGP SIGNATURE-----