Обсуждение: trouble restoring data from postgres 8.3.3 to freshly installed 8.3.6

Hi everybody,

I just finished installing postgres 8.3.6 and was
able to start it.  The log file reads:

  [2009-02-22 00:27:01.824 PST] < 11543  2009-02-22 00:27:01 PST >LOG:  database system was interrupted; last known up at 2009-02-22 00:25:04 PST
  [2009-02-22 00:27:01.825 PST] < 11543  2009-02-22 00:27:01 PST >LOG:  database system was not properly shut down; automatic recovery in progress
  [2009-02-22 00:27:01.827 PST] < 11543  2009-02-22 00:27:01 PST >LOG:  record with zero length at 13D/F78D2378
  [2009-02-22 00:27:01.827 PST] < 11543  2009-02-22 00:27:01 PST >LOG:  redo is not required
  [2009-02-22 00:27:01.880 PST] < 11546  2009-02-22 00:27:01 PST >LOG:  autovacuum launcher started
  [2009-02-22 00:27:01.881 PST] < 11542  2009-02-22 00:27:01 PST >LOG:  database system is ready to accept connections

While in postgres's home directory, I issued a command:
  ./bin/psql -f PastLogs/dumpall.out postgres
which is almost verbatim from the man page.  When I had
.pgpass file, it told me:
  psql: FATAL:  password authentication failed for user "postgres"
When I got rid of it, it asked me for one.  Whatever I gave was
no avail.  The password it is asking is not login password?

Can somebody please help?

Thank you.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu

Hi,

I realized what's going, but still don't know what to do.
What's happening is that there is no pg_user table in my
new postgres, which is the problem I was going to attack
via psql.  So this seems like a catch 22.

What can I do?

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Tena Sakai
Sent: Sun 2/22/2009 12:45 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] trouble restoring data from postgres 8.3.3 to freshly installed 8.3.6

Hi everybody,

I just finished installing postgres 8.3.6 and was
able to start it.  The log file reads:

  [2009-02-22 00:27:01.824 PST] < 11543  2009-02-22 00:27:01 PST >LOG:  database system was interrupted; last known up at 2009-02-22 00:25:04 PST
  [2009-02-22 00:27:01.825 PST] < 11543  2009-02-22 00:27:01 PST >LOG:  database system was not properly shut down; automatic recovery in progress
  [2009-02-22 00:27:01.827 PST] < 11543  2009-02-22 00:27:01 PST >LOG:  record with zero length at 13D/F78D2378
  [2009-02-22 00:27:01.827 PST] < 11543  2009-02-22 00:27:01 PST >LOG:  redo is not required
  [2009-02-22 00:27:01.880 PST] < 11546  2009-02-22 00:27:01 PST >LOG:  autovacuum launcher started
  [2009-02-22 00:27:01.881 PST] < 11542  2009-02-22 00:27:01 PST >LOG:  database system is ready to accept connections

While in postgres's home directory, I issued a command:
  ./bin/psql -f PastLogs/dumpall.out postgres
which is almost verbatim from the man page.  When I had
.pgpass file, it told me:
  psql: FATAL:  password authentication failed for user "postgres"
When I got rid of it, it asked me for one.  Whatever I gave was
no avail.  The password it is asking is not login password?

Can somebody please help?

Thank you.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu

Hi,

I am able to revert back to 8.3.3 system.  Maybe
I can use 8.3.3 psql to read the dumpall output.
How can I direct the output to data directory of
8.3.6 postgres?

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Tena Sakai
Sent: Sun 2/22/2009 1:03 AM
To: Tena Sakai; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] trouble restoring data from postgres 8.3.3 to freshly installed 8.3.6

Hi,

I realized what's going, but still don't know what to do.
What's happening is that there is no pg_user table in my
new postgres, which is the problem I was going to attack
via psql.  So this seems like a catch 22.

What can I do?

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Tena Sakai
Sent: Sun 2/22/2009 12:45 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] trouble restoring data from postgres 8.3.3 to freshly installed 8.3.6

Hi everybody,

I just finished installing postgres 8.3.6 and was
able to start it.  The log file reads:

  [2009-02-22 00:27:01.824 PST] < 11543  2009-02-22 00:27:01 PST >LOG:  database system was interrupted; last known up at 2009-02-22 00:25:04 PST
  [2009-02-22 00:27:01.825 PST] < 11543  2009-02-22 00:27:01 PST >LOG:  database system was not properly shut down; automatic recovery in progress
  [2009-02-22 00:27:01.827 PST] < 11543  2009-02-22 00:27:01 PST >LOG:  record with zero length at 13D/F78D2378
  [2009-02-22 00:27:01.827 PST] < 11543  2009-02-22 00:27:01 PST >LOG:  redo is not required
  [2009-02-22 00:27:01.880 PST] < 11546  2009-02-22 00:27:01 PST >LOG:  autovacuum launcher started
  [2009-02-22 00:27:01.881 PST] < 11542  2009-02-22 00:27:01 PST >LOG:  database system is ready to accept connections

While in postgres's home directory, I issued a command:
  ./bin/psql -f PastLogs/dumpall.out postgres
which is almost verbatim from the man page.  When I had
.pgpass file, it told me:
  psql: FATAL:  password authentication failed for user "postgres"
When I got rid of it, it asked me for one.  Whatever I gave was
no avail.  The password it is asking is not login password?

Can somebody please help?

Thank you.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu

On Sun, Feb 22, 2009 at 2:32 AM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
> Hi,
>
> I am able to revert back to 8.3.3 system.  Maybe
> I can use 8.3.3 psql to read the dumpall output.
> How can I direct the output to data directory of
> 8.3.6 postgres?

You don't have to dump and restore for a minor point update.  Just
stop your 8.3.3 and start 8.3.6 in the same data directory.

First make sure you stop the 8.3.3 / 8.3.6 server and then start 8.3.6 server and point the data path to 8.3.3 data path.

/usr/postgres/8.3.3/bin/pg_ctl -D  /usr/postgres/8.3.3/data/pgsql8.3.3 stop

/usr/postgres/8.3.6/bin/postgres -i -D /usr/postgres/8.3.3/data/pgsql8.3.3 >logfile 2>&1 &

Enjoy

Thanks
Deepak Murthy

Fighting spirit spreads like fire from one person to another. A leader's daring spirit will kindle flames of enthusiasm and inspire an entire organization. - Daisaku Ikeda
Nam Myoho Renge Kyo - The Key to Unlock Your Highest Potential
(www.sgi-usa.org)

Many thanks, Scott.

> Just stop your 8.3.3 and start 8.3.6 in the same data directory.

My set up is that I have a fair size partition named /pgsql.
In it, I have subdirectories such as 830, 833, and 836.  As
I move from one rev to another, I basically make a link from
that directory (in my latest case, 836) to /usr/local/pgsql.

In the end, I just made a symbolic link from /pgsql/833/data
to /usr/local/pgsql/data.  Is there any better way of achiving
the same thing?

I am running tests and so far it seems to work fine.  I will
check back tomorrow and maybe I can give a report as to how
all this turn out.

BTW, I fixed work_mem back to 512MB.

Many thank you for your kindness and guidance.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu



-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Sun 2/22/2009 1:43 AM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] trouble restoring data from postgres 8.3.3 to freshly installed 8.3.6

On Sun, Feb 22, 2009 at 2:32 AM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
> Hi,
>
> I am able to revert back to 8.3.3 system.  Maybe
> I can use 8.3.3 psql to read the dumpall output.
> How can I direct the output to data directory of
> 8.3.6 postgres?

You don't have to dump and restore for a minor point update.  Just
stop your 8.3.3 and start 8.3.6 in the same data directory.

Of course, Deepak!

I had made an alias for "pg_ctl start ..." and always
had -D setting as /usr/local/pgsql/data.  I have been
relying on this alias so much, I completely forgot
that's how actually it gets done.

Thank you.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: DM [mailto:dm.aeqa@gmail.com]
Sent: Sun 2/22/2009 2:32 AM
To: Scott Marlowe
Cc: Tena Sakai; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] trouble restoring data from postgres 8.3.3 to freshly installed 8.3.6

First make sure you stop the 8.3.3 / 8.3.6 server and then start 8.3.6
server and point the data path to 8.3.3 data path.

/usr/postgres/8.3.3/bin/pg_ctl -D  /usr/postgres/8.3.3/data/pgsql8.3.3 stop

/usr/postgres/8.3.6/bin/postgres -i -D /usr/postgres/8.3.3/data/pgsql8.3.3
>logfile 2>&1 &

Enjoy

Thanks
Deepak Murthy

Fighting spirit spreads like fire from one person to another. A leader's
daring spirit will kindle flames of enthusiasm and inspire an entire
organization. - Daisaku Ikeda
Nam Myoho Renge Kyo - The Key to Unlock Your Highest Potential
(www.sgi-usa.org)

On Sun, Feb 22, 2009 at 1:43 AM, Scott Marlowe <scott.marlowe@gmail.com>wrote:

> On Sun, Feb 22, 2009 at 2:32 AM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
> > Hi,
> >
> > I am able to revert back to 8.3.3 system.  Maybe
> > I can use 8.3.3 psql to read the dumpall output.
> > How can I direct the output to data directory of
> > 8.3.6 postgres?
>
> You don't have to dump and restore for a minor point update.  Just
> stop your 8.3.3 and start 8.3.6 in the same data directory.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

Hello,

Tena Sakai wrote:

> I just finished installing postgres 8.3.6 and was
> able to start it.  The log file reads:
>
>   [2009-02-22 00:27:01.824 PST] < 11543  2009-02-22 00:27:01 PST >LOG:
> database system was interrupted; last known up at 2009-02-22 00:25:04 PST
>   [2009-02-22 00:27:01.825 PST] < 11543  2009-02-22 00:27:01 PST >LOG:
> database system was not properly shut down; automatic recovery in progress
>   [2009-02-22 00:27:01.827 PST] < 11543  2009-02-22 00:27:01 PST >LOG:
> record with zero length at 13D/F78D2378
>   [2009-02-22 00:27:01.827 PST] < 11543  2009-02-22 00:27:01 PST >LOG:
> redo is not required
>   [2009-02-22 00:27:01.880 PST] < 11546  2009-02-22 00:27:01 PST >LOG:
> autovacuum launcher started
>   [2009-02-22 00:27:01.881 PST] < 11542  2009-02-22 00:27:01 PST >LOG:
> database system is ready to accept connections

If you haven't installed the PostgreSQL server as a service, then you
have to manually shut down (pg_ctl etc.) the server before shutting down
the system of course.

> While in postgres's home directory, I issued a command:
>   ./bin/psql -f PastLogs/dumpall.out postgres
> which is almost verbatim from the man page.  When I had
> .pgpass file, it told me:
>   psql: FATAL:  password authentication failed for user "postgres"
> When I got rid of it, it asked me for one.  Whatever I gave was
> no avail.  The password it is asking is not login password?
>
> Can somebody please help?

Try setting the authentification method for local (127.0.0.1) to trust
in the pg_hba.conf file (it's in the cluster directory together with
postgresql.conf. Then set a new password for postgres via SQL, pgAdmin
III etc. ...
After that don't forget to set the authentification method back to the
old setting.

Good luck,

Peter

Hi Peter,

Thanks for your reply and idea.  I was up way
past my bed time and my mind was not with my
finger tips.  As I followed suggestions from
Scott, I was able to get it to work fast.  More
often than not, I push the door, when it is
written on the door: "PULL"  :)

Regards,

Tena Sakai

-----Original Message-----
From: Jan-Peter Seifert [mailto:Jan-Peter.Seifert@gmx.de]
Sent: Sun 2/22/2009 10:23 AM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] trouble restoring data from postgres 8.3.3 to freshly installed 8.3.6

Hello,

Tena Sakai wrote:

> I just finished installing postgres 8.3.6 and was
> able to start it.  The log file reads:
>
>   [2009-02-22 00:27:01.824 PST] < 11543  2009-02-22 00:27:01 PST >LOG:
> database system was interrupted; last known up at 2009-02-22 00:25:04 PST
>   [2009-02-22 00:27:01.825 PST] < 11543  2009-02-22 00:27:01 PST >LOG:
> database system was not properly shut down; automatic recovery in progress
>   [2009-02-22 00:27:01.827 PST] < 11543  2009-02-22 00:27:01 PST >LOG:
> record with zero length at 13D/F78D2378
>   [2009-02-22 00:27:01.827 PST] < 11543  2009-02-22 00:27:01 PST >LOG:
> redo is not required
>   [2009-02-22 00:27:01.880 PST] < 11546  2009-02-22 00:27:01 PST >LOG:
> autovacuum launcher started
>   [2009-02-22 00:27:01.881 PST] < 11542  2009-02-22 00:27:01 PST >LOG:
> database system is ready to accept connections

If you haven't installed the PostgreSQL server as a service, then you
have to manually shut down (pg_ctl etc.) the server before shutting down
the system of course.

> While in postgres's home directory, I issued a command:
>   ./bin/psql -f PastLogs/dumpall.out postgres
> which is almost verbatim from the man page.  When I had
> .pgpass file, it told me:
>   psql: FATAL:  password authentication failed for user "postgres"
> When I got rid of it, it asked me for one.  Whatever I gave was
> no avail.  The password it is asking is not login password?
>
> Can somebody please help?

Try setting the authentification method for local (127.0.0.1) to trust
in the pg_hba.conf file (it's in the cluster directory together with
postgresql.conf. Then set a new password for postgres via SQL, pgAdmin
III etc. ...
After that don't forget to set the authentification method back to the
old setting.

Good luck,

Peter