Re: postgres 9 streaming replication

Khadtare, Sharad wrote:
> Hi,
>
> Problem solved after removing trigger entry from recovery.conf file
>
> Thx for help
>
> Regards,
> Sharad K
>
> -----Original Message-----
> From: Fujii Masao [mailto:masao(dot)fujii(at)gmail(dot)com]
> Sent: Tuesday, January 25, 2011 5:55 PM
> To: Khadtare, Sharad
> Cc: pgsql-bugs(at)postgresql(dot)org
> Subject: Re: [BUGS] postgres 9 streaming replication
>
> On Tue, Jan 25, 2011 at 8:59 PM, Khadtare, Sharad <sharad(dot)khadtare(at)teamaol(dot)com> wrote:
>
>> Pls find below logfile of standby and recovery.conf in standby data directory.
>>
>> bash-3.2$ cat logfile
>> LOG: database system was interrupted while in recovery at log time
>> 2011-01-25 05:28:35 EST
>> HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
>> LOG: entering standby mode
>> LOG: restored log file "000000010000000000000037" from archive
>> LOG: redo starts at 0/37000020
>> LOG: consistent recovery state reached at 0/38000000
>> LOG: database system is ready to accept read only connections
>> cp: cannot stat `/db/postgres/archive/000000010000000000000038': No
>> such file or directory
>> LOG: unexpected pageaddr 0/30000000 in log file 0, segment 56, offset
>> 0
>> cp: cannot stat `/db/postgres/archive/000000010000000000000038': No
>> such file or directory
>> LOG: trigger file found: /db/postgres/trigger
>>
>
> The above message means that you created the trigger file and promoted the standby server to the master. Since the standby was running as the master, walreceiver was not invoked in the standby.
>
> To start replication, you need to create the standby (taking the base backup from the master is required) and start it after you
> *ensure* that there is no trigger file in the standby.
>
> I hope this helps..
>
> Regards,
>
> --
> Fujii Masao
> NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
>
>
I have a same problem trying to get slave replication using 9.0
streaming replication.
After the solution, I still not able to get through.
First, I scp the whole data directory from master to slave server.
In master server, I added the following lines in pg_hba.conf.

host replication my_user_name slave_ip/32 trust
host all all x.x.x.x/32 trust
Note: my_user_name replaced with my real super user name and slave_ip
replaced with slave server's ip.

In master server, I also added the following lines in postgresql.conf.

wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
listen_addresses='*'

In slave server, I added the following line in postgresql.conf.
hot_standby = on

In slave server, I added the following line in recovery.conf.
standby_mode = 'on'
primary_conninfo = 'host=master_ip port=5432 user=my_user_name'
trigger_file = '/path_to/pgsql.trigger'
Note: my_user_name replaced with my real super user name and master_ip
replaced with master server's ip.
I removed trigger_file from recovery.conf and restart the
slave. It still have the same problem.

I then started master and slave servers.
I got the error message from slave pg_log.
...
cp: cannot stat `/path_to/arch_replicate/000000010000004A0000001F': No
such file or directory
cp: cannot stat `/path_to/arch_replicate/000000010000004A0000001F': No
such file or directory
107602011-03-22 09:42:03 EDTFATAL: could not connect to the primary
server: FATAL:
no pg_hba.conf entry for replication connection from host
"slave_server_ip", user "my_super_user_name"

I got the error message from master pg_log.
...
1380902011-03-22 09:37:51 EDTLOG: connection received:
host=slave_server_ip port=51686
1380902011-03-22 09:37:51 EDTauthenticationFATAL: no pg_hba.conf entry
for replication connection from host "slave_server_ip", user
"my_super_user_name"
1381002011-03-22 09:37:56 EDTLOG: connection received:
host=slave_server_ip port=51687
1381002011-03-22 09:37:56 EDTauthenticationFATAL: no pg_hba.conf entry
for replication connection from host "slave_server_ip", user
"my_super_user_name"
1381102011-03-22 09:38:00 EDTLOG: connection received:
host=slave_server_ip port=51688
1381102011-03-22 09:38:00 EDTauthenticationFATAL: no pg_hba.conf entry
for replication connection from host "slave_server_ip", user
"my_super_user_name"

I was able to psql connect from slave to master or from master to slave
by using.
From slave server, I can connect from slave to master by the following
command.
psql database_name -f master_ip -p 5432
From master server, I can connect from master to slave by the following
command.
psql database_name -f slave_ip -p 5432

I have no idea why I keep getting the message:\
could not connect to the primary server: FATAL:
no pg_hba.conf entry for replication connection from host
"slave_server_ip", user "my_super_user_name"

Your help will be highly appreciated.

Alex Lai

--
Best regards,

Alex Lai
OMI SIPS DBA ADNET Systems , Inc.
7515 Mission Drive,
Suite A100 Lanham, MD 20706
301-352-4657 (phone)
301-352-0437 (fax)
alai(at)sesda2(dot)com