headers
wespvp | 1 Jul 2004 01:23

Re: backups

On 6/30/04 11:59 AM, "jearl <at> bullysports.com" <jearl <at> bullysports.com> wrote:

> You don't want to back files in the pgdata directory directly.
> Instead you want to use pg_dump to create a snapshot of your database
> and use that as your backup.

That's great for a small to medium database, but doesn't work worth a hoot
for large databases.  With several hundred million to over a billion rows,
the pg_dump isn't too bad, but I can't wait days for the reload to complete.
While replication may be an option to avoid shutting the primary DB down, we
currently have to shut down the database and do file system dumps (full
weekly, incrementals nightly).  Even with replication, we'd need to shut
down the shadow DB and do a file system backup - a replicate doesn't protect
you against replicated garbage.  We are also investigating using file system
snapshots - shut the DB down, snapshot, bring it back up.

I'm looking forward to point in time recovery...

What do other sites with mondo databases do?

Wes

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match
Permalink | Reply | 8 comments |
headers
mallah | 1 Jul 2004 02:04

case for lock_timeout


Hi,

With  reference to:

http://archives.postgresql.org/pgsql-hackers/2004-06/msg00935.php

Consider a case

1. wherein a database dump takes 2 hrs to complete.
2. A cron  gets fired which drops a certain table and recreates it.
3. A set of frequently requested web pages depends on reading from the table in 2

Since 2 will wait for 1 till it finishes, and 3 will wait for 2  this means those
set of webpages  will become inaccessible while the backup is in progress.

Whereas if we have something like a lock_timeout 2 will abort within a limited
time duration automatically and the sys admin could get notified  to reschedule
it and this problem can get averted.

I feel lock_timeout it will be a nice feature . Lemme know what would be
the solution of the above problem from existing set of  features.

Regds
Mallah.

-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
(Continue reading)

Permalink | Reply | 3 comments |
headers
Christopher Cashell | 1 Jul 2004 02:57

Problems restarting after database crashed (signal 11).

Yesterday, while attempting to access a database, I received errors
saying that the database was innaccessible.  After investigating a
little, I found the following in the PostgreSQL log files:

2004-06-30 08:30:19 [24119] LOG:  checkpoint process (PID 28423) was
terminated by signal 11
2004-06-30 08:30:19 [24119] LOG:  terminating any other active server
processes
2004-06-30 08:30:19 [28383] WARNING:  terminating connection because of
crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back
the curre nt transaction and exit, because another server process exited
abnormally and po ssibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat yo ur command.
2004-06-30 08:30:19 [28362] WARNING:  terminating connection because of
crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back
the curre nt transaction and exit, because another server process exited
abnormally and po ssibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat yo ur command.

The last bit then repeated a few more times, and then:

2004-06-30 08:30:20 [24119] LOG:  all server processes terminated;
reinitializing
2004-06-30 08:30:20 [28424] LOG:  database system was interrupted at 2004-06-30
08:22:23 CDT
2004-06-30 08:30:20 [28424] LOG:  checkpoint record is at 8/77703F9C
(Continue reading)

Permalink | Reply | 14 comments |
headers
Tom Lane | 1 Jul 2004 03:05
Picon

Re: Slow dump with pg_dump/pg_restore ? How to improve ?

Soeren Gerlach <soeren <at> all-about-shift.com> writes:
>  * pg_dump takes 2/3 of the [single] CPU, postmaster the other 1/3 for both
>    dumps

Really!?  Hmm, that seems fairly backwards ... thinks ...

In the -Fc case this makes some amount of sense because pg_dump runs
gzip-style compression on the data (which is why the output file is
so much smaller).  But for plain text dump, pg_dump should be just
pushing the data straight through to stdout; it really ought not take
much CPU as far as I can see.  There may be some simple performance
glitch involved there.  Are you interested in recompiling with -pg
and getting a gprof profile of pg_dump?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html
Permalink | Reply | 2 comments |
headers
Tom Lane | 1 Jul 2004 03:17
Picon

Re: query failing with out of memory error message.

"Joe Maldonado" <jmaldonado <at> webehosting.biz> writes:
> Where can I find a version of pg_filedump compatible with 7.4?

http://sources.redhat.com/rhdb/

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo <at> postgresql.org so that your
      message can get through to the mailing list cleanly
Permalink | Reply | 0 comments |
headers
Tom Lane | 1 Jul 2004 03:28
Picon

Re: Internationalization

Dennis Gearon <gearond <at> fireserve.net> writes:
> Is there anyway for a single statement to access more than one database?
> Could a query, regexes, etc be facing indexes in different 
> encodings/sorting collations if different databases in a cluster had 
> different encodings/collations?

The indexes on the shared system tables (eg, pg_database) are the only
issue here.  One possible solution is to require that no locale-aware
datatypes ever be used in these indexes.  I think right now this is true
because "name" doesn't use locale-aware sorting; but we'd have to be
careful not to break the restriction in future.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Permalink | Reply | 4 comments |
headers
Justin Clift | 1 Jul 2004 03:21
Favicon

Re: Running PostgreSQL on Windows 9x/2000/2003...

Thomas LeBlanc wrote:
> How to I run PostgreSQL on an Intel machine with Windows?

Hi Thomas,

This is probably the page you want:

http://techdocs.postgresql.org/guides/Windows

:-)

Regards and best wishes,

Justin Clift

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org
Permalink | Reply | 0 comments |
headers
Tom Lane | 1 Jul 2004 03:38
Picon

Re: case for lock_timeout

<mallah <at> trade-india.com> writes:
> I feel lock_timeout it will be a nice feature . Lemme know what would be
> the solution of the above problem from existing set of  features.

AFAICS statement_timeout would work just fine for that; or you could
use NOWAIT.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match
Permalink | Reply | 2 comments |
headers
Tom Lane | 1 Jul 2004 03:24
Picon

Re: ~Strange Operators~

Chris Gamache <cgg007 <at> yahoo.com> writes:
> ~>=~
> ~<=~
> ~<>~
> ~<~
> ~=~
> ~>~

> They aren't familiar to me, but I can tell that they are text comparison
> operators. The details of what and how they compare is a mystery to me! 

Those are the non-locale-aware operators that Peter added to support
LIKE.   I'm surprised to hear they aren't documented.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org
Permalink | Reply | 1 comment |
headers
Tom Lane | 1 Jul 2004 03:54
Picon

Re: Problems restarting after database crashed (signal 11).

Christopher Cashell <topher-pgsql <at> zyp.org> writes:
> Eventually I attempted to shut it down and restart it, however that
> failed too.  When I attempted to shut it down, I discovered a hung
> 'startup subprocess' that can't be killed.

This is interesting because it seems just about exactly like this
recent Red Hat bug report:
https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=126885

As I commented there, I think that it must be a kernel or hardware
issue --- Postgres itself can surely not make an unkillable process.
However it's common to see processes that don't respond to kill if
they are stuck inside a kernel I/O request.  That could mean either
unresponsive hardware or a kernel bug.

I wonder whether you have any similarities in hardware or Linux kernel
to the person who filed the above report?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match
Permalink | Reply | 8 comments |

Gmane