Replication of specific tables using Postgres 9.4.4 and Amazon Web Services
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have currently setup a Postgres 9.4.4 RDS instance on Amazon Web Services. On this instance I hold 20 tables. In addition I also have Postgres 9.4.4 installed on my local server (OS: 64-bit Linux) which holds about 30 tables under one database. Both the cloud instance and my local server instance have overlapping tables i.e. there are some tables on the cloud that I replicate to my local and vice versa. I am not well versed with Postgres Replication procedures as I have never needed them and at the moment have been doing the transfer rather inefficiently via python scripts to read/write/append tables between these two databases.
I would appreciate any guidance from the community on what is the best procedure. Ideally I would like a solution where some tables on the cloud can be MASTER and SLAVE locally as well as allow other other tables to be a MASTER locally and a SLAVE in the cloud. Is this possible?
Have heard that maybe Burcardo or Slony could do this? Any good documentation/sites on this area would also be helpful.
postgresql replication amazon-rds transactional-replication postgresql-9.4
asked Aug 29 '15 at 2:27
DarthtraderDarthtrader
1384
add a comment |
I have currently setup a Postgres 9.4.4 RDS instance on Amazon Web Services. On this instance I hold 20 tables. In addition I also have Postgres 9.4.4 installed on my local server (OS: 64-bit Linux) which holds about 30 tables under one database. Both the cloud instance and my local server instance have overlapping tables i.e. there are some tables on the cloud that I replicate to my local and vice versa. I am not well versed with Postgres Replication procedures as I have never needed them and at the moment have been doing the transfer rather inefficiently via python scripts to read/write/append tables between these two databases.
I would appreciate any guidance from the community on what is the best procedure. Ideally I would like a solution where some tables on the cloud can be MASTER and SLAVE locally as well as allow other other tables to be a MASTER locally and a SLAVE in the cloud. Is this possible?
Have heard that maybe Burcardo or Slony could do this? Any good documentation/sites on this area would also be helpful.
postgresql replication amazon-rds transactional-replication postgresql-9.4
asked Aug 29 '15 at 2:27
DarthtraderDarthtrader
1384
Any help from anyone? Please let me know if I am not clear.
– Darthtrader
Aug 31 '15 at 0:38
add a comment |
I have currently setup a Postgres 9.4.4 RDS instance on Amazon Web Services. On this instance I hold 20 tables. In addition I also have Postgres 9.4.4 installed on my local server (OS: 64-bit Linux) which holds about 30 tables under one database. Both the cloud instance and my local server instance have overlapping tables i.e. there are some tables on the cloud that I replicate to my local and vice versa. I am not well versed with Postgres Replication procedures as I have never needed them and at the moment have been doing the transfer rather inefficiently via python scripts to read/write/append tables between these two databases.
I would appreciate any guidance from the community on what is the best procedure. Ideally I would like a solution where some tables on the cloud can be MASTER and SLAVE locally as well as allow other other tables to be a MASTER locally and a SLAVE in the cloud. Is this possible?
Have heard that maybe Burcardo or Slony could do this? Any good documentation/sites on this area would also be helpful.
postgresql replication amazon-rds transactional-replication postgresql-9.4
asked Aug 29 '15 at 2:27
DarthtraderDarthtrader
1384
I have currently setup a Postgres 9.4.4 RDS instance on Amazon Web Services. On this instance I hold 20 tables. In addition I also have Postgres 9.4.4 installed on my local server (OS: 64-bit Linux) which holds about 30 tables under one database. Both the cloud instance and my local server instance have overlapping tables i.e. there are some tables on the cloud that I replicate to my local and vice versa. I am not well versed with Postgres Replication procedures as I have never needed them and at the moment have been doing the transfer rather inefficiently via python scripts to read/write/append tables between these two databases.
I would appreciate any guidance from the community on what is the best procedure. Ideally I would like a solution where some tables on the cloud can be MASTER and SLAVE locally as well as allow other other tables to be a MASTER locally and a SLAVE in the cloud. Is this possible?
Have heard that maybe Burcardo or Slony could do this? Any good documentation/sites on this area would also be helpful.
postgresql replication amazon-rds transactional-replication postgresql-9.4
postgresql replication amazon-rds transactional-replication postgresql-9.4
asked Aug 29 '15 at 2:27
DarthtraderDarthtrader
1384
asked Aug 29 '15 at 2:27
DarthtraderDarthtrader
1384
edited Aug 29 '15 at 13:22
Darthtrader
asked Aug 29 '15 at 2:27
DarthtraderDarthtrader
1384
asked Aug 29 '15 at 2:27
DarthtraderDarthtrader
1384
asked Aug 29 '15 at 2:27
DarthtraderDarthtrader
1384
1384
Any help from anyone? Please let me know if I am not clear.
– Darthtrader
Aug 31 '15 at 0:38
add a comment |
Any help from anyone? Please let me know if I am not clear.
– Darthtrader
Aug 31 '15 at 0:38
Any help from anyone? Please let me know if I am not clear.
– Darthtrader
Aug 31 '15 at 0:38
Any help from anyone? Please let me know if I am not clear.
– Darthtrader
Aug 31 '15 at 0:38
add a comment |
1 Answer
1
active
oldest
votes
According to https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication, from version 9.4.9 logical replication is possible from RDS Postgres instances. That would solve the RDS -> local direction.
The documentation doesn't make it very clear, but it seems that from version 10.4 an RDS instance can even be the receiver end (the subscriber) of logical replication:
Beginning with PostgreSQL version 10.4, RDS supports the publication and subscription SQL Syntax for PostgreSQL 10 Logical Replication.
answered 5 mins ago
dezsodezso
22.6k116097
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f112601%2freplication-of-specific-tables-using-postgres-9-4-4-and-amazon-web-services%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
According to https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication, from version 9.4.9 logical replication is possible from RDS Postgres instances. That would solve the RDS -> local direction.
The documentation doesn't make it very clear, but it seems that from version 10.4 an RDS instance can even be the receiver end (the subscriber) of logical replication:
Beginning with PostgreSQL version 10.4, RDS supports the publication and subscription SQL Syntax for PostgreSQL 10 Logical Replication.
answered 5 mins ago
dezsodezso
22.6k116097
add a comment |
According to https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication, from version 9.4.9 logical replication is possible from RDS Postgres instances. That would solve the RDS -> local direction.
The documentation doesn't make it very clear, but it seems that from version 10.4 an RDS instance can even be the receiver end (the subscriber) of logical replication:
Beginning with PostgreSQL version 10.4, RDS supports the publication and subscription SQL Syntax for PostgreSQL 10 Logical Replication.
answered 5 mins ago
dezsodezso
22.6k116097
add a comment |
According to https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication, from version 9.4.9 logical replication is possible from RDS Postgres instances. That would solve the RDS -> local direction.
The documentation doesn't make it very clear, but it seems that from version 10.4 an RDS instance can even be the receiver end (the subscriber) of logical replication:
Beginning with PostgreSQL version 10.4, RDS supports the publication and subscription SQL Syntax for PostgreSQL 10 Logical Replication.
answered 5 mins ago
dezsodezso
22.6k116097
According to https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication, from version 9.4.9 logical replication is possible from RDS Postgres instances. That would solve the RDS -> local direction.
The documentation doesn't make it very clear, but it seems that from version 10.4 an RDS instance can even be the receiver end (the subscriber) of logical replication:
Beginning with PostgreSQL version 10.4, RDS supports the publication and subscription SQL Syntax for PostgreSQL 10 Logical Replication.
answered 5 mins ago
dezsodezso
22.6k116097
answered 5 mins ago
dezsodezso
22.6k116097
answered 5 mins ago
dezsodezso
22.6k116097
answered 5 mins ago
dezsodezso
22.6k116097
22.6k116097
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f112601%2freplication-of-specific-tables-using-postgres-9-4-4-and-amazon-web-services%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Any help from anyone? Please let me know if I am not clear.
– Darthtrader
Aug 31 '15 at 0:38