Ygthkb

Magento 2 : Call Helper Without Using __construct in Own Module

Slow moving projectiles from a hand-held weapon - how do they reach the target?

Why did other German political parties disband so fast when Hitler was appointed chancellor?

Why does String.replaceAll() work differently in Java 8 from Java 9?

What does Cypher mean when he says Neo is "gonna pop"?

Typing Amharic inside a math equation?

How do you funnel food off a cutting board?

Would these multi-classing house rules cause unintended problems?

Is it a fallacy if someone claims they need an explanation for every word of your argument to the point where they don't understand common terms?

How to acknowledge an embarrassing job interview, now that I work directly with the interviewer?

Why does a metal block make a shrill sound but not a wooden block upon hammering?

Is there any differences between "Gucken" and "Schauen"?

Enable Advanced Currency Management using CLI

Every character has a name - does this lead to too many named characters?

What is a jet (unit) shown in Windows 10 calculator?

Parsing a string of key-value pairs as a dictionary

What was the earliest start time of a Catholic mass before 1957?

Explain the objections to these measures against human trafficking

Adding a new switch to a C9300 stack

We are very unlucky in my court

Avoiding morning and evening handshakes

It took me a lot of time to make this, pls like. (YouTube Comments #1)

Caruana vs Carlsen game 10 (WCC) why not 18...Nxb6?

What is the purpose of easy combat scenarios that don't need resource expenditure?

Archiving strategy

Data archival strategy that handles schema changes?PostgreSQL replication for archivingWAL archiving from a standby backup strategy Postgresql 9.3What exactly are secondary members acknowledging in replicasets with MongoDB?SQL Server archivingPostgreSQL : Master-Slave replication strategy and solution for 3 servers.MySQL Backup StrategyDoes PostgreSQL Logical Replication replicates data or query?Archiving SQL Server data on disk to be pottentially restoredglobal foreign_key_checks = 0 on RBR slave

0

I have an instance with only one database which contains 4 years of data (400 gigas). I name this instance inst_live
I want to implement this following archiving strategy:

1. create an archive instance (inst_archive) and copy the database from inst_live to inst_archive.


2. all days, delete from inst_live all datas which are over two years


3. replay inst_live transactions in inst_archive except the purge.

Implement a streaming replication from inst_live to inst_archive is a solution to maintain freshness data in inst_archive but I fear the purge applied in inst_live will be applied in inst_archive too.

Inst_live has to contain only 2 years of data
Inst_archive has to contains 4 years of data.

Is anyone have any ideas please ?

I am using Postgresql 9.5 in Red Hat 7.1

Pglogical seems allowing logical replication based only on INSERT and UPDATE statements.
Does anyone implement it in production? Is there tutorials other than official documentation? I have some trouble to follow step by step official documentation.

Does Slony allow logical replication based on INSERT and UPDATE only?

postgresql replication backup architecture archive

share|improve this question

edited Mar 6 '18 at 18:50

Mika

asked Mar 1 '18 at 19:15

MikaMika

607

bumped to the homepage by Community♦ 16 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 | 

0

I have an instance with only one database which contains 4 years of data (400 gigas). I name this instance inst_live
I want to implement this following archiving strategy:

1. create an archive instance (inst_archive) and copy the database from inst_live to inst_archive.


2. all days, delete from inst_live all datas which are over two years


3. replay inst_live transactions in inst_archive except the purge.

Implement a streaming replication from inst_live to inst_archive is a solution to maintain freshness data in inst_archive but I fear the purge applied in inst_live will be applied in inst_archive too.

Inst_live has to contain only 2 years of data
Inst_archive has to contains 4 years of data.

Is anyone have any ideas please ?

I am using Postgresql 9.5 in Red Hat 7.1

Pglogical seems allowing logical replication based only on INSERT and UPDATE statements.
Does anyone implement it in production? Is there tutorials other than official documentation? I have some trouble to follow step by step official documentation.

Does Slony allow logical replication based on INSERT and UPDATE only?

postgresql replication backup architecture archive

share|improve this question

edited Mar 6 '18 at 18:50

Mika

asked Mar 1 '18 at 19:15

MikaMika

607

bumped to the homepage by Community♦ 16 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 have an instance with only one database which contains 4 years of data (400 gigas). I name this instance inst_live
I want to implement this following archiving strategy:

1. create an archive instance (inst_archive) and copy the database from inst_live to inst_archive.


2. all days, delete from inst_live all datas which are over two years


3. replay inst_live transactions in inst_archive except the purge.

Implement a streaming replication from inst_live to inst_archive is a solution to maintain freshness data in inst_archive but I fear the purge applied in inst_live will be applied in inst_archive too.

Inst_live has to contain only 2 years of data
Inst_archive has to contains 4 years of data.

Is anyone have any ideas please ?

I am using Postgresql 9.5 in Red Hat 7.1

Pglogical seems allowing logical replication based only on INSERT and UPDATE statements.
Does anyone implement it in production? Is there tutorials other than official documentation? I have some trouble to follow step by step official documentation.

Does Slony allow logical replication based on INSERT and UPDATE only?

postgresql replication backup architecture archive

share|improve this question

edited Mar 6 '18 at 18:50

Mika

asked Mar 1 '18 at 19:15

MikaMika

607

share|improve this question

edited Mar 6 '18 at 18:50

Mika

asked Mar 1 '18 at 19:15

MikaMika

607

share|improve this question

edited Mar 6 '18 at 18:50

Mika

asked Mar 1 '18 at 19:15

MikaMika

607

asked Mar 1 '18 at 19:15

MikaMika

607

asked Mar 1 '18 at 19:15

MikaMika

607

1 Answer
1

active

oldest

votes

0

Are there any deletions other than the purges occuring on inst_live? If not, you could use logical replication to replicate just the inserts and updates over. This would require v10 to do it easily. It can be done in 9.5, but you would need to create your own plugin as logical replication had only a skeletal implementation in-core in that version.

If you tell us why you need to do this (security? Conserve disk space?) it might inspire more answers.

share|improve this answer

answered Mar 2 '18 at 17:30

jjanesjjanes

13.5k917

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Deletions occurs only through the purge. Logical replication with Insert and Update subscription is exactly the need. As you know, it is only implement in v10. Can you please tell me how could it be implement in v9.5? Thanks a lot! The inst_live has to be purged because its size is too high (400gb) and the project team works with only 2 years old datas. Archive is used for BI and audit.
  
  – Mika
  Mar 3 '18 at 16:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, I don't know enough to help for 9.5, I just know it is possible. If the doc (postgresql.org/docs/9.5/static/logicaldecoding.html) isn't enough to go on, you might need paid support/consulting. (But I would think the time and money would be better spent overcoming the hurdles that are keeping you on 9.5)
  
  – jjanes
  Mar 4 '18 at 17:58
  

  
  
  
  

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');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f199162%2farchiving-strategy%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Are there any deletions other than the purges occuring on inst_live? If not, you could use logical replication to replicate just the inserts and updates over. This would require v10 to do it easily. It can be done in 9.5, but you would need to create your own plugin as logical replication had only a skeletal implementation in-core in that version.

If you tell us why you need to do this (security? Conserve disk space?) it might inspire more answers.

share|improve this answer

answered Mar 2 '18 at 17:30

jjanesjjanes

13.5k917

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Deletions occurs only through the purge. Logical replication with Insert and Update subscription is exactly the need. As you know, it is only implement in v10. Can you please tell me how could it be implement in v9.5? Thanks a lot! The inst_live has to be purged because its size is too high (400gb) and the project team works with only 2 years old datas. Archive is used for BI and audit.
  
  – Mika
  Mar 3 '18 at 16:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, I don't know enough to help for 9.5, I just know it is possible. If the doc (postgresql.org/docs/9.5/static/logicaldecoding.html) isn't enough to go on, you might need paid support/consulting. (But I would think the time and money would be better spent overcoming the hurdles that are keeping you on 9.5)
  
  – jjanes
  Mar 4 '18 at 17:58
  

  
  
  
  

add a comment | 

0

Are there any deletions other than the purges occuring on inst_live? If not, you could use logical replication to replicate just the inserts and updates over. This would require v10 to do it easily. It can be done in 9.5, but you would need to create your own plugin as logical replication had only a skeletal implementation in-core in that version.

If you tell us why you need to do this (security? Conserve disk space?) it might inspire more answers.

share|improve this answer

answered Mar 2 '18 at 17:30

jjanesjjanes

13.5k917

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Deletions occurs only through the purge. Logical replication with Insert and Update subscription is exactly the need. As you know, it is only implement in v10. Can you please tell me how could it be implement in v9.5? Thanks a lot! The inst_live has to be purged because its size is too high (400gb) and the project team works with only 2 years old datas. Archive is used for BI and audit.
  
  – Mika
  Mar 3 '18 at 16:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, I don't know enough to help for 9.5, I just know it is possible. If the doc (postgresql.org/docs/9.5/static/logicaldecoding.html) isn't enough to go on, you might need paid support/consulting. (But I would think the time and money would be better spent overcoming the hurdles that are keeping you on 9.5)
  
  – jjanes
  Mar 4 '18 at 17:58
  

  
  
  
  

add a comment | 

Are there any deletions other than the purges occuring on inst_live? If not, you could use logical replication to replicate just the inserts and updates over. This would require v10 to do it easily. It can be done in 9.5, but you would need to create your own plugin as logical replication had only a skeletal implementation in-core in that version.

If you tell us why you need to do this (security? Conserve disk space?) it might inspire more answers.

share|improve this answer

answered Mar 2 '18 at 17:30

jjanesjjanes

13.5k917

share|improve this answer

answered Mar 2 '18 at 17:30

jjanesjjanes

13.5k917

answered Mar 2 '18 at 17:30

jjanesjjanes

13.5k917

answered Mar 2 '18 at 17:30

jjanesjjanes

13.5k917