pg_receivewal configuration issues

From: Pavan Kumar <pavan(dot)dba27(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: pg_receivewal configuration issues
Date: 2019-05-07 21:54:24
Message-ID: CA+M0sHHZq0CPqUWA2rUuSR6KUhVC2tCEATvGLYgMUwxPSCn8HA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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 *

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laurenz Albe 2019-05-07 23:33:11 Re: pg_receivewal configuration issues
Previous Message Keith Fiske 2019-05-07 17:58:46 Re: Re: Upgrade from psql version 9.3 to 9.6.