Thread: streaming replication question

streaming replication question

From

Roberto Scattini

Date:

21 March 2013, 21:32:34

hi list,

i'm installing a streaming replication master-slave setup in ubuntu 12.04 LTS, with postgresql 9.1

the tutorials and the documentation are a very good start point, but i have one question related to some fine grained configurations.

it is said that i should stop the master db in order to make de starting backup, OR run pg_start_backup and keep the archive files for the slave to catch up. if i dont do this, maybe the slave stays far behind the master and the wal_segments arent enough to start the replication.

if I understand this right, i can "combine" the old "warm standby" configs of archive_command in the master and restore command in the slave to ensure that the slave will have the necessary info to start the replication.

i mean, i can have both configurations enabled?

right now, my streaming replication setup has this configs:

recovery.conf (slave)

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

standby_mode = 'on'

primary_conninfo = 'host=192.168.206.134'

trigger_file = '/var/lib/postgresql/9.1/sgi/sgi.trigger'

restore_command = '/usr/lib/postgresql/9.1/bin/pg_standby -d -k 100 -s 20 -t /var/lib/postgresql/9.1/sgi.trigger /var/lib/postgresql/9.1/archive %f %p'

archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/archive %r'

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

postgresql.conf (master)

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

data_directory = '/var/lib/postgresql/9.1/sgi'

hba_file = '/etc/postgresql/9.1/sgi/pg_hba.conf'

ident_file = '/etc/postgresql/9.1/sgi/pg_ident.conf'

external_pid_file = '/var/run/postgresql/9.1-sgi.pid'

listen_addresses = '*'

port = 5432

max_connections = 100

unix_socket_directory = '/var/run/postgresql'

ssl = true

shared_buffers = 24MB

wal_level = hot_standby

archive_mode = on

archive_command = 'rsync -arv %p 192.168.206.133:/var/lib/postgresql/9.1/archive/%f </dev/null'

archive_timeout = 900

max_wal_senders = 5

wal_keep_segments = 100

log_line_prefix = '%t '

datestyle = 'iso, mdy'

lc_messages = 'en_US.UTF-8'

lc_monetary = 'en_US.UTF-8'

lc_numeric = 'en_US.UTF-8'

lc_time = 'en_US.UTF-8'

default_text_search_config = 'pg_catalog.english'

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

thanks,

--
Roberto Scattini

Re: streaming replication question

From

Roberto Scattini

Date:

21 March 2013, 21:46:13

On Thu, Mar 21, 2013 at 3:32 PM, Roberto Scattini <roberto.scattini@gmail.com> wrote:

hi list,

i'm installing a streaming replication master-slave setup in ubuntu 12.04 LTS, with postgresql 9.1

the tutorials and the documentation are a very good start point, but i have one question related to some fine grained configurations.

it is said that i should stop the master db in order to make de starting backup, OR run pg_start_backup and keep the archive files for the slave to catch up. if i dont do this, maybe the slave stays far behind the master and the wal_segments arent enough to start the replication.
if I understand this right, i can "combine" the old "warm standby" configs of archive_command in the master and restore command in the slave to ensure that the slave will have the necessary info to start the replication.

i mean, i can have both configurations enabled?

no, i think my setup, with this config, is just doing warm standby. but the streaming replication was working before adding archive_command to master and restore_command to slave.

i used the info from this site:

http://wiki.postgresql.org/wiki/Streaming_Replication

--
Roberto Scattini

Re: streaming replication question

From

Roberto Scattini

Date:

21 March 2013, 22:05:39

On Thu, Mar 21, 2013 at 3:45 PM, Roberto Scattini <roberto.scattini@gmail.com> wrote:

i mean, i can have both configurations enabled?

no, i think my setup, with this config, is just doing warm standby. but the streaming replication was working before adding archive_command to master and restore_command to slave.

if i remove restore_command and archive_cleanup_command from recovery.conf on slave the streaming replication works again.

so, i cant combine both methods? i must shut down my master to start the streaming replication? this comment confused me:

# Specifies a command to load archive segments from the WAL archive. If

# wal_keep_segments is a high enough number to retain the WAL segments

# required for the standby server, this may not be necessary. But

# a large workload can cause segments to be recycled before the standby

# is fully synchronized, requiring you to start again from a new base backup.

restore_command = 'cp /path_to/archive/%f "%p"'

--
Roberto Scattini

Re: streaming replication question

From

Date:

22 March 2013, 05:11:24

You can combine warm standby and streaming, we do this in our product database.

When the standby is to far from the master, the slave will try to use restore_command to restore the database(warm standby), when the standby catch up the master, the steaming will working again.

BTW: we use ominipitr.

On Fri, Mar 22, 2013 at 2:32 AM, Roberto Scattini <roberto.scattini@gmail.com> wrote:

hi list,

i'm installing a streaming replication master-slave setup in ubuntu 12.04 LTS, with postgresql 9.1

the tutorials and the documentation are a very good start point, but i have one question related to some fine grained configurations.

it is said that i should stop the master db in order to make de starting backup, OR run pg_start_backup and keep the archive files for the slave to catch up. if i dont do this, maybe the slave stays far behind the master and the wal_segments arent enough to start the replication.
if I understand this right, i can "combine" the old "warm standby" configs of archive_command in the master and restore command in the slave to ensure that the slave will have the necessary info to start the replication.

i mean, i can have both configurations enabled?
right now, my streaming replication setup has this configs:

recovery.conf (slave)
-----------------------------------
standby_mode = 'on'
primary_conninfo = 'host=192.168.206.134'
trigger_file = '/var/lib/postgresql/9.1/sgi/sgi.trigger'
restore_command = '/usr/lib/postgresql/9.1/bin/pg_standby -d -k 100 -s 20 -t /var/lib/postgresql/9.1/sgi.trigger /var/lib/postgresql/9.1/archive %f %p'
archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/archive %r'
-----------------------------------

postgresql.conf (master)
-----------------------------------
data_directory = '/var/lib/postgresql/9.1/sgi'
hba_file = '/etc/postgresql/9.1/sgi/pg_hba.conf'
ident_file = '/etc/postgresql/9.1/sgi/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.1-sgi.pid'
listen_addresses = '*'
port = 5432
max_connections = 100
unix_socket_directory = '/var/run/postgresql'
ssl = true
shared_buffers = 24MB
wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -arv %p 192.168.206.133:/var/lib/postgresql/9.1/archive/%f </dev/null'
archive_timeout = 900
max_wal_senders = 5
wal_keep_segments = 100
log_line_prefix = '%t '
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
-----------------------------------

thanks,

--
Roberto Scattini

Re: streaming replication question

From

Roberto Scattini

Date:

22 March 2013, 06:27:20

On Thursday, March 21, 2013, wd <wd@wdicc.com> wrote:
> You can combine warm standby and streaming, we do this in our product database.
> When the standby is to far from the master, the slave will try to use restore_command to restore the database(warm standby), when the standby catch up the master, the steaming will working again.
>
> BTW: we use ominipitr.
>

ok. tomorrow i will check that again. i think that when restore_command was enabled in the slave, the wal receiver proccess didnt appear (and i didnt see the changes inmediately happening in the slave).

can you post your recovery.conf?

thanks for the reply!

--
Roberto Scattini

Re: streaming replication question

From

Date:

22 March 2013, 14:04:09

On Fri, Mar 22, 2013 at 11:27 AM, Roberto Scattini <roberto.scattini@gmail.com> wrote:

On Thursday, March 21, 2013, wd <wd@wdicc.com> wrote:
> You can combine warm standby and streaming, we do this in our product database.
> When the standby is to far from the master, the slave will try to use restore_command to restore the database(warm standby), when the standby catch up the master, the steaming will working again.
>
> BTW: we use ominipitr.
>

ok. tomorrow i will check that again. i think that when restore_command was enabled in the slave, the wal receiver proccess didnt appear (and i didnt see the changes inmediately happening in the slave).

I think you should use an other restore_command, in warm standby, the resotre_command will not exit and continue check xlog files and restore them, the streaming won't start when in restore. Try use cp instead of it.

can you post your recovery.conf?

primary_conninfo = ''
restore_command = '/opt/omnipitr/bin/omnipitr-restore -l /export/omnipitr/log/omnipitr-^Y^m^d.log -s gzip=/export/vacation_wal_archive -f /export/omnipitr/finish.recovery -r -sr -v -t /export/omnipitr/tmp/ %f %p'
archive_cleanup_command = '/opt/omnipitr/bin/omnipitr-cleanup -l /export/omnipitr/log/cleanup.log -a gzip=/export/vacation_wal_archive %r'

thanks for the reply!

--
Roberto Scattini