pg_receivewal configuration issues

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_receivewal configuration issues

Pavan Kumar-3
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@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  
Reply | Threaded
Open this post in threaded view
|

Re: pg_receivewal configuration issues

Laurenz Albe
Pavan Kumar wrote:
> 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?

It should be "-S" (upper case s).

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: pg_receivewal configuration issues

Pavan Kumar-3
In reply to this post by Pavan Kumar-3
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 <[hidden email]> 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@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  
Reply | Threaded
Open this post in threaded view
|

Re: pg_receivewal configuration issues

Rui DeSousa


On May 9, 2019, at 8:34 AM, Pavan Kumar <[hidden email]> wrote:

pg_receivewal -D /oraworkspace/pg_stream_xlog/5400 -s 5400_xlog_4_recovery -h localhost -p 5400 -U postgres


The command is using the wrong parameter.  Use "--slot=“ or “-S” not “-s”.

  -s, --status-interval=SEC  time between status packets sent to server (default: 10)
  -S, --slot=SLOTNAME    replication slot to use