pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPgBackRest PITR restore
12+ messages / 5 participants
[nested] [flat]
* PgBackRest PITR restore
@ 2021-09-07 10:32 Gokan Atmaca <linux.gokan@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Gokan Atmaca @ 2021-09-07 10:32 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello
Using PgBackRest I am able to restore the backup from 2 minutes ago.
Can I restore only one database?
Thanks.
--
⢀⣴⠾⠻⢶⣦⠀
⣾⠁⢠⠒⠀⣿⡁ Debian - The universal operating system
⢿⡄⠘⠷⠚⠋⠀ https://www.debian.org
⠈⠳⣄⠀⠀⠀⠀
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PgBackRest PITR restore
@ 2021-09-07 11:52 Victor Yegorov <vyegorov@gmail.com>
parent: Gokan Atmaca <linux.gokan@gmail.com>
0 siblings, 3 replies; 12+ messages in thread
From: Victor Yegorov @ 2021-09-07 11:52 UTC (permalink / raw)
To: Gokan Atmaca <linux.gokan@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
вт, 7 сент. 2021 г. в 13:32, Gokan Atmaca <linux.gokan@gmail.com>:
> Using PgBackRest I am able to restore the backup from 2 minutes ago.
> Can I restore only one database?
>
Yup.
https://pgbackrest.org/command.html#command-restore
Look for --db-include
--
Victor Yegorov
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PgBackRest PITR restore
@ 2021-09-07 11:55 Frédéric Boulet <boulet.f@gmail.com>
parent: Victor Yegorov <vyegorov@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Frédéric Boulet @ 2021-09-07 11:55 UTC (permalink / raw)
To: Victor Yegorov <vyegorov@gmail.com>; +Cc: Gokan Atmaca <linux.gokan@gmail.com>; pgsql-admin@lists.postgresql.org
Hello,
You can restore 1 database, but be careful, the other databases are also
created but *empty *!
BR
Le mar. 7 sept. 2021 à 13:52, Victor Yegorov <vyegorov@gmail.com> a écrit :
> вт, 7 сент. 2021 г. в 13:32, Gokan Atmaca <linux.gokan@gmail.com>:
>
>> Using PgBackRest I am able to restore the backup from 2 minutes ago.
>> Can I restore only one database?
>>
>
> Yup.
>
> https://pgbackrest.org/command.html#command-restore
>
> Look for --db-include
>
>
> --
> Victor Yegorov
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PgBackRest PITR restore
@ 2021-09-07 12:36 Gokan Atmaca <linux.gokan@gmail.com>
parent: Victor Yegorov <vyegorov@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Gokan Atmaca @ 2021-09-07 12:36 UTC (permalink / raw)
To: Victor Yegorov <vyegorov@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> https://pgbackrest.org/command.html#command-restore
>
> Look for --db-include
Very thanks, worked without a problem.
On Tue, Sep 7, 2021 at 2:52 PM Victor Yegorov <vyegorov@gmail.com> wrote:
>
> вт, 7 сент. 2021 г. в 13:32, Gokan Atmaca <linux.gokan@gmail.com>:
>>
>> Using PgBackRest I am able to restore the backup from 2 minutes ago.
>> Can I restore only one database?
>
>
> Yup.
>
> https://pgbackrest.org/command.html#command-restore
>
> Look for --db-include
>
>
> --
> Victor Yegorov
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PgBackRest PITR restore
@ 2021-09-07 13:00 Gokan Atmaca <linux.gokan@gmail.com>
parent: Victor Yegorov <vyegorov@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Gokan Atmaca @ 2021-09-07 13:00 UTC (permalink / raw)
To: Victor Yegorov <vyegorov@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> Look for --db-include
This option restores a single database but corrupts the others. Is it
necessary to take a backup with "pg_dump" before doing this?
Other database error:
FATAL: relation mapping file "base/16386/pg_filenode.map" contains invalid data
On Tue, Sep 7, 2021 at 2:52 PM Victor Yegorov <vyegorov@gmail.com> wrote:
>
> вт, 7 сент. 2021 г. в 13:32, Gokan Atmaca <linux.gokan@gmail.com>:
>>
>> Using PgBackRest I am able to restore the backup from 2 minutes ago.
>> Can I restore only one database?
>
>
> Yup.
>
> https://pgbackrest.org/command.html#command-restore
>
> Look for --db-include
>
>
> --
> Victor Yegorov
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PgBackRest PITR restore
@ 2021-09-07 13:01 Gokan Atmaca <linux.gokan@gmail.com>
parent: Frédéric Boulet <boulet.f@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Gokan Atmaca @ 2021-09-07 13:01 UTC (permalink / raw)
To: Frédéric Boulet <boulet.f@gmail.com>; +Cc: Victor Yegorov <vyegorov@gmail.com>; pgsql-admin@lists.postgresql.org
> You can restore 1 database, but be careful, the other databases are also created but empty !
you're right, now I get it. :)
On Tue, Sep 7, 2021 at 2:55 PM Frédéric Boulet <boulet.f@gmail.com> wrote:
>
> Hello,
>
> You can restore 1 database, but be careful, the other databases are also created but empty !
>
> BR
>
> Le mar. 7 sept. 2021 à 13:52, Victor Yegorov <vyegorov@gmail.com> a écrit :
>>
>> вт, 7 сент. 2021 г. в 13:32, Gokan Atmaca <linux.gokan@gmail.com>:
>>>
>>> Using PgBackRest I am able to restore the backup from 2 minutes ago.
>>> Can I restore only one database?
>>
>>
>> Yup.
>>
>> https://pgbackrest.org/command.html#command-restore
>>
>> Look for --db-include
>>
>>
>> --
>> Victor Yegorov
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PgBackRest PITR restore
@ 2021-09-07 13:19 Frédéric Boulet <boulet.f@gmail.com>
parent: Gokan Atmaca <linux.gokan@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Frédéric Boulet @ 2021-09-07 13:19 UTC (permalink / raw)
To: Gokan Atmaca <linux.gokan@gmail.com>; +Cc: Victor Yegorov <vyegorov@gmail.com>; pgsql-admin@lists.postgresql.org
You need to restore the databases in a different directory, the "recover"
the one you want.
Le mar. 7 sept. 2021 à 15:01, Gokan Atmaca <linux.gokan@gmail.com> a écrit :
> > You can restore 1 database, but be careful, the other databases are also
> created but empty !
>
> you're right, now I get it. :)
>
> On Tue, Sep 7, 2021 at 2:55 PM Frédéric Boulet <boulet.f@gmail.com> wrote:
> >
> > Hello,
> >
> > You can restore 1 database, but be careful, the other databases are also
> created but empty !
> >
> > BR
> >
> > Le mar. 7 sept. 2021 à 13:52, Victor Yegorov <vyegorov@gmail.com> a
> écrit :
> >>
> >> вт, 7 сент. 2021 г. в 13:32, Gokan Atmaca <linux.gokan@gmail.com>:
> >>>
> >>> Using PgBackRest I am able to restore the backup from 2 minutes ago.
> >>> Can I restore only one database?
> >>
> >>
> >> Yup.
> >>
> >> https://pgbackrest.org/command.html#command-restore
> >>
> >> Look for --db-include
> >>
> >>
> >> --
> >> Victor Yegorov
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PgBackRest PITR restore
@ 2021-09-09 14:29 Gokan Atmaca <linux.gokan@gmail.com>
parent: Frédéric Boulet <boulet.f@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Gokan Atmaca @ 2021-09-09 14:29 UTC (permalink / raw)
To: Frédéric Boulet <boulet.f@gmail.com>; +Cc: Victor Yegorov <vyegorov@gmail.com>; pgsql-admin@lists.postgresql.org
> You need to restore the databases in a different directory, the "recover" the one you want.
When I do a PITR, the database is in recovery mode. (in archive recovery)
What do I need to do to get it back in "in production" mode?
On Tue, Sep 7, 2021 at 4:19 PM Frédéric Boulet <boulet.f@gmail.com> wrote:
>
> You need to restore the databases in a different directory, the "recover" the one you want.
>
> Le mar. 7 sept. 2021 à 15:01, Gokan Atmaca <linux.gokan@gmail.com> a écrit :
>>
>> > You can restore 1 database, but be careful, the other databases are also created but empty !
>>
>> you're right, now I get it. :)
>>
>> On Tue, Sep 7, 2021 at 2:55 PM Frédéric Boulet <boulet.f@gmail.com> wrote:
>> >
>> > Hello,
>> >
>> > You can restore 1 database, but be careful, the other databases are also created but empty !
>> >
>> > BR
>> >
>> > Le mar. 7 sept. 2021 à 13:52, Victor Yegorov <vyegorov@gmail.com> a écrit :
>> >>
>> >> вт, 7 сент. 2021 г. в 13:32, Gokan Atmaca <linux.gokan@gmail.com>:
>> >>>
>> >>> Using PgBackRest I am able to restore the backup from 2 minutes ago.
>> >>> Can I restore only one database?
>> >>
>> >>
>> >> Yup.
>> >>
>> >> https://pgbackrest.org/command.html#command-restore
>> >>
>> >> Look for --db-include
>> >>
>> >>
>> >> --
>> >> Victor Yegorov
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PgBackRest PITR restore
@ 2021-09-09 14:36 Gokan Atmaca <linux.gokan@gmail.com>
parent: Gokan Atmaca <linux.gokan@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Gokan Atmaca @ 2021-09-09 14:36 UTC (permalink / raw)
To: Frédéric Boulet <boulet.f@gmail.com>; +Cc: Victor Yegorov <vyegorov@gmail.com>; pgsql-admin@lists.postgresql.org
> What do I need to do to get it back in "in production" mode?
I did as below. looks correct now. Is there any other method?
select pg_wal_replay_resume();
pg_wal_replay_resume
----------------------
(1 row)
On Thu, Sep 9, 2021 at 5:29 PM Gokan Atmaca <linux.gokan@gmail.com> wrote:
>
> > You need to restore the databases in a different directory, the "recover" the one you want.
> When I do a PITR, the database is in recovery mode. (in archive recovery)
> What do I need to do to get it back in "in production" mode?
>
>
>
>
>
> On Tue, Sep 7, 2021 at 4:19 PM Frédéric Boulet <boulet.f@gmail.com> wrote:
> >
> > You need to restore the databases in a different directory, the "recover" the one you want.
> >
> > Le mar. 7 sept. 2021 à 15:01, Gokan Atmaca <linux.gokan@gmail.com> a écrit :
> >>
> >> > You can restore 1 database, but be careful, the other databases are also created but empty !
> >>
> >> you're right, now I get it. :)
> >>
> >> On Tue, Sep 7, 2021 at 2:55 PM Frédéric Boulet <boulet.f@gmail.com> wrote:
> >> >
> >> > Hello,
> >> >
> >> > You can restore 1 database, but be careful, the other databases are also created but empty !
> >> >
> >> > BR
> >> >
> >> > Le mar. 7 sept. 2021 à 13:52, Victor Yegorov <vyegorov@gmail.com> a écrit :
> >> >>
> >> >> вт, 7 сент. 2021 г. в 13:32, Gokan Atmaca <linux.gokan@gmail.com>:
> >> >>>
> >> >>> Using PgBackRest I am able to restore the backup from 2 minutes ago.
> >> >>> Can I restore only one database?
> >> >>
> >> >>
> >> >> Yup.
> >> >>
> >> >> https://pgbackrest.org/command.html#command-restore
> >> >>
> >> >> Look for --db-include
> >> >>
> >> >>
> >> >> --
> >> >> Victor Yegorov
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PgBackRest PITR restore
@ 2021-09-09 14:38 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Gokan Atmaca <linux.gokan@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: MichaelDBA @ 2021-09-09 14:38 UTC (permalink / raw)
To: Gokan Atmaca <linux.gokan@gmail.com>; +Cc: Frédéric Boulet <boulet.f@gmail.com>; Victor Yegorov <vyegorov@gmail.com>; pgsql-admin@lists.postgresql.org
* “standby.signal” – indicates the server should start up as a hot standby
* “recovery.signal” – indicates the server should start up in targeted
recovery mode
Assuming we are at V12 or higher, simply remove standby.signal if exists
in datadir and add the empty file recovery.signal in datadir.
Regards,
Michael Vitale
Gokan Atmaca wrote on 9/9/2021 10:29 AM:
>> You need to restore the databases in a different directory, the "recover" the one you want.
> When I do a PITR, the database is in recovery mode. (in archive recovery)
> What do I need to do to get it back in "in production" mode?
>
>
>
>
>
> On Tue, Sep 7, 2021 at 4:19 PM Frédéric Boulet <boulet.f@gmail.com> wrote:
>> You need to restore the databases in a different directory, the "recover" the one you want.
>>
>> Le mar. 7 sept. 2021 à 15:01, Gokan Atmaca <linux.gokan@gmail.com> a écrit :
>>>> You can restore 1 database, but be careful, the other databases are also created but empty !
>>> you're right, now I get it. :)
>>>
>>> On Tue, Sep 7, 2021 at 2:55 PM Frédéric Boulet <boulet.f@gmail.com> wrote:
>>>> Hello,
>>>>
>>>> You can restore 1 database, but be careful, the other databases are also created but empty !
>>>>
>>>> BR
>>>>
>>>> Le mar. 7 sept. 2021 à 13:52, Victor Yegorov <vyegorov@gmail.com> a écrit :
>>>>> вт, 7 сент. 2021 г. в 13:32, Gokan Atmaca <linux.gokan@gmail.com>:
>>>>>> Using PgBackRest I am able to restore the backup from 2 minutes ago.
>>>>>> Can I restore only one database?
>>>>>
>>>>> Yup.
>>>>>
>>>>> https://pgbackrest.org/command.html#command-restore
>>>>>
>>>>> Look for --db-include
>>>>>
>>>>>
>>>>> --
>>>>> Victor Yegorov
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PgBackRest PITR restore
@ 2021-09-11 15:41 Gokan Atmaca <linux.gokan@gmail.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 0 replies; 12+ messages in thread
From: Gokan Atmaca @ 2021-09-11 15:41 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Frédéric Boulet <boulet.f@gmail.com>; Victor Yegorov <vyegorov@gmail.com>; pgsql-admin@lists.postgresql.org
> “standby.signal” – indicates the server should start up as a hot standby
> “recovery.signal” – indicates the server should start up in targeted recovery mode
>
> Assuming we are at V12 or higher, simply remove standby.signal if exists in datadir and add the empty file recovery.signal in datadir.
I'm sorry for the late reply. Thanks for information. I found the
following in your git repository.
I will try as soon as possible. Thanks for these apps. :)
https://github.com/MichaelDBA/pg_alert
https://github.com/MichaelDBA/pg_report
On Thu, Sep 9, 2021 at 5:38 PM MichaelDBA <MichaelDBA@sqlexec.com> wrote:
>
> “standby.signal” – indicates the server should start up as a hot standby
> “recovery.signal” – indicates the server should start up in targeted recovery mode
>
> Assuming we are at V12 or higher, simply remove standby.signal if exists in datadir and add the empty file recovery.signal in datadir.
>
> Regards,
> Michael Vitale
>
>
>
> Gokan Atmaca wrote on 9/9/2021 10:29 AM:
>
> You need to restore the databases in a different directory, the "recover" the one you want.
>
> When I do a PITR, the database is in recovery mode. (in archive recovery)
> What do I need to do to get it back in "in production" mode?
>
>
>
>
>
> On Tue, Sep 7, 2021 at 4:19 PM Frédéric Boulet <boulet.f@gmail.com> wrote:
>
> You need to restore the databases in a different directory, the "recover" the one you want.
>
> Le mar. 7 sept. 2021 à 15:01, Gokan Atmaca <linux.gokan@gmail.com> a écrit :
>
> You can restore 1 database, but be careful, the other databases are also created but empty !
>
> you're right, now I get it. :)
>
> On Tue, Sep 7, 2021 at 2:55 PM Frédéric Boulet <boulet.f@gmail.com> wrote:
>
> Hello,
>
> You can restore 1 database, but be careful, the other databases are also created but empty !
>
> BR
>
> Le mar. 7 sept. 2021 à 13:52, Victor Yegorov <vyegorov@gmail.com> a écrit :
>
> вт, 7 сент. 2021 г. в 13:32, Gokan Atmaca <linux.gokan@gmail.com>:
>
> Using PgBackRest I am able to restore the backup from 2 minutes ago.
> Can I restore only one database?
>
> Yup.
>
> https://pgbackrest.org/command.html#command-restore
>
> Look for --db-include
>
>
> --
> Victor Yegorov
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PgBackRest PITR restore
@ 2021-09-13 19:43 Stephen Frost <sfrost@snowman.net>
parent: Gokan Atmaca <linux.gokan@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Stephen Frost @ 2021-09-13 19:43 UTC (permalink / raw)
To: Gokan Atmaca <linux.gokan@gmail.com>; +Cc: Victor Yegorov <vyegorov@gmail.com>; pgsql-admin@lists.postgresql.org
Greetings,
* Gokan Atmaca (linux.gokan@gmail.com) wrote:
> > Look for --db-include
>
> This option restores a single database but corrupts the others. Is it
> necessary to take a backup with "pg_dump" before doing this?
"Corrupts" is only kind of correct- it explicitly doesn't restore the
other databases (since you asked for just one to be restored) and makes
it such that you're not able to log into them.
Performing an export with pg_dump is one approach to getting a point in
time view of all of the data in a given database, but you won't be able
to perform PITR-style restore with pg_dump's.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2021-09-13 19:43 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-07 10:32 PgBackRest PITR restore Gokan Atmaca <linux.gokan@gmail.com>
2021-09-07 11:52 ` Victor Yegorov <vyegorov@gmail.com>
2021-09-07 11:55 ` Frédéric Boulet <boulet.f@gmail.com>
2021-09-07 13:01 ` Gokan Atmaca <linux.gokan@gmail.com>
2021-09-07 13:19 ` Frédéric Boulet <boulet.f@gmail.com>
2021-09-09 14:29 ` Gokan Atmaca <linux.gokan@gmail.com>
2021-09-09 14:36 ` Gokan Atmaca <linux.gokan@gmail.com>
2021-09-09 14:38 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-09-11 15:41 ` Gokan Atmaca <linux.gokan@gmail.com>
2021-09-07 12:36 ` Gokan Atmaca <linux.gokan@gmail.com>
2021-09-07 13:00 ` Gokan Atmaca <linux.gokan@gmail.com>
2021-09-13 19:43 ` Stephen Frost <sfrost@snowman.net>
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