pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPITR Setup Using Hot StandBy
9+ messages / 4 participants
[nested] [flat]
* PITR Setup Using Hot StandBy
@ 2016-05-24 13:05 Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 15:31 ` Re: PITR Setup Using Hot StandBy Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Vicky Soni - Quipment India @ 2016-05-24 13:05 UTC (permalink / raw)
To: pgsql-admin
[Quipment Logo]
Hello Friends,
I need to setup PITR. Using master, I am able to do wal archiving.
But using slave I am not able to archive WAL files.
Can anyone please guide me, how to setup PITR using Hot StandBy?
I have setup LogShpping + Streaming Replication in my dev environment, now using this slave I want to setup PITR.
I have already setup following parameters:
wal_level = hot_standby
archive_mode = on
archive_command = 'copy "c:\\Program Files\\PostgreSQL\\9.4\\data\\%p" "\\\\192.168.170.221\\Primary_WAL_Archive\\%f"'
max_wal_senders = 3
wal_keep_segments = 8
hot_standby = on
Any help is appreciated.
Vicky Soni
Database Administrator
.Net Department
T / +91 (0) 93288 80507
vicky.soni@<mailto:%20vicky.soni@quipment.in> / www.quipment.in<http://www.quipment.in;
QTwee Group BV
KvK Arnhem 60858001
BTW NL854090721B01
mail@<mailto:mail@quipment.nl> / www.quipment.nl<http://www.quipment.nl;
Hoofdkantoor Nijmegen
Kerkenbos 1236 C
6546 BE Nijmegen
Tel.
+31 (0)24 372 47 00
Fax.
+31 (0)24 372 47 07
Quipment India
A-212 Shivalik Corporate Park
B/H IOC Petrol Pump
Satellite, Ahmedabad
Gujarat - 380015 - India
Tel. +91 (0) 79 4009 7847
Disclaimer
This e-mail message (including any attachment) is intended only for the personal use of the recipient(s) named above. This message is confidential and may be legally privileged. If you are not an intended recipient, you may not review, copy or distribute this message. If you have received this communication in error, please notify us immediately by e-mail and delete the original message.
Attachments:
image001.jpg (image/jpeg, 23.8K) download
view image
* Re: PITR Setup Using Hot StandBy
2016-05-24 13:05 PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
@ 2016-05-24 15:31 ` Dave Johansen <davejohansen@gmail.com>
2016-05-24 15:48 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 9+ messages in thread
From: Dave Johansen @ 2016-05-24 15:31 UTC (permalink / raw)
To: Vicky Soni - Quipment India <vicky.soni@quipment.nl>; +Cc: pgsql-admin
On Tue, May 24, 2016 at 6:05 AM, Vicky Soni - Quipment India <
vicky.soni@quipment.nl> wrote:
> [image: Quipment Logo]
>
> Hello Friends,
>
>
>
> I need to setup PITR. Using master, I am able to do
> wal archiving.
>
> But using slave I am not able to archive WAL files.
>
>
>
> Can anyone please guide me, how to setup PITR using Hot StandBy?
>
>
>
> I have setup LogShpping + Streaming Replication in my dev environment, now
> using this slave I want to setup PITR.
>
>
>
> I have already setup following parameters:
>
>
>
> wal_level = hot_standby
>
>
>
> archive_mode = on
>
>
>
> archive_command = 'copy "c:\\Program Files\\PostgreSQL\\9.4\\data\\%p"
> "\\\\192.168.170.221\\Primary_WAL_Archive\\%f"'
>
>
>
> max_wal_senders = 3
>
>
>
> wal_keep_segments = 8
>
>
>
> hot_standby = on
>
>
>
> Any help is appreciated.
>
>
>
>
http://www.postgresql.org/docs/9.2/static/continuous-archiving.html
The standby/slave is read only and does not generate WAL files (it only
consumes them to stay in sync with the master).
Attachments:
image001.jpg (image/jpeg, 23.8K) download
view image
* Re: PITR Setup Using Hot StandBy
2016-05-24 13:05 PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 15:31 ` Re: PITR Setup Using Hot StandBy Dave Johansen <davejohansen@gmail.com>
@ 2016-05-24 15:48 ` Scott Whitney <scott@journyx.com>
2016-05-24 17:21 ` Re: PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Whitney @ 2016-05-24 15:48 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; Vicky Soni - Quipment India <vicky.soni@quipment.nl>; +Cc: pgsql-admin
Hi, Dave.
It depends on what you're trying to do.
If you want a slave up in real-time replication mode, that's by far the simplest setup.
The concept is:
1) start a backup
select pg_start_backup("myslave"),true);
2) Rsync (or tar) your pg data directory over to your slave.
3) select pg_stop_backup();
4) Tell your slave how to connect to the master
Start your slave.
This is a pretty straight-forward link for this:
https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/
[https://opensourcedbms.com/dbms/wp-content/uploads/2013/02/Image-0016.png]<https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/;
How to do point in time recovery with PostgreSQL 9.2 ...<https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/;
opensourcedbms.com
This post describes how to do point in time recovery with PostgreSQL 9.2 : PITR. Point in time recovery (PITR) simply means restoring data upto certain point in time.
If you want to do something "clever," like offset how far behind the slave is, that's more complex.
________________________________
From: pgsql-admin-owner@postgresql.org <pgsql-admin-owner@postgresql.org> on behalf of Dave Johansen <davejohansen@gmail.com>
Sent: Tuesday, May 24, 2016 10:31 AM
To: Vicky Soni - Quipment India
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
On Tue, May 24, 2016 at 6:05 AM, Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>> wrote:
[Quipment Logo]
Hello Friends,
I need to setup PITR. Using master, I am able to do wal archiving.
But using slave I am not able to archive WAL files.
Can anyone please guide me, how to setup PITR using Hot StandBy?
I have setup LogShpping + Streaming Replication in my dev environment, now using this slave I want to setup PITR.
I have already setup following parameters:
wal_level = hot_standby
archive_mode = on
archive_command = 'copy "c:\\Program Files\\PostgreSQL\\9.4\\data\\%p" "\\\\192.168.170.221\\Primary_WAL_Archive\\%f"'
max_wal_senders = 3
wal_keep_segments = 8
hot_standby = on
Any help is appreciated.
http://www.postgresql.org/docs/9.2/static/continuous-archiving.html
The standby/slave is read only and does not generate WAL files (it only consumes them to stay in sync with the master).
Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com
p 512.834.8888
f 512-834-8858
Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/
Attachments:
image001.jpg (image/jpeg, 23.8K) download
view image
* Re: PITR Setup Using Hot StandBy
2016-05-24 13:05 PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 15:31 ` Re: PITR Setup Using Hot StandBy Dave Johansen <davejohansen@gmail.com>
2016-05-24 15:48 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
@ 2016-05-24 17:21 ` Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 17:25 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 9+ messages in thread
From: Vicky Soni - Quipment India @ 2016-05-24 17:21 UTC (permalink / raw)
To: Scott Whitney <scott@journyx.com>; Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-admin
Hi,
Basically I do not want to touch my Master while performing PITR.
So here what I am trying to achieve now in my development environment.
1. Take base backup from slave
2. Do WAL archiving from master
3. Try to up postgresql using base backup from slave and wal archives from master.
I don't know, if this will work or not.
But my final goal is to setup entire PITR process using base backup from slave.
Thanks & Regards,
Vicky Soni
Database Administrator
From: Scott Whitney [mailto:scott@journyx.com]
Sent: 24 May 2016 21:18
To: Dave Johansen <davejohansen@gmail.com>; Vicky Soni - Quipment India <vicky.soni@quipment.nl>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
Hi, Dave.
It depends on what you're trying to do.
If you want a slave up in real-time replication mode, that's by far the simplest setup.
The concept is:
1) start a backup
select pg_start_backup("myslave"),true);
2) Rsync (or tar) your pg data directory over to your slave.
3) select pg_stop_backup();
4) Tell your slave how to connect to the master
Start your slave.
This is a pretty straight-forward link for this:
https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/
[https://opensourcedbms.com/dbms/wp-content/uploads/2013/02/Image-0016.png]<https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/;
How to do point in time recovery with PostgreSQL 9.2 ...<https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/;
opensourcedbms.com
This post describes how to do point in time recovery with PostgreSQL 9.2 : PITR. Point in time recovery (PITR) simply means restoring data upto certain point in time.
If you want to do something "clever," like offset how far behind the slave is, that's more complex.
________________________________
From: pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org> <pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org>> on behalf of Dave Johansen <davejohansen@gmail.com<mailto:davejohansen@gmail.com>>
Sent: Tuesday, May 24, 2016 10:31 AM
To: Vicky Soni - Quipment India
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
On Tue, May 24, 2016 at 6:05 AM, Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>> wrote:
[Quipment Logo]
Hello Friends,
I need to setup PITR. Using master, I am able to do wal archiving.
But using slave I am not able to archive WAL files.
Can anyone please guide me, how to setup PITR using Hot StandBy?
I have setup LogShpping + Streaming Replication in my dev environment, now using this slave I want to setup PITR.
I have already setup following parameters:
wal_level = hot_standby
archive_mode = on
archive_command = 'copy "c:\\Program Files\\PostgreSQL\\9.4\\data\\%p" "\\\\192.168.170.221\\Primary_WAL_Archive\\%f<file:///\\\192.168.170.221\Primary_WAL_Archive\%25f>"'
max_wal_senders = 3
wal_keep_segments = 8
hot_standby = on
Any help is appreciated.
http://www.postgresql.org/docs/9.2/static/continuous-archiving.html
The standby/slave is read only and does not generate WAL files (it only consumes them to stay in sync with the master).
Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com<http://www.journyx.com;
p 512.834.8888
f 512-834-8858
Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/
Attachments:
image001.jpg (image/jpeg, 23.8K) download
view image
* Re: PITR Setup Using Hot StandBy
2016-05-24 13:05 PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 15:31 ` Re: PITR Setup Using Hot StandBy Dave Johansen <davejohansen@gmail.com>
2016-05-24 15:48 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
2016-05-24 17:21 ` Re: PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
@ 2016-05-24 17:25 ` Scott Whitney <scott@journyx.com>
2016-05-24 17:32 ` Re: PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Whitney @ 2016-05-24 17:25 UTC (permalink / raw)
To: Vicky Soni - Quipment India <vicky.soni@quipment.nl>; Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-admin
I'm still not sure I fully understand what it is you're trying to accomplish.
Using hot-standby streaming replication, you can have a slave up to date with the master and available for promotion as well as select-only queries.
If you're trying to setup multiple slaves, this is also possible.
I have a production server (call it db-prod). I have a training/demo server (also at my data center -- call it db-back). I have a 3rd server at my HQ (and a 4th and 5th in my DR data center).
DB-prod runs my paying customers
DB-back runs my test/training sites (different port) and replicates DB-prod
DB-HQ runs my internal stuff (standard port) and replicates DB-back. It replicates BOTH DB-back AND DB-prod FROM DB-back. It does not touch the actual production server
DB-DR replicates DB-prod
DB-DR-back replicates DB-back (both clusters)
I accomplish this using streaming replication in PG 9.x.
________________________________
From: Vicky Soni - Quipment India <vicky.soni@quipment.nl>
Sent: Tuesday, May 24, 2016 12:21 PM
To: Scott Whitney; Dave Johansen
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] PITR Setup Using Hot StandBy
Hi,
Basically I do not want to touch my Master while performing PITR.
So here what I am trying to achieve now in my development environment.
1. Take base backup from slave
2. Do WAL archiving from master
3. Try to up postgresql using base backup from slave and wal archives from master.
I don’t know, if this will work or not.
But my final goal is to setup entire PITR process using base backup from slave.
Thanks & Regards,
Vicky Soni
Database Administrator
From: Scott Whitney [mailto:scott@journyx.com]
Sent: 24 May 2016 21:18
To: Dave Johansen <davejohansen@gmail.com>; Vicky Soni - Quipment India <vicky.soni@quipment.nl>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
Hi, Dave.
It depends on what you're trying to do.
If you want a slave up in real-time replication mode, that's by far the simplest setup.
The concept is:
1) start a backup
select pg_start_backup("myslave"),true);
2) Rsync (or tar) your pg data directory over to your slave.
3) select pg_stop_backup();
4) Tell your slave how to connect to the master
Start your slave.
This is a pretty straight-forward link for this:
https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/
[https://opensourcedbms.com/dbms/wp-content/uploads/2013/02/Image-0016.png]<https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/;
How to do point in time recovery with PostgreSQL 9.2 ...<https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/;
opensourcedbms.com
This post describes how to do point in time recovery with PostgreSQL 9.2 : PITR. Point in time recovery (PITR) simply means restoring data upto certain point in time.
If you want to do something "clever," like offset how far behind the slave is, that's more complex.
________________________________
From: pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org> <pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org>> on behalf of Dave Johansen <davejohansen@gmail.com<mailto:davejohansen@gmail.com>>
Sent: Tuesday, May 24, 2016 10:31 AM
To: Vicky Soni - Quipment India
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
On Tue, May 24, 2016 at 6:05 AM, Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>> wrote:
[Quipment Logo]
Hello Friends,
I need to setup PITR. Using master, I am able to do wal archiving.
But using slave I am not able to archive WAL files.
Can anyone please guide me, how to setup PITR using Hot StandBy?
I have setup LogShpping + Streaming Replication in my dev environment, now using this slave I want to setup PITR.
I have already setup following parameters:
wal_level = hot_standby
archive_mode = on
archive_command = 'copy "c:\\Program Files\\PostgreSQL\\9.4\\data\\%p" "\\\\192.168.170.221\\Primary_WAL_Archive\\%f<file:///\\\192.168.170.221\Primary_WAL_Archive\%25f>"'
max_wal_senders = 3
wal_keep_segments = 8
hot_standby = on
Any help is appreciated.
http://www.postgresql.org/docs/9.2/static/continuous-archiving.html
The standby/slave is read only and does not generate WAL files (it only consumes them to stay in sync with the master).
Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com<http://www.journyx.com;
p 512.834.8888
f 512-834-8858
Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/
Attachments:
image001.jpg (image/jpeg, 23.8K) download
view image
* Re: PITR Setup Using Hot StandBy
2016-05-24 13:05 PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 15:31 ` Re: PITR Setup Using Hot StandBy Dave Johansen <davejohansen@gmail.com>
2016-05-24 15:48 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
2016-05-24 17:21 ` Re: PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 17:25 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
@ 2016-05-24 17:32 ` Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 17:38 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 9+ messages in thread
From: Vicky Soni - Quipment India @ 2016-05-24 17:32 UTC (permalink / raw)
To: Scott Whitney <scott@journyx.com>; Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-admin
Hi Scott/Dave,
Thanks for prompt response.
I want to establish automated process of PITR(Backup and Recovery).
1. Weekly basebackup
2. WAL Archiving
Now in my production environment database size is 300 GB, so my System Admin asked me to check if it is possible to establish PITR using slave or not.
As copying 300GB would consume production server's resources and will make some process slower.
So if we can set base backup and other processes using read-only replica, which is rarely used in our application architecture then it would be win-win for DBA and SysAdmin.
Thanks & Regards,
Vicky Soni
Database Administrator
From: Scott Whitney [mailto:scott@journyx.com]
Sent: 24 May 2016 22:56
To: Vicky Soni - Quipment India <vicky.soni@quipment.nl>; Dave Johansen <davejohansen@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
I'm still not sure I fully understand what it is you're trying to accomplish.
Using hot-standby streaming replication, you can have a slave up to date with the master and available for promotion as well as select-only queries.
If you're trying to setup multiple slaves, this is also possible.
I have a production server (call it db-prod). I have a training/demo server (also at my data center -- call it db-back). I have a 3rd server at my HQ (and a 4th and 5th in my DR data center).
DB-prod runs my paying customers
DB-back runs my test/training sites (different port) and replicates DB-prod
DB-HQ runs my internal stuff (standard port) and replicates DB-back. It replicates BOTH DB-back AND DB-prod FROM DB-back. It does not touch the actual production server
DB-DR replicates DB-prod
DB-DR-back replicates DB-back (both clusters)
I accomplish this using streaming replication in PG 9.x.
________________________________
From: Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>>
Sent: Tuesday, May 24, 2016 12:21 PM
To: Scott Whitney; Dave Johansen
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: RE: [ADMIN] PITR Setup Using Hot StandBy
Hi,
Basically I do not want to touch my Master while performing PITR.
So here what I am trying to achieve now in my development environment.
1. Take base backup from slave
2. Do WAL archiving from master
3. Try to up postgresql using base backup from slave and wal archives from master.
I don't know, if this will work or not.
But my final goal is to setup entire PITR process using base backup from slave.
Thanks & Regards,
Vicky Soni
Database Administrator
From: Scott Whitney [mailto:scott@journyx.com]
Sent: 24 May 2016 21:18
To: Dave Johansen <davejohansen@gmail.com<mailto:davejohansen@gmail.com>>; Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>>
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
Hi, Dave.
It depends on what you're trying to do.
If you want a slave up in real-time replication mode, that's by far the simplest setup.
The concept is:
1) start a backup
select pg_start_backup("myslave"),true);
2) Rsync (or tar) your pg data directory over to your slave.
3) select pg_stop_backup();
4) Tell your slave how to connect to the master
Start your slave.
This is a pretty straight-forward link for this:
https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/
[https://opensourcedbms.com/dbms/wp-content/uploads/2013/02/Image-0016.png]<https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/;
How to do point in time recovery with PostgreSQL 9.2 ...<https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/;
opensourcedbms.com
This post describes how to do point in time recovery with PostgreSQL 9.2 : PITR. Point in time recovery (PITR) simply means restoring data upto certain point in time.
If you want to do something "clever," like offset how far behind the slave is, that's more complex.
________________________________
From: pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org> <pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org>> on behalf of Dave Johansen <davejohansen@gmail.com<mailto:davejohansen@gmail.com>>
Sent: Tuesday, May 24, 2016 10:31 AM
To: Vicky Soni - Quipment India
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
On Tue, May 24, 2016 at 6:05 AM, Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>> wrote:
[Quipment Logo]
Hello Friends,
I need to setup PITR. Using master, I am able to do wal archiving.
But using slave I am not able to archive WAL files.
Can anyone please guide me, how to setup PITR using Hot StandBy?
I have setup LogShpping + Streaming Replication in my dev environment, now using this slave I want to setup PITR.
I have already setup following parameters:
wal_level = hot_standby
archive_mode = on
archive_command = 'copy "c:\\Program Files\\PostgreSQL\\9.4\\data\\%p" "\\\\192.168.170.221\\Primary_WAL_Archive\\%f<file:///\\192.168.170.221\Primary_WAL_Archive\%25f>"'
max_wal_senders = 3
wal_keep_segments = 8
hot_standby = on
Any help is appreciated.
http://www.postgresql.org/docs/9.2/static/continuous-archiving.html
The standby/slave is read only and does not generate WAL files (it only consumes them to stay in sync with the master).
Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com<http://www.journyx.com;
p 512.834.8888
f 512-834-8858
Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/
Attachments:
image001.jpg (image/jpeg, 23.8K) download
view image
* Re: PITR Setup Using Hot StandBy
2016-05-24 13:05 PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 15:31 ` Re: PITR Setup Using Hot StandBy Dave Johansen <davejohansen@gmail.com>
2016-05-24 15:48 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
2016-05-24 17:21 ` Re: PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 17:25 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
2016-05-24 17:32 ` Re: PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
@ 2016-05-24 17:38 ` Scott Whitney <scott@journyx.com>
2016-05-25 07:51 ` Re: PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Whitney @ 2016-05-24 17:38 UTC (permalink / raw)
To: Vicky Soni - Quipment India <vicky.soni@quipment.nl>; Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-admin
Yeah. I was basically in the same boat. That first base backup is a bit of a challenge.
What I did was take a physical USB drive, pg_start_backup(), rsync over to the USB drive, bring it back to the office, rsync it to the server I wanted to use THEN pg_stop_backup(). It worked fine for me.
Even when I have had to restart replication on the slave due to some cleverness, I can still rsync the base from production, and it copies only changed files. It takes me an hour or so, but nothing like the initial sync.
________________________________
From: Vicky Soni - Quipment India <vicky.soni@quipment.nl>
Sent: Tuesday, May 24, 2016 12:32 PM
To: Scott Whitney; Dave Johansen
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] PITR Setup Using Hot StandBy
Hi Scott/Dave,
Thanks for prompt response.
I want to establish automated process of PITR(Backup and Recovery).
1. Weekly basebackup
2. WAL Archiving
Now in my production environment database size is 300 GB, so my System Admin asked me to check if it is possible to establish PITR using slave or not.
As copying 300GB would consume production server’s resources and will make some process slower.
So if we can set base backup and other processes using read-only replica, which is rarely used in our application architecture then it would be win-win for DBA and SysAdmin.
Thanks & Regards,
Vicky Soni
Database Administrator
From: Scott Whitney [mailto:scott@journyx.com]
Sent: 24 May 2016 22:56
To: Vicky Soni - Quipment India <vicky.soni@quipment.nl>; Dave Johansen <davejohansen@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
I'm still not sure I fully understand what it is you're trying to accomplish.
Using hot-standby streaming replication, you can have a slave up to date with the master and available for promotion as well as select-only queries.
If you're trying to setup multiple slaves, this is also possible.
I have a production server (call it db-prod). I have a training/demo server (also at my data center -- call it db-back). I have a 3rd server at my HQ (and a 4th and 5th in my DR data center).
DB-prod runs my paying customers
DB-back runs my test/training sites (different port) and replicates DB-prod
DB-HQ runs my internal stuff (standard port) and replicates DB-back. It replicates BOTH DB-back AND DB-prod FROM DB-back. It does not touch the actual production server
DB-DR replicates DB-prod
DB-DR-back replicates DB-back (both clusters)
I accomplish this using streaming replication in PG 9.x.
________________________________
From: Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>>
Sent: Tuesday, May 24, 2016 12:21 PM
To: Scott Whitney; Dave Johansen
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: RE: [ADMIN] PITR Setup Using Hot StandBy
Hi,
Basically I do not want to touch my Master while performing PITR.
So here what I am trying to achieve now in my development environment.
1. Take base backup from slave
2. Do WAL archiving from master
3. Try to up postgresql using base backup from slave and wal archives from master.
I don’t know, if this will work or not.
But my final goal is to setup entire PITR process using base backup from slave.
Thanks & Regards,
Vicky Soni
Database Administrator
From: Scott Whitney [mailto:scott@journyx.com]
Sent: 24 May 2016 21:18
To: Dave Johansen <davejohansen@gmail.com<mailto:davejohansen@gmail.com>>; Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>>
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
Hi, Dave.
It depends on what you're trying to do.
If you want a slave up in real-time replication mode, that's by far the simplest setup.
The concept is:
1) start a backup
select pg_start_backup("myslave"),true);
2) Rsync (or tar) your pg data directory over to your slave.
3) select pg_stop_backup();
4) Tell your slave how to connect to the master
Start your slave.
This is a pretty straight-forward link for this:
https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/
[https://opensourcedbms.com/dbms/wp-content/uploads/2013/02/Image-0016.png]<https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/;
How to do point in time recovery with PostgreSQL 9.2 ...<https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/;
opensourcedbms.com
This post describes how to do point in time recovery with PostgreSQL 9.2 : PITR. Point in time recovery (PITR) simply means restoring data upto certain point in time.
If you want to do something "clever," like offset how far behind the slave is, that's more complex.
________________________________
From: pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org> <pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org>> on behalf of Dave Johansen <davejohansen@gmail.com<mailto:davejohansen@gmail.com>>
Sent: Tuesday, May 24, 2016 10:31 AM
To: Vicky Soni - Quipment India
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
On Tue, May 24, 2016 at 6:05 AM, Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>> wrote:
[Quipment Logo]
Hello Friends,
I need to setup PITR. Using master, I am able to do wal archiving.
But using slave I am not able to archive WAL files.
Can anyone please guide me, how to setup PITR using Hot StandBy?
I have setup LogShpping + Streaming Replication in my dev environment, now using this slave I want to setup PITR.
I have already setup following parameters:
wal_level = hot_standby
archive_mode = on
archive_command = 'copy "c:\\Program Files\\PostgreSQL\\9.4\\data\\%p" "\\\\192.168.170.221\\Primary_WAL_Archive\\%f<file:///\\192.168.170.221\Primary_WAL_Archive\%25f>"'
max_wal_senders = 3
wal_keep_segments = 8
hot_standby = on
Any help is appreciated.
http://www.postgresql.org/docs/9.2/static/continuous-archiving.html
The standby/slave is read only and does not generate WAL files (it only consumes them to stay in sync with the master).
Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com<http://www.journyx.com;
p 512.834.8888
f 512-834-8858
Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/
Attachments:
image001.jpg (image/jpeg, 23.8K) download
view image
* Re: PITR Setup Using Hot StandBy
2016-05-24 13:05 PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 15:31 ` Re: PITR Setup Using Hot StandBy Dave Johansen <davejohansen@gmail.com>
2016-05-24 15:48 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
2016-05-24 17:21 ` Re: PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 17:25 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
2016-05-24 17:32 ` Re: PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 17:38 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
@ 2016-05-25 07:51 ` Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-25 16:01 ` Re: PITR Setup Using Hot StandBy bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Vicky Soni - Quipment India @ 2016-05-25 07:51 UTC (permalink / raw)
To: Scott Whitney <scott@journyx.com>; Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-admin
Hi,
I have tried on my dev environment.
1. Took base backup from hot standby
2. WAL Archive from Master
3. Configure recovery.conf and start new instance in standby mode.
It worked.
I just wanted it to be verified by some senior community member that, Is it ok strategy for PITR or not? Considering I want to take base backup from Read Only Replica.
Appreciate all replies.
Thanks & Regards,
Vicky Soni
Database Administrator
From: Scott Whitney [mailto:scott@journyx.com]
Sent: 24 May 2016 23:08
To: Vicky Soni - Quipment India <vicky.soni@quipment.nl>; Dave Johansen <davejohansen@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
Yeah. I was basically in the same boat. That first base backup is a bit of a challenge.
What I did was take a physical USB drive, pg_start_backup(), rsync over to the USB drive, bring it back to the office, rsync it to the server I wanted to use THEN pg_stop_backup(). It worked fine for me.
Even when I have had to restart replication on the slave due to some cleverness, I can still rsync the base from production, and it copies only changed files. It takes me an hour or so, but nothing like the initial sync.
________________________________
From: Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>>
Sent: Tuesday, May 24, 2016 12:32 PM
To: Scott Whitney; Dave Johansen
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: RE: [ADMIN] PITR Setup Using Hot StandBy
Hi Scott/Dave,
Thanks for prompt response.
I want to establish automated process of PITR(Backup and Recovery).
1. Weekly basebackup
2. WAL Archiving
Now in my production environment database size is 300 GB, so my System Admin asked me to check if it is possible to establish PITR using slave or not.
As copying 300GB would consume production server's resources and will make some process slower.
So if we can set base backup and other processes using read-only replica, which is rarely used in our application architecture then it would be win-win for DBA and SysAdmin.
Thanks & Regards,
Vicky Soni
Database Administrator
From: Scott Whitney [mailto:scott@journyx.com]
Sent: 24 May 2016 22:56
To: Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>>; Dave Johansen <davejohansen@gmail.com<mailto:davejohansen@gmail.com>>
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
I'm still not sure I fully understand what it is you're trying to accomplish.
Using hot-standby streaming replication, you can have a slave up to date with the master and available for promotion as well as select-only queries.
If you're trying to setup multiple slaves, this is also possible.
I have a production server (call it db-prod). I have a training/demo server (also at my data center -- call it db-back). I have a 3rd server at my HQ (and a 4th and 5th in my DR data center).
DB-prod runs my paying customers
DB-back runs my test/training sites (different port) and replicates DB-prod
DB-HQ runs my internal stuff (standard port) and replicates DB-back. It replicates BOTH DB-back AND DB-prod FROM DB-back. It does not touch the actual production server
DB-DR replicates DB-prod
DB-DR-back replicates DB-back (both clusters)
I accomplish this using streaming replication in PG 9.x.
________________________________
From: Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>>
Sent: Tuesday, May 24, 2016 12:21 PM
To: Scott Whitney; Dave Johansen
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: RE: [ADMIN] PITR Setup Using Hot StandBy
Hi,
Basically I do not want to touch my Master while performing PITR.
So here what I am trying to achieve now in my development environment.
1. Take base backup from slave
2. Do WAL archiving from master
3. Try to up postgresql using base backup from slave and wal archives from master.
I don't know, if this will work or not.
But my final goal is to setup entire PITR process using base backup from slave.
Thanks & Regards,
Vicky Soni
Database Administrator
From: Scott Whitney [mailto:scott@journyx.com]
Sent: 24 May 2016 21:18
To: Dave Johansen <davejohansen@gmail.com<mailto:davejohansen@gmail.com>>; Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>>
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
Hi, Dave.
It depends on what you're trying to do.
If you want a slave up in real-time replication mode, that's by far the simplest setup.
The concept is:
1) start a backup
select pg_start_backup("myslave"),true);
2) Rsync (or tar) your pg data directory over to your slave.
3) select pg_stop_backup();
4) Tell your slave how to connect to the master
Start your slave.
This is a pretty straight-forward link for this:
https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/
[https://opensourcedbms.com/dbms/wp-content/uploads/2013/02/Image-0016.png]<https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/;
How to do point in time recovery with PostgreSQL 9.2 ...<https://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/;
opensourcedbms.com
This post describes how to do point in time recovery with PostgreSQL 9.2 : PITR. Point in time recovery (PITR) simply means restoring data upto certain point in time.
If you want to do something "clever," like offset how far behind the slave is, that's more complex.
________________________________
From: pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org> <pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org>> on behalf of Dave Johansen <davejohansen@gmail.com<mailto:davejohansen@gmail.com>>
Sent: Tuesday, May 24, 2016 10:31 AM
To: Vicky Soni - Quipment India
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] PITR Setup Using Hot StandBy
On Tue, May 24, 2016 at 6:05 AM, Vicky Soni - Quipment India <vicky.soni@quipment.nl<mailto:vicky.soni@quipment.nl>> wrote:
[Quipment Logo]
Hello Friends,
I need to setup PITR. Using master, I am able to do wal archiving.
But using slave I am not able to archive WAL files.
Can anyone please guide me, how to setup PITR using Hot StandBy?
I have setup LogShpping + Streaming Replication in my dev environment, now using this slave I want to setup PITR.
I have already setup following parameters:
wal_level = hot_standby
archive_mode = on
archive_command = 'copy "c:\\Program Files\\PostgreSQL\\9.4\\data\\%p" "\\\\192.168.170.221\\Primary_WAL_Archive\\%f<file:///\\192.168.170.221\Primary_WAL_Archive\%25f>"'
max_wal_senders = 3
wal_keep_segments = 8
hot_standby = on
Any help is appreciated.
http://www.postgresql.org/docs/9.2/static/continuous-archiving.html
The standby/slave is read only and does not generate WAL files (it only consumes them to stay in sync with the master).
Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com<http://www.journyx.com;
p 512.834.8888
f 512-834-8858
Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/
Attachments:
image001.jpg (image/jpeg, 23.8K) download
view image
* Re: PITR Setup Using Hot StandBy
2016-05-24 13:05 PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 15:31 ` Re: PITR Setup Using Hot StandBy Dave Johansen <davejohansen@gmail.com>
2016-05-24 15:48 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
2016-05-24 17:21 ` Re: PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 17:25 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
2016-05-24 17:32 ` Re: PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 17:38 ` Re: PITR Setup Using Hot StandBy Scott Whitney <scott@journyx.com>
2016-05-25 07:51 ` Re: PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
@ 2016-05-25 16:01 ` bricklen <bricklen@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: bricklen @ 2016-05-25 16:01 UTC (permalink / raw)
To: Vicky Soni - Quipment India <vicky.soni@quipment.nl>; +Cc: pgsql-admin
On Wed, May 25, 2016 at 12:51 AM, Vicky Soni - Quipment India <
vicky.soni@quipment.nl> wrote:
> 1. Took base backup from hot standby
> 2. WAL Archive from Master
> 3. Configure recovery.conf and start new instance in standby mode.
> It worked.
> I just wanted it to be verified by some senior community member that, Is
it ok strategy for PITR or not? Considering I want to take base backup from
Read Only Replica.
Assuming your master is shipping WALs to the to-be-created replica, taking
a base backup from an existing replica is perfectly fine.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2016-05-25 16:01 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-05-24 13:05 PITR Setup Using Hot StandBy Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 15:31 ` Dave Johansen <davejohansen@gmail.com>
2016-05-24 15:48 ` Scott Whitney <scott@journyx.com>
2016-05-24 17:21 ` Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 17:25 ` Scott Whitney <scott@journyx.com>
2016-05-24 17:32 ` Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-24 17:38 ` Scott Whitney <scott@journyx.com>
2016-05-25 07:51 ` Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-25 16:01 ` bricklen <bricklen@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