►
From YouTube: Postgres: Partitioning + FDW and schema migrations
Description
Follow-up to https://www.youtube.com/watch?v=MiZFtM84x44 to checkout how we can do schema migrations.
Very basic examples: Adding and dropping a column.
Part 1: https://www.youtube.com/watch?v=MiZFtM84x44
Part 2: https://www.youtube.com/watch?v=nt4Khi9Gr3o&feature=youtu.be
Part 3: https://youtu.be/ztQtNmSYmEo
A
Okay,
so
in
the
previous
demo,
we've
seen
how
to
create
partitions
and
we've
also
pushed
them
to
charts.
In
this
case
they
were
a
shard
is
a
local
database
and
we
created
two
shards
and
we
pushed
eight
partitions
to
to
those
shards
so
that
the
data
actually
lives
in
the
in
the
foreign
table
and
in
this
separate
database
in
this
demo,
we're
looking
at
how
to
do
schema,
migrations
and
we're
basically
just
adding
a
column
and
dropping
that
column
later.
So,
let's
quickly
look
how
how
the
setup
looks
like
at
the
moment.
A
So
we
have
this
new
stable.
It
is
still
partition,
so
we
can
use,
we
can
check
pad.
We
can
see
the
those
are
pretty
standard,
declarative
partitions
here
and
then
once
we
see
where
they
live.
We
realize
this.
Those
are
foreign
tables
right
and
then
we
have
these
two
shards.
Those
are
databases
shown
one
chart
to
and
there
we
actually
find
those
tables.
So
half
of
the
partitions
live
in
chart
one,
the
other
half
lives
in
solitude
and
now
a
standard
migration
would
look
like
this,
adding
a
column
on
a
table.
A
We
would
basically
come
in
and
just
run
the
alter
table
statement.
Adding
the
column
listens
of
course
possible
year
two,
but
now
we've
only
run.
We've
only
run
this
on
the
main
database
and
not
on
the
charts.
So,
let's
see
what
happens
once
we
select
from
the
table,
it's
basically
fails
because
the
column
that
we
added
is
present
on
the
main
database,
but
once
we
reach
out
to
the
shards,
we
don't
see
the
same
schema,
so
this
column
is
basically
missing,
so
we
have
to
do
it
slightly
differently.
My
prepared
is
over
here.
A
So
there
is
an
add
column,
migration.
Let's
look
at
the
main
part.
This
is
basically
what
we
just
been
doing,
adding
column
to
the
main
table,
and
then
there
is
migration
scripts
for
each
of
the
charts-
and
in
this
case
we
simply
add
the
column
to
each
of
the
partitions
and
those
result
and
the
shark
database.
So
let's
run
this
on
one
of
the
charts
first
so
shark
in
this
case
and
then
do
the
same
thing
for
shard
1
and
now,
let's
check
the
result,
so
we
see
that
cards
0,
that's
one
of
the
partitions.
A
Now
has
this
additional
column,
and
so
the
average.
Now,
if
we
connect
to
the
main
database
and
do
the
same
thing
again,
we
can
do
we
can
select
from
the
issues
table.
We
don't
see
the
column
yet
so
this
would
switch
you
up
over
here,
but
we
don't
see
the
column
yet
and
that's
the
reason
for
that
is
that
we
haven't
added
the
column
to
the
main
schema.
So,
let's,
let's
tell
the
main
scheme
our
about
the
columns
on
this
case.
We
execute
that
on
the
main
database.
It's
just
the
alter
table
statement.
A
Now
we
run
that
again.
We
now
see
that
column
here,
so
we
can
actually
do
we
select
foo.
This
is
new
column,
doesn't
have
any
data
in
it,
but
it
is
now
known.
Looking
perhaps
update
the
tables
relate
some
data
into
that
that
updates
all
the
partitions
and
both
shards
and
now
selecting
that
gives
us
data
to
look
at.
A
Now,
if
we
start
by
dropping
the
column
on
the
chart,
it's
going
to
be
a
problem
because
the
queries
from
the
main
database-
they
still
assume
that
there
is
this
column,
so
basically
works
the
other
way
around
and
adding
the
column.
So
in
this
case
we
would
drop
the
column
on
the
main
database.
First.
A
And
then
this
won't
work
anymore
because
that
column
doesn't
exist
anymore.
Let's
to
go
to
the
shards,
we
still
see
the
column
over
there,
so
this
is
still
present
that
we
can
actually
still
do
so
like
crew
from
our
zeroes
or
from
one
of
their
partitions,
and
we
still
see
the
data
and
the
colonists
still
there.
Of
course,
now,
let's
drop
that
from
human
charge,
charge
1,
that's
not
possible
anymore
and
same
goes
for
shard.
A
So
that's
actually
pretty
certain
forward
for
those
examples,
perhaps
that
changed
in
more
in
the
major
version
consisted
of
support
for
Vendetta
replicas,
better,
that's
something
I
find
out,
but
that's
I
think
that
that
would
be
fine.
We
will
be
going
around
sort
of
updating
the
charts
first,
depending
on
the
situation
and
then
up
to
updating
the
main
database
or
the
other
way
around.