0

I am using a replication slot, streaming replication for a standby server. pg_xlog file is growing crazy. I have checked max_wal_size and wal_keep_segment configurations, they all looked reasonable. Is there a way to removed applied logs ? 

# - Checkpoints -
#checkpoint_timeout = 5min              # range 30s-1h
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_completion_target = 0.7     
# checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s               
# 0 disables

and when i try too see checkpoint_segments;

postgres=# show checkpoint_segments;
ERROR:  unrecognized configuration parameter "checkpoint_segments"

last logs from the standby might be relevant; 

    2017-03-08 15:13:18 +03 [43578-3] postgres@e ERROR:  current
transaction is aborted, commands ignored until end of transaction block
2017-03-08 15:13:18 +03 [43578-4] postgres@e STATEMENT:  SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'  WHE$
hece
  • 364
  • 2
  • 15
  • 1
    Is the standby up to date? Are there replication slots defined but nobody is connected? This could keep WAL from being recycled. – Laurenz Albe Mar 07 '17 at 14:14
  • yes it is up to date and only one standby server is connected to the one replication slot. – hece Mar 07 '17 at 14:21
  • 1
    Problems with `archive_command`? Anything in the log? – Laurenz Albe Mar 07 '17 at 15:06
  • both in the master and replica server archive_command and archive_mode parameters are commented out. Where can I check the log for it ? – hece Mar 08 '17 at 10:54
  • 1
    So archiving is ruled out. What is the value of `checkpoint_segments`? Concerning the log, I'd just like you to read the log file and see if there are any messages that might explain what is going on. – Laurenz Albe Mar 08 '17 at 11:22
  • I added all my parameters about checkpoints to the question. Also if you like to get more background, I had followed this tutorial to implement the replication, [link](https://www.itenlight.com/blog/2016/05/19/PostgreSQL+HA+with+pgpool-II+-+Part+2) – hece Mar 08 '17 at 11:36
  • You used the postgresql-9.5 tag, but it looks like you have version 9.6. Please clarify. – Laurenz Albe Mar 08 '17 at 11:51
  • I am using 9.5. Why did you think that it is 9.6? Is there a problem in configurations ? – hece Mar 08 '17 at 12:18
  • `checkpoint_segments` was present in 9.5 but removed in 9.6 (replaced with `min_wal_size` and `max_wal_size`). The error message claiming that this value does not exist and the claim to be using 9.5 are mutually incompatible. – Charles Duffy Mar 08 '17 at 13:16
  • my vesion is [pqsl --version] (PostgreSQL) 9.5.5. I have also checked the release notes. [link](https://www.postgresql.org/docs/9.5/static/release-9-5.html) I believe this implies that checkpoint_segments had replaced before 9.5.5. – hece Mar 08 '17 at 13:30
  • Sorry, my mistake. `checkpoint_segments` was retired in 9.5. – Laurenz Albe Mar 08 '17 at 14:21
  • 1
    Concerning your problem: If it is no archiving problem, no lagging standby, no orphaned replication slot, no excessively large `max_wal_size`, then I'm out of options. – Laurenz Albe Mar 08 '17 at 14:25
  • Thanks a lot anyway, i will try to understand a little bit more. – hece Mar 08 '17 at 14:34

0 Answers0