pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Pitr
10+ messages / 8 participants
[nested] [flat]

* Pitr
@ 2010-12-27 20:18 Geoffrey Myers <lists@serioustechnology.com>
  2010-12-27 20:35 ` Re: Pitr Geoffrey Myers <lists@serioustechnology.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Geoffrey Myers @ 2010-12-27 20:18 UTC (permalink / raw)
  To: pgsql-general

Set up wal shipping on postgresql 8.3.9 and rhel 5.5. When I start the postmaster on the primary, there is no reference to archiving in the log and files do not get shipped. Had this working earlier, but the ssh keys were accidentally over written breaking the shipping. Any clues?

--
Later, Geoffrey
Sent from my iPhone


^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Pitr
  2010-12-27 20:18 Pitr Geoffrey Myers <lists@serioustechnology.com>
@ 2010-12-27 20:35 ` Geoffrey Myers <lists@serioustechnology.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Geoffrey Myers @ 2010-12-27 20:35 UTC (permalink / raw)
  To: Geoffrey Myers <lists@serioustechnology.com>; +Cc: pgsql-general

Patience is my friend. No transactions so no archiving. Waiting long enough produced results. Sorry for the noise.  

--
Later, Geoffrey
Sent from my iPhone

On Dec 27, 2010, at 3:18 PM, Geoffrey Myers <lists@serioustechnology.com> wrote:

> Set up wal shipping on postgresql 8.3.9 and rhel 5.5. When I start the postmaster on the primary, there is no reference to archiving in the log and files do not get shipped. Had this working earlier, but the ssh keys were accidentally over written breaking the shipping. Any clues?
> 
> --
> Later, Geoffrey
> Sent from my iPhone
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* PITR
@ 2023-11-22 08:11 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
  2023-11-22 08:23 ` Re: PITR Holger Jakobs <holger@jakobs.com>
  2023-11-22 09:01 ` Re: PITR Laurenz Albe <laurenz.albe@cybertec.at>
  2023-11-22 10:50 ` Re: PITR Ron Johnson <ronljohnsonjr@gmail.com>
  0 siblings, 3 replies; 10+ messages in thread

From: Rajesh Kumar @ 2023-11-22 08:11 UTC (permalink / raw)
  To: Pgsql-admin <pgsql-admin@lists.postgresql.org>

Hi

A person dropped the table and don't know time of drop.

How do I do PITR. Backup strategy is weekly full backup and daily
differential backup. Using pgbackrest.

Also. In future how do i monitor time of drop commands.

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR
  2023-11-22 08:11 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-11-22 08:23 ` Holger Jakobs <holger@jakobs.com>
  2 siblings, 0 replies; 10+ messages in thread

From: Holger Jakobs @ 2023-11-22 08:23 UTC (permalink / raw)
  To: pgsql-admin@lists.postgresql.org; +Cc: rajeshkumar.dba09@gmail.com


Am 22.11.23 um 09:11 schrieb Rajesh Kumar:
> Hi
>
> A person dropped the table and don't know time of drop.
>
> How do I do PITR. Backup strategy is weekly full backup and daily 
> differential backup. Using pgbackrest.
>
> Also. In future how do i monitor time of drop commands.


Monitoring DDL commands works fine using event triggers

https://www.postgresql.org/docs/current/event-triggers.html

Code of event triggers may even fail a command by raising an exception 
preventing the command becoming effective.

Regards,

Holger


-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012



Attachments:

  OpenPGP_signature (application/pgp-signature, 202B) download

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR
  2023-11-22 08:11 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-11-22 09:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
  2 siblings, 0 replies; 10+ messages in thread

From: Laurenz Albe @ 2023-11-22 09:01 UTC (permalink / raw)
  To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>

On Wed, 2023-11-22 at 13:41 +0530, Rajesh Kumar wrote:
> A person dropped the table and don't know time of drop.
> 
> How do I do PITR. Backup strategy is weekly full backup and daily differential backup. Using pgbackrest.
> 
> Also. In future how do i monitor time of drop commands. 

You can monitor that by setting "log_statement = 'ddl'".

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR
  2023-11-22 08:11 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-11-22 10:50 ` Ron Johnson <ronljohnsonjr@gmail.com>
  2023-11-22 10:54   ` Re: PITR Andreas Kretschmer <andreas@a-kretschmer.de>
  2 siblings, 1 reply; 10+ messages in thread

From: Ron Johnson @ 2023-11-22 10:50 UTC (permalink / raw)
  To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

On Wed, Nov 22, 2023 at 3:12 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:

> Hi
>
> A person dropped the table and don't know time of drop.
>

Revoke his permission to drop tables?


> How do I do PITR. Backup strategy is weekly full backup and daily
> differential backup. Using pgbackrest.
>
> Also. In future how do i monitor time of drop commands.
>

Set log_statement to 'all' or 'ddl', and then grep the log files for "DROP
TABLE ".

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR
  2023-11-22 08:11 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
  2023-11-22 10:50 ` Re: PITR Ron Johnson <ronljohnsonjr@gmail.com>
@ 2023-11-22 10:54   ` Andreas Kretschmer <andreas@a-kretschmer.de>
  0 siblings, 0 replies; 10+ messages in thread

From: Andreas Kretschmer @ 2023-11-22 10:54 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org



Am 22.11.23 um 11:50 schrieb Ron Johnson:
> On Wed, Nov 22, 2023 at 3:12 AM Rajesh Kumar 
> <rajeshkumar.dba09@gmail.com> wrote:
>
>
>
>     How do I do PITR. Backup strategy is weekly full backup and daily
>     differential backup. Using pgbackrest.
>
>     Also. In future how do i monitor time of drop commands.
>

https://blog.hagander.net/locating-the-recovery-point-just-before-a-dropped-table-230/


Andreas

-- 
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* PITR
@ 2024-02-14 18:38 Yongye Serkfem <yserkfem@gmail.com>
  2024-02-14 19:20 ` Re: PITR Ron Johnson <ronljohnsonjr@gmail.com>
  2024-02-14 19:40 ` Re: PITR Greg Sabino Mullane <htamfids@gmail.com>
  0 siblings, 2 replies; 10+ messages in thread

From: Yongye Serkfem @ 2024-02-14 18:38 UTC (permalink / raw)
  To: pgsql-general

Hi,
I hope you are all doing well. I am trying to do PITR on Postgresql v12.
Now that the recovery.conf file is not available in this version, where
should I set the recovery_target_time? I checked the Postgresql.conf file
and can't find the appropriate parameter to set the target time.
I'm looking forward to reading your thoughts.

Best regards
Yong Serkfem

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR
  2024-02-14 18:38 PITR Yongye Serkfem <yserkfem@gmail.com>
@ 2024-02-14 19:20 ` Ron Johnson <ronljohnsonjr@gmail.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Ron Johnson @ 2024-02-14 19:20 UTC (permalink / raw)
  To: pgsql-general

On Wed, Feb 14, 2024 at 1:39 PM Yongye Serkfem <yserkfem@gmail.com> wrote:

> Hi,
> I hope you are all doing well. I am trying to do PITR on Postgresql v12.
> Now that the recovery.conf file is not available in this version, where
> should I set the recovery_target_time? I checked the Postgresql.conf file
> and can't find the appropriate parameter to set the target time.
>

Consider using PgBackRest.  It's multithreaded, and you specify the
"restore-from start point" and "recover until" time on the command line.
It then handles everything for you, besides the final "pg_ctl start -D ...".

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PITR
  2024-02-14 18:38 PITR Yongye Serkfem <yserkfem@gmail.com>
@ 2024-02-14 19:40 ` Greg Sabino Mullane <htamfids@gmail.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Greg Sabino Mullane @ 2024-02-14 19:40 UTC (permalink / raw)
  To: Yongye Serkfem <yserkfem@gmail.com>; +Cc: pgsql-general

On Wed, Feb 14, 2024 at 1:39 PM Yongye Serkfem <yserkfem@gmail.com> wrote:

>  I checked the Postgresql.conf file and can't find the
> appropriate parameter to set the target time.
>

It is set inside the postgresql.conf file. Unless you have modified it,
there is a section in there called "Recovery Target" which has a handful of
commented-out variables starting with "recovery_"

Cheers,
Greg

^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2024-02-14 19:40 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-27 20:18 Pitr Geoffrey Myers <lists@serioustechnology.com>
2010-12-27 20:35 ` Geoffrey Myers <lists@serioustechnology.com>
2023-11-22 08:11 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-11-22 08:23 ` Re: PITR Holger Jakobs <holger@jakobs.com>
2023-11-22 09:01 ` Re: PITR Laurenz Albe <laurenz.albe@cybertec.at>
2023-11-22 10:50 ` Re: PITR Ron Johnson <ronljohnsonjr@gmail.com>
2023-11-22 10:54   ` Re: PITR Andreas Kretschmer <andreas@a-kretschmer.de>
2024-02-14 18:38 PITR Yongye Serkfem <yserkfem@gmail.com>
2024-02-14 19:20 ` Re: PITR Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-14 19:40 ` Re: PITR Greg Sabino Mullane <htamfids@gmail.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