Re: Help needed for replication issue

On Fri, Apr 24, 2020 at 12:00 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
>
>
>
>
>
>
>
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-replication/

Keith