Re: pg_receivewal configuration issues

Hello Experts,

please help me to find why replication slot status is showing false.

On Tue, May 7, 2019 at 4:54 PM Pavan Kumar <pavan(dot)dba27(at)gmail(dot)com> wrote:

> Hello Expert's
>
> I am using pg_receivewall feature to replicate wal segments on different
> location on same server. here is my configuration .
>
> postgres version 11.2
>
> parameters in postgresql.conf
>
> archive_mode = on
> archive_command = 'cp %p /oradbaudit/pg_archive/5400/%f'
> wal_level = replica
> max_wal_senders = 9
> max_replication_slots = 10
>
> added entry in pg_hba.conf
>
> host replication postgres all trust
>
>
>
>
> I have created physical replication slot
>
> select * from pg_create_physical_replication_slot('5400_xlog_4_recovery');
>
> executed pg_receivewal command ...
>
> pg_receivewal -D /oraworkspace/pg_stream_xlog/5400 -s 5400_xlog_4_recovery
> -h localhost -p 5400 -U postgres
>
> when i look at the status of physical replication slots, it shows inactive
> . Am i doing anything wrong?
>
> 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
>
> ----------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
> 5400_xlog_4_recovery | | physical | | | f
> | f | | | | |
> (1 row)
>
> but when i check the wal segments, those are getting created under
> replication slot location "/oraworkspace/pg_stream_xlog/5400"
>
> [postgres(at)sl73osodbd008:/oradbaudit/pg_data_dir/pg_wal ] $ psql
> psql (11.2)
> Type "help" for help.
>
> 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
>
> ----------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
> 5400_xlog_4_recovery | | physical | | | f
> | f | | | | |
> (1 row)
>
> postgres=# select pg_walfile_name(pg_switch_wal());
> pg_walfile_name
> --------------------------
> 000000020000000C0000001A
> (1 row)
>
> postgres=# \! ls -lart /oradbaudit/pg_data_dir/pg_wal/|tail -5 >>>>
> Actual wal segement locaiton
>
> -rw-------. 1 postgres postgres 16777216 May 7 21:37
> 000000020000000C00000019
> -rw-------. 1 postgres postgres 16777216 May 7 21:38
> 000000020000000C0000001A >>>>
> -rw-------. 1 postgres postgres 28810 May 7 21:38 nohup.out
> drwx------. 2 postgres postgres 199 May 7 21:38 archive_status
> -rw-------. 1 postgres postgres 16777216 May 7 21:38
> 000000020000000C0000001B >>>
> postgres=# \! ls -lart /oraworkspace/pg_stream_xlog/5400|tail -5 >>>>>
> replication wal location
> -rw-------. 1 postgres postgres 16777216 May 7 21:27
> 000000020000000C00000018
> -rw-------. 1 postgres postgres 16777216 May 7 21:37
> 000000020000000C00000019
> -rw-------. 1 postgres postgres 16777216 May 7 21:38
> 000000020000000C0000001A
> drwxr-xr-x. 2 postgres postgres 4096 May 7 21:38 .
> -rw-------. 1 postgres postgres 16777216 May 7 21:38
> 000000020000000C0000001B.partial >>>>
> postgres=#
>
>
> if replication slot is not active, how come wal segments are creating in
> replication location ?
> Am i doing anything wrong ?
>
> please advise ..
>
>
> Thank you...
>
> --
>
>
>
> *Regards,#! Pavan Kumar----------------------------------------------*-
> *Sr. Database Administrator..!*
> *NEXT GENERATION PROFESSIONALS, LLC*
> *Cell # 267-799-3182 # pavan.dba27 (Gtalk) *
> *India # 9000459083*
>
> *Take Risks; if you win, you will be very happy. If you lose you will be
> Wise *
>
>

--

*Regards,#! Pavan Kumar----------------------------------------------*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell # 267-799-3182 # pavan.dba27 (Gtalk) *
*India # 9000459083*

*Take Risks; if you win, you will be very happy. If you lose you will be
Wise *