Thread: streaming replication - pgsql 9.2.3

streaming replication - pgsql 9.2.3

From

Jim Mercer

Date:

14 April 2014, 19:40:02

Hi,

I have set up streaming replication between two servers, using the various
walkthroughs the googlepedia found for me.

At this point, it certainly seems that the replication is working, and I am
able to actually blow away the slave, and have it rebuild, resync and be back
in action as a read-only source.

I am confused about the purpose and/or point of the WAL files in the context
of streaming replication.

The documentation seems to be quite insistent that the 'archive_command' should
be enabled on the master, and the 'restore_command' should be enabled on the
slave.

On the master, I made my own script which is fairly pedantic:
- copy the WAL file from $PGDATA/pg_xlog into $MASTER:$ARCHDIR
- rsync file(s) from $MASTER:$ARCHDIR to $SLAVE:$ARCHDIR
- remove copy of file in $MASTER:$ARCHDIR
(i don't have a network share between then, so, i am rsyncing)

On the slave, I made a similarly pedandic script:
- copy the file from $SLAVE:$ARCHDIR to $PGDATA/pg_xlog/RECOVERYXLOG

The script on the master runs with regularity, and i am seeing the WAL files
show up in $SLAVE:$ARCHDIR

however, on the slave side, i'm seeing some issues:

- the filename being handed to the script is sometimes for a non-existent file
- it appears to be the next file in sequence, but that filename hasn't even
been generated on the master yet.
- it appears, that if there are multiple files in $SLAVE:$ARCHDIR, they might
actually overwrite themselves when copied to pg_xlog/RECOVERYXLOG

these are the logs my script is generating, if i do a stop/start of postgres:

Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=0 - cp [/export/pg-archive/00000001000000220000007F]
/export/postgresql/data/main/[pg_xlog/RECOVERYXLOG]
Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=0 - cp [/export/pg-archive/000000010000002200000080]
/export/postgresql/data/main/[pg_xlog/RECOVERYXLOG]
Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such file[/export/pg-archive/000000010000002200000081]
Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such file[/export/pg-archive/000000010000002200000081]


am i doing something wrong?

please advise.


--
Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

Re: streaming replication - pgsql 9.2.3

From

"Gilberto Castillo"

Date:

14 April 2014, 19:52:02


>
> Hi,
>
> I have set up streaming replication between two servers, using the various
> walkthroughs the googlepedia found for me.

Why not test rpmgr?




Saludos,
Gilberto Castillo
La Habana, Cuba
---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>

Re: streaming replication - pgsql 9.2.3

From

Scott Whitney

Date:

14 April 2014, 19:58:04

My particular opinion on this sometimes causes "a bit of a stir," let us say, so take this as my opinion and my use case.

I have:

1) Production server

2) "Non-production" (demos/training) used to replicate #1

3) Offsite "internal production" used to replicate #1 and #2

So,

#2 replicates #1 (gigabit back end)

#3 replicates #2 _and_ #1 (65Mbps back end)

Each does their own individual pg_dump of each db nightly for DR/backup purposes.

I do not archive the WAL files and I feel it's more trouble than it is worth. Rather, I set:

wal_keep_segments = 500

500 on my production server gives me about 30 hours, give or take.

I know this because I watch how quickly I fill up a 16MB log over the course

of several says.

So...why keep WAL files in a replicated environment?

I only see them as useful for one of 2 reasons.

a) In theory you could do something clever and have your replicated server set up to "be behind"

a certain amount, and you could likely use those WAL files for a PITR at some point in the past

(in between your current replication time and the latest WAL file). Maybe. Don't know. Never tried.

This would be useful if you "lost" your production cluster at some point in between those 2 windows,

and you were fast enough to notice it. Maybe.

b) If your server stops replicating (out of space, can't connect, fell behind due to bandwidth are

the 3 scenarios I have run into more than once), and I get to 502 WAL files required, in order to

get my slave back to good, I then have to take #3 offline, do my pg_startbackup(), rsync, and bring it

back online in order to get replication functioning again. If I _had_ archived those files, in theory it would

eventually catch up rather than missing a current WAL file that is needed for replication.

That's my .02 on the matter. I've been running it this way for about a year, now, and it works great for me.

Hi,

I have set up streaming replication between two servers, using the various
walkthroughs the googlepedia found for me.

At this point, it certainly seems that the replication is working, and I am
able to actually blow away the slave, and have it rebuild, resync and be back
in action as a read-only source.

I am confused about the purpose and/or point of the WAL files in the context
of streaming replication.

The documentation seems to be quite insistent that the 'archive_command' should
be enabled on the master, and the 'restore_command' should be enabled on the
slave.

On the master, I made my own script which is fairly pedantic:
- copy the WAL file from $PGDATA/pg_xlog into $MASTER:$ARCHDIR
- rsync file(s) from $MASTER:$ARCHDIR to $SLAVE:$ARCHDIR
- remove copy of file in $MASTER:$ARCHDIR
(i don't have a network share between then, so, i am rsyncing)

On the slave, I made a similarly pedandic script:
- copy the file from $SLAVE:$ARCHDIR to $PGDATA/pg_xlog/RECOVERYXLOG

The script on the master runs with regularity, and i am seeing the WAL files
show up in $SLAVE:$ARCHDIR

however, on the slave side, i'm seeing some issues:

- the filename being handed to the script is sometimes for a non-existent file
- it appears to be the next file in sequence, but that filename hasn't even
been generated on the master yet.
- it appears, that if there are multiple files in $SLAVE:$ARCHDIR, they might
actually overwrite themselves when copied to pg_xlog/RECOVERYXLOG

these are the logs my script is generating, if i do a stop/start of postgres:

Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=0 - cp [/export/pg-archive/00000001000000220000007F] /export/postgresql/data/main/[pg_xlog/RECOVERYXLOG]
Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=0 - cp [/export/pg-archive/000000010000002200000080] /export/postgresql/data/main/[pg_xlog/RECOVERYXLOG]
Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such file[/export/pg-archive/000000010000002200000081]
Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such file[/export/pg-archive/000000010000002200000081]

am i doing something wrong?

please advise.

--
Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: streaming replication - pgsql 9.2.3

From

Jason Mathis

Date:

15 April 2014, 00:14:39

Just keep in mind that while wal archiving and replication sound very similar they are intended for two different things.

Wal Archiving = backup/DR/PITR/standby

Replication = High availability/reporting

You don’t need to set up archiving for streaming replication, although you may want to; I think scott explains that well. If something bad happens like a mistaken dml or ddl statement, that will get replicated (streaming replication works nice and fast;) and you can not go back to recover, unless you do something tricky with the keep_segments; don’t rely on that. Although, if you are even thinking about doing something tricky with the keep_segments then just setup archiving and forget about it.

With archiving you can go back to ANY point in time. With replication you can have a new server w/data up and ready in seconds or off-load read queries for reporting. Use one, the other, or both!

Hope that explains it? Let us know if not.

-jason

On April 14, 2014 at 10:58:29 AM, Scott Whitney (scott@journyx.com) wrote:

My particular opinion on this sometimes causes "a bit of a stir," let us say, so take this as my opinion and my use case.

I have:

1) Production server

2) "Non-production" (demos/training) used to replicate #1

3) Offsite "internal production" used to replicate #1 and #2

So,

#2 replicates #1 (gigabit back end)

#3 replicates #2 _and_ #1 (65Mbps back end)

Each does their own individual pg_dump of each db nightly for DR/backup purposes.

I do not archive the WAL files and I feel it's more trouble than it is worth. Rather, I set:

wal_keep_segments = 500

500 on my production server gives me about 30 hours, give or take.

I know this because I watch how quickly I fill up a 16MB log over the course

of several says.

So...why keep WAL files in a replicated environment?

I only see them as useful for one of 2 reasons.

a) In theory you could do something clever and have your replicated server set up to "be behind"

a certain amount, and you could likely use those WAL files for a PITR at some point in the past

(in between your current replication time and the latest WAL file). Maybe. Don't know. Never tried.

This would be useful if you "lost" your production cluster at some point in between those 2 windows,

and you were fast enough to notice it. Maybe.

b) If your server stops replicating (out of space, can't connect, fell behind due to bandwidth are

the 3 scenarios I have run into more than once), and I get to 502 WAL files required, in order to

get my slave back to good, I then have to take #3 offline, do my pg_startbackup(), rsync, and bring it

back online in order to get replication functioning again. If I _had_ archived those files, in theory it would

eventually catch up rather than missing a current WAL file that is needed for replication.

That's my .02 on the matter. I've been running it this way for about a year, now, and it works great for me.

Hi,

I have set up streaming replication between two servers, using the various
walkthroughs the googlepedia found for me.

At this point, it certainly seems that the replication is working, and I am
able to actually blow away the slave, and have it rebuild, resync and be back
in action as a read-only source.

I am confused about the purpose and/or point of the WAL files in the context
of streaming replication.

The documentation seems to be quite insistent that the 'archive_command' should
be enabled on the master, and the 'restore_command' should be enabled on the
slave.

On the master, I made my own script which is fairly pedantic:
- copy the WAL file from $PGDATA/pg_xlog into $MASTER:$ARCHDIR
- rsync file(s) from $MASTER:$ARCHDIR to $SLAVE:$ARCHDIR
- remove copy of file in $MASTER:$ARCHDIR
(i don't have a network share between then, so, i am rsyncing)

On the slave, I made a similarly pedandic script:
- copy the file from $SLAVE:$ARCHDIR to $PGDATA/pg_xlog/RECOVERYXLOG

The script on the master runs with regularity, and i am seeing the WAL files
show up in $SLAVE:$ARCHDIR

however, on the slave side, i'm seeing some issues:

- the filename being handed to the script is sometimes for a non-existent file
- it appears to be the next file in sequence, but that filename hasn't even
been generated on the master yet.
- it appears, that if there are multiple files in $SLAVE:$ARCHDIR, they might
actually overwrite themselves when copied to pg_xlog/RECOVERYXLOG

these are the logs my script is generating, if i do a stop/start of postgres:

Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=0 - cp [/export/pg-archive/00000001000000220000007F] /export/postgresql/data/main/[pg_xlog/RECOVERYXLOG]
Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=0 - cp [/export/pg-archive/000000010000002200000080] /export/postgresql/data/main/[pg_xlog/RECOVERYXLOG]
Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such file[/export/pg-archive/000000010000002200000081]
Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such file[/export/pg-archive/000000010000002200000081]

am i doing something wrong?

please advise.

--
Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

This transmission contains confidential and privileged information intended solely for the party identified above. If you receive this message in error, you must not use it or convey it to others. Please destroy it immediately and contact the sender at (303) 386-3955 or by return e-mail to the sender.

Re: streaming replication - pgsql 9.2.3

From

Scott Whitney

Date:

15 April 2014, 00:31:44

Only one clarification here, and, Jason, correct me if I am wrong.

You cannot easily use a steaming replication server for PITR. Ideally you would want a WAL replay server that is set to standby (which would allow you to play forward any number of WALs to a point in time that you specify) for PITR, and a replication server for HA/failover.

In my example, I _could_ lose up to 24hrs data, as my pig _ dumps happen nightly. A "for each in/drop database" malicious (or stupid) statement would replicate in real time to my servers, and not having a standby ready to replay the WALs just prior to that point would require use of my nightly backups.

Had I also a standby server that I could instruct to replay the WALs, I could get to the proper PITR (minus whatever my archive timeout is).

So...like I said. I see no value in WAL archiving for a strictly - replication setup, but that MUST be coupled with a valid DR plan.

-------- Original message --------

From: Jason Mathis

Date:04/14/2014 4:14 PM (GMT-06:00)

To: Jim Mercer ,Scott Whitney ,Scott Whitney

Cc: pgsql-admin@postgresql.org

Subject: Re: [ADMIN] streaming replication - pgsql 9.2.3

Just keep in mind that while wal archiving and replication sound very similar they are intended for two different things.

Wal Archiving = backup/DR/PITR/standby

Replication = High availability/reporting

With archiving you can go back to ANY point in time. With replication you can have a new server w/data up and ready in seconds or off-load read queries for reporting. Use one, the other, or both!

Hope that explains it? Let us know if not.

-jason

On April 14, 2014 at 10:58:29 AM, Scott Whitney (scott@journyx.com) wrote:

My particular opinion on this sometimes causes "a bit of a stir," let us say, so take this as my opinion and my use case.

I have:

1) Production server

2) "Non-production" (demos/training) used to replicate #1

3) Offsite "internal production" used to replicate #1 and #2

So,

#2 replicates #1 (gigabit back end)

#3 replicates #2 _and_ #1 (65Mbps back end)

Each does their own individual pg_dump of each db nightly for DR/backup purposes.

I do not archive the WAL files and I feel it's more trouble than it is worth. Rather, I set:

wal_keep_segments = 500

500 on my production server gives me about 30 hours, give or take.

I know this because I watch how quickly I fill up a 16MB log over the course

of several says.

So...why keep WAL files in a replicated environment?

I only see them as useful for one of 2 reasons.

a) In theory you could do something clever and have your replicated server set up to "be behind"

a certain amount, and you could likely use those WAL files for a PITR at some point in the past

(in between your current replication time and the latest WAL file). Maybe. Don't know. Never tried.

This would be useful if you "lost" your production cluster at some point in between those 2 windows,

and you were fast enough to notice it. Maybe.

b) If your server stops replicating (out of space, can't connect, fell behind due to bandwidth are

the 3 scenarios I have run into more than once), and I get to 502 WAL files required, in order to

get my slave back to good, I then have to take #3 offline, do my pg_startbackup(), rsync, and bring it

back online in order to get replication functioning again. If I _had_ archived those files, in theory it would

eventually catch up rather than missing a current WAL file that is needed for replication.

That's my .02 on the matter. I've been running it this way for about a year, now, and it works great for me.

Hi,

I have set up streaming replication between two servers, using the various
walkthroughs the googlepedia found for me.

At this point, it certainly seems that the replication is working, and I am
able to actually blow away the slave, and have it rebuild, resync and be back
in action as a read-only source.

I am confused about the purpose and/or point of the WAL files in the context
of streaming replication.

The documentation seems to be quite insistent that the 'archive_command' should
be enabled on the master, and the 'restore_command' should be enabled on the
slave.

On the master, I made my own script which is fairly pedantic:
- copy the WAL file from $PGDATA/pg_xlog into $MASTER:$ARCHDIR
- rsync file(s) from $MASTER:$ARCHDIR to $SLAVE:$ARCHDIR
- remove copy of file in $MASTER:$ARCHDIR
(i don't have a network share between then, so, i am rsyncing)

On the slave, I made a similarly pedandic script:
- copy the file from $SLAVE:$ARCHDIR to $PGDATA/pg_xlog/RECOVERYXLOG

The script on the master runs with regularity, and i am seeing the WAL files
show up in $SLAVE:$ARCHDIR

however, on the slave side, i'm seeing some issues:

- the filename being handed to the script is sometimes for a non-existent file
- it appears to be the next file in sequence, but that filename hasn't even
been generated on the master yet.
- it appears, that if there are multiple files in $SLAVE:$ARCHDIR, they might
actually overwrite themselves when copied to pg_xlog/RECOVERYXLOG

these are the logs my script is generating, if i do a stop/start of postgres:

Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=0 - cp [/export/pg-archive/00000001000000220000007F] /export/postgresql/data/main/[pg_xlog/RECOVERYXLOG]
Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=0 - cp [/export/pg-archive/000000010000002200000080] /export/postgresql/data/main/[pg_xlog/RECOVERYXLOG]
Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such file[/export/pg-archive/000000010000002200000081]
Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such file[/export/pg-archive/000000010000002200000081]

am i doing something wrong?

please advise.

--
Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: streaming replication - pgsql 9.2.3

From

Jim Mercer

Date:

15 April 2014, 00:51:50

On Mon, Apr 14, 2014 at 03:14:46PM -0600, Jason Mathis wrote:
> Just keep in mind that while wal archiving and replication sound very similar they are intended for two different
things.??
>
> Wal Archiving = backup/DR/PITR/standby
> Replication = High availability/reporting??

it would be nice if the documentation reflected this.

the current information i derived, basically says to do both.

i'm looking to revise my settings to:
- nullify/NOP the archive_command and restore_command
- use the slave to do a pg_dumpall as the backup

the standby node serves as a read-only slave, which allows for some
performance gains.

the standby node could be promoted to master, should the master have issues.

the backup would allow for a recovery from a catestrophic/cascading error
(ie. drop all tables).

does this make sense?

--
Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

Re: streaming replication - pgsql 9.2.3

From

Jason Mathis

Date:

15 April 2014, 00:57:03

Yes, you cannot use streaming replication for PITR. Although you don’t necessarily need a standby server up all the time to do a PITR and in fact may defeat the purpose. For example if that bad thing happens, depending on the time when you found out it could have been applied to the standby server. Then you are stuck going back to just doing the PITR again. While there are some other uses this is the splitting moment of DR/HA.

As long as you have a base backup(not a dump!) and all the wal files stored somewhere (NAS/S3/another server) you can go to any point in time. It may take some time to get there, but you can do it.

If you can lose 24 hours of data and take nightly dumps, then you are go to good and probably do not need archiving. Although if you can’t lose more than x minutes/hours/seconds of incoming data then you probably need archiving.

hope that clears it up.

On April 14, 2014 at 3:31:39 PM, Scott Whitney (scott@journyx.com) wrote:

Only one clarification here, and, Jason, correct me if I am wrong.

Had I also a standby server that I could instruct to replay the WALs, I could get to the proper PITR (minus whatever my archive timeout is).

So...like I said. I see no value in WAL archiving for a strictly - replication setup, but that MUST be coupled with a valid DR plan.

-------- Original message --------

From: Jason Mathis

Date:04/14/2014 4:14 PM (GMT-06:00)

To: Jim Mercer ,Scott Whitney ,Scott Whitney

Cc: pgsql-admin@postgresql.org

Subject: Re: [ADMIN] streaming replication - pgsql 9.2.3

Just keep in mind that while wal archiving and replication sound very similar they are intended for two different things.

Wal Archiving = backup/DR/PITR/standby

Replication = High availability/reporting

With archiving you can go back to ANY point in time. With replication you can have a new server w/data up and ready in seconds or off-load read queries for reporting. Use one, the other, or both!

Hope that explains it? Let us know if not.

-jason

On April 14, 2014 at 10:58:29 AM, Scott Whitney (scott@journyx.com) wrote:

My particular opinion on this sometimes causes "a bit of a stir," let us say, so take this as my opinion and my use case.

I have:
1) Production server
2) "Non-production" (demos/training) used to replicate #1
3) Offsite "internal production" used to replicate #1 and #2

So,
#2 replicates #1 (gigabit back end)
#3 replicates #2 _and_ #1 (65Mbps back end)

Each does their own individual pg_dump of each db nightly for DR/backup purposes.

I do not archive the WAL files and I feel it's more trouble than it is worth. Rather, I set:

wal_keep_segments = 500

500 on my production server gives me about 30 hours, give or take.

I know this because I watch how quickly I fill up a 16MB log over the course
of several says.

So...why keep WAL files in a replicated environment?

I only see them as useful for one of 2 reasons.

a) In theory you could do something clever and have your replicated server set up to "be behind"
a certain amount, and you could likely use those WAL files for a PITR at some point in the past
(in between your current replication time and the latest WAL file). Maybe. Don't know. Never tried.
This would be useful if you "lost" your production cluster at some point in between those 2 windows,
and you were fast enough to notice it. Maybe.

b) If your server stops replicating (out of space, can't connect, fell behind due to bandwidth are
the 3 scenarios I have run into more than once), and I get to 502 WAL files required, in order to
get my slave back to good, I then have to take #3 offline, do my pg_startbackup(), rsync, and bring it
back online in order to get replication functioning again. If I _had_ archived those files, in theory it would
eventually catch up rather than missing a current WAL file that is needed for replication.

That's my .02 on the matter. I've been running it this way for about a year, now, and it works great for me.

Hi,

I have set up streaming replication between two servers, using the various
walkthroughs the googlepedia found for me.

At this point, it certainly seems that the replication is working, and I am
able to actually blow away the slave, and have it rebuild, resync and be back
in action as a read-only source.

I am confused about the purpose and/or point of the WAL files in the context
of streaming replication.

The documentation seems to be quite insistent that the 'archive_command' should
be enabled on the master, and the 'restore_command' should be enabled on the
slave.

On the master, I made my own script which is fairly pedantic:
- copy the WAL file from $PGDATA/pg_xlog into $MASTER:$ARCHDIR
- rsync file(s) from $MASTER:$ARCHDIR to $SLAVE:$ARCHDIR
- remove copy of file in $MASTER:$ARCHDIR
(i don't have a network share between then, so, i am rsyncing)

On the slave, I made a similarly pedandic script:
- copy the file from $SLAVE:$ARCHDIR to $PGDATA/pg_xlog/RECOVERYXLOG

The script on the master runs with regularity, and i am seeing the WAL files
show up in $SLAVE:$ARCHDIR

however, on the slave side, i'm seeing some issues:

- the filename being handed to the script is sometimes for a non-existent file
- it appears to be the next file in sequence, but that filename hasn't even
been generated on the master yet.
- it appears, that if there are multiple files in $SLAVE:$ARCHDIR, they might
actually overwrite themselves when copied to pg_xlog/RECOVERYXLOG

these are the logs my script is generating, if i do a stop/start of postgres:

Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=0 - cp [/export/pg-archive/00000001000000220000007F] /export/postgresql/data/main/[pg_xlog/RECOVERYXLOG]
Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=0 - cp [/export/pg-archive/000000010000002200000080] /export/postgresql/data/main/[pg_xlog/RECOVERYXLOG]
Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such file[/export/pg-archive/000000010000002200000081]
Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such file[/export/pg-archive/000000010000002200000081]

am i doing something wrong?

please advise.

--
Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: streaming replication - pgsql 9.2.3

From

Jason Mathis

Date:

15 April 2014, 01:19:11

Yep, as long as you are ok with losing the data in between the “dump all” then it sounds good.

For example:

9pm = pg_dumpall

during the day you modified and added a bunch of data.

Then the next day 8pm = you accidentally drop the database.

Your recovery is last night’s backup and everything that happened during the day is gone.

Also you may want to check out the keep_segments.

http://www.postgresql.org/docs/9.2/static/warm-standby.html

"If you use streaming replication without file-based continuous archiving, you have to set wal_keep_segments in the master to a value high enough to ensure that old WAL segments are not recycled too early, while the standby might still need them to catch up. If the standby falls behind too much, it needs to be reinitialized from a new base backup. If you set up a WAL archive that's accessible from the standby, wal_keep_segments is not required as the standby can always use the archive to catch up."

-jason

On April 14, 2014 at 3:51:50 PM, Jim Mercer (jim@reptiles.org) wrote:

On Mon, Apr 14, 2014 at 03:14:46PM -0600, Jason Mathis wrote:
> Just keep in mind that while wal archiving and replication sound very similar they are intended for two different things.??
>
> Wal Archiving = backup/DR/PITR/standby
> Replication = High availability/reporting??

it would be nice if the documentation reflected this.

the current information i derived, basically says to do both.

i'm looking to revise my settings to:
- nullify/NOP the archive_command and restore_command
- use the slave to do a pg_dumpall as the backup

the standby node serves as a read-only slave, which allows for some
performance gains.

the standby node could be promoted to master, should the master have issues.

the backup would allow for a recovery from a catestrophic/cascading error
(ie. drop all tables).

does this make sense?

--
Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633
"He who dies with the most toys is nonetheless dead"