restoring a database to its initial state

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

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

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

-----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-----