Search Postgresql Archives

Re: Streaming replication - 11.5

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 3/11/20 11:59 AM, Nicola Contu wrote:
I am actually cascading.
The master is in nyh, the first slave is in Dallas and the one having problems is in Dallas as well on the same switch of the one replicating from the master.

It always worked not sure what is wrong now. We just encrypted disks on all servers

Do you have before and after on CPU load, I/O throughput?

Do system logs show anything relevant during replication drop out?



Il mer 11 mar 2020, 18:57 Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> ha scritto:

    On 3/11/20 2:54 AM, Nicola Contu wrote:
     > These are the lines before
     >
     > 2020-03-11 09:05:08 GMT [127.0.0.1(40214)] [43853]: [1-1]
     > db=cmdv3,user=zabbix_check ERROR:  recovery is in progress
     > 2020-03-11 09:05:08 GMT [127.0.0.1(40214)] [43853]: [2-1]
     > db=cmdv3,user=zabbix_check HINT:  WAL control functions cannot be
     > executed during recovery.
     > 2020-03-11 09:05:08 GMT [127.0.0.1(40214)] [43853]: [3-1]
     > db=cmdv3,user=zabbix_check STATEMENT:  select
     > greatest(0,pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) from
     > pg_stat_replication where client_addr ='10.150.20.22'
     >
     > That query is made by Zabbix. So I stopped the zabbix agent and
    tested
     > again. But still failing, because of this now :
     >
     > pg_basebackup: starting background WAL receiver
     > pg_basebackup: created temporary replication slot
    "pg_basebackup_51199"
     > *pg_basebackup: could not receive data from WAL stream: SSL SYSCALL
     > error: EOF detected
     > *^C4699810/504983062 kB (70%), 0/1 tablespace
     > (...ql11/data/base/16401/231363544.2)

    So you started over with a pg_basebackup?

    Also from below:

    2020-03-11 09:43:53 GMT [10.150.20.22(54906)] [51199]: [1-1]
    db=[unknown],user=replicator LOG:  terminating walsender process due to
    replication timeout

    Where are the master and standby in relation to each other network wise?

    Intervening firewalls, network latency issues?



     >
     >
     > here the full log starting right before the last try :
     >
     > 2020-03-11 09:22:44 GMT [] [12598]: [4508-1] db=,user= LOG:
     >   restartpoint complete: wrote 19565 buffers (0.2%); 0 WAL file(s)
     > added, 0 removed, 7 recycled; write=270.014 s, sync=0.009 s,
     > total=270.036 s; sync files=804, longest=0.001 s, average=0.000 s;
     > distance=131239 kB, estimate=725998 kB
     > 2020-03-11 09:22:44 GMT [] [12598]: [4509-1] db=,user= LOG:
      recovery
     > restart point at 643A/D8C05F70
     > 2020-03-11 09:22:44 GMT [] [12598]: [4510-1] db=,user= DETAIL:  Last
     > completed transaction was at log time 2020-03-11 09:22:44.050084+00.
     > 2020-03-11 09:23:14 GMT [] [12598]: [4511-1] db=,user= LOG:
     >   restartpoint starting: time
     > 2020-03-11 09:27:44 GMT [] [12598]: [4512-1] db=,user= LOG:
     >   restartpoint complete: wrote 17069 buffers (0.2%); 0 WAL file(s)
     > added, 0 removed, 17 recycled; write=269.879 s, sync=0.006 s,
     > total=269.902 s; sync files=811, longest=0.001 s, average=0.000 s;
     > distance=120469 kB, estimate=665445 kB
     > 2020-03-11 09:27:44 GMT [] [12598]: [4513-1] db=,user= LOG:
      recovery
     > restart point at 643A/E01AB438
     > 2020-03-11 09:27:44 GMT [] [12598]: [4514-1] db=,user= DETAIL:  Last
     > completed transaction was at log time 2020-03-11 09:27:43.945485+00.
     > 2020-03-11 09:27:44 GMT [] [12598]: [4515-1] db=,user= LOG:
     >   restartpoint starting: force wait
     > 2020-03-11 09:29:24 GMT [10.222.8.2(47834)] [50961]: [1-1]
     > db=cmdv3,user=nis LOG:  duration: 1402.004 ms  statement: SELECT id,
     > name, parent_id, parent, short_name, sales_rep_id FROM mmx_clients;
     > 2020-03-11 09:29:34 GMT [10.222.8.2(47834)] [50961]: [2-1]
     > db=cmdv3,user=nis LOG:  duration: 9493.259 ms  statement: SELECT
    slid,
     > gnid, sof_id, client_id, product FROM mmx_slids;
     > 2020-03-11 09:32:14 GMT [] [12598]: [4516-1] db=,user= LOG:
     >   restartpoint complete: wrote 71260 buffers (0.8%); 0 WAL file(s)
     > added, 0 removed, 13 recycled; write=269.953 s, sync=0.012 s,
     > total=269.979 s; sync files=760, longest=0.002 s, average=0.000 s;
     > distance=123412 kB, estimate=611242 kB
     > 2020-03-11 09:32:14 GMT [] [12598]: [4517-1] db=,user= LOG:
      recovery
     > restart point at 643A/E7A30498
     > 2020-03-11 09:32:14 GMT [] [12598]: [4518-1] db=,user= DETAIL:  Last
     > completed transaction was at log time 2020-03-11 09:32:13.916101+00.
     > 2020-03-11 09:32:44 GMT [] [12598]: [4519-1] db=,user= LOG:
     >   restartpoint starting: time
     > 2020-03-11 09:37:14 GMT [] [12598]: [4520-1] db=,user= LOG:
     >   restartpoint complete: wrote 27130 buffers (0.3%); 0 WAL file(s)
     > added, 0 removed, 12 recycled; write=270.026 s, sync=0.007 s,
     > total=270.052 s; sync files=814, longest=0.001 s, average=0.000 s;
     > distance=280595 kB, estimate=578177 kB
     > 2020-03-11 09:37:14 GMT [] [12598]: [4521-1] db=,user= LOG:
      recovery
     > restart point at 643A/F8C351C8
     > 2020-03-11 09:37:14 GMT [] [12598]: [4522-1] db=,user= DETAIL:  Last
     > completed transaction was at log time 2020-03-11 09:37:14.067443+00.
     > 2020-03-11 09:37:44 GMT [] [12598]: [4523-1] db=,user= LOG:
     >   restartpoint starting: time
     > 2020-03-11 09:42:14 GMT [] [12598]: [4524-1] db=,user= LOG:
     >   restartpoint complete: wrote 26040 buffers (0.3%); 0 WAL file(s)
     > added, 0 removed, 9 recycled; write=269.850 s, sync=0.019 s,
     > total=269.886 s; sync files=834, longest=0.002 s, average=0.000 s;
     > distance=236392 kB, estimate=543999 kB
     > 2020-03-11 09:42:14 GMT [] [12598]: [4525-1] db=,user= LOG:
      recovery
     > restart point at 643B/730F3F8
     > 2020-03-11 09:42:14 GMT [] [12598]: [4526-1] db=,user= DETAIL:  Last
     > completed transaction was at log time 2020-03-11 09:42:13.900088+00.
     > 2020-03-11 09:42:44 GMT [] [12598]: [4527-1] db=,user= LOG:
     >   restartpoint starting: time
     > 2020-03-11 09:43:53 GMT [10.150.20.22(54906)] [51199]: [1-1]
     > db=[unknown],user=replicator LOG:  terminating walsender process
    due to
     > replication timeout
     > 2020-03-11 09:47:14 GMT [] [12598]: [4528-1] db=,user= LOG:
     >   restartpoint complete: wrote 20966 buffers (0.2%); 0 WAL file(s)
     > added, 0 removed, 9 recycled; write=270.048 s, sync=0.014 s,
     > total=270.085 s; sync files=852, longest=0.001 s, average=0.000 s;
     > distance=183749 kB, estimate=507974 kB
     > 2020-03-11 09:47:14 GMT [] [12598]: [4529-1] db=,user= LOG:
      recovery
     > restart point at 643B/12680A80
     > 2020-03-11 09:47:14 GMT [] [12598]: [4530-1] db=,user= DETAIL:  Last
     > completed transaction was at log time 2020-03-11 09:47:14.069731+00.
     > 2020-03-11 09:47:44 GMT [] [12598]: [4531-1] db=,user= LOG:
     >   restartpoint starting: time
     >
     >
     >
     > Il giorno mer 11 mar 2020 alle ore 01:53 Adrian Klaver
     > <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
    <mailto:adrian.klaver@xxxxxxxxxxx
    <mailto:adrian.klaver@xxxxxxxxxxx>>> ha scritto:
     >
     >     On 3/10/20 8:17 AM, Nicola Contu wrote:
     >     Please post to list also.
     >     Ccing list.
     >
     >     What came immediately before the temporary file error?
     >
     >      >    2020-03-10 15:10:17 GMT [[local]] [28171]: [1-1]
     >      > db=postgres,user=postgres LOG:  temporary file: path
     >      > "base/pgsql_tmp/pgsql_tmp28171.0", size 382474936
     >      > 2020-03-10 15:10:17 GMT [[local]] [28171]: [4-1]
     >      > db=postgres,user=postgres LOG:  could not send data to client:
     >     Broken pipe
     >      > 2020-03-10 15:10:17 GMT [[local]] [28171]: [5-1]
     >      > db=postgres,user=postgres FATAL:  connection to client lost
     >      > 2020-03-10 15:10:26 GMT [] [12598]: [3544-1] db=,user= LOG:
     >      >   restartpoint complete: wrote 37315 buffers (0.4%); 0 WAL
    file(s)
     >      > added, 0 removed, 16 recycled; write=269.943 s, sync=0.039 s,
     >      > total=269.999 s; sync files=1010, longest=0.001 s,
    average=0.000 s;
     >      > distance=175940 kB, estimate=416149 kB
     >      > 2020-03-10 15:10:26 GMT [] [12598]: [3545-1] db=,user= LOG:
     >       recovery
     >      > restart point at 6424/1D7DEDE8
     >      >
     >      > It is a cascade replication
     >      >
     >      > Il giorno mar 10 mar 2020 alle ore 15:58 Adrian Klaver
     >      > <adrian.klaver@xxxxxxxxxxx
    <mailto:adrian.klaver@xxxxxxxxxxx> <mailto:adrian.klaver@xxxxxxxxxxx
    <mailto:adrian.klaver@xxxxxxxxxxx>>
     >     <mailto:adrian.klaver@xxxxxxxxxxx
    <mailto:adrian.klaver@xxxxxxxxxxx>
     >     <mailto:adrian.klaver@xxxxxxxxxxx
    <mailto:adrian.klaver@xxxxxxxxxxx>>>> ha scritto:
     >      >
     >      >     On 3/10/20 2:26 AM, Nicola Contu wrote:
     >      >      > Hello,
     >      >      > I have two servers connected to the same switch running
     >     postgres 11.5
     >      >      >
     >      >      > I am trying to replicate one of those servers after
    a planned
     >      >     work on
     >      >      > the master, so the replica has been lost. It has
    always worked
     >      >     but now I
     >      >      > get this :
     >      >      >
     >      >      > pg_basebackup: could not receive data from WAL
    stream: server
     >      >     closed the
     >      >      > connection unexpectedly
     >      >      >          This probably means the server terminated
    abnormally
     >      >      >          before or while processing the request.
     >      >      >
     >      >      > I don't really understand what the issue is.
     >      >
     >      >     I would start with the logs from the Postgres server
    you are
     >     taking the
     >      >     backup from.
     >      >
     >      >      > I had this issue last week as well in another DC
    and I had to
     >      >     reboot the
     >      >      > slave to make it working (not sure why it helped)
     >      >      >
     >      >      > Do you know what can cause this?
     >      >      >
     >      >      > Thank you,
     >      >      > Nicola
     >      >
     >      >
     >      >     --
     >      >     Adrian Klaver
     >      > adrian.klaver@xxxxxxxxxxx
    <mailto:adrian.klaver@xxxxxxxxxxx> <mailto:adrian.klaver@xxxxxxxxxxx
    <mailto:adrian.klaver@xxxxxxxxxxx>>
     >     <mailto:adrian.klaver@xxxxxxxxxxx
    <mailto:adrian.klaver@xxxxxxxxxxx> <mailto:adrian.klaver@xxxxxxxxxxx
    <mailto:adrian.klaver@xxxxxxxxxxx>>>
     >      >
     >
     >
     >     --
     >     Adrian Klaver
     > adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
    <mailto:adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>>
     >


-- Adrian Klaver
    adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>



--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux