PITR and warm standby setup questions

On Nov 12, 2007 6:59 PM, Mason Hale <masonhale@gmail.com> wrote:

I am setting up a warm standby configuration as described here:

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

Using PostgreSql 8.2.5

My production server is archiving 16MB wal segment files at a rate of 1
every 5 to 10 seconds
My standby server is processing the wal segment files at a rate of 1 every
10 to 40 seconds

At this rate the standby will never keep up with the production server.

The production server has a 10 disk RAID 1+0 configuration and 32GB RAM
The standby server has a 4 disk RAID 1+0 configuration and 16GB RAM, with an
extra disk to hold the wal archive files (separate from the RAID)
otherwise they are identically configured

your i/o must be really random to be seeing numbers that lousy (10
seconds to replay a file is 1.6 megabytes/sec), or there is some other
unexplained problem with your server. is your raid controller
properly caching wites? have you benchmarked the volume with bonnie++
or similar tool (pay close attention to seeks).

merlin

your i/o must be really random to be seeing numbers that lousy (10
seconds to replay a file is 1.6 megabytes/sec), or there is some other
unexplained problem with your server. is your raid controller
properly caching wites? have you benchmarked the volume with bonnie++
or similar tool (pay close attention to seeks).

Here's the bonnie++ output (two runs):

Version  1.03       ------Sequential Output------ --Sequential Input-
--Random-
                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
--Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec
%CP
dev-db-2        32G 43174  99 87421  24 45614  12 48302  97 164574  23 205.3
0
                    ------Sequential Create------ --------Random
Create--------
                    -Create-- --Read--- -Delete-- -Create-- --Read---
-Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec
%CP
                 16 +++++ +++ +++++ +++ +++++ +++ +++++ +++ +++++ +++ +++++
+++

Having never used bonnie++ before, I don't have a baseline to compare this
against, but that looks like 87MB/s writes and 164MB/s reads to me. Am I
reading this correctly? It looks pretty good to me.

Here is some output from iostat

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 0.00 0.00 0.00 0 0
sdb 1.00 0.00 55.72 0 112
sdc 1.00 0.00 63.68 0 128
sdd 101.49 1699.50 0.00 3416 0

avg-cpu: %user %nice %system %iowait %steal %idle
0.06 0.00 0.06 12.37 0.00 87.51

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 2.50 0.00 24.00 0 48
sdb 0.00 0.00 0.00 0 0
sdc 42.50 0.00 8288.00 0 16576
sdd 101.50 1688.00 0.00 3376 0

avg-cpu: %user %nice %system %iowait %steal %idle
0.12 0.00 0.06 12.35 0.00 87.46

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 0.00 0.00 0.00 0 0
sdb 0.00 0.00 0.00 0 0
sdc 0.00 0.00 0.00 0 0
sdd 112.44 1787.06 0.00 3592 0

avg-cpu: %user %nice %system %iowait %steal %idle
0.12 0.00 0.06 12.36 0.00 87.45

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 0.00 0.00 0.00 0 0
sdb 4.50 0.00 48.00 0 96
sdc 0.50 0.00 4.00 0 8
sdd 97.50 1752.00 0.00 3504 0

In the above: sdb holds the pg_xlog directory, sdc holds the wal archive,
and sdd is the 4 disk RAID 1+0 where the pgdata directory is stored. All
these disks are ext3 with noatime,data=writeback mount options. The RAID
controller is an Adaptec 3805 with 128MB battery backed cache (only option
offered by our hosting provider for this server class).

Does any of this shed any light on how to boost my restore performance?

thanks,
Mason

On Mon, 12 Nov 2007, Mason Hale wrote:

After the wal segment file is copied by the restore_command script, is
it safe to delete it from my archive?

While I believe you can toss them immediately, you should considering
keeping those around for a bit regardless as an additional layer of
disaster recovery resources. I try to avoid deleting them until a new
base backup is made, because if you have the last backup and all the
archived segments it gives you another potential way to rebuild the
database in case of a large disaster damages both the primary and the
secondary. You can never have too many ways to try and recover from such
a situation.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Nov 12, 2007 11:03 PM, Mason Hale <masonhale@gmail.com> wrote:

your i/o must be really random to be seeing numbers that lousy (10
seconds to replay a file is 1.6 megabytes/sec), or there is some other
unexplained problem with your server. is your raid controller
properly caching wites? have you benchmarked the volume with bonnie++
or similar tool (pay close attention to seeks).

Here's the bonnie++ output (two runs):
Version 1.03 ------Sequential Output------ --Sequential Input-
--Random-
-Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
--Seeks--
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec
%CP
dev-db-2 32G 43174 99 87421 24 45614 12 48302 97 164574 23 205.3

your seeks are low, especially if these are 15k drives. The
sequential numbers are ok but not very important in the scheme of
things. Interestingly, your 'sdd' device is not doing any writing in
the iostat samples you sent...is that a typical sample? how long are
your iostat intervals? your iowait numbers are also remarkably
stable. did you iostat the device when doing bonnie? (an iostat
during wal replay is much more interesting)

here are some random suggestions:

* play with partial wal writes setting and see if that helps
* double check raid controller is configured for writeback (it should,
with a bbu)
* experiment with xfs on data volume which may help compensate for
lousy seeking hardware
* try and describe with a little more detail your workload on the primary

merlin

On Tuesday 13 November 2007 00:07, Greg Smith wrote:

On Mon, 12 Nov 2007, Mason Hale wrote:

After the wal segment file is copied by the restore_command script, is
it safe to delete it from my archive?

While I believe you can toss them immediately, you should considering
keeping those around for a bit regardless as an additional layer of
disaster recovery resources. I try to avoid deleting them until a new
base backup is made, because if you have the last backup and all the
archived segments it gives you another potential way to rebuild the
database in case of a large disaster damages both the primary and the
secondary. You can never have too many ways to try and recover from such
a situation.

Actually I'd more strongly recommend you keep around the last 2 segments you
have processed. Coming out of replay mode (for example, during a failover
scenario) the server often has a desire to reread the last file you
processed, and if you dont have it complains.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On Nov 12, 2007, at 11:07 PM, Greg Smith wrote:

On Mon, 12 Nov 2007, Mason Hale wrote:

After the wal segment file is copied by the restore_command
script, is it safe to delete it from my archive?

While I believe you can toss them immediately, you should
considering keeping those around for a bit regardless as an
additional layer of disaster recovery resources. I try to avoid
deleting them until a new base backup is made, because if you have
the last backup and all the archived segments it gives you another
potential way to rebuild the database in case of a large disaster
damages both the primary and the secondary. You can never have too
many ways to try and recover from such a situation.

Plus, the new resumable recovery probably won't be happy if you're
too aggressive about nuking WAL logs from the archive.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

On Tue, 2007-11-13 at 00:07 -0500, Greg Smith wrote:

On Mon, 12 Nov 2007, Mason Hale wrote:

After the wal segment file is copied by the restore_command script, is
it safe to delete it from my archive?

While I believe you can toss them immediately,

This is almost never possible. The last WAL file that must be kept
should be sufficient to allow recovery to restart from the last
restartpoint. So a variable number of WAL files needs to be kept, not 1,
not 2 and certainly never 0.

pg_standby with 8.2 provides a -k option to allow keeping last N files,
whereas 8.3 passes the %r parameter to show the filename of the last
file that must be kept.

you should considering
keeping those around for a bit regardless as an additional layer of
disaster recovery resources. I try to avoid deleting them until a new
base backup is made, because if you have the last backup and all the
archived segments it gives you another potential way to rebuild the
database in case of a large disaster damages both the primary and the
secondary. You can never have too many ways to try and recover from such
a situation.

Agreed

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

I am on 8.2 production and it will be difficult to upgrade to 8.3. Is
it possible to backport the "%r" fix from 8.3 to 8.2?

Regards
Dhaval

On Nov 13, 2007 11:26 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Tue, 2007-11-13 at 00:07 -0500, Greg Smith wrote:

On Mon, 12 Nov 2007, Mason Hale wrote:

After the wal segment file is copied by the restore_command script, is
it safe to delete it from my archive?

While I believe you can toss them immediately,

This is almost never possible. The last WAL file that must be kept
should be sufficient to allow recovery to restart from the last
restartpoint. So a variable number of WAL files needs to be kept, not 1,
not 2 and certainly never 0.

pg_standby with 8.2 provides a -k option to allow keeping last N files,
whereas 8.3 passes the %r parameter to show the filename of the last
file that must be kept.

you should considering
keeping those around for a bit regardless as an additional layer of
disaster recovery resources. I try to avoid deleting them until a new
base backup is made, because if you have the last backup and all the
archived segments it gives you another potential way to rebuild the
database in case of a large disaster damages both the primary and the
secondary. You can never have too many ways to try and recover from such
a situation.

Agreed

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Dhaval Shah

Dhaval Shah wrote:

I am on 8.2 production and it will be difficult to upgrade to 8.3. Is
it possible to backport the "%r" fix from 8.3 to 8.2?

You need to troll through the CVS archives to find that patch and try to
apply it to 8.2. This feature will not be backpatched because we don't
backpatch features to previous branches.

---------------------------------------------------------------------------

Regards
Dhaval

On Nov 13, 2007 11:26 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Tue, 2007-11-13 at 00:07 -0500, Greg Smith wrote:

On Mon, 12 Nov 2007, Mason Hale wrote:

After the wal segment file is copied by the restore_command script, is
it safe to delete it from my archive?

While I believe you can toss them immediately,

This is almost never possible. The last WAL file that must be kept
should be sufficient to allow recovery to restart from the last
restartpoint. So a variable number of WAL files needs to be kept, not 1,
not 2 and certainly never 0.

pg_standby with 8.2 provides a -k option to allow keeping last N files,
whereas 8.3 passes the %r parameter to show the filename of the last
file that must be kept.

you should considering
keeping those around for a bit regardless as an additional layer of
disaster recovery resources. I try to avoid deleting them until a new
base backup is made, because if you have the last backup and all the
archived segments it gives you another potential way to rebuild the
database in case of a large disaster damages both the primary and the
secondary. You can never have too many ways to try and recover from such
a situation.

Agreed

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Dhaval Shah

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Quick question, are there any native functions in PostGreSQL 8.1.4 that will
strip HTML tags, escape chars, etc?

thanx:)

On Nov 14, 2007 2:40 PM, madhtr <madhtr@schif.org> wrote:

Quick question, are there any native functions in PostGreSQL 8.1.4 that will
strip HTML tags, escape chars, etc?

I can't think of a lot of native functions, but it's sure easy enough
to roll your own with things like the regex functionality built in.

On Nov 14, 2007, at 4:23 PM, Scott Marlowe wrote:

On Nov 14, 2007 2:40 PM, madhtr <madhtr@schif.org> wrote:

Quick question, are there any native functions in PostGreSQL 8.1.4
that will
strip HTML tags, escape chars, etc?

I can't think of a lot of native functions, but it's sure easy enough
to roll your own with things like the regex functionality built in.

Please don't do that- there are corner cases where a naive regex can
fail, leaving the programmer thinking he is covered when he is not.
The variety of web languages include filtering modules
(HTML::Scrubber)- in the case of Perl or PHP, it can even be run
server-side.

Furthermore, one shouldn't use an API which allows for SQL injections.

Cheers,
M

On Nov 14, 2007 4:51 PM, A.M. <agentm@themactionfaction.com> wrote:

On Nov 14, 2007, at 4:23 PM, Scott Marlowe wrote:

On Nov 14, 2007 2:40 PM, madhtr <madhtr@schif.org> wrote:

Quick question, are there any native functions in PostGreSQL 8.1.4
that will
strip HTML tags, escape chars, etc?

I can't think of a lot of native functions, but it's sure easy enough
to roll your own with things like the regex functionality built in.

Please don't do that- there are corner cases where a naive regex can
fail, leaving the programmer thinking he is covered when he is not.
The variety of web languages include filtering modules
(HTML::Scrubber)- in the case of Perl or PHP, it can even be run
server-side.

And given that pl/PHP can run that inside the database, there's a
reason you can't do it there?

Furthermore, one shouldn't use an API which allows for SQL injections.

Oh heck, I hadn't even noticed he was asking about escaping things. I
guess it really matters what he means by escaping them. If he's
talking url encoding decoding, that's something you could do safely in
the db (again, with something like pl/PHP or pl/perl) but SQL escaping
should be done before the db ever sees the data.

No problem.

One more question, is there a way to find out, without going through a
test install, and from release notes etc. for 8.3 if the database
needs migration from 8.2 to 8.3 or not.

Regards
Dhaval

On Nov 14, 2007 10:44 AM, Bruce Momjian <bruce@momjian.us> wrote:

Dhaval Shah wrote:

I am on 8.2 production and it will be difficult to upgrade to 8.3. Is
it possible to backport the "%r" fix from 8.3 to 8.2?

You need to troll through the CVS archives to find that patch and try to
apply it to 8.2. This feature will not be backpatched because we don't
backpatch features to previous branches.

---------------------------------------------------------------------------

Regards
Dhaval

On Nov 13, 2007 11:26 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Tue, 2007-11-13 at 00:07 -0500, Greg Smith wrote:

On Mon, 12 Nov 2007, Mason Hale wrote:

After the wal segment file is copied by the restore_command script, is
it safe to delete it from my archive?

While I believe you can toss them immediately,

This is almost never possible. The last WAL file that must be kept
should be sufficient to allow recovery to restart from the last
restartpoint. So a variable number of WAL files needs to be kept, not 1,
not 2 and certainly never 0.

pg_standby with 8.2 provides a -k option to allow keeping last N files,
whereas 8.3 passes the %r parameter to show the filename of the last
file that must be kept.

you should considering
keeping those around for a bit regardless as an additional layer of
disaster recovery resources. I try to avoid deleting them until a new
base backup is made, because if you have the last backup and all the
archived segments it gives you another potential way to rebuild the
database in case of a large disaster damages both the primary and the
secondary. You can never have too many ways to try and recover from such
a situation.

Agreed

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Dhaval Shah

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

--
Dhaval Shah

Dhaval Shah wrote:

No problem.

One more question, is there a way to find out, without going through a
test install, and from release notes etc. for 8.3 if the database
needs migration from 8.2 to 8.3 or not.

What is migration? Application changes? The release notes pretty much
tell you everything you need.

---------------------------------------------------------------------------

Regards
Dhaval

On Nov 14, 2007 10:44 AM, Bruce Momjian <bruce@momjian.us> wrote:

Dhaval Shah wrote:

I am on 8.2 production and it will be difficult to upgrade to 8.3. Is
it possible to backport the "%r" fix from 8.3 to 8.2?

You need to troll through the CVS archives to find that patch and try to
apply it to 8.2. This feature will not be backpatched because we don't
backpatch features to previous branches.

---------------------------------------------------------------------------

Regards
Dhaval

On Nov 13, 2007 11:26 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Tue, 2007-11-13 at 00:07 -0500, Greg Smith wrote:

On Mon, 12 Nov 2007, Mason Hale wrote:

After the wal segment file is copied by the restore_command script, is
it safe to delete it from my archive?

While I believe you can toss them immediately,

This is almost never possible. The last WAL file that must be kept
should be sufficient to allow recovery to restart from the last
restartpoint. So a variable number of WAL files needs to be kept, not 1,
not 2 and certainly never 0.

pg_standby with 8.2 provides a -k option to allow keeping last N files,
whereas 8.3 passes the %r parameter to show the filename of the last
file that must be kept.

you should considering
keeping those around for a bit regardless as an additional layer of
disaster recovery resources. I try to avoid deleting them until a new
base backup is made, because if you have the last backup and all the
archived segments it gives you another potential way to rebuild the
database in case of a large disaster damages both the primary and the
secondary. You can never have too many ways to try and recover from such
a situation.

Agreed

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Dhaval Shah

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

--
Dhaval Shah

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Nov 14, 2007 5:19 PM, Dhaval Shah <dhaval.shah.m@gmail.com> wrote:

No problem.

One more question, is there a way to find out, without going through a
test install, and from release notes etc. for 8.3 if the database
needs migration from 8.2 to 8.3 or not.

Well, you HAVE to do a dump from one to the other, because major
versions can't read each other's data stores.

Whether or not the upgrade will break your app is a question only you
can answer though.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 14 Nov 2007 18:35:00 -0500 (EST)
Bruce Momjian <bruce@momjian.us> wrote:

Dhaval Shah wrote:

No problem.

One more question, is there a way to find out, without going
through a test install, and from release notes etc. for 8.3 if the
database needs migration from 8.2 to 8.3 or not.

What is migration? Application changes? The release notes pretty
much tell you everything you need.

http://www.postgresql.org/docs/8.3/static/release-8-3.html

Sincerely,

Joshua D. Drake

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHO4kxATb/zqfZUUQRAtRaAJ4t99bQ9e+iPqJ4WbYwY0gtVDeSGgCgmPtO
sW/YuUUicDUTDZy+Hzn4ug8=
=t0dY
-----END PGP SIGNATURE-----

this is a very simple html tag strip routine
I dont understand what security you had in mind ..

so I take it you're not a fan of dojo or GWT?

M--
----- Original Message -----
From: "Ian Barwick" <barwick@gmail.com>
Cc: "Scott Marlowe" <scott.marlowe@gmail.com>; "pgsql-general"
<pgsql-general@postgresql.org>
Sent: Wednesday, November 14, 2007 7:21 PM
Subject: Re: [GENERAL] stripping HTML, SQL injections ...

Martin,

2000/11/15, Martin Gainty <mgainty@hotmail.com>:

Scott-

In JavaScript

http://www.java2s.com/Tutorial/JavaScript/0520__Regular-Expressions/StripHTM

Martin Gainty escribi�:

this is a very simple html tag strip routine
I dont understand what security you had in mind ..

so I take it you're not a fan of dojo or GWT?

Let's say the user disables javascript on the browser?

--
Alvaro Herrera http://www.advogato.org/person/alvherre
"Aprende a avergonzarte m�s ante ti que ante los dem�s" (Dem�crito)