Обсуждение: Migrating a Database to a new tablespace
Hello, What is the easiest way to migrate a complete database from one tablespace to another? ALTER DATABASE only allows to set the default tablespace, but not migrating the existing database. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Hello, Markus Schaber wrote: >>What is the easiest way to migrate a complete database from one >>tablespace to another? >> >>ALTER DATABASE only allows to set the default tablespace, but not >>migrating the existing database. > > Is there really no way to do this? Via some scripting, I now migrated all tables and indices, however I found no way to migrate sequences. The problem is that I cannot just move the tablespace itsself via mv/symlink/fiddling_of_systables, because the tablespace contains some other databases that are meant to remain there. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Hello, Markus Schaber wrote: > What is the easiest way to migrate a complete database from one > tablespace to another? > > ALTER DATABASE only allows to set the default tablespace, but not > migrating the existing database. Is there really no way to do this? Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Hello, I now pulled the plug, migrated all databases via "create database ... tempate olddatabase tablespace newts" to new tablespaces, one for each database, and dropped all old databases that contained references to the tablespace. Pgadmin3 also shows that the tablespace is not referenced by anything. But I cannot drop it, I get the following message: postgres=# drop TABLESPACE foo; ERROR: tablespace "foo" is not empty It seems that the whole tablespace thing is not yet 100% waterproof, good that this did happen on a developer machine, and not on a production machine. Thanks for your patience, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
That means that the tablespace directory isn't empty. On Mon, Apr 24, 2006 at 01:34:33PM +0200, Markus Schaber wrote: > Hello, > > I now pulled the plug, migrated all databases via "create database ... > tempate olddatabase tablespace newts" to new tablespaces, one for each > database, and dropped all old databases that contained references to the > tablespace. Pgadmin3 also shows that the tablespace is not referenced by > anything. > > But I cannot drop it, I get the following message: > > postgres=# drop TABLESPACE foo; > ERROR: tablespace "foo" is not empty > > > It seems that the whole tablespace thing is not yet 100% waterproof, > good that this did happen on a developer machine, and not on a > production machine. > > Thanks for your patience, > Markus > > -- > Markus Schaber | Logical Tracking&Tracing International AG > Dipl. Inf. | Software Development GIS > > Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Hi, Jim, Jim C. Nasby wrote: > That means that the tablespace directory isn't empty. This might be some artifacts from backend kills / crashes, partially during COPY and CREATE DATABASE operations. (It's a developer machine after all). So when pgadmin3 displays no dependencies, can I assume it is safe to empty the tablespace directory manually, and then drop the tablespace? Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Markus Schaber <schabi@logix-tt.com> writes:
> So when pgadmin3 displays no dependencies, can I assume it is safe to
> empty the tablespace directory manually, and then drop the tablespace?
Instead of assuming anything, why don't you look in the tablespace
directory and see what's there? A quick "ls -aR" would give more
information than guessing.
regards, tom lane
Hi, Tom, Tom Lane wrote: >>So when pgadmin3 displays no dependencies, can I assume it is safe to >>empty the tablespace directory manually, and then drop the tablespace? > > Instead of assuming anything, why don't you look in the tablespace > directory and see what's there? A quick "ls -aR" would give more > information than guessing. There's plenty of stuff there, 8.8 Gigabytes in total. The question is how to determine if any of those files are still needed, and how to migrate them so I can drop that tablespace. -> ls -aR .: . .. 16406 760866 PG_VERSION ./16406: . 10308 10740 1255 2602 2614 2655 2667 2683 2699 .. 10310 10742 1259 2603 2615 2656 2668 2684 2700 10287 10723 10743 16789 2604 2616 2657 2669 2685 2701 10289 10725 10745 16791 2605 2617 2658 2670 2686 2702 10293 10727 10747 16793 2606 2618 2659 2673 2687 2703 10295 10728 10748 16794 2607 2619 2660 2674 2688 2704 10299 10730 10750 16796 2608 2620 2661 2675 2689 PG_VERSION 10301 10732 10752 16798 2609 2650 2662 2678 2690 pg_internal.init 10302 10733 1247 16800 2610 2651 2663 2679 2691 10304 10735 1248 16801 2611 2652 2664 2680 2692 10305 10737 1249 2600 2612 2653 2665 2681 2693 10307 10738 1250 2601 2613 2654 2666 2682 2696 ./760866: . 2620 301718 301891 302062 302231 318719 397700 .. 2650 301722 301893 302064 302233 318721 397702 10287 2651 301724 301895 302068 302237 319117 397704 10289 2652 301728 301899 302070 302239 319119 397706 10293 2653 301730 301901 302072 302244 319122 397707 10295 2654 301732 301906 302076 302246 319123 397709 10299 2655 301736 301908 302078 302248 319124 397711 10301 2656 301738 301910 302082 302252 319125 397715 10302 2657 301743 301914 302084 302254 319126 397716 10304 2658 301745 301916 302086 302258 319128 397717 10305 2659 301747 301921 302090 302260 319129 397718 10307 2660 301751 301923 302092 302262 319130 397720 10308 2661 301753 301925 302096 302262.1 319132 397722 10310 2662 301758 301929 302098 302266 319133 397752 10723 2663 301760 301931 302100 302268 319138 397754 10725 2664 301762 301936 302104 302273 319140 397756 10727 2665 301766 301938 302106 302275 319141 399801 10728 2666 301768 301940 302111 302277 319143 399802 10730 2667 301773 301944 302113 302281 319145 399804 10732 2668 301775 301946 302115 302283 319146 399806 10733 2669 301777 301951 302119 302287 319147 399807 10735 2670 301781 301953 302121 302289 319149 399808 10737 2673 301783 301955 302126 302291 319151 399809 10738 2674 301788 301959 302128 302295 319153 399810 10740 2675 301790 301961 302130 302297 319155 399811 10742 2678 301792 301966 302134 302302 319185 399812 10743 2679 301796 301968 302136 302304 319187 399814 10745 2680 301798 301970 302141 302306 319189 399816 10747 2681 301803 301974 302143 302310 319193 399817 10748 2682 301805 301976 302145 302312 319195 399818 10750 2683 301807 301981 302149 302317 390770 399820 10752 2684 301811 301983 302151 302319 390772 399822 1247 2685 301813 301985 302156 302321 390774 399824 1248 2686 301818 301989 302158 302325 396855 399825 1249 2687 301820 301991 302160 302327 396857 399827 1250 2688 301822 301996 302164 302332 396859 399829 1255 2689 301826 301998 302166 302334 397257 400025 1259 2690 301828 302000 302170 302336 397258 400026 2600 2691 301833 302004 302172 302340 397263 400028 2601 2692 301835 302006 302174 302342 397265 400030 2602 2693 301837 302011 302178 302347 397267 400037 2603 2696 301841 302013 302180 302349 397277 400038 2604 2699 301843 302015 302185 302351 397278 400040 2605 2700 301848 302019 302187 302351.1 397280 400042 2606 2701 301850 302021 302189 302355 397282 400082 2607 2702 301852 302026 302193 302357 397305 400083 2608 2703 301856 302028 302195 302362 397306 400085 2609 2704 301858 302030 302199 302364 397308 400087 2610 301500 301863 302034 302201 302366 397310 400483 2611 301502 301865 302036 302203 302370 397347 400484 2612 301504 301867 302040 302207 302372 397348 PG_VERSION 2613 301505 301871 302042 302209 317537 397350 pg_internal.init 2614 301507 301873 302044 302214 317540 397352 2615 301509 301877 302048 302216 318418 397397 2616 301511 301879 302050 302218 318420 397417 2617 301512 301881 302054 302222 318553 397419 2618 301714 301885 302056 302224 318554 397421 2619 301716 301887 302058 302229 318718 397692 Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Markus Schaber <schabi@logix-tt.com> writes:
> Tom Lane wrote:
>> Instead of assuming anything, why don't you look in the tablespace
>> directory and see what's there? A quick "ls -aR" would give more
>> information than guessing.
> There's plenty of stuff there, 8.8 Gigabytes in total. The question is
> how to determine if any of those files are still needed, and how to
> migrate them so I can drop that tablespace.
Match the subdirectory names against pg_database.oid --- any subdir that
doesn't correspond to any live entry in pg_database is junk and can be
flushed. Within a valid database's subdirectory, match the file names
to that database's pg_class.relfilenode (not oid!) to see if any of them
are live.
regards, tom lane
Hi, Tom, Tom Lane wrote: > Match the subdirectory names against pg_database.oid --- any subdir that > doesn't correspond to any live entry in pg_database is junk and can be > flushed. Within a valid database's subdirectory, match the file names > to that database's pg_class.relfilenode (not oid!) to see if any of them > are live. Ah, that did it. Both "top level" Directories are not found in pg_database.oid, so they are leftovers from dropped databases. I deleted them, and then the tablespace could be dropped. Thanks a lot, Tom. As I said the leftovers are likely to be caused by hard kills and backend crashes, so I would not go into deeper analysis, but maybe the finding and possibly removing of such leftovers should be half-automated to assist server admins. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Markus Schaber <schabi@logix-tt.com> writes:
> As I said the leftovers are likely to be caused by hard kills and
> backend crashes, so I would not go into deeper analysis, but maybe the
> finding and possibly removing of such leftovers should be half-automated
> to assist server admins.
It's been discussed. Personally I'm afraid of the idea of automatically
deleting files that seem unreferenced, but having a tool to find them
for manual deletion isn't a bad idea. I think Bruce had a prototype
patch at one point --- not sure what the status is.
regards, tom lane
Tom Lane wrote: > Markus Schaber <schabi@logix-tt.com> writes: > > As I said the leftovers are likely to be caused by hard kills and > > backend crashes, so I would not go into deeper analysis, but maybe the > > finding and possibly removing of such leftovers should be half-automated > > to assist server admins. > > It's been discussed. Personally I'm afraid of the idea of automatically > deleting files that seem unreferenced, but having a tool to find them > for manual deletion isn't a bad idea. I think Bruce had a prototype > patch at one point --- not sure what the status is. > I have work someone did in the past. I just need to be updated to deal with tablespaces. ftp://candle.pha.pa.us/pub/postgresql/mypatches/checkfile.* Let me know if you want details. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Wed, Apr 26, 2006 at 12:35:39PM -0400, Bruce Momjian wrote: > Tom Lane wrote: > > Markus Schaber <schabi@logix-tt.com> writes: > > > As I said the leftovers are likely to be caused by hard kills and > > > backend crashes, so I would not go into deeper analysis, but maybe the > > > finding and possibly removing of such leftovers should be half-automated > > > to assist server admins. > > > > It's been discussed. Personally I'm afraid of the idea of automatically > > deleting files that seem unreferenced, but having a tool to find them > > for manual deletion isn't a bad idea. I think Bruce had a prototype > > patch at one point --- not sure what the status is. > > > > I have work someone did in the past. I just need to be updated to deal > with tablespaces. > > ftp://candle.pha.pa.us/pub/postgresql/mypatches/checkfile.* > > Let me know if you want details. Is it able to also delete the cruft? Seems to be a useful extension, especially on windows, which AFAIK doesn't have an equivalent to ``. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Hi, Tom, Tom Lane wrote: >>As I said the leftovers are likely to be caused by hard kills and >>backend crashes, so I would not go into deeper analysis, but maybe the >>finding and possibly removing of such leftovers should be half-automated >>to assist server admins. > > It's been discussed. Personally I'm afraid of the idea of automatically > deleting files that seem unreferenced, but having a tool to find them > for manual deletion isn't a bad idea. I also don't like the idea of full automated deletion, that's why I wrote half-automated. I thought of a tool that enumarates suspective files. Then admins can look at the mtime/atime, or move them away and try what happens, or even pipe the output to "xargs rm -rf" if they want. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Hi, Bruce, Bruce Momjian wrote: > I have work someone did in the past. I just need to be updated to deal > with tablespaces. > > ftp://candle.pha.pa.us/pub/postgresql/mypatches/checkfile.* > > Let me know if you want details. It looks nice, but I would not like automatic deletion (or only optional or configurable), there might be valid reasons for "alien" files to exist. (README files for admins to communicate what this directory is used for, MacOS ressource forks, Reiser4 special files etc...) What about putting the identification code in a set returning function or view that can be called by the admin. Then tools like nagios can call it and ring the alarm bells when unknown files appear, or possibly autovacuum calls it once on each session to generate log warnings. Btw, are CLUSTER and ALTER TABLE ALTER COLUMN statements that "create or drop a relation" in this sense? And what is with tables getting extended to more than one file? Another idea would be to first create and sync log entries about the files to be created / deleted, and then actually create / delete the files. So on log replay, the backend could watch out for file operations from transactions that never completed. It will slow those operations down, but such file operations are both seldom and relatively expensive on most filesystems. And it does not help in case of fsync=off, but those admins already take worse risks than wasting disk space. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Markus Schaber <schabi@logix-tt.com> writes:
> Bruce Momjian wrote:
>> I have work someone did in the past. I just need to be updated to deal
>> with tablespaces.
>>
>> ftp://candle.pha.pa.us/pub/postgresql/mypatches/checkfile.*
> It looks nice, but I would not like automatic deletion (or only optional
> or configurable), there might be valid reasons for "alien" files to
> exist. (README files for admins to communicate what this directory is
> used for, MacOS ressource forks, Reiser4 special files etc...)
> What about putting the identification code in a set returning function
> or view that can be called by the admin.
Yeah, one thing I never liked about the previously proposed patches was
that they were tied to postmaster start/restart. It'd be better to have
an external tool that could be invoked on demand. Maybe something close
to oid2name.
regards, tom lane