Re: trouble restoring database

Hi Kevin,

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

testdb=# 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
-------+-----------+-------------+-------------+---------------+-----------+
----------+--------------+-------------+--------------+-------------+-------
------
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 - postgres

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 = template0 OWNER =
postgres ENCODING = '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(at)gallo(dot)ucsf(dot)edu

On 8/24/10 12:12 PM, "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

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