Re: [GENERAL] Prevent new timeline during archive recovery

[Jonatan Evald Buus]

Cheers for the input Simon, please find a couple of thoughts / questions
below

/Jona

On Thu, Dec 18, 2008 at 12:04 AM, Simon Riggs si...@2ndquadrant.com wrote:


 On Wed, 2008-12-17 at 17:54 +0100, Jonatan Evald Buus wrote:
  Greetings,
  We're currently developing an alternative to pg_standby, internally
  named pg_ha.
  pg_ha uses Record Based Log Shipping fetched by the warm standby
  server at millisecond intervals and will automatically restore
  completed WAL files on the standby server to minimize the time
  required when restoring it to production status after a failure on the
  primary server.
  Once completed we'd be happy to donate the code to the PostGreSQL
  community to assist in promoting the database server as a High
  Availability option.

 We're reworking replication now for 8.4 and it sounds like we're already
 some way ahead of you on that development.

We're quite looking forward to this work being completed, a simple
replication option that would transfer changes from a master node to one or
more slave nodes every couple of seconds would go a long way to solve most
HA scenarios.
I suspect we'll abandon our own solution and use the built-in replication
features of 8.4 once it's released.




 Have a look at Skytools' walmgr if you want a stream using 8.3. It uses
 production APIs built into the server in 8.2.

We did have a look at walmgr originally but it didn't seem to meet our needs
as it doesn't appear to be doing automatic restore of completed WAL files on
a continual basis. I wager that replaying a year's worth of WAL files from a
busy server would take quite a while thus decreasing the value of having a
Warm Standby Server.
It does however have a restore option but upon a closer look of that
specific part of the code it doesn't appear to address the automatically
increasing timeline during archive recovery.
Essentially it takes the same approach to restoring WAL archives as we have
opted for:
1) Create recovery.conf file in PGDATA, specifying restore_command = 'cp
[ARCHIVE DIR] /%f %p' (walmgr specifies itself as the restore command but
essentially simply does the copying)
2) Start the Postmaster process using pc_ctl start

The recovery.conf file used by walmgr is as follows:
restore_command = '%s %s %s'\n % (self.script, cf_file, 'xrestore %f %p')
# recovery_target_time=''
# recovery_target_xid=''
# recovery_target_inclusive=true
# recovery_target_timeline=''

In comparison we specify:
restore_command = 'cp [ARCHIVE DIR]/%f %p'
recovery_target_inclusive = 'true'

Unless I'm missing something, none of of these recovery.conf files will
address the incrementing timeline during archive recovery?




  During each restore cycle however we notice that PostGreSQL spawns a
  new timeline for the recovered archive as described in section 24.3.4
  at
 
 http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-BASE-BACKUP
 .
  Is it possible to specify that no new timeline should be spawned
  during the archiver recovery process?
 
  The only workaround otherwise appears to be to detect the current
  timeline number using pg_controldata and rename copied WAL files
  accordingly prior to initiating the archive recovery process.

 That sounds like a bad idea, for minor but annoying technical reasons.
 Please check the archives if you're interested further.

Agreed, it's a pretty appauling approach but it does appear to work.
An alternative would be to call pg_resetxlog after each archive restore
using the -l switch to reset the timeline.
i.e. pg_resetxlog -l 0x01,0x[CURRENT FILE ID],0x[NEXT LOG SEGMENT] [DATA
DIR]
This also seems like a bad idea which could have unforseen consequences due
to the internal cleanups performed.

Doing automatic restore of completed WAL files on the standby server would
greatly increase the timeline id as an archive restore might be executed
every couple of minutes depending on the load on the primary server.
Thus the best approach by far would be an optiong to specifying that the
timeline shouldn't be changed during archive recovery.
I haven't been able to find anything in relation to this topic in the
archives, seems that the timeline feature is discussed very seldom but I
might simply be searching for the wrong keywords.
If you could point me in the right direction I'd greatly appreciate it.




 --
  Simon Riggs   www.2ndQuadrant.com
  PostgreSQL Training, Services and Support

[GENERAL] Prevent new timeline during archive recovery

[Jonatan Evald Buus]

Greetings,
We're currently developing an alternative to pg_standby, internally named
pg_ha.
pg_ha uses Record Based Log Shipping fetched by the warm standby server at
millisecond intervals and will automatically restore completed WAL files on
the standby server to minimize the time required when restoring it to
production status after a failure on the primary server.
Once completed we'd be happy to donate the code to the PostGreSQL community
to assist in promoting the database server as a High Availability option.

During each restore cycle however we notice that PostGreSQL spawns a new
timeline for the recovered archive as described in section 24.3.4 at
http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-BASE-BACKUP
.
Is it possible to specify that no new timeline should be spawned during the
archiver recovery process?

The only workaround otherwise appears to be to detect the current timeline
number using pg_controldata and rename copied WAL files accordingly prior to
initiating the archive recovery process.

Any input would be most appreciated.

Cheers
Jona

[GENERAL] row-level security (Dynamically rewrite queries)

[Jonatan Evald Buus]

Hi there,
Is it possible to dynamically rewrite an SQL query for all operations
(SELECT, INSERT, UPATE, DELETE etc.)?
It seems that using RULES it's possible to replace one query with another,
but can the actual query that was executed by retrieved so it can be
dynamically modified?
I.e. I'd like to create a rule along the lines of:
CREATE RULE txn_vpd ON
SELECT TO Transaction_Tbl
DO INSTEAD replace('WHERE', 'INNER JOIN User_Tbl ON Transaction_Tbl.userid =
User_Tbl.id WHERE User_Tbl.name = \'CURRENT_USER\'', $SQL)
Where $SQL represents the actual SQL query that was executed on the table
and the table has a column: userid which indicates the owner of the row.

Essentially what I'd like to do is implement row-level security (what Oracle
calls Virtual Private Database) but as far as I can find the last time
this was discussed is several years ago and the general consensus ended up
being use veil.
Veil seems overly like an complicated approach for something that (in
theroy) should be possible with a dynamic query rewrite using search and
replace prior to execution.
Oracle's implementation seems quiete elegant for this, please see
http://www.oracle.com/technology/pub/articles/10gdba/week14_10gdba.html or
http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/for
examples.

One other approach that I could think of, would be to create a view for
every table and use the view for accessing the data:
CREATE VIEW Transaction_Vw AS
SELECT * FROM Transaction_Tbl Txn
INNER JOIN User_Tbl U ON Txn.userid = U.id
WHERE U.name = 'CURRENT_USER'
However, can usage this view be enforced by the database by removing SELECT
priviliges from Transaction_Tbl?
Also, I suspect that performance would go down the drain if complex joins
are used? There could potentially be a lot of unnecessary joins to User_Tbl
if multiple secure view where joined together.
Is the planner capable of taking this into account and auto-magically
optimize the query?

Are there any other alternatives to implementing row-level security that can
be enforced at the database level?

Appreciate the input

Cheers
Jonatan

[GENERAL] Fetch query which triggered a rule

[Jonatan Evald Buus]

Greetings,
Is it possible to fetch the query which triggered a rule?

I.e. suppose I create the following rule:
CREATE RULE MyRule AS ON Select TO MyTable_Tbl DO INSTEAD
process_query($sql);

Where $sql represents the query which triggered the rule.

Appreciate the input

Cheers
Jona