Postgres Pro
Downloads
Home   >   mailing lists

Thread: restoring a database to its initial state

restoring a database to its initial state

From
Manlio Perillo
Date:
-----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-----

Re: restoring a database to its initial state

From
Pavel Stehule
Date:
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
>

Re: restoring a database to its initial state

From
Adrian von Bidder
Date:
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
Attachment

Re: restoring a database to its initial state

From
Tom Lane
Date:
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

Re: restoring a database to its initial state

From
Manlio Perillo
Date:
-----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-----