pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMigrating a Database to a new tablespace
16+ messages / 4 participants
[nested] [flat]
* Migrating a Database to a new tablespace
@ 2006-04-18 12:05 Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 16+ messages in thread
From: Markus Schaber @ 2006-04-18 12:05 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
@ 2006-04-24 10:05 ` Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 16+ messages in thread
From: Markus Schaber @ 2006-04-24 10:05 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
@ 2006-04-24 10:33 ` Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 16+ messages in thread
From: Markus Schaber @ 2006-04-24 10:33 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
@ 2006-04-24 11:34 ` Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 16+ messages in thread
From: Markus Schaber @ 2006-04-24 11:34 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
@ 2006-04-25 00:15 ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 13:13 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 16+ messages in thread
From: Jim C. Nasby @ 2006-04-25 00:15 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
@ 2006-04-25 13:13 ` Markus Schaber <schabi@logix-tt.com>
2006-04-25 15:19 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Markus Schaber @ 2006-04-25 13:13 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 13:13 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
@ 2006-04-25 15:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-25 16:50 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2006-04-25 15:19 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 13:13 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 15:19 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-04-25 16:50 ` Markus Schaber <schabi@logix-tt.com>
2006-04-25 18:13 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Markus Schaber @ 2006-04-25 16:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 13:13 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 15:19 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-25 16:50 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
@ 2006-04-25 18:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 08:10 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2006-04-25 18:13 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 13:13 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 15:19 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-25 16:50 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 18:13 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-04-26 08:10 ` Markus Schaber <schabi@logix-tt.com>
2006-04-26 16:33 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Markus Schaber @ 2006-04-26 08:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 13:13 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 15:19 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-25 16:50 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 18:13 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 08:10 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
@ 2006-04-26 16:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 16:35 ` Re: Migrating a Database to a new tablespace Bruce Momjian <pgman@candle.pha.pa.us>
2006-04-27 08:53 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
0 siblings, 2 replies; 16+ messages in thread
From: Tom Lane @ 2006-04-26 16:33 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 13:13 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 15:19 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-25 16:50 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 18:13 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 08:10 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-26 16:33 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-04-26 16:35 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-04-27 05:38 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
2006-04-27 09:09 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
1 sibling, 2 replies; 16+ messages in thread
From: Bruce Momjian @ 2006-04-26 16:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Markus Schaber <schabi@logix-tt.com>; pgsql-sql
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. +
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 13:13 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 15:19 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-25 16:50 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 18:13 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 08:10 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-26 16:33 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 16:35 ` Re: Migrating a Database to a new tablespace Bruce Momjian <pgman@candle.pha.pa.us>
@ 2006-04-27 05:38 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 16+ messages in thread
From: Jim C. Nasby @ 2006-04-27 05:38 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Markus Schaber <schabi@logix-tt.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 13:13 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 15:19 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-25 16:50 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 18:13 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 08:10 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-26 16:33 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 16:35 ` Re: Migrating a Database to a new tablespace Bruce Momjian <pgman@candle.pha.pa.us>
@ 2006-04-27 09:09 ` Markus Schaber <schabi@logix-tt.com>
2006-04-27 15:06 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 16+ messages in thread
From: Markus Schaber @ 2006-04-27 09:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 13:13 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 15:19 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-25 16:50 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 18:13 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 08:10 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-26 16:33 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 16:35 ` Re: Migrating a Database to a new tablespace Bruce Momjian <pgman@candle.pha.pa.us>
2006-04-27 09:09 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
@ 2006-04-27 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 16+ messages in thread
From: Tom Lane @ 2006-04-27 15:06 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Migrating a Database to a new tablespace
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Re: Migrating a Database to a new tablespace Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 13:13 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 15:19 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-25 16:50 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-25 18:13 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 08:10 ` Re: Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-26 16:33 ` Re: Migrating a Database to a new tablespace Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-04-27 08:53 ` Markus Schaber <schabi@logix-tt.com>
1 sibling, 0 replies; 16+ messages in thread
From: Markus Schaber @ 2006-04-27 08:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2006-04-27 15:06 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-18 12:05 Migrating a Database to a new tablespace Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:05 ` Markus Schaber <schabi@logix-tt.com>
2006-04-24 10:33 ` Markus Schaber <schabi@logix-tt.com>
2006-04-24 11:34 ` Markus Schaber <schabi@logix-tt.com>
2006-04-25 00:15 ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 13:13 ` Markus Schaber <schabi@logix-tt.com>
2006-04-25 15:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-25 16:50 ` Markus Schaber <schabi@logix-tt.com>
2006-04-25 18:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 08:10 ` Markus Schaber <schabi@logix-tt.com>
2006-04-26 16:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 16:35 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-04-27 05:38 ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-27 09:09 ` Markus Schaber <schabi@logix-tt.com>
2006-04-27 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-27 08:53 ` Markus Schaber <schabi@logix-tt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox