Issue with WAL files in streaming replication

From: Leroy Tennison <leroy_tennison(at)prodigy(dot)net>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Issue with WAL files in streaming replication
Date: 2018-01-08 03:10:58
Message-ID: ce9e652d-2812-8eb9-eeaf-818185f0dd02@prodigy.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I have attempted to set up a Postgres 9.5 server as a streaming
replication backup (guessing my way through the process after reading
the Postgresql documentation and a few tutorials) but am running into an
issue.  The WAL archive files are not being removed on the backup.  I'm
wondering if everything is OK and I should have discontinued the WAL
archive process or if something else is wrong.  Thanks for any help. 
The following should be the relevant configuration.

Master:
  postgresql.conf
    listen_addresses = '*'
    wal_level = hot_standby
    archive_mode = on
    archive_command = 'ssh (IP address of slave) "test ! -f
/var/lib/postgresql/archive-dest/%f" && scp %p (IP address of
slave):/var/lib/postgresql/archive-dest/%f'
    archive_timeout = 0
    max_wal_senders = 2
    max_replication_slots = 3
  pb_hba.conf
    host    replication     postgres 127.0.0.1/32            md5
    host    replication     (user)        (IP address of
master)/32        md5
    host    replication     (user)        (IP address of
slave)/32        md5

Slave:
  postgresql.conf
    listen_addresses = '*'
    max_connections = 500
    wal_level = hot_standby
    max_wal_senders = 2
    wal_keep_segments = 10
    hot_standby = on
  pb_hba.conf
    host    replication     postgres 127.0.0.1/32            md5
    host    replication     (user)        (IP address of
master)/32        md5
    host    replication     (user)        (IP address of
slave)/32        md5

What I'm seeing is

Master:
  ps -ef | grep post
    .
    .
    .
    postgres  1659  1294  0  2017 ?        00:01:57 postgres: wal
writer process
    .
    postgres  1661  1294  0  2017 ?        00:00:02 postgres: archiver
process   last was 0000000100000017000000DC
    .
    .
    postgres 12186  1294  0  2017 ?        00:03:40 postgres: wal
sender process (user) (IP address of slave)(33872) streaming 17/DDA464A8
  ls -al /var/lib/postgresql/9.5/main/pg_xlog
    (earliest is) -rw-------  1 postgres postgres 16777216 Jan  5 06:44
0000000100000017000000DE
    .
    .
    (latest is) -rw-------  1 postgres postgres 16777216 Jan  5 16:15
0000000100000017000000DD
  psql -c 'table pg_stat_replication;'
    12186 | 17760 | (user) | walreceiver | (IP address of slave) | |
33872 | 2017-12-29 13:01:36.740747-06 | | streaming | 17/DDA464A8 |
17/DDA464A8 | 17/DDA464A8 | 17/DDA464A8 | 0 | async

Slave:
  ps -ef | grep post
    .
    .
    postgres 23909 23908  0  2017 ?        00:01:05 postgres: startup
process   recovering 0000000100000017000000DD
    .
    postgres 23913 23908  0  2017 ?        00:07:56 postgres: wal
receiver process   streaming 17/DDA464A8
  ls -al /var/lib/postgresql/(archive target directory)
    (earliest is almost 5 days ago, 31 total: earliest to latest)
    .
    (latest is) -rw------- 1 postgres postgres 16777216 Jan  5 14:35
0000000100000017000000DC
  psql -c 'pg_is_in_recovery();'
     pg_is_in_recovery
    -------------------
    t
    (1 row)
  psql -c 'select now() - pg_last_xact_replay_timestamp() AS
replication_delay;'
     replication_delay
    -------------------
    00:00:05.842482
    (1 row)

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Shreeyansh Dba 2018-01-08 05:56:14 Re: Issue with WAL files in streaming replication
Previous Message Rui DeSousa 2018-01-06 19:59:04 Re: Ideas to deal with table corruption