Thread: Urgent: Restoring many schemas in a database

Urgent: Restoring many schemas in a database

From
"Stephen J. Thompson"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello all,

I am trying to restore a 7.3 database with many schemas and schema users in
it. The issue I am having is that there are many \connect statements in the
backup file and it keeps asking me for a new password each time. Is there an
easier way than examining the file and carefully following the connect
statement orders and entering the correct password. (btw there are over 100
connect statements).

Thanks.


Stephen.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE+3yA7DAwPLUWQb6sRAn27AJ9TgcQ8BNqStKA6WKGpV3+De26m5wCgnHas
5jBZaAp/3NchLvRpof14emU=
=Z9On
-----END PGP SIGNATURE-----


Re: Urgent: Restoring many schemas in a database

From
Date:
Hi,

have you  considered the option of putting   "trust"
as auhentication in pg_hba.conf already.

other option is to user .pgpass file but i  am not
sure of the exact details


regds
mallah.

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello all,
>
> I am trying to restore a 7.3 database with many schemas and schema users in  it. The issue I am
> having is that there are many \connect statements in the  backup file and it keeps asking me
> for a new password each time. Is there an  easier way than examining the file and carefully
> following the connect  statement orders and entering the correct password. (btw there are over
> 100  connect statements).
>
> Thanks.
>
>
> Stephen.
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.2 (GNU/Linux)
>
> iD8DBQE+3yA7DAwPLUWQb6sRAn27AJ9TgcQ8BNqStKA6WKGpV3+De26m5wCgnHas
> 5jBZaAp/3NchLvRpof14emU=Z9On
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and
> unsubscribe commands go to majordomo@postgresql.org



-----------------------------------------
Get your free web based email at trade-india.com.
   "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/



Re: Urgent: Restoring many schemas in a database

From
Oliver Elphick
Date:
On Thu, 2003-06-05 at 11:49, Stephen J. Thompson wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello all,
>
> I am trying to restore a 7.3 database with many schemas and schema users in
> it. The issue I am having is that there are many \connect statements in the
> backup file and it keeps asking me for a new password each time. Is there an
> easier way than examining the file and carefully following the connect
> statement orders and entering the correct password. (btw there are over 100
> connect statements).

The simplest and quickest way is to alter pg_hba.conf to allow trust
authentication while you are reloading; then it will not ask for any
authentication at all.  Send the postmaster a SIGHUP to tell it to
reread the file.

There is a period of vulnerability to unwanted access while you are
doing this, so you should take what measures you can to reduce it, such
as allowing trust for a local user only and restoring pg_hba.conf as
soon as the reload is finished.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Let your conversation be without covetousness; and be
      content with such things as ye have. For he hath said,
      I will never leave thee, nor forsake thee."
                                             Hebrews 13:5


Re: Urgent: Restoring many schemas in a database

From
Tom Lane
Date:
"Stephen J. Thompson" <stephen@cass-ltd.co.uk> writes:
> I am trying to restore a 7.3 database with many schemas and schema users in
> it. The issue I am having is that there are many \connect statements in the
> backup file and it keeps asking me for a new password each time. Is there an
> easier way than examining the file and carefully following the connect
> statement orders and entering the correct password. (btw there are over 100
> connect statements).

Use a non-password-based authentication method (TRUST even, if you are
the only person on the machine).  Or if you can re-dump, use -X
use-set-session-authorization (I'm really not sure why we haven't made
this the default...)

            regards, tom lane

Re: Urgent: Restoring many schemas in a database

From
Bruno Wolff III
Date:
On Thu, Jun 05, 2003 at 11:49:25 +0100,
  "Stephen J. Thompson" <stephen@cass-ltd.co.uk> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello all,
>
> I am trying to restore a 7.3 database with many schemas and schema users in
> it. The issue I am having is that there are many \connect statements in the
> backup file and it keeps asking me for a new password each time. Is there an
> easier way than examining the file and carefully following the connect
> statement orders and entering the correct password. (btw there are over 100
> connect statements).

Assuming you are doing the restore on the server running the database
and that your OS supports getpeerid of the equivalent, you should use
ident authentication. You can allow your *nix administration account
to connect as any of the needed postgres accounts and then you won't
have to supply passwords.

Re: Urgent: Restoring many schemas in a database

From
Jonathan Gardner
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 05 June 2003 03:49, Stephen J. Thompson wrote:
> Hello all,
>
> I am trying to restore a 7.3 database with many schemas and schema users in
> it. The issue I am having is that there are many \connect statements in the
> backup file and it keeps asking me for a new password each time. Is there
> an easier way than examining the file and carefully following the connect
> statement orders and entering the correct password. (btw there are over 100
> connect statements).
>

Try ~/.pgpass with one line:
*:*:*:<username>:<password>

- --
Jonathan Gardner <jgardner@jonathangardner.net>
(was jgardn@alumni.washington.edu)
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE+4QN5WgwF3QvpWNwRAuzdAJwJUYr/aabryj1ZyvfeLrgcMBJGGwCgk7FC
itynkuB0XX3Y6VgqGzTayA8=
=wdgM
-----END PGP SIGNATURE-----

Re: Urgent: Restoring many schemas in a database

From
Jonathan Gardner
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 05 June 2003 03:49, Stephen J. Thompson wrote:
> Hello all,
>
> I am trying to restore a 7.3 database with many schemas and schema users in
> it. The issue I am having is that there are many \connect statements in the
> backup file and it keeps asking me for a new password each time. Is there
> an easier way than examining the file and carefully following the connect
> statement orders and entering the correct password. (btw there are over 100
> connect statements).
>

Put a .pgpass file in the home directory of the user.
It should have a line:
*:*:*:<username>:<password>

- --
Jonathan Gardner <jgardner@jonathangardner.net>
(was jgardn@alumni.washington.edu)
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE+36W3WgwF3QvpWNwRAnvkAJ4zcFGBMemqGRqYTRTdcPQTZqUvwACeMmIn
Lgiq5VrDOowcpmdSbn5EtDU=
=Ldm6
-----END PGP SIGNATURE-----