Repmgr: Error to promote Standby Cluster to Master Cluster in failover

I maked the configuration of the failover system based in Repmgr, but i don't haved success to promote the Standby Cluster to Master Cluster in case of failover.

Following my configurations, this process shoulded happens automatically, but does not works.

Additional Information:

Debian 9.2, PostgresSQL 9.6, Repmgr 3.3

IP Master (host1): 10.0.0.1, IP Standby (host2): 10.0.0.2

Contents included in /etc/postgresql/9.6/main/postgresql.conf, the same for all

wal_keep_segments = 5000

hot_standby = on

archive_mode = on

listen_addresses = '*'

max_wal_senders = 18

wal_level = hot_standby

shared_preload_libraries = 'repmgr_funcs'

archive_command = 'cd .'

Contents of /etc/postgresql/9.6/main/pg_hba.conf

Master

    local   all             postgres                                peer

    local   all             all                                     peer

    host    all             all             127.0.0.1/32            trust

    host    all             all             ::1/128                 md5

    host    repmgr          repmgr          10.0.0.2/32             trust

    host    replication     repmgr          10.0.0.2/32             trust

Standby

    local   all             postgres                                peer

    local   all             all                                     peer

    host    all             all             127.0.0.1/32            trust

    host    all             all             ::1/128                 md5

    host    repmgr          repmgr          10.0.0.1/32             trust

    host    replication     repmgr          10.0.0.1/32             trust

Contents of /etc/repmgr.conf

Master

    cluster=cluster

    node=1

    node_name=host1

    conninfo='host=host1 user=repmgr dbname=repmgr connect_timeout=2'

    failover=automatic

    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'

    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'

    logfile='/var/log/postgresql/repmgr.log'

    loglevel=NOTICE

    reconnect_attempts=4

    reconnect_interval=5

Standby

    cluster=cluster

    node=2

    node_name=host2

    conninfo='host=host2 user=repmgr dbname=repmgr connect_timeout=2'

    failover=automatic

    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'

    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'

    logfile='/var/log/postgresql/repmgr.log'

    loglevel=NOTICE

    reconnect_attempts=4

    reconnect_interval=5

Contents included in /etc/default/repmgrd, the same for all
```
REPMGRD_ENABLED=yes

REPMGRD_CONF="/etc/repmgr.conf"
```

Result of repmgr cluster show command

postgres@host1:~$ repmgr cluster show

Role      | Name   | Upstream | Connection String

----------+--------|----------|--------------------------------------------------------

* master  | host1  |          | host=host1 user=repmgr dbname=repmgr connect_timeout=2

  standby | host2  | host1    | host=host2 user=repmgr dbname=repmgr connect_timeout=2

Error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd"

[ERROR] unable to retrieve record for node 1: ERROR: relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo, slot_name, priority, active FROM "repmgr_cl...

Complete Tutorial: https://medium.com/@victor.boissiere/how-to-setup-postgresql-cluster-with-repmgr-febc2f10c243

edited Sep 10 '18 at 17:36

asked Sep 6 '18 at 15:32

Alessandro Cardoso

112

bumped to the homepage by Community♦ 5 mins ago

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

What does the show command show if you shit down master and what errors do you get when you failover manually.

– eckes
Sep 6 '18 at 23:06

error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd" [ERROR] unable to retrieve record for node 1: ERROR: relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo, slot_name, priority, active FROM "repmgr_cl...

– Alessandro Cardoso
Sep 10 '18 at 13:44

That looks like an important info, I would add it untruncated to the question

– eckes
Sep 10 '18 at 17:09

add a comment |

I maked the configuration of the failover system based in Repmgr, but i don't haved success to promote the Standby Cluster to Master Cluster in case of failover.

Following my configurations, this process shoulded happens automatically, but does not works.

Additional Information:

Debian 9.2, PostgresSQL 9.6, Repmgr 3.3

IP Master (host1): 10.0.0.1, IP Standby (host2): 10.0.0.2

Contents included in /etc/postgresql/9.6/main/postgresql.conf, the same for all

wal_keep_segments = 5000

hot_standby = on

archive_mode = on

listen_addresses = '*'

max_wal_senders = 18

wal_level = hot_standby

shared_preload_libraries = 'repmgr_funcs'

archive_command = 'cd .'

Contents of /etc/postgresql/9.6/main/pg_hba.conf

Master

    local   all             postgres                                peer

    local   all             all                                     peer

    host    all             all             127.0.0.1/32            trust

    host    all             all             ::1/128                 md5

    host    repmgr          repmgr          10.0.0.2/32             trust

    host    replication     repmgr          10.0.0.2/32             trust

Standby

    local   all             postgres                                peer

    local   all             all                                     peer

    host    all             all             127.0.0.1/32            trust

    host    all             all             ::1/128                 md5

    host    repmgr          repmgr          10.0.0.1/32             trust

    host    replication     repmgr          10.0.0.1/32             trust

Contents of /etc/repmgr.conf

Master

    cluster=cluster

    node=1

    node_name=host1

    conninfo='host=host1 user=repmgr dbname=repmgr connect_timeout=2'

    failover=automatic

    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'

    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'

    logfile='/var/log/postgresql/repmgr.log'

    loglevel=NOTICE

    reconnect_attempts=4

    reconnect_interval=5

Standby

    cluster=cluster

    node=2

    node_name=host2

    conninfo='host=host2 user=repmgr dbname=repmgr connect_timeout=2'

    failover=automatic

    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'

    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'

    logfile='/var/log/postgresql/repmgr.log'

    loglevel=NOTICE

    reconnect_attempts=4

    reconnect_interval=5

Contents included in /etc/default/repmgrd, the same for all
```
REPMGRD_ENABLED=yes

REPMGRD_CONF="/etc/repmgr.conf"
```

Result of repmgr cluster show command

postgres@host1:~$ repmgr cluster show

Role      | Name   | Upstream | Connection String

----------+--------|----------|--------------------------------------------------------

* master  | host1  |          | host=host1 user=repmgr dbname=repmgr connect_timeout=2

  standby | host2  | host1    | host=host2 user=repmgr dbname=repmgr connect_timeout=2

Error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd"

[ERROR] unable to retrieve record for node 1: ERROR: relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo, slot_name, priority, active FROM "repmgr_cl...

Complete Tutorial: https://medium.com/@victor.boissiere/how-to-setup-postgresql-cluster-with-repmgr-febc2f10c243

edited Sep 10 '18 at 17:36

asked Sep 6 '18 at 15:32

Alessandro Cardoso

112

bumped to the homepage by Community♦ 5 mins ago

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

What does the show command show if you shit down master and what errors do you get when you failover manually.

– eckes
Sep 6 '18 at 23:06

error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd" [ERROR] unable to retrieve record for node 1: ERROR: relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo, slot_name, priority, active FROM "repmgr_cl...

– Alessandro Cardoso
Sep 10 '18 at 13:44

That looks like an important info, I would add it untruncated to the question

– eckes
Sep 10 '18 at 17:09

add a comment |

I maked the configuration of the failover system based in Repmgr, but i don't haved success to promote the Standby Cluster to Master Cluster in case of failover.

Following my configurations, this process shoulded happens automatically, but does not works.

Additional Information:

Debian 9.2, PostgresSQL 9.6, Repmgr 3.3

IP Master (host1): 10.0.0.1, IP Standby (host2): 10.0.0.2

Contents included in /etc/postgresql/9.6/main/postgresql.conf, the same for all

wal_keep_segments = 5000

hot_standby = on

archive_mode = on

listen_addresses = '*'

max_wal_senders = 18

wal_level = hot_standby

shared_preload_libraries = 'repmgr_funcs'

archive_command = 'cd .'

Contents of /etc/postgresql/9.6/main/pg_hba.conf

Master

    local   all             postgres                                peer

    local   all             all                                     peer

    host    all             all             127.0.0.1/32            trust

    host    all             all             ::1/128                 md5

    host    repmgr          repmgr          10.0.0.2/32             trust

    host    replication     repmgr          10.0.0.2/32             trust

Standby

    local   all             postgres                                peer

    local   all             all                                     peer

    host    all             all             127.0.0.1/32            trust

    host    all             all             ::1/128                 md5

    host    repmgr          repmgr          10.0.0.1/32             trust

    host    replication     repmgr          10.0.0.1/32             trust

Contents of /etc/repmgr.conf

Master

    cluster=cluster

    node=1

    node_name=host1

    conninfo='host=host1 user=repmgr dbname=repmgr connect_timeout=2'

    failover=automatic

    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'

    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'

    logfile='/var/log/postgresql/repmgr.log'

    loglevel=NOTICE

    reconnect_attempts=4

    reconnect_interval=5

Standby

    cluster=cluster

    node=2

    node_name=host2

    conninfo='host=host2 user=repmgr dbname=repmgr connect_timeout=2'

    failover=automatic

    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'

    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'

    logfile='/var/log/postgresql/repmgr.log'

    loglevel=NOTICE

    reconnect_attempts=4

    reconnect_interval=5

Contents included in /etc/default/repmgrd, the same for all
```
REPMGRD_ENABLED=yes

REPMGRD_CONF="/etc/repmgr.conf"
```

Result of repmgr cluster show command

postgres@host1:~$ repmgr cluster show

Role      | Name   | Upstream | Connection String

----------+--------|----------|--------------------------------------------------------

* master  | host1  |          | host=host1 user=repmgr dbname=repmgr connect_timeout=2

  standby | host2  | host1    | host=host2 user=repmgr dbname=repmgr connect_timeout=2

Error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd"

[ERROR] unable to retrieve record for node 1: ERROR: relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo, slot_name, priority, active FROM "repmgr_cl...

Complete Tutorial: https://medium.com/@victor.boissiere/how-to-setup-postgresql-cluster-with-repmgr-febc2f10c243

edited Sep 10 '18 at 17:36

asked Sep 6 '18 at 15:32

Alessandro Cardoso

112

I maked the configuration of the failover system based in Repmgr, but i don't haved success to promote the Standby Cluster to Master Cluster in case of failover.

Following my configurations, this process shoulded happens automatically, but does not works.

Additional Information:

Debian 9.2, PostgresSQL 9.6, Repmgr 3.3

IP Master (host1): 10.0.0.1, IP Standby (host2): 10.0.0.2

Contents included in /etc/postgresql/9.6/main/postgresql.conf, the same for all

wal_keep_segments = 5000

hot_standby = on

archive_mode = on

listen_addresses = '*'

max_wal_senders = 18

wal_level = hot_standby

shared_preload_libraries = 'repmgr_funcs'

archive_command = 'cd .'

Contents of /etc/postgresql/9.6/main/pg_hba.conf

Master

    local   all             postgres                                peer

    local   all             all                                     peer

    host    all             all             127.0.0.1/32            trust

    host    all             all             ::1/128                 md5

    host    repmgr          repmgr          10.0.0.2/32             trust

    host    replication     repmgr          10.0.0.2/32             trust

Standby

    local   all             postgres                                peer

    local   all             all                                     peer

    host    all             all             127.0.0.1/32            trust

    host    all             all             ::1/128                 md5

    host    repmgr          repmgr          10.0.0.1/32             trust

    host    replication     repmgr          10.0.0.1/32             trust

Contents of /etc/repmgr.conf

Master

    cluster=cluster

    node=1

    node_name=host1

    conninfo='host=host1 user=repmgr dbname=repmgr connect_timeout=2'

    failover=automatic

    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'

    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'

    logfile='/var/log/postgresql/repmgr.log'

    loglevel=NOTICE

    reconnect_attempts=4

    reconnect_interval=5

Standby

    cluster=cluster

    node=2

    node_name=host2

    conninfo='host=host2 user=repmgr dbname=repmgr connect_timeout=2'

    failover=automatic

    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'

    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'

    logfile='/var/log/postgresql/repmgr.log'

    loglevel=NOTICE

    reconnect_attempts=4

    reconnect_interval=5

Contents included in /etc/default/repmgrd, the same for all
```
REPMGRD_ENABLED=yes

REPMGRD_CONF="/etc/repmgr.conf"
```

Result of repmgr cluster show command

postgres@host1:~$ repmgr cluster show

Role      | Name   | Upstream | Connection String

----------+--------|----------|--------------------------------------------------------

* master  | host1  |          | host=host1 user=repmgr dbname=repmgr connect_timeout=2

  standby | host2  | host1    | host=host2 user=repmgr dbname=repmgr connect_timeout=2

Error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd"

[ERROR] unable to retrieve record for node 1: ERROR: relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo, slot_name, priority, active FROM "repmgr_cl...

Complete Tutorial: https://medium.com/@victor.boissiere/how-to-setup-postgresql-cluster-with-repmgr-febc2f10c243

postgresql clustering failover debian repmgr

edited Sep 10 '18 at 17:36

asked Sep 6 '18 at 15:32

Alessandro Cardoso

112

edited Sep 10 '18 at 17:36

asked Sep 6 '18 at 15:32

Alessandro Cardoso

112

edited Sep 10 '18 at 17:36

asked Sep 6 '18 at 15:32

Alessandro Cardoso

112

asked Sep 6 '18 at 15:32

Alessandro Cardoso

112

asked Sep 6 '18 at 15:32

Alessandro Cardoso

112

bumped to the homepage by Community♦ 5 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♦ 5 mins ago

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

What does the show command show if you shit down master and what errors do you get when you failover manually.

– eckes
Sep 6 '18 at 23:06

error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd" [ERROR] unable to retrieve record for node 1: ERROR: relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo, slot_name, priority, active FROM "repmgr_cl...

– Alessandro Cardoso
Sep 10 '18 at 13:44

That looks like an important info, I would add it untruncated to the question

– eckes
Sep 10 '18 at 17:09

add a comment |

What does the show command show if you shit down master and what errors do you get when you failover manually.

– eckes
Sep 6 '18 at 23:06

error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd" [ERROR] unable to retrieve record for node 1: ERROR: relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo, slot_name, priority, active FROM "repmgr_cl...

– Alessandro Cardoso
Sep 10 '18 at 13:44

That looks like an important info, I would add it untruncated to the question

– eckes
Sep 10 '18 at 17:09

What does the show command show if you shit down master and what errors do you get when you failover manually.

– eckes
Sep 6 '18 at 23:06

error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd"

[ERROR] unable to retrieve record for node 1: ERROR:  relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo,        slot_name, priority, active  FROM "repmgr_cl...

– Alessandro Cardoso
Sep 10 '18 at 13:44

error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd"

[ERROR] unable to retrieve record for node 1: ERROR:  relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo,        slot_name, priority, active  FROM "repmgr_cl...

– Alessandro Cardoso
Sep 10 '18 at 13:44

That looks like an important info, I would add it untruncated to the question

– eckes
Sep 10 '18 at 17:09

add a comment |

1 Answer
1

active

oldest

votes

check out this useful link :

• http://raghavt.blogspot.in/2015/01/configuring-automatic-failover-using.html

answered Sep 7 '18 at 9:31

sugandh

Welcome to DBA.SE. We appreciate your contribution/participation. However, the community does expect a certain quality in the answers posted. Please consider reading the following article: How do I write a good answer? (Help Centre) In the section Answer the question there is a short sentence which reads: Links to external resources are encouraged, but please add context around the link so your fellow users will have some idea what it is and why it’s there

– hot2use
Sep 10 '18 at 9:09

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%2f216929%2frepmgr-error-to-promote-standby-cluster-to-master-cluster-in-failover%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

check out this useful link :

• http://raghavt.blogspot.in/2015/01/configuring-automatic-failover-using.html

answered Sep 7 '18 at 9:31

sugandh

Welcome to DBA.SE. We appreciate your contribution/participation. However, the community does expect a certain quality in the answers posted. Please consider reading the following article: How do I write a good answer? (Help Centre) In the section Answer the question there is a short sentence which reads: Links to external resources are encouraged, but please add context around the link so your fellow users will have some idea what it is and why it’s there

– hot2use
Sep 10 '18 at 9:09

add a comment |

check out this useful link :

• http://raghavt.blogspot.in/2015/01/configuring-automatic-failover-using.html

answered Sep 7 '18 at 9:31

sugandh

Welcome to DBA.SE. We appreciate your contribution/participation. However, the community does expect a certain quality in the answers posted. Please consider reading the following article: How do I write a good answer? (Help Centre) In the section Answer the question there is a short sentence which reads: Links to external resources are encouraged, but please add context around the link so your fellow users will have some idea what it is and why it’s there

– hot2use
Sep 10 '18 at 9:09

add a comment |

check out this useful link :

• http://raghavt.blogspot.in/2015/01/configuring-automatic-failover-using.html

answered Sep 7 '18 at 9:31

sugandh

check out this useful link :

• http://raghavt.blogspot.in/2015/01/configuring-automatic-failover-using.html

answered Sep 7 '18 at 9:31

sugandh

answered Sep 7 '18 at 9:31

sugandh

answered Sep 7 '18 at 9:31

sugandh

answered Sep 7 '18 at 9:31

sugandh

Welcome to DBA.SE. We appreciate your contribution/participation. However, the community does expect a certain quality in the answers posted. Please consider reading the following article: How do I write a good answer? (Help Centre) In the section Answer the question there is a short sentence which reads: Links to external resources are encouraged, but please add context around the link so your fellow users will have some idea what it is and why it’s there

– hot2use
Sep 10 '18 at 9:09

add a comment |

Welcome to DBA.SE. We appreciate your contribution/participation. However, the community does expect a certain quality in the answers posted. Please consider reading the following article: How do I write a good answer? (Help Centre) In the section Answer the question there is a short sentence which reads: Links to external resources are encouraged, but please add context around the link so your fellow users will have some idea what it is and why it’s there

– hot2use
Sep 10 '18 at 9:09

Welcome to DBA.SE. We appreciate your contribution/participation. However, the community does expect a certain quality in the answers posted. Please consider reading the following article: How do I write a good answer? (Help Centre) In the section Answer the question there is a short sentence which reads: Links to external resources are encouraged, but please add context around the link so your fellow users will have some idea what it is and why it’s there

– hot2use
Sep 10 '18 at 9:09

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