Replicação [RESOLVIDO]

welckson
(usa Ubuntu)

Enviado em 27/03/2014 - 11:54h

Boa tarde pessoal,

Gostaria da ajuda de vocês na seguinte situação:

Como realizar a Replicação de banco de dados com PostgreSQL, mas que os dados podem ser sincronizados "simultaneamente" ou após algum tempo;

madrux
(usa Ubuntu)

Enviado em 27/03/2014 - 11:58h

Usa o recurso streaming replication, que esta disponível a partir da versão 9

welckson
(usa Ubuntu)

Enviado em 27/03/2014 - 12:00h

Tudo bem,

Mas tem algum Tutorial como eu posso realizar esse procedimento?

madrux
(usa Ubuntu)

Enviado em 27/03/2014 - 12:02h

http://www.postgresql.org/docs/9.0/static/protocol-replication.html

http://eulerto.blogspot.com.br/2010/11/hot-standby-e-streaming-replication.html

welckson
(usa Ubuntu)

Enviado em 31/03/2014 - 16:43h

Boa tarde srs,

Estou fazendo as configurações no PostgreSQL (Servidor) mas ao subir o serviço apresentou o seguinte erro:

2014-03-31 15:53:29 BRT LOG: received fast shutdown request
2014-03-31 15:53:29 BRT LOG: aborting any active transactions
2014-03-31 15:53:29 BRT LOG: autovacuum launcher shutting down
2014-03-31 15:53:29 BRT LOG: shutting down
2014-03-31 15:53:29 BRT LOG: database system is shut down
2014-03-31 18:53:30 GMT FATAL: WAL streaming (max_wal_senders > 0) requires wal_level "archive" or "hot_standby"
2014-03-31 18:54:26 GMT FATAL: WAL streaming (max_wal_senders > 0) requires wal_level "archive" or "hot_standby"
2014-03-31 18:55:06 GMT FATAL: WAL streaming (max_wal_senders > 0) requires wal_level "archive" or "hot_standby"
2014-03-31 18:57:43 GMT FATAL: WAL streaming (max_wal_senders > 0) requires wal_level "archive" or "hot_standby"
2014-03-31 19:00:07 GMT FATAL: WAL streaming (max_wal_senders > 0) requires wal_level "archive" or "hot_standby"
2014-03-31 19:00:18 GMT FATAL: WAL streaming (max_wal_senders > 0) requires wal_level "archive" or "hot_standby"


Estou realizando as configurações de replicação no postgres.

madrux
(usa Ubuntu)

Enviado em 31/03/2014 - 16:51h

você não definiu o número máximo de servidores secundários, analise o seu arquivo de log.

welckson
(usa Ubuntu)

Enviado em 31/03/2014 - 17:17h

Mas eu fiz essa configuração abaixo:

# - Master Server -

# These settings are ignored on a standby server

max_wal_senders = 1 # max number of walsender processes
# (change requires restart)
#wal_sender_delay = 1s # walsender cycle time, 1-10000 milliseconds
wal_keep_segments = 100 # in logfile segments, 16MB each; 0 disables
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
#replication_timeout = 60s # in milliseconds; 0 disables
#synchronous_standby_names = '' # standby servers that provide sync rep
# comma-separated list of application_name
# from standby(s); '*' = all

# - Standby Servers -

# These settings are ignored on a master server

#hot_standby = off # "on" allows queries during recovery
# (change requires restart)
#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
#max_standby_streaming_delay = 30s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
#wal_receiver_status_interval = 10s # send replies at least this often
# 0 disables
#hot_standby_feedback = off # send info from standby to prevent
# query conflicts

madrux
(usa Ubuntu)

Enviado em 31/03/2014 - 17:21h

Você deve habilitar o recurso de hot_standby no servidor slave

welckson
(usa Ubuntu)

Enviado em 31/03/2014 - 17:29h

Pronto, realizei a configuração no servidor, deu certo, realmente você tinha razão. Mas para configurar o Escravo, como eu posso fazer o pg_basebackup e criar o recovery.conf.

welckson
(usa Ubuntu)

Enviado em 03/04/2014 - 09:14h

Bom dia

Estou com esse seguinte erro ao copiar os dados do servidor Master para o Slave:

ssh: connect to host 172.29.0.181 port 22: Connection refused
rsync: connection unexpectedly closed (0 bytes received so far) [sender]
rsync error: error in rsync protocol data stream (code 12) at io.c(605) [sender=3.0.9]

welckson
(usa Ubuntu)

Enviado em 03/04/2014 - 17:14h

Bom dia, fiz o processo conforme a sua orientação, mas deu o seguinte erro:

root@slave:~# ssh-keygen -f "/root/.ssh/know_hosts" -R 172.29.0.181
line 1 missing key: YzHtveD8Lw0e4qRz8mkJCX25vxFVlRDLhX1NjT/7...
line 2 missing key: WFTYetyUnNzcYDUDS3LTMC1HOUBZ8hizvp93kKKd...
line 3 missing key: /o7558to8FFLo/D47dssi15RlRvzu5crQnPI4wID...
line 4 missing key: i2/bX3SrvLF5zYomZyHWDceQWpMYC/fzojSbTBSP...
line 5 missing key: Ps2EE60ntlNki62+mFF+eYBA3KHA2UjxZlFOthiO...
line 6 missing key: JEIXouJi0vJLwtAuTG/19wMeG/0Q9/rw+Y0/VFZH...
line 7 missing key: UHiU2G6xARAeYZmS9CzTPb8mQrA67cLmepXdvZv1...
line 8 missing key: ZJLCI6TnRJzbef3XEYdN9ftxFnFbnmJyQR/JJAyk...
line 9 missing key: O8mfnRkCgYEA/FwROkOo5uBZ/SwJCbj3n25kQdfQ...
line 10 missing key: bNtBXA30XuvaWC7dfDr3e+qRBxwzPXWefo5IzpKC...
line 11 missing key: LYz/dBpVmicShLFdJGqs7md3L4y4JX1lNJ6rIIYB...
line 12 missing key: 5swgIE/78eCHBm9XwtvBhZ1nUSSx/IzmkjCL/aRJ...
line 13 missing key: KLbMVy32oduS2Fzcu6XQ7mq7gzO6OHaQk61nipUk...
line 14 missing key: lrv33UfdcYUurpWnVzr40o/JT5j1RvJ+0qFzUy8C...
line 15 missing key: wvGoCxlOwHYOe23cKU4X9UaKBAMy7qnlGwqlI/P8...
line 16 missing key: rMpK/MLov+V5Kpd0Q8DlGzouEahovvoHYaTSp89W...
line 17 missing key: DDvmiDx6lmOtAEt1eLGqgpkCgYAms72ASkTwvXWh...
line 18 missing key: 42TQfZeoJP9rWvPwOWQzP8N9Debea6QPK8eh/E4v...
line 19 missing key: Pxy+L0Dcrqbtxj657OI/oWq/QneD99IM3Dcg5vM9...
line 20 missing key: X1QxoQKBgBKsToBA0c99QxlwM7hzr3OAMDGWmQ+j...
line 21 missing key: 8WTOkm47zHlM+WhHwasar5jFYc3MoZa/lUQZiZd3...
line 22 missing key: 6HVYsESxWXVx/uAJXbNguCl+5WUlGamFDDhNti5Z...
line 23 invalid key: -----END...
/root/.ssh/know_hosts is not a valid known_hosts file.
Not replacing existing known_hosts file because of errorsroot@slave:~# ssh-keygen -f "/root/.ssh/know_hosts" -R 172.29.0.181



Repeti o processo, mas deu o mesmo erro:


@ WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED! @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
Someone could be eavesdropping on you right now (man-in-the-middle attack)!
It is also possible that a host key has just been changed.
The fingerprint for the ECDSA key sent by the remote host is
fd:2e:b6:00:fb:76:16:40:12:bc:df:33:de:67:cf:06.
Please contact your system administrator.

Add correct host key in /root/.ssh/known_hosts to get rid of this message.
Offending ECDSA key in /root/.ssh/known_hosts:1
remove with: ssh-keygen -f "/root/.ssh/known_hosts" -R 172.29.0.181
ECDSA host key for 172.29.0.181 has changed and you have requested strict checking.

Host key verification failed.
rsync: connection unexpectedly closed (0 bytes received so far) [sender]
rsync error: error in rsync protocol data stream (code 12) at io.c(605) [sender=3.0.9]

welckson
(usa Ubuntu)

Enviado em 12/04/2014 - 11:41h

Bom dia Srs,

Estou apenas com esse problema:

The PostgreSQL server failed to start. Please check the log output:
2014-04-12 08:48:52 BRT FATAL: could not load server certificate file "server.crt": Permissão negada