Re: streaming replication - pgsql 9.2.3

[Jason Mathis <jmathis@xxxxxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxx) 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@xxxxxxxxxxxx    +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
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.