pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help to restore database
5+ messages / 5 participants
[nested] [flat]
* Need help to restore database
@ 2009-03-17 08:50 Lori, Giancarlo <giancarlo.lori@eds.com>
2009-03-17 11:01 ` Re: Need help to restore database adamr@informatyka.gdansk.pl
2009-03-17 13:18 ` Re: Need help to restore database Alvaro Herrera <alvherre@commandprompt.com>
2009-03-17 14:10 ` Re: Need help to restore database Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 3 replies; 5+ messages in thread
From: Lori, Giancarlo @ 2009-03-17 08:50 UTC (permalink / raw)
To: pgsql-admin
Hi all,
we are facing with a struggle problem with PostgreSql database
production.
A client system based on pgsql is down because the database raise an
error message like " no left space on device". We have deleted some log
files on this machine and after a system restart the db (and the system)
was up&running. After two days the database has crashed again. Looking
the postgres log there was an error on xlog file (I have inspected the
directory pg_xlog and no files I found it). I'll try to re-build the
xlog file with pg_resetxlog. The utilities has created a new xlog file,
the postmaster process run, but any command pg_dump, etc I try to
execute failed. The system respond that there are not database in the
system. Can anyone help us ?
Thanks in advance
Giancarlo
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help to restore database
2009-03-17 08:50 Need help to restore database Lori, Giancarlo <giancarlo.lori@eds.com>
@ 2009-03-17 11:01 ` adamr@informatyka.gdansk.pl
2009-03-17 13:32 ` Re: Need help to restore database Ray Stell <stellr@cns.vt.edu>
2 siblings, 1 reply; 5+ messages in thread
From: adamr@informatyka.gdansk.pl @ 2009-03-17 11:01 UTC (permalink / raw)
To: Lori, Giancarlo <giancarlo.lori@eds.com>; +Cc: pgsql-admin
On Tue, 17 Mar 2009 09:50:55 +0100, "Lori, Giancarlo"
<giancarlo.lori@eds.com> wrote:
> Hi all,
> we are facing with a struggle problem with PostgreSql database
> production.
> A client system based on pgsql is down because the database raise an
> error message like " no left space on device". We have deleted some log
> files on this machine and after a system restart the db (and the system)
> was up&running. After two days the database has crashed again. Looking
> the postgres log there was an error on xlog file (I have inspected the
> directory pg_xlog and no files I found it). I'll try to re-build the
> xlog file with pg_resetxlog. The utilities has created a new xlog file,
> the postmaster process run, but any command pg_dump, etc I try to
> execute failed. The system respond that there are not database in the
> system. Can anyone help us ?
>
> Thanks in advance
> Giancarlo
Maybe it's not good idea, but...
First - stop the postgreSQL service.
Make the copy of Your database cluster and existing tablespaces.
Try to run a standalone backend instead normal PostgreSQL, giving for
switch -P name of database "postgres".
You can run it like this (if Your system user, who usually rruns postgresql
service is named "postgres" - when not, use for "su the proper account
name":
su postgres -c "/usr/local/pgsql/bin/postgres -D /home/postgres/bazy -O -P
postgres"
In "standalone backend" You can run SQL commands - run VACUUM FULL.
Press Ctrl+D to leave the standalone backend.
Repeat this all for Your database and other, wchich are listed for VACUUM,
when standalone backend starts.
Read about "standalone backend" in the PostgreSQL documenatation.
If You running PostgreSQL on Windows server, use runas with its specific
options instead su, of course.
Brgds
Adam
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help to restore database
2009-03-17 08:50 Need help to restore database Lori, Giancarlo <giancarlo.lori@eds.com>
2009-03-17 11:01 ` Re: Need help to restore database adamr@informatyka.gdansk.pl
@ 2009-03-17 13:32 ` Ray Stell <stellr@cns.vt.edu>
0 siblings, 0 replies; 5+ messages in thread
From: Ray Stell @ 2009-03-17 13:32 UTC (permalink / raw)
To: adamr@informatyka.gdansk.pl; +Cc: Lori, Giancarlo <giancarlo.lori@eds.com>; pgsql-admin
On Tue, Mar 17, 2009 at 12:01:01PM +0100, adamr@informatyka.gdansk.pl wrote:
> On Tue, 17 Mar 2009 09:50:55 +0100, "Lori, Giancarlo"
> <giancarlo.lori@eds.com> wrote:
> > the postmaster process run, but any command pg_dump, etc I try to
> > execute failed. The system respond that there are not database in the
> > system. Can anyone help us ?
I'm no expert, the quiet from the pros is deafening to me.
What does the postmaster log say when you start up?
What is the exact error msg that you refer to as "there are not database in the system?"
What does the \l meta-command say, anything like this:
template1=# \l
List of databases
Name | Owner | Encoding
-------------+---------+----------
postgres | pgadmin | UTF8
template0 | pgadmin | UTF8
template1 | pgadmin | UTF8
(3 rows)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help to restore database
2009-03-17 08:50 Need help to restore database Lori, Giancarlo <giancarlo.lori@eds.com>
@ 2009-03-17 13:18 ` Alvaro Herrera <alvherre@commandprompt.com>
2 siblings, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2009-03-17 13:18 UTC (permalink / raw)
To: Lori, Giancarlo <giancarlo.lori@eds.com>; +Cc: pgsql-admin
Lori, Giancarlo wrote:
> A client system based on pgsql is down because the database raise an
> error message like " no left space on device". We have deleted some log
> files on this machine and after a system restart the db (and the system)
> was up&running. After two days the database has crashed again. Looking
> the postgres log there was an error on xlog file (I have inspected the
> directory pg_xlog and no files I found it). I'll try to re-build the
> xlog file with pg_resetxlog. The utilities has created a new xlog file,
> the postmaster process run, but any command pg_dump, etc I try to
> execute failed. The system respond that there are not database in the
> system.
Looks like somebody deleted critical files to get some space back.
> Can anyone help us ?
Find the most recent backup ...
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help to restore database
2009-03-17 08:50 Need help to restore database Lori, Giancarlo <giancarlo.lori@eds.com>
@ 2009-03-17 14:10 ` Simon Riggs <simon@2ndQuadrant.com>
2 siblings, 0 replies; 5+ messages in thread
From: Simon Riggs @ 2009-03-17 14:10 UTC (permalink / raw)
To: Lori, Giancarlo <giancarlo.lori@eds.com>; +Cc: pgsql-admin
On Tue, 2009-03-17 at 09:50 +0100, Lori, Giancarlo wrote:
> we are facing with a struggle problem with PostgreSql database
> production.
>
> A client system based on pgsql is down because the database raise an
> error message like “ no left space on device”. We have deleted some
> log files on this machine and after a system restart the db (and the
> system) was up&running. After two days the database has crashed again.
> Looking the postgres log there was an error on xlog file (I have
> inspected the directory pg_xlog and no files I found it). I’ll try to
> re-build the xlog file with pg_resetxlog. The utilities has created a
> new xlog file, the postmaster process run, but any command pg_dump,
> etc I try to execute failed. The system respond that there are not
> database in the system. Can anyone help us ?
2ndQuadrant has a lot of experience with helping people out with
problems like this, including data recovery from damaged databases.
Please let us know if we can be of any help. Apologies for mentioning
commercial matters on list, though this work helps fund further
developments in this area.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-03-17 14:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-03-17 08:50 Need help to restore database Lori, Giancarlo <giancarlo.lori@eds.com>
2009-03-17 11:01 ` adamr@informatyka.gdansk.pl
2009-03-17 13:32 ` Ray Stell <stellr@cns.vt.edu>
2009-03-17 13:18 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-03-17 14:10 ` Simon Riggs <simon@2ndQuadrant.com>
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