Thread: How do I restore data changes made after the backup?
Newbie here... How do I restore data changes made after the backup? I've been combing the docs and the web site but I don't see anything on this. Given a backup taken this morning, and a failure requiring a database restore, how do I restroe the data that was added after the backup was taken this morning? Isn't there any log files to apply to pg_restore? Does PostgreSQL have the ability to restore to the point of failure so no transactions are lost? (Like Oracle?) Is replication commonly used for this? Please advise, Steve Orr
Orr, Steve wrote: > Newbie here... > > How do I restore data changes made after the backup? I've been combing the > docs and the web site but I don't see anything on this. Given a backup taken > this morning, and a failure requiring a database restore, how do I restroe > the data that was added after the backup was taken this morning? Isn't there > any log files to apply to pg_restore? Does PostgreSQL have the ability to > restore to the point of failure so no transactions are lost? (Like Oracle?) > Is replication commonly used for this? You want point-in-time recovery. That is for 7.4. That should be a 6 months away. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
THANKS! for the quick answer. Yes I want point in time recovery and I can wait 6 months. $64K question... Will it be delivered on schedule? ;-) I also want replication. Any other info other than what on the web site? Anyone used eRServer? Is the Postgres-R project a separate endeavor? Is there a schedule for production release? Where can I find out more? THANKS! -----Original Message----- From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] Sent: Tuesday, September 17, 2002 4:16 PM To: Orr, Steve Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] How do I restore data changes made after the backup? Orr, Steve wrote: > Newbie here... > > How do I restore data changes made after the backup? I've been combing the > docs and the web site but I don't see anything on this. Given a backup taken > this morning, and a failure requiring a database restore, how do I restroe > the data that was added after the backup was taken this morning? Isn't there > any log files to apply to pg_restore? Does PostgreSQL have the ability to > restore to the point of failure so no transactions are lost? (Like Oracle?) > Is replication commonly used for this? You want point-in-time recovery. That is for 7.4. That should be a 6 months away. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
Orr, Steve wrote: > THANKS! for the quick answer. > > Yes I want point in time recovery and I can wait 6 months. $64K question... > Will it be delivered on schedule? ;-) Usually. > I also want replication. Any other info other than what on the web site? > Anyone used eRServer? Is the Postgres-R project a separate endeavor? Is > there a schedule for production release? Where can I find out more? There are several projects. /contrib/rserv and others are find for master/slave replication. Multimaster will require pgreplication on gborg.postgresql.org. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Tue, Sep 17, 2002 at 04:57:45PM -0600, Orr, Steve wrote:
> THANKS! for the quick answer.
>
> Yes I want point in time recovery and I can wait 6 months. $64K question...
> Will it be delivered on schedule? ;-)
Well, 7.3 looks like it will be.
If you have some dollars to spread around, I'll bet someone would
even be willing to figure out how to patch 7.3 for this. But keep in
mind that the patches will not be widely tested, so you'd want to
reply on them only in really dire emergencies (i.e. don't build your
plans with them, but use them as a faint-hope recovery method in the
first iteration).
> I also want replication. Any other info other than what on the web site?
> Anyone used eRServer? Is the Postgres-R project a separate endeavor? Is
Afilias and its child company Liberty RMS (for whom I work) use
eRserver (the commercial version, from PostgreSQL, Inc). We're very
happy with it.
The contrib/ module also works, although it's not terribly fast, and
may have some bugs. People find it hard to set up. I can probably
help if you have a particular question.
The 7.3 release (currently in beta) will have another replication
scheme in contrib/ as well. I haven't used it.
Postgres-R apparently just recently made master-slave with 7.2. But
it's brand new, and I don't think I'd use it for production yet.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
We just encountered the same condition sited in the following post snipped
from Goggle groups:
>fredrik chabot <fredrik@f6.nl> writes:
>> so I migrated the database to a non-smp machine running RH 7.2 and the
postgres
>> 7.2.1-5 rpm's<br>
>> <br>
>> Kernel 2.4.7-10<br>
>> <br>
>> and I still get
>> <blockquote type="cite" cite="mid25853.1032724594@sss.pgh.pa.us">
>> <pre wrap="">FATAL 1: LWLockAcquire: can't wait without a PROC
structure</pre>
>> </blockquote>
>> The code tells me this should never heppen :-<<br>
>
>I don't think it should happen either ;-) When do you see it exactly
>--- is it during startup of a fresh connection, or while executing
>queries in an existing backend, or ...?
>
> regards, tom lane
I could not find any info that said the cause or cure had been identified.
Can anyone fill me in?
For the record:
ccc=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
Also a snip from our postmaster log:
2002-10-15 16:00:02 [2067] DEBUG: connection: host=10.1.2.100
user=BSPEIRS database=template1
2002-10-15 16:00:03 [2085] DEBUG: connection: host=10.1.2.100
user=BSPEIRS database=ccc
2002-10-15 16:00:03 [2086] DEBUG: connection: host=10.1.2.100
user=BSPEIRS database=template0
2002-10-15 16:00:03 [2086] FATAL 1: Database "template0" is not
currently accepting connections
2002-10-15 16:00:04 [2087] DEBUG: connection: host=10.1.2.100
user=BSPEIRS database=template0
2002-10-15 16:00:04 [2087] FATAL 1: Database "template0" is not
currently accepting connections
2002-10-15 16:00:04 [2087] FATAL 1: LWLockAcquire: can't wait without a
PROC structure
2002-10-15 16:00:04 [23427] DEBUG: server process (pid 2087) exited with
exit code 1
2002-10-15 16:00:04 [23427] DEBUG: terminating any other active server
processes
.... followed by a slew of "Postmaster has informed me..." messages.
The process attempting to connect was pgAdminII v1.2.0 .
Postgres had been up for 15 straight days prior to encountering this and
this is the first occurrence in 3 months of otherwise trouble-free up-time.
Any help in responding to this would be appreciated and we're open to
helping further diagnose if the cause is still undetermined.
Thanks,
Marc Mitchell - Senior Application Architect
Enterprise Information Solutions, Inc.
Downers Grove, IL 60515
marcm@eisolution.com
"Marc Mitchell" <marcm@eisolution.com> writes:
> We just encountered the same condition sited in the following post snipped
> from Goggle groups:
> FATAL 1: LWLockAcquire: can't wait without a PROC structure
> I could not find any info that said the cause or cure had been identified.
This is fixed (AFAIK) in 7.2.3. If you can reproduce it after updating
we would very much like to hear about it ...
regards, tom lane