Обсуждение: replication problems 9.0

Hi,

I configured replication in 2 AWS instances.
I have shell scripts to dynamically configure the Ips after the
instances are UP.
the Postgres versions are the same in master&  slave, also the
architecture of instances.


Added the lines in the Master
root@ip-172-19-1-37:~# cat  /etc/postgresql/9.0/main/pg_hba.conf |grep trust
host all postgres 172.19.1.101/32 trust

root@ip-172-19-1-37:~# tail -n6  /etc/postgresql/9.0/main/postgresql.conf
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /nfsdata/postgres_wal/main_cware/%f'


And in the slave
root@ip-172-19-1-101:~# cat /var/lib/postgresql/9.0/main/recovery.conf
standby_mode = 'on'
trigger_file = '/path_to/trigger'
restore_command = 'cp /nfsdata/postgres_wal/main_cware/%f "%p"'
primary_conninfo = 'host=172.19.1.37 port=5432 user=postgres'

root@ip-172-19-1-101:~# cat /etc/postgresql/9.0/main/postgresql.conf
|grep hot
hot_standby = on

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

I have in the master log
2011-06-07 17:53:04 UTC FATAL:  no pg_hba.conf entry for replication
connection from host "172.19.1.101", user "postgres", SSL off
2011-06-07 17:53:09 UTC FATAL:  no pg_hba.conf entry for replication
connection from host "172.19.1.101", user "postgres", SSL off

and in the slave
cp: cannot stat
`/nfsdata/postgres_wal/main_cware/00000001000000000000002E': No such
file or directory
2011-06-07 17:53:34 UTC FATAL:  could not connect to the primary server:
FATAL:  no pg_hba.conf entry for replication connection from host
"172.19.1.101", user "postgres", SSL off

__________________________________

I'm rsync the files from master
     rsync -a --delete --exclude postmaster.pid --exclude pg_xlog
/data/postgresql/main/  /nfsdata/postgres_wal/main_cware/bkp/

and restoring the DB in slave
     rsync -a --exclude '.conf' /nfsdata/postgres_wal/main_cware/bkp/
/var/lib/postgresql/9.0/main


replication is not working since the records added to te master car not
present in the slave.
Any help will be appreciated.

regards
Owen

On 06/07/2011 03:05 PM, Owen Marinas wrote:
> Added the lines in the Master
> root@ip-172-19-1-37:~# cat /etc/postgresql/9.0/main/pg_hba.conf |grep trust
> host all postgres 172.19.1.101/32 trust
>

You need in pg_hba.conf

host    replication     repuser         172.19.1.101/32          md5

Database must be replication for the replication user

Regards

Rodrigo Gonzalez

Thx, Merci, Gracias Rodrigo
it worked indeed, Im wondering why replication is not included in "All"

regards
Owen


On Tue, Jun 7, 2011 at 2:40 PM, Rodrigo Gonzalez
<rjgonzale@estrads.com.ar> wrote:
> On 06/07/2011 03:05 PM, Owen Marinas wrote:
>>
>> Added the lines in the Master
>> root@ip-172-19-1-37:~# cat /etc/postgresql/9.0/main/pg_hba.conf |grep
>> trust
>> host all postgres 172.19.1.101/32 trust
>>
>
> You need in pg_hba.conf
>
> host    replication     repuser         172.19.1.101/32          md5
>
> Database must be replication for the replication user
>
> Regards
>
> Rodrigo Gonzalez
>

On June 7, 2011 04:38:16 PM owen marinas wrote:
> Thx, Merci, Gracias Rodrigo
> it worked indeed, Im wondering why replication is not included in "All"
>

Probably because it gives access to all the data being written to the
database.

--
Obama has now fired more cruise missiles than all other Nobel Peace prize
winners combined.

I am not an expert but I will try to explain.

replication user has access to WAL stream, bypassing any revoked
permission on any database.

I think that is the reason to force a specific user with specific
permissions for replication....you don't want user x reading WAL stream
while you don't grant the user access to all database objects

I hope I am clear in my poor English

Regards

Rodrigo Gonzalez

On 06/07/2011 08:38 PM, owen marinas wrote:
> Thx, Merci, Gracias Rodrigo
> it worked indeed, Im wondering why replication is not included in "All"
>
> regards
> Owen
>
>
> On Tue, Jun 7, 2011 at 2:40 PM, Rodrigo Gonzalez
> <rjgonzale@estrads.com.ar>  wrote:
>> On 06/07/2011 03:05 PM, Owen Marinas wrote:
>>>
>>> Added the lines in the Master
>>> root@ip-172-19-1-37:~# cat /etc/postgresql/9.0/main/pg_hba.conf |grep
>>> trust
>>> host all postgres 172.19.1.101/32 trust
>>>
>>
>> You need in pg_hba.conf
>>
>> host    replication     repuser         172.19.1.101/32          md5
>>
>> Database must be replication for the replication user
>>
>> Regards
>>
>> Rodrigo Gonzalez
>>
>