From: Tena Sakai <tsakai@gallo.ucsf.edu>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>, "pgsql-admin@postgresql.org"
	<pgsql-admin@postgresql.org>
CC: "ian.lea@gmail.com" <ian.lea@gmail.com>
Date: Tue, 24 Aug 2010 14:22:32 -0700
Subject: Re: trouble restoring database
Thread-Topic: [ADMIN] trouble restoring database
Thread-Index: ActDwErZ0DR3aZ0HROWwFASyEPOt8QAEiue2
Message-ID: <C8998628.C121%tsakai@gallo.ucsf.edu>
In-Reply-To: <4C73D33F0200002500034AF1@gw.wicourts.gov>
Accept-Language: en-US
Content-Language: en-US
user-agent: Microsoft-Entourage/13.4.0.100208
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

Hi Kevin,

My apology for insufficient post.
Here's what I did and its response:

testdb=3D# select * from pg_stat_database;
 datid |  datname  | numbackends | xact_commit | xact_rollback | blks_read =
|
blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated |
tup_deleted=20
-------+-----------+-------------+-------------+---------------+-----------=
+
----------+--------------+-------------+--------------+-------------+------=
-
------
     1 | template1 |           0 |           0 |             0 |         0 =
|
0 |            0 |           0 |            0 |
       0 |           0
 11563 | template0 |           0 |           0 |             0 |         0 =
|
0 |            0 |           0 |            0 |
       0 |           0
 11564 | postgres  |           0 |        2568 |            89 |       475 =
|
76273 |       618461 |       24179 |         4374 |
     295 |           1
 16384 | testdb    |           1 |         310 |             0 |       105 =
|
5808 |        85597 |        1080 |            0 |
       0 |           0
(4 rows)

Under datname column, I expected to see a name "canon".

Having said that, I think Ian is correct that I was missing -c flag in the
command below:
  gunzip ..../dumpall20100822.0.gz | /usr/local/pgsql/bin/psql -f - postgre=
s

This command, I think, uncompressed the file, but didn't feed the output
into the pipe and psql just waited and then received nothing and terminated=
.

Since the file got uncompressed, I am now running the command:
  psql -f /home/tsakai/Notes/postgres/dumpall20100822.0
and I think it is working.  It is giving messsages:
  ERROR:  role "postgres" already exists
  STATEMENT:  CREATE ROLE postgres;
  ERROR:  database "testdb" already exists
  STATEMENT:  CREATE DATABASE testdb WITH TEMPLATE =3D template0 OWNER =3D
postgres ENCODING =3D 'UTF8';
  LOG:  checkpoints are occurring too frequently (5 seconds apart)
  HINT:  Consider increasing the configuration parameter
"checkpoint_segments".


The last 2 lines are repeated many, many times.  This is not terribly
Serious, is it?  (I will fix it via postgresql.conf file shortly.)

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


On 8/24/10 12:12 PM, "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:

> Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
> =20
>>    gunzip ..../dumpall20100822.0.gz
>>    | /usr/local/pgsql/bin/psql -f * postgres
>> Which terminated with exit status 0.
>>=20
>> But when I look at pg_stat_database I don*t see the database I
>> expected.
> =20
> What database did you expect?  What do you see?  Is there anything
> interesting in the log file?
> =20
>> Can somebody please tell me why this didn*t work?  Also, what must
>> I do to successfully restore the database?
> =20
> Please read this page and post again with more information:
> =20
> http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
> =20
> -Kevin