agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedBack-out V10 upgrade but keep new data
7+ messages / 2 participants
[nested] [flat]
* Back-out V10 upgrade but keep new data
@ 2019-01-23 19:06 Rossi, Maria <maria.rossi@jackson.com>
0 siblings, 2 replies; 7+ messages in thread
From: Rossi, Maria @ 2019-01-23 19:06 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>; +Cc: Rossi, Maria <maria.rossi@jackson.com>
Hi,
If I upgrade Postgres from V9.3 to V10, then need to roll-back to V9.3 after data been entered, is there a way for me to save the new data so that I can re-apply it to the V9.3? After I restore my V9.3 backup, can I simply replay the V10 log unto V9.3? What are my options, if any?
Thanks.
Maria A Rossi
Database Administration
Jackson National Life
email: maria.rossi@jackson.com<mailto:maria.rossi@jackson.com>
cell phone: 517.256.4392
work phone: 517.367.3099
work phone extension: 23099
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Back-out V10 upgrade but keep new data
@ 2019-01-23 19:12 Rene Romero Benavides <rene.romero.b@gmail.com>
parent: Rossi, Maria <maria.rossi@jackson.com>
1 sibling, 1 reply; 7+ messages in thread
From: Rene Romero Benavides @ 2019-01-23 19:12 UTC (permalink / raw)
To: Rossi, Maria <maria.rossi@jackson.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Check this out for CDC, haven't used it myself but I'm looking forward.
https://debezium.io/
On Wed, Jan 23, 2019 at 1:06 PM Rossi, Maria <maria.rossi@jackson.com>
wrote:
> Hi,
>
>
>
> If I upgrade Postgres from V9.3 to V10, then need to roll-back to V9.3
> after data been entered, is there a way for me to save the new data so that
> I can re-apply it to the V9.3? After I restore my V9.3 backup, can I
> simply replay the V10 log unto V9.3? What are my options, if any?
>
>
>
> Thanks.
>
>
>
> *Maria A Rossi*
>
> Database Administration
>
> Jackson National Life
>
> email: maria.rossi@jackson.com
> cell phone: 517.256.4392
>
> work phone: 517.367.3099
> work phone extension: 23099
>
>
>
--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Back-out V10 upgrade but keep new data
@ 2019-01-23 19:31 Rene Romero Benavides <rene.romero.b@gmail.com>
parent: Rossi, Maria <maria.rossi@jackson.com>
1 sibling, 0 replies; 7+ messages in thread
From: Rene Romero Benavides @ 2019-01-23 19:31 UTC (permalink / raw)
To: Rossi, Maria <maria.rossi@jackson.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
You also have the option of using multi-master replication with Bucardo,
but last time we tried to use it with the latest PG versions (10), we ran
into several issues, it seems the project hasn't been very active in recent
years.
On Wed, Jan 23, 2019 at 1:06 PM Rossi, Maria <maria.rossi@jackson.com>
wrote:
> Hi,
>
>
>
> If I upgrade Postgres from V9.3 to V10, then need to roll-back to V9.3
> after data been entered, is there a way for me to save the new data so that
> I can re-apply it to the V9.3? After I restore my V9.3 backup, can I
> simply replay the V10 log unto V9.3? What are my options, if any?
>
>
>
> Thanks.
>
>
>
> *Maria A Rossi*
>
> Database Administration
>
> Jackson National Life
>
> email: maria.rossi@jackson.com
> cell phone: 517.256.4392
>
> work phone: 517.367.3099
> work phone extension: 23099
>
>
>
--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Back-out V10 upgrade but keep new data
@ 2019-01-23 19:51 Rossi, Maria <maria.rossi@jackson.com>
parent: Rene Romero Benavides <rene.romero.b@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Rossi, Maria @ 2019-01-23 19:51 UTC (permalink / raw)
To: Rene Romero Benavides <rene.romero.b@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Also, if we set the log_statement = ‘all’, can I extract those statements and run against the restored V9.3 database?
What do you think? Would that work?
Thanks.
Maria
From: Rene Romero Benavides <rene.romero.b@gmail.com>
Sent: Wednesday, January 23, 2019 2:12 PM
To: Rossi, Maria <maria.rossi@jackson.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Back-out V10 upgrade but keep new data
EXTERNAL EMAIL
Check this out for CDC, haven't used it myself but I'm looking forward.
https://debezium.io/
On Wed, Jan 23, 2019 at 1:06 PM Rossi, Maria <maria.rossi@jackson.com<mailto:maria.rossi@jackson.com>> wrote:
Hi,
If I upgrade Postgres from V9.3 to V10, then need to roll-back to V9.3 after data been entered, is there a way for me to save the new data so that I can re-apply it to the V9.3? After I restore my V9.3 backup, can I simply replay the V10 log unto V9.3? What are my options, if any?
Thanks.
Maria
--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Back-out V10 upgrade but keep new data
@ 2019-01-23 21:38 Rene Romero Benavides <rene.romero.b@gmail.com>
parent: Rossi, Maria <maria.rossi@jackson.com>
0 siblings, 2 replies; 7+ messages in thread
From: Rene Romero Benavides @ 2019-01-23 21:38 UTC (permalink / raw)
To: Rossi, Maria <maria.rossi@jackson.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
In theory it could work, but I think that's error prone, you'd need to
parse statements and their parameters, and concurrency could play a role in
data consistency depending on your transaction isolation levels, etc. If
any of the statements fails for whatever reason, you can't just skip them
if you are to guarantee consistency, pgpool works by doing what you suggest
but all PG versions must be the same.
What's your average dml transaction volume ?
On Wed, Jan 23, 2019 at 1:51 PM Rossi, Maria <maria.rossi@jackson.com>
wrote:
> Also, if we set the *log_statement = ‘all’*, can I extract those
> statements and run against the restored V9.3 database?
>
> What do you think? Would that work?
>
>
>
> Thanks.
>
>
>
> Maria
>
>
>
>
>
> *From:* Rene Romero Benavides <rene.romero.b@gmail.com>
> *Sent:* Wednesday, January 23, 2019 2:12 PM
> *To:* Rossi, Maria <maria.rossi@jackson.com>
> *Cc:* pgsql-sql@lists.postgresql.org
> *Subject:* Re: Back-out V10 upgrade but keep new data
>
>
>
> *EXTERNAL EMAIL*
>
> Check this out for CDC, haven't used it myself but I'm looking forward.
>
> https://debezium.io/
>
>
>
> On Wed, Jan 23, 2019 at 1:06 PM Rossi, Maria <maria.rossi@jackson.com>
> wrote:
>
> Hi,
>
>
>
> If I upgrade Postgres from V9.3 to V10, then need to roll-back to V9.3
> after data been entered, is there a way for me to save the new data so that
> I can re-apply it to the V9.3? After I restore my V9.3 backup, can I
> simply replay the V10 log unto V9.3? What are my options, if any?
>
>
>
> Thanks.
>
> Maria
>
>
>
>
>
>
> --
>
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
>
>
--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Back-out V10 upgrade but keep new data
@ 2019-01-23 21:41 Rene Romero Benavides <rene.romero.b@gmail.com>
parent: Rene Romero Benavides <rene.romero.b@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Rene Romero Benavides @ 2019-01-23 21:41 UTC (permalink / raw)
To: Rossi, Maria <maria.rossi@jackson.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
You could also use Slony-I it replicates data through different PG
versions.
On Wed, Jan 23, 2019 at 3:38 PM Rene Romero Benavides <
rene.romero.b@gmail.com> wrote:
> In theory it could work, but I think that's error prone, you'd need to
> parse statements and their parameters, and concurrency could play a role in
> data consistency depending on your transaction isolation levels, etc. If
> any of the statements fails for whatever reason, you can't just skip them
> if you are to guarantee consistency, pgpool works by doing what you suggest
> but all PG versions must be the same.
> What's your average dml transaction volume ?
>
>
>
> On Wed, Jan 23, 2019 at 1:51 PM Rossi, Maria <maria.rossi@jackson.com>
> wrote:
>
>> Also, if we set the *log_statement = ‘all’*, can I extract those
>> statements and run against the restored V9.3 database?
>>
>> What do you think? Would that work?
>>
>>
>>
>> Thanks.
>>
>>
>>
>> Maria
>>
>>
>>
>>
>>
>> *From:* Rene Romero Benavides <rene.romero.b@gmail.com>
>> *Sent:* Wednesday, January 23, 2019 2:12 PM
>> *To:* Rossi, Maria <maria.rossi@jackson.com>
>> *Cc:* pgsql-sql@lists.postgresql.org
>> *Subject:* Re: Back-out V10 upgrade but keep new data
>>
>>
>>
>> *EXTERNAL EMAIL*
>>
>> Check this out for CDC, haven't used it myself but I'm looking forward.
>>
>> https://debezium.io/
>>
>>
>>
>> On Wed, Jan 23, 2019 at 1:06 PM Rossi, Maria <maria.rossi@jackson.com>
>> wrote:
>>
>> Hi,
>>
>>
>>
>> If I upgrade Postgres from V9.3 to V10, then need to roll-back to V9.3
>> after data been entered, is there a way for me to save the new data so that
>> I can re-apply it to the V9.3? After I restore my V9.3 backup, can I
>> simply replay the V10 log unto V9.3? What are my options, if any?
>>
>>
>>
>> Thanks.
>>
>> Maria
>>
>>
>>
>>
>>
>>
>> --
>>
>> El genio es 1% inspiración y 99% transpiración.
>> Thomas Alva Edison
>> http://pglearn.blogspot.mx/
>>
>>
>>
>
>
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
>
--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Back-out V10 upgrade but keep new data
@ 2019-01-23 22:22 Rene Romero Benavides <rene.romero.b@gmail.com>
parent: Rene Romero Benavides <rene.romero.b@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Rene Romero Benavides @ 2019-01-23 22:22 UTC (permalink / raw)
To: Sonnenberg-Carstens, Stefan <ssonnenberg@ophardt.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Yeah, time spent in testing is always time well spent. Same, I've never had
to rollback to a previous PG version, but one thing to take in
consideration is the use of certain extensions, like PostGIS, usually you
need to upgrade them as well, and make sure that nothing breaks if your
application relies on them.
On Wed, Jan 23, 2019 at 4:03 PM Sonnenberg-Carstens, Stefan <
ssonnenberg@ophardt.com> wrote:
> Hi,
>
>
>
> no, this won’t work.
>
> The parameters passed to statements are missing there.
>
>
>
> I thinks the easiest thing to do is pg_dump at the PG 10 regularly and
> feed it into PG 9 if it becomes necessary.
>
> Perhaps you need to tweak a few statements, but that should be simple.
>
>
>
> Provided that the database is not too big and you don’t require PITR or
> something.
>
> Then I also would opt for something different, perhaps slony.
>
>
>
> Cheers
>
>
>
> P.S.: Why would that happen? If you are able to import the database dump
> without errors, and your application is
>
> compatible (and all of what it required like jdbc, odbc, psycopg2 …) then
> why do you think something should
>
> come up out of nothing? Perhaps you should invest in testing beforehand.
>
> I did 4 upgrades in 2018 and never had to go back.
>
>
>
> *Von:* Rene Romero Benavides [mailto:rene.romero.b@gmail.com]
> *Gesendet:* Mittwoch, 23. Januar 2019 22:38
> *An:* Rossi, Maria
> *Cc:* pgsql-sql@lists.postgresql.org
> *Betreff:* Re: Back-out V10 upgrade but keep new data
>
>
>
> In theory it could work, but I think that's error prone, you'd need to
> parse statements and their parameters, and concurrency could play a role in
> data consistency depending on your transaction isolation levels, etc. If
> any of the statements fails for whatever reason, you can't just skip them
> if you are to guarantee consistency, pgpool works by doing what you suggest
> but all PG versions must be the same.
>
> What's your average dml transaction volume ?
>
>
>
>
>
>
>
> On Wed, Jan 23, 2019 at 1:51 PM Rossi, Maria <maria.rossi@jackson.com>
> wrote:
>
> Also, if we set the *log_statement = ‘all’*, can I extract those
> statements and run against the restored V9.3 database?
>
> What do you think? Would that work?
>
>
>
> Thanks.
>
>
>
> Maria
>
>
>
>
>
> *From:* Rene Romero Benavides <rene.romero.b@gmail.com>
> *Sent:* Wednesday, January 23, 2019 2:12 PM
> *To:* Rossi, Maria <maria.rossi@jackson.com>
> *Cc:* pgsql-sql@lists.postgresql.org
> *Subject:* Re: Back-out V10 upgrade but keep new data
>
>
>
> *EXTERNAL EMAIL*
>
> Check this out for CDC, haven't used it myself but I'm looking forward.
>
> https://debezium.io/
>
>
>
> On Wed, Jan 23, 2019 at 1:06 PM Rossi, Maria <maria.rossi@jackson.com>
> wrote:
>
> Hi,
>
>
>
> If I upgrade Postgres from V9.3 to V10, then need to roll-back to V9.3
> after data been entered, is there a way for me to save the new data so that
> I can re-apply it to the V9.3? After I restore my V9.3 backup, can I
> simply replay the V10 log unto V9.3? What are my options, if any?
>
>
>
> Thanks.
>
> Maria
>
>
>
>
>
>
> --
>
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
>
>
>
>
>
> --
>
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
>
>
--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-01-23 22:22 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-23 19:06 Back-out V10 upgrade but keep new data Rossi, Maria <maria.rossi@jackson.com>
2019-01-23 19:12 ` Rene Romero Benavides <rene.romero.b@gmail.com>
2019-01-23 19:51 ` Rossi, Maria <maria.rossi@jackson.com>
2019-01-23 21:38 ` Rene Romero Benavides <rene.romero.b@gmail.com>
2019-01-23 21:41 ` Rene Romero Benavides <rene.romero.b@gmail.com>
2019-01-23 22:22 ` Rene Romero Benavides <rene.romero.b@gmail.com>
2019-01-23 19:31 ` Rene Romero Benavides <rene.romero.b@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox