Thread: postgres 9 streaming replication
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
-----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-----
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
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
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-----
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
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
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
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
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