Detach/attach table and index data files from one cluster to another
Hello,
The current process of transferring data files from one cluster to another
by using pg_dump and pg_restore is time consuming.
The proposed tool tries to make migration faster for tables and indices
only by copying their binary data files. This is like pg_upgrade but used
for migration of table and indices
* *The discussion here @
/messages/by-id/CAA-aLv5cQf09zvFRCB1XXUQLSp-ouX0S_Hq6ryScd6CtamipFQ@mail.gmail.com
speaks of possibility detaching/attaching databases as an alternative to
dump/restore. But the process of freezing XID’s and zeroing out LSN’s make
the solution equally time consuming if not more.
But if we consider just tables and indexes to be detached/reattached,
would this be a viable alternative to dump and restore of tables?
The same discussion indicates it could be done but is more complicated as
one has to deal with system catalogs of the newly mounted table and map old
OID’s to new ones. This is required to ensure consistency in roles, and
objects owned by those roles.
We would also need to ensure LSN values of the reattached pages are less
than the current WAL endpoint in receiver.
Are there any more issues we need to be aware of?
regards
Sameer
Sameer Thakur <samthakur74@gmail.com> writes:
The proposed tool tries to make migration faster for tables and indices
only by copying their binary data files.
There's 0 chance of making that work, because the two databases wouldn't
have the same notions of committed XIDs. You apparently don't
understand what you read in the other discussion --- the steps you are
objecting to are not optional, whether copying a whole tablespace or
only one table.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 04/12/2013 10:15 AM, Tom Lane wrote:
Sameer Thakur <samthakur74@gmail.com> writes:
The proposed tool tries to make migration faster for tables and indices
only by copying their binary data files.There's 0 chance of making that work, because the two databases wouldn't
have the same notions of committed XIDs.
Yeah. Trying to think way outside the box, could we invent some sort of
fixup mechanism that could be applied to adopted files? Of course, that
could slow things down so much that it wouldn't be worth it, but it
might be a nice research project.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andrew Dunstan escribió:
On 04/12/2013 10:15 AM, Tom Lane wrote:
Sameer Thakur <samthakur74@gmail.com> writes:
The proposed tool tries to make migration faster for tables and indices
only by copying their binary data files.There's 0 chance of making that work, because the two databases wouldn't
have the same notions of committed XIDs.Yeah. Trying to think way outside the box, could we invent some sort
of fixup mechanism that could be applied to adopted files? Of
course, that could slow things down so much that it wouldn't be
worth it, but it might be a nice research project.
I think the fixup procedure involves freezing Xids (prior to the
transporting), which the OP said he didn't want to do.
If you don't freeze beforehand, there's not enough info in the new
cluster to know which tuples are dead/alive. Another option would be to
have a "private" copy of pg_clog/pg_subtrans for the transported
table(s), but that seems very difficult to arrange.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andrew Dunstan <andrew@dunslane.net> writes:
On 04/12/2013 10:15 AM, Tom Lane wrote:
There's 0 chance of making that work, because the two databases wouldn't
have the same notions of committed XIDs.
Yeah. Trying to think way outside the box, could we invent some sort of
fixup mechanism that could be applied to adopted files?
Well, it wouldn't be that hard to replace XIDs with FrozenXID or
InvalidXID as appropriate, if you had access to the source database's
clog while you did the copying. It just wouldn't be very fast.
I suppose it would still be faster than a COPY transfer, but I'm not
sure it'd be enough faster to justify the work and the additional
portability hits you'd be taking.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-04-12 12:14:24 -0400, Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
On 04/12/2013 10:15 AM, Tom Lane wrote:
There's 0 chance of making that work, because the two databases wouldn't
have the same notions of committed XIDs.Yeah. Trying to think way outside the box, could we invent some sort of
fixup mechanism that could be applied to adopted files?Well, it wouldn't be that hard to replace XIDs with FrozenXID or
InvalidXID as appropriate, if you had access to the source database's
clog while you did the copying. It just wouldn't be very fast.
I think if one goes over the heap and hint bits everything (so the item
pointers don't have to be immediately rewritten), freeze everything and
such it should be doable at about disk speed unless you have a really
fast disk subsystem.
But it still is fairly complicated and I doubt its really necessary.
I suppose it would still be faster than a COPY transfer, but I'm not
sure it'd be enough faster to justify the work and the additional
portability hits you'd be taking.
Using binary copy might already give quite a speedup, Sameer, did you
try that?
Also, do you really need parts of a cluster or would a base backup of
the whole cluster do the trick?
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Apr 12, 2013 at 9:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
On 04/12/2013 10:15 AM, Tom Lane wrote:
There's 0 chance of making that work, because the two databases wouldn't
have the same notions of committed XIDs.Yeah. Trying to think way outside the box, could we invent some sort of
fixup mechanism that could be applied to adopted files?Well, it wouldn't be that hard to replace XIDs with FrozenXID or
InvalidXID as appropriate, if you had access to the source database's
clog while you did the copying. It just wouldn't be very fast.
Would it be possible to fix the XIDs *after* copying the data files,
potentially on a different server so as to avoid any additional overhead on
the main server ? I guess so, though we will probably need some mechanism
to lock out access to the table (which seems easy), flush all its data
pages to the disk and some way to reliably flush all clog pages as well so
that they can be copied along with the data files. The page LSNs seem to be
easy to handle and can be easily zeroed out outside the server.
I wonder though if this all look like a material for something like
pg_reorg(pack) though some kind of support from the core may be required.
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
On Fri, Apr 12, 2013 at 10:22:38PM +0530, Pavan Deolasee wrote:
On Fri, Apr 12, 2013 at 9:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
On 04/12/2013 10:15 AM, Tom Lane wrote:
There's 0 chance of making that work, because the two databases wouldn't
have the same notions of committed XIDs.Yeah. Trying to think way outside the box, could we invent some sort of
fixup mechanism that could be applied to adopted files?Well, it wouldn't be that hard to replace XIDs with FrozenXID or
InvalidXID as appropriate, if you had access to the source database's
clog while you did the copying. It just wouldn't be very fast.Would it be possible to fix the XIDs *after* copying the data files,
potentially on a different server so as to avoid any additional overhead on the
main server ? I guess so, though we will probably need some mechanism to lock
out access to the table (which seems easy), flush all its data pages to the
disk and some way to reliably flush all clog pages as well so that they can be
copied along with the data files. The page LSNs seem to be easy to handle and
can be easily zeroed out outside the server.I wonder though if this all look like a material for something like pg_reorg
(pack) though some kind of support from the core may be required.
Uh, now that you mention it, pg_upgrade in non-link mode does
something similer, in that it copies the data files and clog. You could
use pg_upgrade in non-link mode, run VACUUM FREEZE on the upgraded
cluster, and then copy the data files.
The only problem is that pg_upgrade can't upgrade tablespaces with the
same system catalog version because the tablespace directory names would
conflict.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Well, it wouldn't be that hard to replace XIDs with FrozenXID or
InvalidXID as appropriate, if you had access to the source database's
clog while you did the copying. It just wouldn't be very fast.
If you're doing that in a streaming method, it strikes me that it'd be
plenty fast.
I suppose it would still be faster than a COPY transfer, but I'm not
sure it'd be enough faster to justify the work and the additional
portability hits you'd be taking.
The big win here over a binary COPY is pulling through the indexes as-is
as well- without having to rebuild them.
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
I suppose it would still be faster than a COPY transfer, but I'm not
sure it'd be enough faster to justify the work and the additional
portability hits you'd be taking.
The big win here over a binary COPY is pulling through the indexes as-is
as well- without having to rebuild them.
Meh. That raises the ante another substantial multiple with respect to
the amount of portability risk (eg, you're now absolutely dependent on
locale sort orders to be identical in both databases). And I think
you'd have to freeze all updates to the table while you were copying the
table+indexes, if you wanted them to be consistent.
I can't imagine that we'd accept a patch that says to the recipient
database, "here are some large binary blobs, please believe that
they represent a valid table and associated indexes. Oh, and don't you
dare try to actually check them, because that would be slow."
Some other interesting things to think about here would be toast-table
OIDs embedded in toast pointers, data type OIDs embedded in arrays (and
maybe records too, I forget), enum value OIDs, btree vacuum cycle IDs,
GiST NSNs ... not sure what else, but I bet that's not a complete list.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
The big win here over a binary COPY is pulling through the indexes as-is
as well- without having to rebuild them.
[... lots of reasons this is hard ...]
I agree that it's quite a bit more difficult, to the point that logical
replication which can be selective (eg: give me only table X + indexes)
might end up being the only answer, but otherwise this approach will
likely only be a modest improvement over binary COPY FREEZE- and there
only because we essentially end up skipping the type validation (which
we could just provide as an option, similar to COPY FREEZE...).
Thanks,
Stephen
On Fri, Apr 12, 2013 at 9:52 PM, Andres Freund <andres@2ndquadrant.com>wrote:
Show quoted text
On 2013-04-12 12:14:24 -0400, Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
On 04/12/2013 10:15 AM, Tom Lane wrote:
There's 0 chance of making that work, because the two databases
wouldn't
have the same notions of committed XIDs.
Yeah. Trying to think way outside the box, could we invent some sort of
fixup mechanism that could be applied to adopted files?Well, it wouldn't be that hard to replace XIDs with FrozenXID or
InvalidXID as appropriate, if you had access to the source database's
clog while you did the copying. It just wouldn't be very fast.I think if one goes over the heap and hint bits everything (so the item
pointers don't have to be immediately rewritten), freeze everything and
such it should be doable at about disk speed unless you have a really
fast disk subsystem.
But it still is fairly complicated and I doubt its really necessary.I suppose it would still be faster than a COPY transfer, but I'm not
sure it'd be enough faster to justify the work and the additional
portability hits you'd be taking.Using binary copy might already give quite a speedup, Sameer, did you
try that?
No we have not so far, was soliciting feedback first from the hackers and
possibly implement as a contrib module. Also i did misread the earlier post
on the subject.
Also, do you really need parts of a cluster or would a base backup of
the whole cluster do the trick?We were looking at parts of cluster as an faster alternative to pg_dump
and restore--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services