Skip site navigation (1) Skip section navigation (2)

Re: Upgrade Backup Issue

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: operationsengineer1(at)yahoo(dot)com, pgsql-novice(at)postgresql(dot)org
Subject: Re: Upgrade Backup Issue
Date: 2006-12-15 02:30:22
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
> 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

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

In response to


pgsql-novice by date

Next:From: Bruce MomjianDate: 2006-12-15 02:37:03
Subject: Re: Upgrade Backup Issue
Previous:From: operationsengineer1Date: 2006-12-15 00:46:58
Subject: Upgrade Backup Issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group