Thread: Urgent: Restoring many schemas in a database
-----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-----
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/
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
"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
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.
-----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-----
-----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-----