Thread: postgres 9 streaming replication

postgres 9 streaming replication

From
"Khadtare, Sharad"
Date:
Hi,

Configured postgres 9 streaming replication and changed parameter in postgr=
esql.conf & pg_hba.conf file

Problem is wal sender & receiver process not started , is there any way to =
check process please suggest.


172.21.132.1 ( primary )

172.18.221.211 ( standby )


pg_hba.conf (primary & standby )

host   replication    all            172.18.221.211  255.255.255.255   trus


recovery.conf ( standby )

standby_mode =3D 'on'
primary_conninfo =3D 'host=3D172.21.132.1 port=3D5432 user=3Drepusr'
trigger_file =3D '/db/postgres/trigger'
restore_command =3D 'cp -f /db/postgres/data/pg_wal/%f %p < /dev/null '
postgresql.conf ( primary )

listen_addresses =3D '*'
port =3D 5432
archive_mode =3D on
archive_command =3D 'cp "%p" /db/postgres/data/pg_wal/"%f" < /dev/null'
max_wal_senders =3D 3
wal_level =3D hot_standby

postgresql.conf ( standby )

listen_addresses =3D '*'
port =3D 5432
archive_mode =3D on
archive_command =3D 'cp "%p" /db/postgres/data/pg_wal/"%f" < /dev/null'
max_wal_senders =3D 3
wal_level =3D hot_standby
hot_standby =3D on

Re: postgres 9 streaming replication

From
tushar
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Khadtare, Sharad wrote:
> Hi,
>
> Configured postgres 9 streaming replication and changed parameter in
> postgresql.conf & pg_hba.conf file
>
> Problem is wal sender & receiver process not started , is there any way
> to check process please suggest.

1. Make no changes in pg_hba.conf of standby. Use the default one.
2. Add "host   replication    postgres          172.18.221.211/32   trust" to pg_hba.conf of primary.
3. Add "wal_keep_segments = 32" to postgresql.conf of primary and standby.
4. Add "archive_mode = off" to postgresql.conf of standby.

You can check if the WAL sender and receiver processes are running using ps command.



- --
regards,
tushar
Lead QA Engineer
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
www.enterprisedb.com




-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFNPqCEfQNodY2PIRoRAuVjAJsFd0NN8lsHxbj4RQ2YE8thOWYMngCfcWgL
EBCVKLZb5JpKOAFr2gAggYA=
=/tIc
-----END PGP SIGNATURE-----

Re: postgres 9 streaming replication

From
Fujii Masao
Date:
On Tue, Jan 25, 2011 at 5:01 PM, Khadtare, Sharad
<sharad.khadtare@teamaol.com> wrote:
> Configured postgres 9 streaming replication and changed parameter in
> postgresql.conf & pg_hba.conf file
>
> Problem is wal sender & receiver process not started , is there any way to
> check process please suggest.

What log messages did you get right after starting the standby?

Did you locate the recovery.conf in PostgreSQL's data directory,
not /etc or elsewhere?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Re: postgres 9 streaming replication

From
Fujii Masao
Date:
On Tue, Jan 25, 2011 at 8:59 PM, Khadtare, Sharad
<sharad.khadtare@teamaol.com> wrote:
> Pls find below logfile of standby and recovery.conf in standby data direc=
tory.
>
> bash-3.2$ cat logfile
> LOG: =A0database system was interrupted while in recovery at log time 201=
1-01-25 05:28:35 EST
> HINT: =A0If this has occurred more than once some data might be corrupted=
 and you might need to choose an earlier recovery target.
> LOG: =A0entering standby mode
> LOG: =A0restored log file "000000010000000000000037" from archive
> LOG: =A0redo starts at 0/37000020
> LOG: =A0consistent recovery state reached at 0/38000000
> LOG: =A0database system is ready to accept read only connections
> cp: cannot stat `/db/postgres/archive/000000010000000000000038': No such =
file or directory
> LOG: =A0unexpected pageaddr 0/30000000 in log file 0, segment 56, offset 0
> cp: cannot stat `/db/postgres/archive/000000010000000000000038': No such =
file or directory
> LOG: =A0trigger 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,

--=20
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Re: postgres 9 streaming replication

From
"Khadtare, Sharad"
Date:
Hi Tushar,

Thx for immediate response.

I verified with ps commmand and there is no process running on host.

Regards,
Sharad K
=20

-----Original Message-----
From: tushar [mailto:tushar.ahuja@enterprisedb.com]=20
Sent: Tuesday, January 25, 2011 3:36 PM
To: Khadtare, Sharad
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] postgres 9 streaming replication

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Khadtare, Sharad wrote:
> Hi,
>=20=20
> Configured postgres 9 streaming replication and changed parameter in=20
> postgresql.conf & pg_hba.conf file
>=20=20
> Problem is wal sender & receiver process not started , is there any=20
> way to check process please suggest.

1. Make no changes in pg_hba.conf of standby. Use the default one.
2. Add "host   replication    postgres          172.18.221.211/32   trust" =
to pg_hba.conf of primary.
3. Add "wal_keep_segments =3D 32" to postgresql.conf of primary and standby.
4. Add "archive_mode =3D off" to postgresql.conf of standby.

You can check if the WAL sender and receiver processes are running using ps=
 command.



- --
regards,
tushar
Lead QA Engineer
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
www.enterprisedb.com




-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFNPqCEfQNodY2PIRoRAuVjAJsFd0NN8lsHxbj4RQ2YE8thOWYMngCfcWgL
EBCVKLZb5JpKOAFr2gAggYA=3D
=3D/tIc
-----END PGP SIGNATURE-----

Re: postgres 9 streaming replication

From
"Khadtare, Sharad"
Date:
Hi,

Pls find below logfile of standby and recovery.conf in standby data directo=
ry.

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 fi=
le or directory
LOG:  unexpected pageaddr 0/30000000 in log file 0, segment 56, offset 0
cp: cannot stat `/db/postgres/archive/000000010000000000000038': No such fi=
le or directory
LOG:  trigger file found: /db/postgres/trigger
LOG:  redo done at 0/370000D8
LOG:  restored log file "000000010000000000000037" from archive
cp: cannot stat `/db/postgres/archive/00000002.history': No such file or di=
rectory
LOG:  selected new timeline ID: 2
cp: cannot stat `/db/postgres/archive/00000001.history': No such file or di=
rectory
LOG:  archive recovery complete
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections

Regards,
Sharad k


-----Original Message-----
From: Fujii Masao [mailto:masao.fujii@gmail.com]=20
Sent: Tuesday, January 25, 2011 3:38 PM
To: Khadtare, Sharad
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] postgres 9 streaming replication

On Tue, Jan 25, 2011 at 5:01 PM, Khadtare, Sharad <sharad.khadtare@teamaol.=
com> wrote:
> Configured postgres 9 streaming replication and changed parameter in=20
> postgresql.conf & pg_hba.conf file
>
> Problem is wal sender & receiver process not started , is there any=20
> way to check process please suggest.

What log messages did you get right after starting the standby?

Did you locate the recovery.conf in PostgreSQL's data directory, not /etc o=
r elsewhere?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center

Re: postgres 9 streaming replication

From
"Khadtare, Sharad"
Date:
Hi,

Problem solved after removing trigger entry from recovery.conf file

Thx for help

Regards,
Sharad K=20

-----Original Message-----
From: Fujii Masao [mailto:masao.fujii@gmail.com]=20
Sent: Tuesday, January 25, 2011 5:55 PM
To: Khadtare, Sharad
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] postgres 9 streaming replication

On Tue, Jan 25, 2011 at 8:59 PM, Khadtare, Sharad <sharad.khadtare@teamaol.=
com> wrote:
> Pls find below logfile of standby and recovery.conf in standby data direc=
tory.
>
> bash-3.2$ cat logfile
> LOG: =A0database system was interrupted while in recovery at log time=20
> 2011-01-25 05:28:35 EST
> HINT: =A0If this has occurred more than once some data might be corrupted=
 and you might need to choose an earlier recovery target.
> LOG: =A0entering standby mode
> LOG: =A0restored log file "000000010000000000000037" from archive
> LOG: =A0redo starts at 0/37000020
> LOG: =A0consistent recovery state reached at 0/38000000
> LOG: =A0database system is ready to accept read only connections
> cp: cannot stat `/db/postgres/archive/000000010000000000000038': No=20
> such file or directory
> LOG: =A0unexpected pageaddr 0/30000000 in log file 0, segment 56, offset=
=20
> 0
> cp: cannot stat `/db/postgres/archive/000000010000000000000038': No=20
> such file or directory
> LOG: =A0trigger 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 backu=
p 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

Re: postgres 9 streaming replication

From
Alex Lai
Date:
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.fujii@gmail.com]
> Sent: Tuesday, January 25, 2011 5:55 PM
> To: Khadtare, Sharad
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] postgres 9 streaming replication
>
> On Tue, Jan 25, 2011 at 8:59 PM, Khadtare, Sharad <sharad.khadtare@teamaol.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
recoverytarget. 
>> 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
standbywas 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
itafter 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@sesda2.com

Re: postgres 9 streaming replication

From
Fujii Masao
Date:
On Wed, Mar 23, 2011 at 9:07 PM, Alex Lai <alai@sesda2.com> wrote:
> I have no idea why I keep getting the message:\
> =A0could not connect to the primary server: FATAL: =A0no pg_hba.conf entr=
y for
> replication connection from host "slave_server_ip", user
> "my_super_user_name"

Can you connect from slave to master by the following command?

    psql "replication=3D1" -h <slave_ip> -p 5432 -U <my_user_name>

If successful, the settings in recovery.conf would be wrong.
Otherwise, I guess that the setting in pg_hba.conf of the master would
be wrong. Did you reload the configuration files? What happens if you
use "all" keyword instead of specifying the real user name? What happens
if you use "0.0.0.0/0" instead of specifying the slave's ip? You would need
to do trial and error approach.

Regards,

--=20
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Re: postgres 9 streaming replication

From
Alex Lai
Date:
Fujii Masao wrote:
> On Wed, Mar 23, 2011 at 9:07 PM, Alex Lai <alai@sesda2.com> wrote:
>
>> 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"
>>
>
> Can you connect from slave to master by the following command?
>
>     psql "replication=1" -h <slave_ip> -p 5432 -U <my_user_name>
>
> If successful, the settings in recovery.conf would be wrong.
> Otherwise, I guess that the setting in pg_hba.conf of the master would
> be wrong. Did you reload the configuration files? What happens if you
> use "all" keyword instead of specifying the real user name? What happens
> if you use "0.0.0.0/0" instead of specifying the slave's ip? You would need
> to do trial and error approach.
>
> Regards,
>
>
After changed to all for -U in pg_hba.conf, it connected without
problem.  Thanks for your help.

--
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@sesda2.com