Обсуждение: trouble restoring database

At a glance, you may need gunzip -c.


--
Ian.


On Tue, Aug 24, 2010 at 7:22 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
> Hi Everybody,
>
> I installed a postgres 8.4.4 onto a centos 5.5 machine and attempted to
> restore
> data from a file made by pg_dumpall.  It didn’t work, it seems.  What I did
> was to
> follow the instruction given in INSTALL file which comes with the source
> distribution.
> The section Getting Started gives a list and I did all but #5 (createdb
> testdb + psql testdb).
> Instead of #5, I ran the command below:
>    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.
> Can somebody please tell me why this didn’t work?  Also, what must I do to
> successfully
> restore the database?
>
> Thank you in advance.
>
> Regards,
>
> Tena Sakai
> tsakai@gallo.ucsf.edu

Tena Sakai <tsakai@gallo.ucsf.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

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@gallo.ucsf.edu


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

> Tena Sakai <tsakai@gallo.ucsf.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

On Tue, Aug 24, 2010 at 3:22 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
> 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

Quick tip, you can use zcat to get the output of a gzipped file like:

zcat ..../dumpall20100822.0.gz|psql

There's also zgrep, zless, zegrep, zxpdf, and zdiff.  All do what it
looks like they do.


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

No.  It's just telling you that from a performance perspective things
would be faster with more checkpoint segments.  Unless you spend a
fair portion of your day restoring dbs, it's probably not big deal.
If your machines only do a little writing at a time then you can
probably leave it.  However, increasing checkpoint segments does help
a lot if you do handle a fair bit of writes, and it's cheap and easy
to do.

Tena Sakai <tsakai@gallo.ucsf.edu> wrote:

> 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

Yeah.  Good spot on Ian's part.

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

Agreed.

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

The frequent checkpoints may affect the speed of the restore,
nothing more serious than that.

-Kevin

Hi Scott,

Thank you for your quick tips.  I shall remember zcat (and
hopefully others).

Regards,

Tena Sakai



On 8/24/10 2:30 PM, "Scott Marlowe" <scott.marlowe@gmail.com> wrote:

> On Tue, Aug 24, 2010 at 3:22 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>> 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
>
> Quick tip, you can use zcat to get the output of a gzipped file like:
>
> zcat ..../dumpall20100822.0.gz|psql
>
> There's also zgrep, zless, zegrep, zxpdf, and zdiff.  All do what it
> looks like they do.
>
>
>> 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.)
>
> No.  It's just telling you that from a performance perspective things
> would be faster with more checkpoint segments.  Unless you spend a
> fair portion of your day restoring dbs, it's probably not big deal.
> If your machines only do a little writing at a time then you can
> probably leave it.  However, increasing checkpoint segments does help
> a lot if you do handle a fair bit of writes, and it's cheap and easy
> to do.