Pratica di Shadowing: Easy Migration from Postgres to Databricks Lakebase - Impara a parlare inglese con i video
Creazione lezione...
1
Hello, Spark fans.
2
Welcome back to Advancing Spark.
3
Brought to you by Advancing Analytics, your favorite data and AI engineering consultancy.
4
I want to talk to you about Lake Base some more.
5
So I did a video fairly recently just about what leg base is, how you get started with the world's most...
6
Underwhelming demo of just kind of honor networks.
7
I'm I It's really interesting because there's a whole bunch of people who are using LakeBase now
8
and the most common thing is for apps I've built an agent, I've made a little chat agent, I've made a little kind of data entry app,
9
vibe -coded things up, and I want to have good state management, and so I put it lake -based.
10
Makes sense.
11
and the thing that that misses is what if I'm already doing that?
12
Perhaps aren't new.
13
Databricks, whilst they've been innovating and building a load of crazy stuff, The whole idea of I've got an application, that's not a new thing.
14
So what if you already have?
15
an app database knocking around.
16
You know, it would be nice if it just talked to Unity Catalog.
17
I'd love to use the branching, the scale to zero, the instant restore, all of that good stuff.
18
That sounds great.
19
Can I have that, please?
20
Well, how do you get there?
21
So I ended a webinar earlier this week with Denny Briggs about how you migrate Lake Base.
22
How you take an existing Postgres database and then just port it over, restore it into a lake -based instance, and then get going with it.
23
So I want to do a bit of a bit of a run through.
24
We had a couple of connection problems and I had deleted a lake base
25
and left a soft deleted tombstone and it was annoying and it broke.
26
So I just want to show you how it works when we validate things and we migrate things. and advancing.
27
So yeah, that's the plan.
28
So how do you go from a Postgres database into LakeBase?
29
What are the gotchas you need to look for really quickly?
30
But then how do we do it?
31
How do we just hit a button and migrate and get it across?
32
That's the plan for today.
33
So, if you're new around here, as always, don't forget to like and subscribe.
34
I will be talking about Dead Reye Summit very, very soon, because that is coming up right up in just two weeks.
35
But, yeah, let's go have a look at this stuff.
36
Start!
37
Yeah, I've got a big old slide deck.
38
I'm going to skip.
39
We don't need slides.
40
Skip, skip, skip, skip, skip.
41
Ignore, ignore, ignore.
42
Yeah.
43
One, if you're migrating.
44
If you're looking at OLTP, it's different to OLAP.
45
Well, that's no shock.
46
We know that.
47
That is fine.
48
And then there's this piece.
49
Okay, well, it is Postgres, but it's not Postgres.
50
I mean, that's not quite true.
51
So LakeBase is a managed instance of Postgres, and like all managed instances, it has some things
52
that they've put into the platform you don't have as much control as you would have
53
if you were doing it by yourself.
54
And so I want to run a couple of those gotchas first.
55
Because you are inside a managed pooled some kind of state managed instance of Postgres.
56
There's some stuff you'd be doing yourself if you've just built your own server
57
and you're running it manually that you can't do in Lakebase.
58
because you're giving away that control.
59
in order to get all the nice auto scaling, parallelized separation of storage compute.
60
So yeah, the things that you can control are slightly different So you don't have the shell, you don't have Postgres logs,
61
you don't have to go and put a load of traces and scrapers around the Postgres logs to actually manage things.
62
You don't have the various different authentication conf files you'd have to go
63
because you're living inside of the Databricks control security ecosystem And You don't have as many things you can tune.
64
So, observability, that kind of stuff.
65
You can do your PG stat statements and some of the ways you build it manually.
66
other things are just automatically gathered for you
67
There's a whole bunch of telemetry that just exists in the system
68
that if you've built that into your own existing Postgres app, you probably need to pull that out because some of those extensions aren't installed.
69
So it is not just a straight PG dump, PG restoring life is going to be good.
70
It is for the most part, but there's some features that are turned off because they're replaced by inherent Databricks functionality.
71
That's the main thing that you're going to come across.
72
So Those kind of gotchas come across these four areas.
73
Session stay is a biggie.
74
We'll talk about what that means.
75
Auth types, it's not really that big, it's just they consolidate into two main types.
76
Extensions might trip you up if you've kind of built your own ecosystem inside of a single Postgres database.
77
There's just a different way you do it inside of Databricks.
78
And quotas probably won't trip you up, but it's worth being aware that there are some quota size limits on the platform. So, Session stay.
79
The main thing comes down to because you are going through this kind of session pool.
80
If you've built a lot of things inside Postgres that are
81
Managing state within a single session and then you've told it to scale to zero
82
when it comes back it's going to have a different session state Kind of makes sense.
83
So if you're using temp tables, If you have a big, long -running process that you've paused for someone to respond to in the app,
84
And then actually it's turned off in the meantime, because you had it scale down to zero in one minute.
85
And they come back.
86
Well, they're going to come back and find that there aren't things.
87
The temp table is not there anymore.
88
The prepared statements are gone.
89
One of the classics that a lot of people use Postgres for is doing big, long migration wizards.
90
and if that's using advisory locks to manage the state of what it has and hasn't done yet, but then that goes away and there's a different system waiting for approvals and things.
91
When it comes back, those locks may have disappeared because the thing's turned off.
92
So if you've got anything that's looking at the actual state context, Just be careful about how you're doing the scalability and how you're looking after scale to zero.
93
Because if it turns off, yeah, you're going to save some money, but you're going to lose your session state.
94
So just manage that.
95
Think about the separation of what you're managing inside Postgres, what's being managed by the app, and actually how you link the two together.
96
Is the app sending false keeper lives, in which case you're not going to send the scale down to zero, Or is it basically a dead state, a syncretist is going to push it back again, in which case, if it does scale down,
97
you just need to re -architect things literally.
98
All it is, it's just you might want to design some stuff slightly differently if it's going to turn off.
99
No shock there.
100
Extensions are probably the one that's going to cause the most gotchas in terms of what people currently use it for.
101
There's a bunch of things that people build into this stuff, which you wouldn't necessarily do when it's inside of Databricks.
102
So we will went through some of them.
103
There's a whole bunch of extensions that are pre -installed in Lakebase that you don't need to worry about.
104
So the only issue is if you're using some of the stuff that isn't pre -installed, and most of the time it's pre -installed because
105
It's not pre -installed because actually there's a different data rich feature that replaces it.
106
So take PG -Cron, for example.
107
That is, if you're like me from the old days of SQL Server, instead of your SQL agent, that is your temporal scheduler.
108
run this every hour, run this every day, it's a cron job, right? that doesn't exist, that is not being implemented inside of LakeBase,
109
it's not an allowed extension But if you're doing that inside of Databricks, Well, you'd run it via DataBix jobs.
110
DataBix has its own scheduling system.
111
And actually, if you had a little cottage industry of things scheduled inside your Postgres database,
112
inside Lakebase, well then that's not going to be visible to the rest of the database ecosystem that's not ideal
113
So it's that kind of thing where we tend to see those extensions are enabled.
114
So yeah, PG -Cron just pull them out and replace them with database jobs and workflows the kind of foreign data warehouse, kind of the links.
115
I mean, Yes, you would do that via either automatically using the Lake Bay Sync into Unity Catalog,
116
and then just running queries on there or using things like foreign catalogs and You can run a cross database query, cross database query inside Databricks from Unity Catalog,
117
pulling together things from different lake bases.
118
That's perfectly fine.
119
You wouldn't do it inside the lake base.
120
So just a slight difference in terms of where you think about things.
121
If you have a little operational
122
job that needs to kick off inside your lake base that needs to do a foreign database query, you need to think about actually how you're going to architect that because that is not enabled inside a lake base.
123
Time scale DB? and they've not really enabled it for that kind of workflows.
124
There's different areas of Databricks where you would build that kind of real -time analytical querying.
125
And then anything where you're kind of building out your own random systems using programming languages where you've got PL Python 3, PL Perl, you're building out Perl,
126
Java, Python, all those extensions.
127
No. Certainly what we've found is the majority of times people use those things.
128
It tends to because Well, the SQL functions didn't exist back in the day to do that and actually they do now
129
but people haven't gone back and refactored all of their legacy code.
130
It's quite rare you actually need to do that.
131
especially because you can just lift it up into the app layer for that kind of stuff.
132
or you've got all the rest of Databricks, which is very programming language heavy. So...
133
Like it's an architectural layer separation of going, no, no, lake bases should just be pure database things.
134
And if that's the case, to look at what you're doing with that code so
135
if you do have a really really heavy load of
136
that kind of extension that kind of extra custom code built stuff
137
that's not so easy to migrate because those extensions aren't enabled.
138
Also the Pub /Sub stuff.
139
So if you're expecting people to be able to subscribe and pub all within there, that's kindle into the session state, kindling to extensions.
140
You wouldn't do that inside LakeBase either.
141
Yeah, it bubbles down to essentially using either OAuth, which would generate a token for you, And that token has a one -hour time to live.
142
So you just need to make sure whatever app you're building that's integrating into Lakebase, either it uses the, it's a Databricks app,
143
which has got its own Lakebase integration baked in with the token kind of revamp, or you're building an app separately,
144
you just need to build in the time of how long until it forces a recheck out of that token, because tokens are going to expire after an hour.
145
That's fine.
146
That's a very, very standard integration.
147
Or it uses postcode password, which isn't ideal and we don't No one recommends ever.
148
So if you're doing things that have certificates and you're doing some things in the slightly more esoteric, one of the many different ways you can set that up in Postgres,
149
you just need to change how you're managing that security when you migrate to Lightbase.
150
Thank you.
151
And then finally, yeah, the limits.
152
So the raw lake -based limits, again, these are lake -based limits as of when I put this stuff together probably about a month ago.
153
And again, this may change.
154
Currently per lake -based project, you've got about a 16 terabytes storage limitation per instance.
155
You can look at what that's going to be.
156
4000 max concurrent connections.
157
Although if you have a synchronized table, that's going to just squat on top of 16 of those.
158
So if you've got 100 synchronized tables, that's going to take a fair chunk of your connections.
159
You should have an idea about how big your leg base can be.
160
The majority of times we've seen people using it, especially for your business apps kind of thing, get nowhere near these kind of things. So...
161
Yeah there are some limitations.
162
Thank you.
163
all right
164
so we're gonna skip through how you run it i mean
165
how do you run a migration you check it check what you need to do
166
and validate it at the start you decide any limitations
167
and compromises you rehearse it and you test it and you make sure it is
168
Absolutely perfect and spot on when you migrate it over.
169
And then you do a cut over and it all works happily and you go off to the pub.
170
tends to be how we run things.
171
That's more for the webinar, not for here.
172
This is what I want to talk about.
173
I want to talk about how we actually run it.
174
And at that stage, we can get rid of some slides and actually just talk about a little accelerator that we built.
175
This is kind of like the way most people are building migrations these days is Because...
176
clawed and all that kind of stuff and made it so much easier to build these things.
177
I built this out originally since the demo.
178
And then actually it works so well we've been using it
179
and actually just using it to run migrations and we're building out.
180
Pulled out my old demo environment just to show you guys a cut down version of how it looks.
181
I'm going to start off going to get this started going.
182
So essentially I've got this
183
this CLI kind of environment and what this does it's got like four demo Postgres databases in there.
184
So it's going to spin up like four fixtures for me, a nice clean one.
185
So just basically a bog standard, nothing special copy of Postgres with a sample database in there.
186
It can take the same sample database and then install some features
187
and use a few things that just aren't quite compatible that need a few fixes.
188
Those kind of fixes we can do automatically.
189
There's some things you can get, oh, you've got a certain user assigned to a super user.
190
That's all going to be the Databricks super user.
191
We can just fix that through as we migrate.
192
There's then some someone which uses some features that you actually kind of quite easily switch over from.
193
And then there's one that's got a ton of things that just simply do not work.
194
So step one is actually just to understand what's going on.
195
So we built a validator that's got basically a config file.
196
That's got what the current compatibility level of LakeBase in there.
197
We can do essentially a scrape out of Postgres, compare the two and go, what features is it using?
198
What's it not using?
199
What does it actually look like?
200
So if I just pull that over into here, I'm going to pull it up a little bit so you guys can see everything.
201
And I'll bring it up.
202
Thank you.
203
So if I just run this validate step, Now normally we'd validate it just for a certain database.
204
In this case, I'm just running all the validation reports.
205
Indeed, that's the kind of thing I'm expecting to get, right?
206
So what's.. how clean is it?
207
Is it just to play supported?
208
supported with a few manual changes, supported, but you're going to get some degraded performance.
209
It's you've got a compromise baked in there.
210
There's manual fixes you need to do, or there's an absolute blocker that cannot be worked around.
211
You need to re -architect how that works before you can migrate.
212
That's kind of the level of things we're just trying to get this idea of.
213
Basically, what are we dealing with?
214
So if someone rang us up and said, "Hey, I want to do a migration," It's where we'd start, right?
215
We just run this script, run that scraper and go, Does it work?
216
Does it not work?
217
It's a major rework.
218
Yeah, it's going to need some major rework.
219
Let's have a look at what that markdown looks like.
220
We're going to leg base, going to PG -17.
221
It's...
222
Got a load of problems in there.
223
Oh, it's not wanting that.
224
There we go.
225
So we can see the things it's got, the blockers.
226
Yeah, it's got a Pub /Sub in there.
227
It's trying to do some replication.
228
um that's not going to work those things are not enabled not installed in lake base There's a few things that yeah, it's just going to rewrite super user -owned objects.
229
That's all fine.
230
But the real thing is what we're going to do with those bits that it's got at the top.
231
I hope we can have a look at a clean version.
232
Well, it's a lot easier.
233
So we can see, yes, the security role learner we need to change over.
234
Everything else is just a straight rewriter security.
235
No other things that we're actually going to lose perfectly fine.
236
If we go and have a look at the minor fixes, probably the one that we'll run with, Yes, so that does have a few degraded things.
237
Degraded is all about the security granularity that we've got and the public session caches.
238
It's just slightly different.
239
the way it manages caching because again caching is all around the state
240
and management and when you're in a shared buffer pool
241
so we can do the separation of storage compute it's just gonna work slightly differently um so yeah
242
essentially we're fairly good there so we know at least the clean
243
and the minor fixes we can just migrate those we can
244
just go yeah pick those up push it over deploy that over So I've got a DeadRex workspace up here.
245
I'm in my nice shiny lake base persona switcher.
246
So I can see all my lake bases that I've currently got sitting in here.
247
I'm just going to try and run a migration wizard, say.
248
Just copy over.
249
Just push it straight over and see how it goes.
250
and this is the thing that broke for me
251
because i had deleted it just beforehand
252
so i'm trying to do it with the same name i'm going to grab
253
that pick it up they run from the the last version of the report which is one i've just done
254
Go ahead and push that across.
255
which is essentially going to go and actually pull some things out, basically do a PG dump and a PG restore behind the scenes, make some changes of what we're actually doing as it goes,
256
create a new database project.
257
There you can see my old project is a ghost deletion that's sitting around.
258
We're going to get demo fixes too.
259
Fine.
260
That was a fix I hadn't put in previously.
261
So it's provisioning out, it's building out the schema, it'll then do the data upload, and then do a quick bit of validation to make sure it's gone across successfully.
262
And then we can go and have a check.
263
And again, this is how most migrators are working these days.
264
You can actually start to build these things really effectively, really easily.
265
to go It's a lot easier to migrate something from A to B than it used to be.
266
There goes the schema has gone across.
267
We're now doing a data push across, so we should actually be able to go and see
268
We've got our leg base over here already or is it kind of currently building it?
269
We will see!
270
There we go!
271
So demo fixes number two has gone across.
272
We've got that.
273
So it's currently turned on.
274
We can see it's currently active.
275
It's pushing that stuff in.
276
There we go.
277
Ah, perfect. So...
278
Project's created, schema's created, data's created.
279
It's done some verification.
280
Easy as that.
281
Takes a couple of minutes.
282
I bet again, for a fairly small database, it's not crazy.
283
We're going to have a look at the tables in here.
284
We should be able to see we've got a bunch of stuff that has been migrated.
285
There we go, and this is just the.. is it pager?
286
straightforward Postgres sample, it's the adventure works of Postgres World.
287
Just going, I've got a whole bunch of data.
288
You can see how quickly that's gone across, how quickly it created, given how recently we actually set this project up.
289
That's all ready to go.
290
If we go and have a dig into it, you can see constraints are across, indexes are off across all of our various different things have been pulled across.
291
It's not just a straight scrape the basics and pull it.
292
That is a full migration of all the complexity, everything that was in there.
293
pushed from A to B as a quick CLI check.
294
And that's how it should look.
295
I mean, yeah, you can put a shiny app on top of a little progress bar and that kind of thing.
296
But as a developer of Songkink, we've got a Postgres database.
297
We need to plan a migration project.
298
This is going to take us months and months to months to get it over.
299
I mean, honestly, it might do if you have huge amounts of custom code using some of the extensions like PLPython3u.
300
But if you're just using straight Postgres and you've got a load of data and it's about, I need to get the data from A to B.
301
So easy to get it migrated and set up inside a lake base.
302
Now, I mean...
303
Slight bit of fish -eatishness there.
304
Obviously, You're going to need to run a load of testing.
305
We've got some validation reports and kind of testing matrices and that kind of stuff that we'd run on top of it.
306
You need to plan a cutover.
307
So yeah, you can hit go and say, does this actually work?
308
Into a branch, delete that branch, make sure it's working.
309
Maybe you copy it over into a branch You put a copy of the app onto it, make sure that works, keep the two apps dual running and then switch over when you're happy things are going.
310
The fact in like basically you've got the various branches gives you the ability to do test migrations
311
and change reworks and test some stuff.
312
push it a few times, do an elegant cutter, when you promoted one to production.
313
Life's good.
314
So yeah, lots of ways you can do it.
315
Lots of...
316
Yes, I've migrated the nice, easiest version to show how quick it is.
317
But this is stuff that used to take weeks.
318
to get put over.
319
You can now, if you're looking at Lakebase and you've got a Postgres database already, So easy just to...
320
Spin it up, try it out, see if it works.
321
what are you gonna lose in terms of pushing it giving
322
it a go testing it kicking the tiles in it and going oh
323
this is really fast and it spins up from zero in less than 500 milliseconds
324
means you can have a really really tightly coupled compute profile
325
to your usage profile of who's actually using the apps and things they're using them for.
326
just makes sense to me so that is all i wanted to show you
327
that is how we go about migrating lake base from uh an existing managed progres into lake base proper Now, if you're doing it from something else,
328
If you're doing it from SQL Server, maybe you're doing it from a different object store, you're doing it from a different managed postgres elsewhere.
329
Maybe going from Oracle and you're like, "Well, I want to switch over to Postgres and I want to move it into Lakebase."
330
There's a few other validators that we do as a pre -step.
331
You essentially do a syntax translation first into a default postgres
332
and then push it via the same tool just so we can keep those components working.
333
That's how we go about doing it.
334
loads of different ways you can do but Don't let the fact that you'll have to change some things put you off, because it is so easy to get you into there.
335
All righty, that is all I wanted to run through as always.
336
Thanks for joining me.
337
Don't forget to like and subscribe and yeah check it out because it is impressively fast and scalable.
338
I like it.
339
Cheers.
✨ Video consigliato
C11
497 frasi
Technical deep dive on Managed Instance for App Service | BRK102
Business EnglishUniversity StudentsWorking Adults
C11
202 frasi
How to Create Gold, Silver, and Bronze Tables in Postgres for a Medallion Data Architecture!
Business EnglishUniversity StudentsWorking Adults
C115
29 frasi
Amazon Athena explained in 120 seconds💡
Business EnglishUniversity StudentsWorking Adults
C11
689 frasi
Database Sharding! Designing Data-Intensive Applications chapter 6
Business EnglishUniversity StudentsWorking Adults
C11
162 frasi
The Simplest Way to Connect Claude and Power BI
Business EnglishUniversity StudentsWorking Adults
C111
93 frasi
Naive Bayes
Business EnglishUniversity StudentsWorking Adults
C1231 frasi
Redis Crash Course - the What, Why and How to use Redis as your primary database
Business EnglishUniversity StudentsWorking Adults
C11
12 frasi
Rapid Engineering With AI
Business EnglishUniversity StudentsWorking Adults
C11
674 frasi
FULL Data Science Mock Interview | SQL, Statistics, Business
Business EnglishInterview EnglishUniversity Students
C11
133 frasi
Data Ingestion Best Practices! How to Ingest Data Efficiently at Enterprise Scale!
Business EnglishUniversity StudentsWorking Adults
C11
207 frasi
How I Made $30,000 Hacking Broken Access Control
Business EnglishUniversity StudentsWorking Adults
B22
189 frasi
"SHIP GOES HERE" for flight 14 - Starbase Summary
Business EnglishUniversity StudentsWorking Adults
Informazioni su questa lezione
Cos'è la tecnica dello Shadowing?
Shadowing è una tecnica di apprendimento delle lingue supportata da studi scientifici, originariamente sviluppata per la formazione dei traduttori professionisti e resa popolare dal poliglotta Dr. Alexander Arguelles. Il metodo è semplice ma potente: ascolti un audio in inglese di madrelingua e lo ripeti immediatamente ad alta voce — come un'ombra che segue il parlante con un ritardo di solo 1–2 secondi. A differenza dell'ascolto passivo o degli esercizi di grammatica, lo shadowing costringe il tuo cervello e i muscoli della bocca a elaborare e riprodurre simultaneamente i modelli di discorso reale. La ricerca dimostra che migliora significativamente la precisione della pronuncia, l'intonazione, il ritmo, il discorso connesso, la comprensione dell'ascolto e la fluidità del parlato — rendendolo uno dei metodi più efficaci per la preparazione alla prova di speaking dell'IELTS e per la comunicazione reale in inglese.