pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtrouble restoring database
7+ messages / 4 participants
[nested] [flat]
* trouble restoring database
@ 2010-08-24 18:22 Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 2 replies; 7+ messages in thread
From: Tena Sakai @ 2010-08-24 18:22 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: trouble restoring database
@ 2010-08-24 19:07 Ian Lea <ian.lea@gmail.com>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Ian Lea @ 2010-08-24 19:07 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: trouble restoring database
@ 2010-08-24 19:12 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
1 sibling, 1 reply; 7+ messages in thread
From: Kevin Grittner @ 2010-08-24 19:12 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: trouble restoring database
@ 2010-08-24 21:22 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 7+ messages in thread
From: Tena Sakai @ 2010-08-24 21:22 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin; +Cc: ian.lea@gmail.com <ian.lea@gmail.com>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: trouble restoring database
@ 2010-08-24 21:30 Scott Marlowe <scott.marlowe@gmail.com>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
1 sibling, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2010-08-24 21:30 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin; ian.lea@gmail.com <ian.lea@gmail.com>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: trouble restoring database
@ 2010-08-24 21:30 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Kevin Grittner @ 2010-08-24 21:30 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; pgsql-admin; +Cc: ian.lea@gmail.com <ian.lea@gmail.com>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: trouble restoring database
@ 2010-08-25 04:25 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tena Sakai @ 2010-08-25 04:25 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin; ian.lea@gmail.com <ian.lea@gmail.com>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-08-25 04:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-24 18:22 trouble restoring database Tena Sakai <tsakai@gallo.ucsf.edu>
2010-08-24 19:07 ` Ian Lea <ian.lea@gmail.com>
2010-08-24 19:12 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-24 21:22 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2010-08-24 21:30 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-25 04:25 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2010-08-24 21:30 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox