Why Is My pg_xlog Directory So Big?

I am running a PostgreSQL 9.5 datbase. There is a master and a slave that is configured as hot_standby using streaming replication (I don't think this is related).

On the master I have the following wal settings:

postgres=# select name, setting, unit from pg_settings where name like '%wal_%';

             name             |   setting   | unit

------------------------------+-------------+------

 max_wal_senders              | 5           |

 max_wal_size                 | 128         | 16MB

 min_wal_size                 | 64          | 16MB

 wal_block_size               | 8192        |

 wal_buffers                  | 983         | 8kB

 wal_compression              | off         |

 wal_keep_segments            | 10          |

 wal_level                    | hot_standby |

 wal_log_hints                | off         |

 wal_receiver_status_interval | 10          | s

 wal_receiver_timeout         | 60000       | ms

 wal_retrieve_retry_interval  | 5000        | ms

 wal_segment_size             | 2048        | 8kB

 wal_sender_timeout           | 60000       | ms

 wal_sync_method              | fdatasync   |

 wal_writer_delay             | 200         | ms

(16 rows)

I guess the pg_xlog directory should grow to be 2GB max (max_wal_size = 128 * 16MB = 2GB), but it is 5GB now.

I don't understand why.

edited May 2 '18 at 7:46

hot2use

8,11952055

asked May 2 '18 at 6:42

Simon

1011

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment |

I am running a PostgreSQL 9.5 datbase. There is a master and a slave that is configured as hot_standby using streaming replication (I don't think this is related).

On the master I have the following wal settings:

postgres=# select name, setting, unit from pg_settings where name like '%wal_%';

             name             |   setting   | unit

------------------------------+-------------+------

 max_wal_senders              | 5           |

 max_wal_size                 | 128         | 16MB

 min_wal_size                 | 64          | 16MB

 wal_block_size               | 8192        |

 wal_buffers                  | 983         | 8kB

 wal_compression              | off         |

 wal_keep_segments            | 10          |

 wal_level                    | hot_standby |

 wal_log_hints                | off         |

 wal_receiver_status_interval | 10          | s

 wal_receiver_timeout         | 60000       | ms

 wal_retrieve_retry_interval  | 5000        | ms

 wal_segment_size             | 2048        | 8kB

 wal_sender_timeout           | 60000       | ms

 wal_sync_method              | fdatasync   |

 wal_writer_delay             | 200         | ms

(16 rows)

I guess the pg_xlog directory should grow to be 2GB max (max_wal_size = 128 * 16MB = 2GB), but it is 5GB now.

I don't understand why.

edited May 2 '18 at 7:46

hot2use

8,11952055

asked May 2 '18 at 6:42

Simon

1011

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment |

I am running a PostgreSQL 9.5 datbase. There is a master and a slave that is configured as hot_standby using streaming replication (I don't think this is related).

On the master I have the following wal settings:

postgres=# select name, setting, unit from pg_settings where name like '%wal_%';

             name             |   setting   | unit

------------------------------+-------------+------

 max_wal_senders              | 5           |

 max_wal_size                 | 128         | 16MB

 min_wal_size                 | 64          | 16MB

 wal_block_size               | 8192        |

 wal_buffers                  | 983         | 8kB

 wal_compression              | off         |

 wal_keep_segments            | 10          |

 wal_level                    | hot_standby |

 wal_log_hints                | off         |

 wal_receiver_status_interval | 10          | s

 wal_receiver_timeout         | 60000       | ms

 wal_retrieve_retry_interval  | 5000        | ms

 wal_segment_size             | 2048        | 8kB

 wal_sender_timeout           | 60000       | ms

 wal_sync_method              | fdatasync   |

 wal_writer_delay             | 200         | ms

(16 rows)

I guess the pg_xlog directory should grow to be 2GB max (max_wal_size = 128 * 16MB = 2GB), but it is 5GB now.

I don't understand why.

edited May 2 '18 at 7:46

hot2use

8,11952055

asked May 2 '18 at 6:42

Simon

1011

I am running a PostgreSQL 9.5 datbase. There is a master and a slave that is configured as hot_standby using streaming replication (I don't think this is related).

On the master I have the following wal settings:

postgres=# select name, setting, unit from pg_settings where name like '%wal_%';

             name             |   setting   | unit

------------------------------+-------------+------

 max_wal_senders              | 5           |

 max_wal_size                 | 128         | 16MB

 min_wal_size                 | 64          | 16MB

 wal_block_size               | 8192        |

 wal_buffers                  | 983         | 8kB

 wal_compression              | off         |

 wal_keep_segments            | 10          |

 wal_level                    | hot_standby |

 wal_log_hints                | off         |

 wal_receiver_status_interval | 10          | s

 wal_receiver_timeout         | 60000       | ms

 wal_retrieve_retry_interval  | 5000        | ms

 wal_segment_size             | 2048        | 8kB

 wal_sender_timeout           | 60000       | ms

 wal_sync_method              | fdatasync   |

 wal_writer_delay             | 200         | ms

(16 rows)

I guess the pg_xlog directory should grow to be 2GB max (max_wal_size = 128 * 16MB = 2GB), but it is 5GB now.

I don't understand why.

postgresql postgresql-9.5

edited May 2 '18 at 7:46

hot2use

8,11952055

asked May 2 '18 at 6:42

Simon

1011

edited May 2 '18 at 7:46

hot2use

8,11952055

asked May 2 '18 at 6:42

Simon

1011

edited May 2 '18 at 7:46

hot2use

8,11952055

edited May 2 '18 at 7:46

hot2use

8,11952055

edited May 2 '18 at 7:46

hot2use

8,11952055

asked May 2 '18 at 6:42

Simon

1011

asked May 2 '18 at 6:42

Simon

1011

asked May 2 '18 at 6:42

Simon

1011

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment |

1 Answer
1

active

oldest

votes

I had streaming replication set up using a replication slot, but the slave did not use the slot.
Writing

primary_slot_name = 'replication_slot_1'

in /var/lib/postgresql/9.5/main/recovery.conf on the slave solved the problem.

answered May 2 '18 at 7:29

Simon

1011

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f205523%2fwhy-is-my-pg-xlog-directory-so-big%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

I had streaming replication set up using a replication slot, but the slave did not use the slot.
Writing

primary_slot_name = 'replication_slot_1'

in /var/lib/postgresql/9.5/main/recovery.conf on the slave solved the problem.

answered May 2 '18 at 7:29

Simon

1011

add a comment |

I had streaming replication set up using a replication slot, but the slave did not use the slot.
Writing

primary_slot_name = 'replication_slot_1'

in /var/lib/postgresql/9.5/main/recovery.conf on the slave solved the problem.

answered May 2 '18 at 7:29

Simon

1011

add a comment |

I had streaming replication set up using a replication slot, but the slave did not use the slot.
Writing

primary_slot_name = 'replication_slot_1'

in /var/lib/postgresql/9.5/main/recovery.conf on the slave solved the problem.

answered May 2 '18 at 7:29

Simon

1011

I had streaming replication set up using a replication slot, but the slave did not use the slot.
Writing

primary_slot_name = 'replication_slot_1'

in /var/lib/postgresql/9.5/main/recovery.conf on the slave solved the problem.

answered May 2 '18 at 7:29

Simon

1011

answered May 2 '18 at 7:29

Simon

1011

answered May 2 '18 at 7:29

Simon

1011

answered May 2 '18 at 7:29

Simon

1011

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Database Administrators Stack Exchange!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vryjdfkk