Обсуждение: pg_receivewal configuration issues
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)
#! 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
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
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.dba27@gmail.com> wrote:
Hello Expert'sI am using pg_receivewall feature to replicate wal segments on different location on same server. here is my configuration .postgres version 11.2parameters in postgresql.confarchive_mode = onarchive_command = 'cp %p /oradbaudit/pg_archive/5400/%f'wal_level = replicamax_wal_senders = 9max_replication_slots = 10added entry in pg_hba.confhost replication postgres all trustI have created physical replication slotselect * 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 postgreswhen 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 ] $ psqlpsql (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.outdrwx------. 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 000000020000000C0000001Adrwxr-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 # 9000459083Take 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)
#! 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
On May 9, 2019, at 8:34 AM, Pavan Kumar <pavan.dba27@gmail.com> 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