Re: [GENERAL] Question about switchover with PG9 replication

[Wouter D'Haeseleer]

Question 1 : is it possible to have such a replication configuration
with the streaming replication of PG9 (cascaded replication) ?


Nope, as far as I have tested pg only has 1 master and can have
a number of slaves, so having 2 masters is not possible.


Question 2 : All the procedures I have seen describing a switchover
between 2 PG servers require to copy (or rsync) the database from the
new master (old slave) to the new slave (old master).
Is it possible to do switchover between sites (between PGMaster1 and
PGMaster2) whithout copying all the database from the new PG master to
the new PG slave ?
If it is not possible yet, shall it be possible in future releases ?


Nope this is not possible, pg requires to have an updates
basebackup at the slave, this is because if you loose a lot of
streamed wal files it is impossible for the slave to catch up
and have consistent data.

[GENERAL] Hot-Standby and sequences

[Wouter D'Haeseleer]

Hi All,

I have a question about sequences in combination with streaming
replication.
It seems something strange is happening with sequences which are
streamed to the slave.

When updating the sequence the last_value on the slave shifts with 32
and halts at this value until the master increased the value also with
32.
Is this normal behavior ?

Please see the example below:

Using Setval
--

# psql -t -h master -c select setval('foo', 10)
  10

# psql -t -h master  -c select last_value from foo
  10

# psql -t -h slave  -c select last_value from foo
  10

Using Nextval
--

# psql -t -h master -c select nextval('foo')
   11

# psql -t -h master  -c select last_value from foo
  11

# psql -t -h slave -c select last_value from foo
 43


-- 
Wouter D'Haeseleer
Linux System Engineer

VASCO Data Security
Dellingstraat 28b
2800 Mechelen
Belgium

phone: +32 15 50 44 00
email: w...@vasco.com 
web: www.vasco.com

[GENERAL] Hot-Standby and sequences

[Wouter D'Haeseleer]

Hi All,

I have a question about sequences in combination with streaming
replication.
It seems something strange is happening with sequences which are
streamed to the slave.

When updating the sequence the last_value on the slave shifts with 32
and halts at this value until the master increased the value also with
32.
Is this normal behavior ?

Please see the example below:

Using Setval
--

# psql -t -h master -c select setval('foo', 10)
  10

# psql -t -h master  -c select last_value from foo
  10

# psql -t -h slave  -c select last_value from foo
  10

Using Nextval
--

# psql -t -h master -c select nextval('foo')
   11

# psql -t -h master  -c select last_value from foo
  11

# psql -t -h slave -c select last_value from foo
 43


-- 
Wouter D'Haeseleer
Linux System Engineer

VASCO Data Security
Dellingstraat 28b
2800 Mechelen
Belgium

phone: +32 15 50 44 00
email: w...@vasco.com 
web: www.vasco.com

Re: [GENERAL] Hot-Standby and sequences

[Wouter D'Haeseleer]

OKay Tom,

Thanks for this clarification