pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp needed for replication issue
9+ messages / 5 participants
[nested] [flat]
* Help needed for replication issue
@ 2020-04-23 06:14 Ashok kumar Mani <amani@accelaero.com>
2020-04-23 09:00 ` Re: Help needed for replication issue Prince Pathria <prince.pathria@goevive.com>
2020-04-26 06:22 ` RE: Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
0 siblings, 2 replies; 9+ messages in thread
From: Ashok kumar Mani @ 2020-04-23 06:14 UTC (permalink / raw)
To: pgsql-admin; 'pgsql-admin@lists.postgresql.org' <pgsql-admin@lists.postgresql.org>
Classification: Internal
Dear All,
I have created Postgresql 10.6 hot standb ,but while creating physical replication slots by default it is in inactive more
I recreated the standby but still I am unable to make slots is in active and also I could not able to see the LSN value on both sites.
Regards,
Ashok
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help needed for replication issue
2020-04-23 06:14 Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
@ 2020-04-23 09:00 ` Prince Pathria <prince.pathria@goevive.com>
2020-04-24 03:59 ` RE: Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
1 sibling, 1 reply; 9+ messages in thread
From: Prince Pathria @ 2020-04-23 09:00 UTC (permalink / raw)
To: Ashok kumar Mani <amani@accelaero.com>; +Cc: pgsql-admin; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hey Ashok,
Please share the steps you've followed till now so we help further.
Thanks!
Happy to help :)
Prince Pathria Systems Engineer | Certified Kubernetes Administrator | AWS
Solutions Architect Evive +91 9478670472 goevive.com
On Thu, Apr 23, 2020 at 11:44 AM Ashok kumar Mani <amani@accelaero.com>
wrote:
> Classification: Internal
>
> Dear All,
>
> I have created Postgresql 10.6 hot standb ,but while creating physical
> replication slots by default it is in inactive more
>
> I recreated the standby but still I am unable to make slots is in active
> and also I could not able to see the LSN value on both sites.
>
>
>
>
>
> Regards,
>
> Ashok
>
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Help needed for replication issue
2020-04-23 06:14 Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
2020-04-23 09:00 ` Re: Help needed for replication issue Prince Pathria <prince.pathria@goevive.com>
@ 2020-04-24 03:59 ` Ashok kumar Mani <amani@accelaero.com>
2020-04-24 05:03 ` Re: Help needed for replication issue Keith <keith@keithf4.com>
2020-04-24 13:42 ` Re: Help needed for replication issue soumitra bhandary <soumitra.bhandary@hotmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Ashok kumar Mani @ 2020-04-24 03:59 UTC (permalink / raw)
To: pgsql-admin; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Classification: External
Hi Pathiria,
Step1:- Enable the below parameters in postgresql.conf file for hotstandby
wal_level=replica [ Default : hot_standby]
wal_log_hints = on
synchronous_commit = on
wal_log_hints = on
archive_mode = on [
archive_timeout = 30
archive_command = 'scp %p 172.30.8.199:/data/ARCHIVELOG/%f'
step 2:- #Replication
max_wal_senders = 10
wal_keep_segments = 3200 # in logfile segments, 16MB each; 0 disables[
hot_standby = on
hot_standby_feedback = on # send info from standby to prevent query conflicts
#Log file configurations
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 0
log_rotation_size = 100MB
log_timezone = 'GMT'
Step3:- backup master database then transfer into replica server by using rsync
[postgres@de284194 ~]$ psql -c "SELECT pg_start_backup('label', true)"
pg_start_backup
-----------------
10/DA000028
(1 row)
[postgres@de284194 pg_data]$ rsync -cva --inplace --exclude=*pg_xlog* \
> ${PGDATA}/ 172.29.7.194:$PGDATA
sending incremental file list
./
pg_wal/archive_status/0000000100000010000000CC.done
pg_wal/archive_status/0000000100000010000000CD.00000028.backup.done
pg_wal/archive_status/0000000100000010000000CD.done
pg_wal/archive_status/0000000100000010000000D5.done
pg_xact/
pg_xact/0000
sent 1,053,663,924 bytes received 21,480 bytes 15,160,941.06 bytes/sec
total size is 1,053,341,161 speedup is 1.00
[postgres@de284194 pg_data]$
Step 4:- Stop the backup
[postgres@de284194 pg_data]$ psql -c "select pg_stop_backup(), current_timestamp"
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
Step 5;- Create recovery.conf
postgres=# SELECT pg_create_physical_replication_slot('amos_prod_replica1');
pg_create_physical_replication_slot
-------------------------------------
(amos_prod_replica1,)
(1 row)
Start the db server in replica server:-
Regards,
Ashok
From: Prince Pathria <prince.pathria@goevive.com>
Sent: Thursday, April 23, 2020 1:00 PM
To: Ashok kumar Mani <amani@accelaero.com>
Cc: pgsql-admin@postgresql.org; pgsql-admin@lists.postgresql.org
Subject: Re: Help needed for replication issue
Information Security Email Alert: This email is from an EXTERNAL source. Please use caution when clicking on links or opening attachments from an unknown or suspicious sender. To report a suspected phishing email, Send us an Email on Servicedesk@accelaero.com<mailto:Servicedesk@accelaero.com>
Hey Ashok,
Please share the steps you've followed till now so we help further.
Thanks!
Happy to help :)
Prince Pathria Systems Engineer | Certified Kubernetes Administrator | AWS Solutions Architect Evive +91 9478670472 goevive.com<https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgoevive.com%2F&data=02%7C01%7Ca...;
On Thu, Apr 23, 2020 at 11:44 AM Ashok kumar Mani <amani@accelaero.com<mailto:amani@accelaero.com>> wrote:
Classification: Internal
Dear All,
I have created Postgresql 10.6 hot standb ,but while creating physical replication slots by default it is in inactive more
I recreated the standby but still I am unable to make slots is in active and also I could not able to see the LSN value on both sites.
Regards,
Ashok
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help needed for replication issue
2020-04-23 06:14 Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
2020-04-23 09:00 ` Re: Help needed for replication issue Prince Pathria <prince.pathria@goevive.com>
2020-04-24 03:59 ` RE: Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
@ 2020-04-24 05:03 ` Keith <keith@keithf4.com>
1 sibling, 0 replies; 9+ messages in thread
From: Keith @ 2020-04-24 05:03 UTC (permalink / raw)
To: Ashok kumar Mani <amani@accelaero.com>; +Cc: pgsql-admin; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Fri, Apr 24, 2020 at 12:00 AM Ashok kumar Mani <amani@accelaero.com>
wrote:
> Classification: External
>
>
>
> Hi Pathiria,
>
>
>
> *Step1*:- Enable the below parameters in postgresql.conf file for
> hotstandby
>
> wal_level=replica [ Default : hot_standby]
>
> wal_log_hints = on
>
> synchronous_commit = on
>
>
>
> wal_log_hints = on
>
>
>
> archive_mode = on [
>
>
>
> archive_timeout = 30
>
>
>
> archive_command = 'scp %p 172.30.8.199:/data/ARCHIVELOG/%f'
>
>
>
>
>
>
> step 2:- #Replication
>
> max_wal_senders = 10
>
> wal_keep_segments = 3200 # in logfile segments, 16MB each; 0
> disables[
>
> hot_standby = on
>
> hot_standby_feedback = on # send info from standby to
> prevent query conflicts
>
> #Log file configurations
>
> log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
>
> log_rotation_age = 0
>
> log_rotation_size = 100MB
>
> log_timezone = 'GMT'
>
>
>
> Step3:- backup master database then transfer into replica server by using
> rsync
>
>
>
> [postgres@de284194 ~]$ psql -c "SELECT pg_start_backup('label', true)"
>
> pg_start_backup
>
> -----------------
>
> 10/DA000028
>
> (1 row)
>
>
>
> [postgres@de284194 pg_data]$ rsync -cva --inplace --exclude=*pg_xlog* \
>
> > ${PGDATA}/ 172.29.7.194:$PGDATA
>
> sending incremental file list
>
> ./
>
> pg_wal/archive_status/0000000100000010000000CC.done
>
> pg_wal/archive_status/0000000100000010000000CD.00000028.backup.done
>
> pg_wal/archive_status/0000000100000010000000CD.done
>
> pg_wal/archive_status/0000000100000010000000D5.done
>
> pg_xact/
>
> pg_xact/0000
>
> sent 1,053,663,924 bytes received 21,480 bytes 15,160,941.06 bytes/sec
>
> total size is 1,053,341,161 speedup is 1.00
>
> [postgres@de284194 pg_data]$
>
>
>
> Step 4:- Stop the backup
>
> [postgres@de284194 pg_data]$ psql -c "select pg_stop_backup(),
> current_timestamp"
>
> NOTICE: pg_stop_backup complete, all required WAL segments have been
> archived
>
>
>
> Step 5;- Create recovery.conf
>
>
>
>
>
> postgres=# SELECT
> pg_create_physical_replication_slot('amos_prod_replica1');
>
> pg_create_physical_replication_slot
>
> -------------------------------------
>
> (amos_prod_replica1,)
>
> (1 row)
>
>
>
>
>
>
>
> Start the db server in replica server:-
>
>
>
> Regards,
>
> Ashok
>
>
>
>
>
>
>
Did you include the replication slot name in your recovery.conf?. Also, I
would recommend using the pg_basebackup tool to build your replica vs
rsync. We've created an interactive tutorial on making streaming replicas
with replication slots here if you want to see how that can be done. This
is for PG11, but the process for 10 is the same.
https://learn.crunchydata.com/pg-administration/courses/basic-postgresql-for-dbas-11/setting-up-repl...
Keith
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help needed for replication issue
2020-04-23 06:14 Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
2020-04-23 09:00 ` Re: Help needed for replication issue Prince Pathria <prince.pathria@goevive.com>
2020-04-24 03:59 ` RE: Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
@ 2020-04-24 13:42 ` soumitra bhandary <soumitra.bhandary@hotmail.com>
2020-04-24 17:44 ` Re: Help needed for replication issue Prince Pathria <prince.pathria@goevive.com>
1 sibling, 1 reply; 9+ messages in thread
From: soumitra bhandary @ 2020-04-24 13:42 UTC (permalink / raw)
To: Ashok kumar Mani <amani@accelaero.com>; +Cc: pgsql-admin; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Ashok,
Though it is not very clear to identify the issue exactly in the environment.
Few checks you can do .
1. Instead of rsync you can use pgbasebackup -R that will write the recovery.conf with primary slot name.
2. I hope that listen address is properly set and connection between primary and slave node are ok.
3. Do some transaction and execute below query and observe any changes in GB behind
postgres=# SELECT redo_lsn, slot_name,restart_lsn,
round((redo_lsn-restart_lsn) / 1024 / 1024 / 1024, 2) AS GB_behind
FROM pg_control_checkpoint(), pg_replication_slots;
4. If slave is able to consume then this will start reducing .
If problem still persists then check in log of both and find what it suggests .
Have a great day ..
Sent from my iPhone
On 24-Apr-2020, at 9:30 AM, Ashok kumar Mani <amani@accelaero.com> wrote:
Classification: External
Hi Pathiria,
Step1:- Enable the below parameters in postgresql.conf file for hotstandby
wal_level=replica [ Default : hot_standby]
wal_log_hints = on
synchronous_commit = on
wal_log_hints = on
archive_mode = on [
archive_timeout = 30
archive_command = 'scp %p 172.30.8.199:/data/ARCHIVELOG/%f'
step 2:- #Replication
max_wal_senders = 10
wal_keep_segments = 3200 # in logfile segments, 16MB each; 0 disables[
hot_standby = on
hot_standby_feedback = on # send info from standby to prevent query conflicts
#Log file configurations
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 0
log_rotation_size = 100MB
log_timezone = 'GMT'
Step3:- backup master database then transfer into replica server by using rsync
[postgres@de284194 ~]$ psql -c "SELECT pg_start_backup('label', true)"
pg_start_backup
-----------------
10/DA000028
(1 row)
[postgres@de284194 pg_data]$ rsync -cva --inplace --exclude=*pg_xlog* \
> ${PGDATA}/ 172.29.7.194:$PGDATA
sending incremental file list
./
pg_wal/archive_status/0000000100000010000000CC.done
pg_wal/archive_status/0000000100000010000000CD.00000028.backup.done
pg_wal/archive_status/0000000100000010000000CD.done
pg_wal/archive_status/0000000100000010000000D5.done
pg_xact/
pg_xact/0000
sent 1,053,663,924 bytes received 21,480 bytes 15,160,941.06 bytes/sec
total size is 1,053,341,161 speedup is 1.00
[postgres@de284194 pg_data]$
Step 4:- Stop the backup
[postgres@de284194 pg_data]$ psql -c "select pg_stop_backup(), current_timestamp"
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
Step 5;- Create recovery.conf
postgres=# SELECT pg_create_physical_replication_slot('amos_prod_replica1');
pg_create_physical_replication_slot
-------------------------------------
(amos_prod_replica1,)
(1 row)
Start the db server in replica server:-
Regards,
Ashok
From: Prince Pathria <prince.pathria@goevive.com>
Sent: Thursday, April 23, 2020 1:00 PM
To: Ashok kumar Mani <amani@accelaero.com>
Cc: pgsql-admin@postgresql.org; pgsql-admin@lists.postgresql.org
Subject: Re: Help needed for replication issue
Information Security Email Alert: This email is from an EXTERNAL source. Please use caution when clicking on links or opening attachments from an unknown or suspicious sender. To report a suspected phishing email, Send us an Email on Servicedesk@accelaero.com<mailto:Servicedesk@accelaero.com>
Hey Ashok,
Please share the steps you've followed till now so we help further.
Thanks!
Happy to help :)
Prince Pathria Systems Engineer | Certified Kubernetes Administrator | AWS Solutions Architect Evive +91 9478670472 goevive.com<https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgoevive.com%2F&data=02%7C01%7Ca...;
On Thu, Apr 23, 2020 at 11:44 AM Ashok kumar Mani <amani@accelaero.com<mailto:amani@accelaero.com>> wrote:
Classification: Internal
Dear All,
I have created Postgresql 10.6 hot standb ,but while creating physical replication slots by default it is in inactive more
I recreated the standby but still I am unable to make slots is in active and also I could not able to see the LSN value on both sites.
Regards,
Ashok
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help needed for replication issue
2020-04-23 06:14 Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
2020-04-23 09:00 ` Re: Help needed for replication issue Prince Pathria <prince.pathria@goevive.com>
2020-04-24 03:59 ` RE: Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
2020-04-24 13:42 ` Re: Help needed for replication issue soumitra bhandary <soumitra.bhandary@hotmail.com>
@ 2020-04-24 17:44 ` Prince Pathria <prince.pathria@goevive.com>
2020-04-26 07:00 ` RE: Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
0 siblings, 1 reply; 9+ messages in thread
From: Prince Pathria @ 2020-04-24 17:44 UTC (permalink / raw)
To: soumitra bhandary <soumitra.bhandary@hotmail.com>; +Cc: Ashok kumar Mani <amani@accelaero.com>; pgsql-admin; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Go for pg_basebackup instead of rsync and you'll be happy with the results.
Happy to help :)
Prince Pathria Systems Engineer | Certified Kubernetes Administrator | AWS
Solutions Architect Evive +91 9478670472 goevive.com
On Fri, Apr 24, 2020 at 7:12 PM soumitra bhandary <
soumitra.bhandary@hotmail.com> wrote:
> Hi Ashok,
>
> Though it is not very clear to identify the issue exactly in the
> environment.
>
> Few checks you can do .
>
> 1. Instead of rsync you can use pgbasebackup -R that will write the
> recovery.conf with primary slot name.
>
> 2. I hope that listen address is properly set and connection between
> primary and slave node are ok.
>
> 3. Do some transaction and execute below query and observe any changes in
> GB behind
>
> postgres=# SELECT redo_lsn, slot_name,restart_lsn,
> round((redo_lsn-restart_lsn) / 1024 / 1024 / 1024, 2) AS GB_behind
> FROM pg_control_checkpoint(), pg_replication_slots;
>
> 4. If slave is able to consume then this will start reducing .
>
> If problem still persists then check in log of both and find what it
> suggests .
>
> Have a great day ..
>
> Sent from my iPhone
>
> On 24-Apr-2020, at 9:30 AM, Ashok kumar Mani <amani@accelaero.com> wrote:
>
>
>
> Classification: External
>
>
>
> Hi Pathiria,
>
>
>
> *Step1*:- Enable the below parameters in postgresql.conf file for
> hotstandby
>
> wal_level=replica [ Default : hot_standby]
>
> wal_log_hints = on
>
> synchronous_commit = on
>
>
>
> wal_log_hints = on
>
>
>
> archive_mode = on [
>
>
>
> archive_timeout = 30
>
>
>
> archive_command = 'scp %p 172.30.8.199:/data/ARCHIVELOG/%f'
>
>
>
>
>
>
> step 2:- #Replication
>
> max_wal_senders = 10
>
> wal_keep_segments = 3200 # in logfile segments, 16MB each; 0
> disables[
>
> hot_standby = on
>
> hot_standby_feedback = on # send info from standby to
> prevent query conflicts
>
> #Log file configurations
>
> log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
>
> log_rotation_age = 0
>
> log_rotation_size = 100MB
>
> log_timezone = 'GMT'
>
>
>
> Step3:- backup master database then transfer into replica server by using
> rsync
>
>
>
> [postgres@de284194 ~]$ psql -c "SELECT pg_start_backup('label', true)"
>
> pg_start_backup
>
> -----------------
>
> 10/DA000028
>
> (1 row)
>
>
>
> [postgres@de284194 pg_data]$ rsync -cva --inplace --exclude=*pg_xlog* \
>
> > ${PGDATA}/ 172.29.7.194:$PGDATA
>
> sending incremental file list
>
> ./
>
> pg_wal/archive_status/0000000100000010000000CC.done
>
> pg_wal/archive_status/0000000100000010000000CD.00000028.backup.done
>
> pg_wal/archive_status/0000000100000010000000CD.done
>
> pg_wal/archive_status/0000000100000010000000D5.done
>
> pg_xact/
>
> pg_xact/0000
>
> sent 1,053,663,924 bytes received 21,480 bytes 15,160,941.06 bytes/sec
>
> total size is 1,053,341,161 speedup is 1.00
>
> [postgres@de284194 pg_data]$
>
>
>
> Step 4:- Stop the backup
>
> [postgres@de284194 pg_data]$ psql -c "select pg_stop_backup(),
> current_timestamp"
>
> NOTICE: pg_stop_backup complete, all required WAL segments have been
> archived
>
>
>
> Step 5;- Create recovery.conf
>
>
>
>
>
> postgres=# SELECT
> pg_create_physical_replication_slot('amos_prod_replica1');
>
> pg_create_physical_replication_slot
>
> -------------------------------------
>
> (amos_prod_replica1,)
>
> (1 row)
>
>
>
>
>
>
>
> Start the db server in replica server:-
>
>
>
> Regards,
>
> Ashok
>
>
>
>
>
>
>
> *From:* Prince Pathria <prince.pathria@goevive.com>
> *Sent:* Thursday, April 23, 2020 1:00 PM
> *To:* Ashok kumar Mani <amani@accelaero.com>
> *Cc:* pgsql-admin@postgresql.org; pgsql-admin@lists.postgresql.org
> *Subject:* Re: Help needed for replication issue
>
>
>
> *Information Security Email Alert:* This email is from an *EXTERNAL*
> source. Please use caution when clicking on links or opening attachments
> from an unknown or suspicious sender. To report a suspected phishing email,
> Send us an Email on Servicedesk@accelaero.com
>
>
>
> Hey Ashok,
>
>
> Please share the steps you've followed till now so we help further.
>
>
>
> Thanks!
>
> Happy to help :)
>
> Prince Pathria Systems Engineer | Certified Kubernetes Administrator | AWS
> Solutions Architect Evive +91 9478670472 goevive.com
> <https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgoevive.com%2F&data=02%7C01%7Ca...;
>
>
>
>
>
> On Thu, Apr 23, 2020 at 11:44 AM Ashok kumar Mani <amani@accelaero.com>
> wrote:
>
> Classification: Internal
>
>
>
> Dear All,
>
> I have created Postgresql 10.6 hot standb ,but while creating physical
> replication slots by default it is in inactive more
>
> I recreated the standby but still I am unable to make slots is in active
> and also I could not able to see the LSN value on both sites.
>
>
>
>
>
> Regards,
>
> Ashok
>
>
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Help needed for replication issue
2020-04-23 06:14 Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
2020-04-23 09:00 ` Re: Help needed for replication issue Prince Pathria <prince.pathria@goevive.com>
2020-04-24 03:59 ` RE: Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
2020-04-24 13:42 ` Re: Help needed for replication issue soumitra bhandary <soumitra.bhandary@hotmail.com>
2020-04-24 17:44 ` Re: Help needed for replication issue Prince Pathria <prince.pathria@goevive.com>
@ 2020-04-26 07:00 ` Ashok kumar Mani <amani@accelaero.com>
0 siblings, 0 replies; 9+ messages in thread
From: Ashok kumar Mani @ 2020-04-26 07:00 UTC (permalink / raw)
To: Prince Pathria <prince.pathria@goevive.com>; soumitra bhandary <soumitra.bhandary@hotmail.com>; +Cc: pgsql-admin; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Classification: External
Hi Prince,
I dropped standby pg_data directory and took backup from primary and transferred into standby but after creating slot “abyproreplica” and created new db test10 with tables but still I can see this slot is in deactive ?
Could you please suggest further?
max_replication_slots
-----------------------
3
(1 row)
postgres=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
--------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+--------------------
-
amos_prod_replica1 | | physical | | | f | f | | | | |
abyprodreplica1 | | physical | | | f | f | | | | |
(2 rows)
postgres=#
From primary:-
postgres=# show max_replication_slots ;
max_replication_slots
-----------------------
3
(1 row)
postgres=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
--------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+--------------------
-
amos_prod_replica1 | | physical | | | f | f | | | | |
abyprodreplica1 | | physical | | | f | f | | | | |
(2 rows)
postgres=#
From: Prince Pathria <prince.pathria@goevive.com>
Sent: Friday, April 24, 2020 9:44 PM
To: soumitra bhandary <soumitra.bhandary@hotmail.com>
Cc: Ashok kumar Mani <amani@accelaero.com>; pgsql-admin@postgresql.org; pgsql-admin@lists.postgresql.org
Subject: Re: Help needed for replication issue
Information Security Email Alert: This email is from an EXTERNAL source. Please use caution when clicking on links or opening attachments from an unknown or suspicious sender. To report a suspected phishing email, Send us an Email on Servicedesk@accelaero.com<mailto:Servicedesk@accelaero.com>
Go for pg_basebackup instead of rsync and you'll be happy with the results.
Happy to help :)
Prince Pathria Systems Engineer | Certified Kubernetes Administrator | AWS Solutions Architect Evive +91 9478670472 goevive.com<https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgoevive.com%2F&data=02%7C01%7Ca...;
On Fri, Apr 24, 2020 at 7:12 PM soumitra bhandary <soumitra.bhandary@hotmail.com<mailto:soumitra.bhandary@hotmail.com>> wrote:
Hi Ashok,
Though it is not very clear to identify the issue exactly in the environment.
Few checks you can do .
1. Instead of rsync you can use pgbasebackup -R that will write the recovery.conf with primary slot name.
2. I hope that listen address is properly set and connection between primary and slave node are ok.
3. Do some transaction and execute below query and observe any changes in GB behind
postgres=# SELECT redo_lsn, slot_name,restart_lsn,
round((redo_lsn-restart_lsn) / 1024 / 1024 / 1024, 2) AS GB_behind
FROM pg_control_checkpoint(), pg_replication_slots;
4. If slave is able to consume then this will start reducing .
If problem still persists then check in log of both and find what it suggests .
Have a great day ..
Sent from my iPhone
On 24-Apr-2020, at 9:30 AM, Ashok kumar Mani <amani@accelaero.com<mailto:amani@accelaero.com>> wrote:
Classification: External
Hi Pathiria,
Step1:- Enable the below parameters in postgresql.conf file for hotstandby
wal_level=replica [ Default : hot_standby]
wal_log_hints = on
synchronous_commit = on
wal_log_hints = on
archive_mode = on [
archive_timeout = 30
archive_command = 'scp %p 172.30.8.199:/data/ARCHIVELOG/%f'
step 2:- #Replication
max_wal_senders = 10
wal_keep_segments = 3200 # in logfile segments, 16MB each; 0 disables[
hot_standby = on
hot_standby_feedback = on # send info from standby to prevent query conflicts
#Log file configurations
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 0
log_rotation_size = 100MB
log_timezone = 'GMT'
Step3:- backup master database then transfer into replica server by using rsync
[postgres@de284194 ~]$ psql -c "SELECT pg_start_backup('label', true)"
pg_start_backup
-----------------
10/DA000028
(1 row)
[postgres@de284194 pg_data]$ rsync -cva --inplace --exclude=*pg_xlog* \
> ${PGDATA}/ 172.29.7.194:$PGDATA
sending incremental file list
./
pg_wal/archive_status/0000000100000010000000CC.done
pg_wal/archive_status/0000000100000010000000CD.00000028.backup.done
pg_wal/archive_status/0000000100000010000000CD.done
pg_wal/archive_status/0000000100000010000000D5.done
pg_xact/
pg_xact/0000
sent 1,053,663,924 bytes received 21,480 bytes 15,160,941.06 bytes/sec
total size is 1,053,341,161 speedup is 1.00
[postgres@de284194 pg_data]$
Step 4:- Stop the backup
[postgres@de284194 pg_data]$ psql -c "select pg_stop_backup(), current_timestamp"
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
Step 5;- Create recovery.conf
postgres=# SELECT pg_create_physical_replication_slot('amos_prod_replica1');
pg_create_physical_replication_slot
-------------------------------------
(amos_prod_replica1,)
(1 row)
Start the db server in replica server:-
Regards,
Ashok
From: Prince Pathria <prince.pathria@goevive.com<mailto:prince.pathria@goevive.com>>
Sent: Thursday, April 23, 2020 1:00 PM
To: Ashok kumar Mani <amani@accelaero.com<mailto:amani@accelaero.com>>
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>; pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: Help needed for replication issue
Information Security Email Alert: This email is from an EXTERNAL source. Please use caution when clicking on links or opening attachments from an unknown or suspicious sender. To report a suspected phishing email, Send us an Email on Servicedesk@accelaero.com<mailto:Servicedesk@accelaero.com>
Hey Ashok,
Please share the steps you've followed till now so we help further.
Thanks!
Happy to help :)
Prince Pathria Systems Engineer | Certified Kubernetes Administrator | AWS Solutions Architect Evive +91 9478670472 goevive.com<https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgoevive.com%2F&data=02%7C01%7Ca...;
On Thu, Apr 23, 2020 at 11:44 AM Ashok kumar Mani <amani@accelaero.com<mailto:amani@accelaero.com>> wrote:
Classification: Internal
Dear All,
I have created Postgresql 10.6 hot standb ,but while creating physical replication slots by default it is in inactive more
I recreated the standby but still I am unable to make slots is in active and also I could not able to see the LSN value on both sites.
Regards,
Ashok
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Help needed for replication issue
2020-04-23 06:14 Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
@ 2020-04-26 06:22 ` Ashok kumar Mani <amani@accelaero.com>
2020-04-26 07:00 ` Re: Help needed for replication issue Olivier Gautherot <ogautherot@gautherot.net>
1 sibling, 1 reply; 9+ messages in thread
From: Ashok kumar Mani @ 2020-04-26 06:22 UTC (permalink / raw)
To: pgsql-admin; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Classification: Internal
Dear All
I am getting this below error during hot standby configuration .
Please help
[postgres@de284194 ~]$ pg_basebackup --host='172.28.4.194' --port=5432 --username=postgres --pgdata=/backup/standby
pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host "172.28.4.194", user "postgres", SSL off
pg_basebackup: removing contents of data directory "/backup/standby"
[postgres@de284194 ~]$
Regards,
Ashok
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help needed for replication issue
2020-04-23 06:14 Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
2020-04-26 06:22 ` RE: Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
@ 2020-04-26 07:00 ` Olivier Gautherot <ogautherot@gautherot.net>
0 siblings, 0 replies; 9+ messages in thread
From: Olivier Gautherot @ 2020-04-26 07:00 UTC (permalink / raw)
To: Ashok kumar Mani <amani@accelaero.com>; +Cc: pgsql-admin; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Ashok!
On Sun, Apr 26, 2020 at 8:22 AM Ashok kumar Mani <amani@accelaero.com>
wrote:
> Classification: Internal
>
>
>
> Dear All
>
> I am getting this below error during hot standby configuration .
>
>
>
> Please help
>
> [postgres@de284194 ~]$ pg_basebackup --host='172.28.4.194' --port=5432
> --username=postgres --pgdata=/backup/standby
>
> pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry
> for replication connection from host "172.28.4.194", user "postgres", SSL
> off
>
> pg_basebackup: removing contents of data directory "/backup/standby"
>
> [postgres@de284194 ~]$
>
Just follow the instructions in the message: you need to add a line to
pg_hba.conf. This is the file that controls the accesses to the database.
It may be located in your data directory (/backup/standby) or in other
places, depending on your system (by default, Ubuntu places this file by
default in /etc/postgresql/...).
If you need more support, remember to state the OS and PG version.
Hope it helps
--
Olivier Gautherot
https://www.linkedin.com/in/ogautherot/
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
Libre
de virus. www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2020-04-26 07:00 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-23 06:14 Help needed for replication issue Ashok kumar Mani <amani@accelaero.com>
2020-04-23 09:00 ` Prince Pathria <prince.pathria@goevive.com>
2020-04-24 03:59 ` Ashok kumar Mani <amani@accelaero.com>
2020-04-24 05:03 ` Keith <keith@keithf4.com>
2020-04-24 13:42 ` soumitra bhandary <soumitra.bhandary@hotmail.com>
2020-04-24 17:44 ` Prince Pathria <prince.pathria@goevive.com>
2020-04-26 07:00 ` Ashok kumar Mani <amani@accelaero.com>
2020-04-26 06:22 ` Ashok kumar Mani <amani@accelaero.com>
2020-04-26 07:00 ` Olivier Gautherot <ogautherot@gautherot.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox