Обсуждение: Upgrade Backup Issue
Okay, i've gotten far enough that i've taken backups of my 8.1 db server from both 8.1's and 8.2's pg_dumpall. i had to enter my password in about 5 or 6 times, which i thought was strange, but is probably due to some fine grained password control that i'm not familiar with. anyway, i'm trying to backup the pg_dumpall file into my 8.2 server. 8.1 is shut down and 8.2's server is running. i put in the following command: C:\>Program Files\PostgreSQL\8.2\bin\psql -d db_name_here -f 8.2.out i get the following error: '"Program is not recognized as an internal or external command, operable program or bath file. however, the first time i ran this, i received the following error: psql: FATAL password authentication failed for user [username] i think they are identical, anyway. maybe i'm missing something. as an aside, i'm using pgadmin3 from 8.2 and i no longer see my 8.1 databases even though 8.1's server is running. i think i'm a bit confused on roles and user and such, too. my username is postgres and i know my password. do i need to use -U with psql when i'm running commands? if so, how come my dumpall worked (or so i think, anyway!) w/o using the -U flag? also, is it only possible to import one db at a time? i have 3 different dbs on my development box. do i just omit the -d flag in order to backup everything that was pg_dumpalled? tia... ____________________________________________________________________________________ Yahoo! Music Unlimited Access over 1 million songs. http://music.yahoo.com/unlimited
> Okay, i've gotten far enough that i've taken backups > of my 8.1 db server from both 8.1's and 8.2's > pg_dumpall. > i had to enter my password in about 5 or 6 times, > which i thought was strange, but is probably due to > some fine grained password control that i'm not > familiar with. Ya, what happens is that you have to enter your password everytime pg_dumpall starts the next database. You need to update you pg_hba.conf to allow your user open all databases without a password. This could be done by your postgres os user account using sameuser or trust authentication. the other option is to set up a .pgpass file. althought this option is not recommended. > anyway, i'm trying to backup the pg_dumpall file into > my 8.2 server. 8.1 is shut down and 8.2's server is > running. > > i put in the following command: > > C:\>Program Files\PostgreSQL\8.2\bin\psql -d > db_name_here -f 8.2.out > > i get the following error: > > '"Program is not recognized as an internal or external > command, operable program or bath file. > > however, the first time i ran this, i received the > following error: > > psql: FATAL password authentication failed for user > [username] > > i think they are identical, anyway. maybe i'm missing > something. ya if you are trying to restore a single database to a new cluster that doesn't have the user that owned your dumped database it will create this error. maybe try pg_restore.exe instead. Either way I believe there is a way to force adding non-existing users. > > as an aside, i'm using pgadmin3 from 8.2 and i no > longer see my 8.1 databases even though 8.1's server > is running. That is odd. be sure that each server is configured to run on a different port instead of the default 5432. > > i think i'm a bit confused on roles and user and such, > too. my username is postgres and i know my password. > do i need to use -U with psql when i'm running > commands? if so, how come my dumpall worked (or so i > think, anyway!) w/o using the -U flag? Check your pg_hba.conf. Yes you need -U unless you set up the pg_hba.conf to use trust of sameuser role are kind of the new groups. You can define a role with a certain class of previledgs as a role and then add users to the role and other roles as a way to simplify dealing with individual user privledges. > also, is it only possible to import one db at a time? > i have 3 different dbs on my development box. do i > just omit the -d flag in order to backup everything > that was pg_dumpalled? > No, you should be about to import all at one time with the output of a pg_dumpall.
Richard Broersma Jr wrote: > > Okay, i've gotten far enough that i've taken backups > > of my 8.1 db server from both 8.1's and 8.2's > > pg_dumpall. > > > i had to enter my password in about 5 or 6 times, > > which i thought was strange, but is probably due to > > some fine grained password control that i'm not > > familiar with. > Ya, what happens is that you have to enter your password everytime > pg_dumpall starts the next database. You need to update you pg_hba.conf > to allow your user open all databases without a password. This could > be done by your postgres os user account using sameuser or trust > authentication. the other option is to set up a .pgpass file. > althought this option is not recommended. Actually, we do recommend .pgpass over changing pg_hba.conf. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
> > Actually, we do recommend .pgpass over changing pg_hba.conf. Thats good to know. Thanks for the correction. I read the following link: http://www.postgresql.org/docs/8.2/static/libpq-pgpass.html But I didn't notice the advantage. Regards, Richard Broersma Jr.