Re: Help needed for replication issue

From: soumitra bhandary <soumitra(dot)bhandary(at)hotmail(dot)com>
To: Ashok kumar Mani <amani(at)accelaero(dot)com>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>, "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Re: Help needed for replication issue
Date: 2020-04-24 13:42:34
Message-ID: MA1PR01MB063479CD311F4DDDB6763CD8E0D00@MA1PR01MB0634.INDPRD01.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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(at)accelaero(dot)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(at)de284194 ~]$ psql -c "SELECT pg_start_backup('label', true)"

pg_start_backup

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

10/DA000028

(1 row)

[postgres(at)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(at)de284194 pg_data]$

Step 4:- Stop the backup

[postgres(at)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(dot)pathria(at)goevive(dot)com>
Sent: Thursday, April 23, 2020 1:00 PM
To: Ashok kumar Mani <amani(at)accelaero(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org; pgsql-admin(at)lists(dot)postgresql(dot)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(at)accelaero(dot)com<mailto:Servicedesk(at)accelaero(dot)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%7Camani%40accelaero.com%7Cc6d76fafc24c46d47b7608d7e764cbf6%7C7bbb8a4d7f1b4a589b3ac250fb8023e3%7C0%7C0%7C637232292431154746&sdata=8GpQu9mi3P79nDIBFMcYyHb%2BAVcY0ltD3nc89I0z%2FSo%3D&reserved=0>

On Thu, Apr 23, 2020 at 11:44 AM Ashok kumar Mani <amani(at)accelaero(dot)com<mailto:amani(at)accelaero(dot)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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laurenz Albe 2020-04-24 14:50:30 Re: Exporting TBs of data in Oracle schema/database into CSV format to migrate into PostgreSQL
Previous Message Avinash Kumar 2020-04-24 13:35:13 Re: Exporting TBs of data in Oracle schema/database into CSV format to migrate into PostgreSQL