Re: 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.
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.