Re: Help needed for replication issue - Mailing list pgsql-admin

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