pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum
33+ messages / 20 participants
[nested] [flat]
* vacuum
@ 2000-07-13 23:47 Rainer Mager <rmager@vgkk.com>
0 siblings, 0 replies; 33+ messages in thread
From: Rainer Mager @ 2000-07-13 23:47 UTC (permalink / raw)
To: pgsql-admin
Hi all,
I'm sure many other people have faced this and I'm wondering what solutions
were found.
We have a product that uses PostgreSQL as its database. Currently, via a
web-based administration interface, we provide the administrators the
ability to vacuum their database whenever they wish to. We would prefer to
provide this functionality automatically such that they don't need to think
about it.
We could easily have the system vacuum once a day (or week or whatever) but
we are concerned that the vacuum process will slow down the system while it
is being performed. This means that we would need to try to do some analysis
on the business of the system and then vacuum at a perceived slow time. This
sounds difficult.
What do other people do? How much of a hit is vacuum when the system is
operating? Any other ideas?
Thanks in advance,
--Rainer
^ permalink raw reply [nested|flat] 33+ messages in thread
* RE: vacuum
@ 2000-07-13 23:48 Neil Toronto <NToronto@Dentrix.com>
2000-07-14 01:55 ` RE: vacuum Rainer Mager <rmager@vgkk.com>
0 siblings, 1 reply; 33+ messages in thread
From: Neil Toronto @ 2000-07-13 23:48 UTC (permalink / raw)
To: pgsql-admin
Maybe a good measure of how often the database needs to be vacuumed is how
often the administrators currently vacuum it?
For instance, if it's being vacuumed less than once a day, it may be
perfectly acceptable to vacuum it once early in the morning when nobody is
using it - assuming nobody uses it then, of course.
If the administrators have been using the product for a while, it may not be
too much of a problem to ask when the "slow times" are - you might be able
to just ask them. We do electronic billing, and I've found that the people
who know the most about slow times and such are those people, and the Tech
Support Queen.
Just a couple of thoughts...
Neil
-----Original Message-----
From: Rainer Mager [mailto:rmager@vgkk.com]
Sent: Thursday, July 13, 2000 5:47 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] vacuum
Hi all,
I'm sure many other people have faced this and I'm wondering what
solutions
were found.
We have a product that uses PostgreSQL as its database. Currently,
via a
web-based administration interface, we provide the administrators the
ability to vacuum their database whenever they wish to. We would prefer to
provide this functionality automatically such that they don't need to think
about it.
We could easily have the system vacuum once a day (or week or
whatever) but
we are concerned that the vacuum process will slow down the system while it
is being performed. This means that we would need to try to do some analysis
on the business of the system and then vacuum at a perceived slow time. This
sounds difficult.
What do other people do? How much of a hit is vacuum when the system
is
operating? Any other ideas?
Thanks in advance,
--Rainer
^ permalink raw reply [nested|flat] 33+ messages in thread
* RE: vacuum
2000-07-13 23:48 RE: vacuum Neil Toronto <NToronto@Dentrix.com>
@ 2000-07-14 01:55 ` Rainer Mager <rmager@vgkk.com>
2000-07-14 01:59 ` Re: vacuum Jean-Marc Pigeon <jmp@safe.ca>
0 siblings, 1 reply; 33+ messages in thread
From: Rainer Mager @ 2000-07-14 01:55 UTC (permalink / raw)
To: pgsql-admin
Good ideas except I think I wasn't clear. The system is a commercial product
with numerous individual customers. Each customer is somewhat unique and we
want to have the system capable of vacuuming automatically for all users.
The thing is that some users might have systems that are used little in the
early morning but others might be very different. We would rather not do
usage statistic gathering/analysis if there was a better way.
-Rainer
> -----Original Message-----
> From: pgsql-admin-owner@hub.org
> [mailto:pgsql-admin-owner@hub.org]On Behalf Of Neil Toronto
> Sent: Friday, July 14, 2000 8:48 AM
> To: pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] vacuum
>
>
> Maybe a good measure of how often the database needs to be vacuumed is how
> often the administrators currently vacuum it?
>
> For instance, if it's being vacuumed less than once a day, it may be
> perfectly acceptable to vacuum it once early in the morning when nobody is
> using it - assuming nobody uses it then, of course.
>
> If the administrators have been using the product for a while, it
> may not be
> too much of a problem to ask when the "slow times" are - you might be able
> to just ask them. We do electronic billing, and I've found that
> the people
> who know the most about slow times and such are those people, and the Tech
> Support Queen.
>
> Just a couple of thoughts...
> Neil
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuum
2000-07-13 23:48 RE: vacuum Neil Toronto <NToronto@Dentrix.com>
2000-07-14 01:55 ` RE: vacuum Rainer Mager <rmager@vgkk.com>
@ 2000-07-14 01:59 ` Jean-Marc Pigeon <jmp@safe.ca>
0 siblings, 0 replies; 33+ messages in thread
From: Jean-Marc Pigeon @ 2000-07-14 01:59 UTC (permalink / raw)
To: rmager@vgkk.com; +Cc: pgsql-admin
Bonjour Rainer Mager
>
> Good ideas except I think I wasn't clear. The system is a commercial product
> with numerous individual customers. Each customer is somewhat unique and we
> want to have the system capable of vacuuming automatically for all users.
>
> The thing is that some users might have systems that are used little in the
> early morning but others might be very different. We would rather not do
> usage statistic gathering/analysis if there was a better way.
We have the same kind of application here.
We vacuum according the load, if the load start to be "high"
we vacuum ->earlier<-, ok there is a little penalty on users, but as soon
the data-base is clean, the performance is dramaticaly improving
and the load just drop. So instead to have user penalised
for a long time, we just use it as a trigger to clean the base.
(we do not want the user to vacuum at will)
A bientot
==========================================================================
Jean-Marc Pigeon Internet: Jean-Marc.Pigeon@safe.ca
SAFE Inc. Phone: (514) 493-4280 Fax: (514) 493-1946
REGULUS, a real time accounting/billing package for ISP
REGULUS' Home base <"http://www.regulus.safe.ca";
==========================================================================
^ permalink raw reply [nested|flat] 33+ messages in thread
* Vacuum.
@ 2001-03-01 01:26 Grant <grant@conprojan.com.au>
0 siblings, 0 replies; 33+ messages in thread
From: Grant @ 2001-03-01 01:26 UTC (permalink / raw)
To: pgsql-admin
Does vacuuming and analyzing a database affect the users if they are
currently inserting/deleting rows from a table on the database?
Thanks.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Vacuum
@ 2001-07-16 19:26 Chris Ruprecht <chrup999@yahoo.com>
2001-07-16 22:59 ` Re: Vacuum Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 33+ messages in thread
From: Chris Ruprecht @ 2001-07-16 19:26 UTC (permalink / raw)
To: pgsql-admin
Hi fellow Postgressers
I have a relatively small database. The data itself is about 1.5GB. After I did some index value changes over the weekend, I decided that it's time to do a vacuum - the db had grown to 10 GB in size - which was just unrealistic.
at 7:13 this otherwise fine morning, I started the vacuum and was hoping for a run time of maybe 2 hours - max. Well, it is 2:20 PM now and it's still ticking. It has created so far 1411 files in $PGDATA/pg_xlog (which, since last night resides on a different drive from the database). Each of the file is 16 MB in size and if this continues for another 3 hours (or so) I'm running out of disk space.
If I would have done a
dump all data
drop db
create new db
restore schema (create tables/indexes/sequences)
load all data
I would have been done in about 2 hours.
Can somebody explain to me what this vacuum is doing with all these files? I suppose, this transaction logging stuff needs some serious looking into/re-writing.
Is there a way to switch this stuff off all together?
Best regards,
Chris
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2001-07-16 19:26 Vacuum Chris Ruprecht <chrup999@yahoo.com>
@ 2001-07-16 22:59 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 33+ messages in thread
From: Tom Lane @ 2001-07-16 22:59 UTC (permalink / raw)
To: Chris Ruprecht <chrup999@yahoo.com>; +Cc: pgsql-admin
"Chris Ruprecht" <chrup999@yahoo.com> writes:
> Can somebody explain to me what this vacuum is doing with all these
> files?
Just wasting space, I'm afraid. You may want to apply the patch at
http://www.ca.postgresql.org/mhonarc/pgsql-patches/2001-06/msg00061.html
However, if the database consists of several small tables and not one
huge one, I'm not sure why VACUUM's WAL log might grow so long. The
log should be truncatable after each table is vacuumed, even without
the patch.
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Vacuum
@ 2001-10-08 14:37 Summer S. Wilson <collectonian@eclectic-world.com>
2001-10-08 16:03 ` Re: Vacuum Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 33+ messages in thread
From: Summer S. Wilson @ 2001-10-08 14:37 UTC (permalink / raw)
To: pgsql-admin
Hi all,
I'm running PostgreSQL 7.1 with Slackware 8 on a dual processor Dell server.
I have a database with 17 tables. I started VacuumDB with the analyze
option on Friday around 3pm...when I came in this morning at 8 it was still
not done. From the verbose info, it seems to be "stuck" on one table,
whowrote which is the second largest table (2546 records).
I'm not sure what is causing it to seem to pause here, since the database
hasn't been put into production or anything, just the design. The table is
nearly identical to another one, called whichcats, that ran through no fine.
I put their table create statements below in case it would help, but my
question is what is taking vacuumdb so long and how can I fix it?
-- Table: whowrote (table its stalling on)
CREATE TABLE "whowrote" (
"connectionid" int4 DEFAULT
nextval('"whowrote_tmp_connectionid_seq"'::text) NOT NULL,
"pubid" int4,
"authorid" int4,
CONSTRAINT "whowrote_tmp_pkey" PRIMARY KEY ("connectionid"),
CONSTRAINT "whowrotefk" FOREIGN KEY (authorid) REFERENCES "authors"
(authorid),
CONSTRAINT "whowrotepubfk" FOREIGN KEY (pubid) REFERENCES "publications"
(pubid)
);
-- Table: whichcats (similar table)
CREATE TABLE "whichcats" (
"connectionid" int4 DEFAULT nextval('"whichcats_connectionid_seq"'::text)
NOT NULL,
"catid" int4,
"pubid" int4,
CONSTRAINT "whichcats_pkey" PRIMARY KEY ("connectionid"),
CONSTRAINT "whichcatfk" FOREIGN KEY (catid) REFERENCES "cats" (catid),
CONSTRAINT "whichcatpubfk" FOREIGN KEY (pubid) REFERENCES "publications"
(pubid)
);
Summer S. Wilson ICQ 26835530
Programmer/Analyst I, EIT TCE
Webmaster, An Eclectic World http://eclectic-world.com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2001-10-08 14:37 Vacuum Summer S. Wilson <collectonian@eclectic-world.com>
@ 2001-10-08 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-10-09 07:45 ` Re: Vacuum Ariunbold Gerelt-Od <gereltod@kikakuya.com>
0 siblings, 1 reply; 33+ messages in thread
From: Tom Lane @ 2001-10-08 16:03 UTC (permalink / raw)
To: Summer S. Wilson <collectonian@eclectic-world.com>; +Cc: pgsql-admin
"Summer S. Wilson" <collectonian@eclectic-world.com> writes:
> I'm running PostgreSQL 7.1 with Slackware 8 on a dual processor Dell server.
> I have a database with 17 tables. I started VacuumDB with the analyze
> option on Friday around 3pm...when I came in this morning at 8 it was still
> not done. From the verbose info, it seems to be "stuck" on one table,
> whowrote which is the second largest table (2546 records).
Is it consuming CPU time, or just sitting? (ps or top would tell you)
If it's just sitting, I'd bet that there is some other connected backend
that's holding an open transaction with a lock on that table.
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2001-10-08 14:37 Vacuum Summer S. Wilson <collectonian@eclectic-world.com>
2001-10-08 16:03 ` Re: Vacuum Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-10-09 07:45 ` Ariunbold Gerelt-Od <gereltod@kikakuya.com>
0 siblings, 0 replies; 33+ messages in thread
From: Ariunbold Gerelt-Od @ 2001-10-09 07:45 UTC (permalink / raw)
To: pgsql-admin
Tom,
Is there any way to check whether there is open transaction with a lock
on stucking table while vacuuming and terminate vacuum?
Tom Lane wrote:
> "Summer S. Wilson" <collectonian@eclectic-world.com> writes:
>
>>I'm running PostgreSQL 7.1 with Slackware 8 on a dual processor Dell server.
>>I have a database with 17 tables. I started VacuumDB with the analyze
>>option on Friday around 3pm...when I came in this morning at 8 it was still
>>not done. From the verbose info, it seems to be "stuck" on one table,
>>whowrote which is the second largest table (2546 records).
>>
>
> Is it consuming CPU time, or just sitting? (ps or top would tell you)
> If it's just sitting, I'd bet that there is some other connected backend
> that's holding an open transaction with a lock on that table.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
> .
>
>
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
@ 2001-10-09 13:19 Summer S. Wilson <collectonian@eclectic-world.com>
2001-10-09 23:00 ` Re: Vacuum Rainer Mager <rmager@vgkk.com>
0 siblings, 1 reply; 33+ messages in thread
From: Summer S. Wilson @ 2001-10-09 13:19 UTC (permalink / raw)
To: pgsql-admin; +Cc: tgl@sss.pgh.pa.us
Hi Tom,
It does not appear to be consuming any CPU time, its just sitting there.
I'm not sure what would be connected to it because we have nothing live
right now, and none of the pages related to that table have been designed
yet. Is there a safe way to end the vacuum?
> "Summer S. Wilson" <collectonian@eclectic-world.com> writes:
>
>>I'm running PostgreSQL 7.1 with Slackware 8 on a dual processor Dell
server.
>>I have a database with 17 tables. I started VacuumDB with the analyze
>>option on Friday around 3pm...when I came in this morning at 8 it was
still
>>not done. From the verbose info, it seems to be "stuck" on one table,
>>whowrote which is the second largest table (2546 records).
>>
>
> Is it consuming CPU time, or just sitting? (ps or top would tell you)
> If it's just sitting, I'd bet that there is some other connected backend
> that's holding an open transaction with a lock on that table.
Summer S. Wilson ICQ 26835530
Programmer/Analyst I, EIT TCE
Webmaster, An Eclectic World http://eclectic-world.com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2001-10-09 13:19 Re: Vacuum Summer S. Wilson <collectonian@eclectic-world.com>
@ 2001-10-09 23:00 ` Rainer Mager <rmager@vgkk.com>
0 siblings, 0 replies; 33+ messages in thread
From: Rainer Mager @ 2001-10-09 23:00 UTC (permalink / raw)
To: pgsql-admin
Hi all,
I haven't been following this thread that carefully but when yesterday we
also had vacuuming problems I decided I should. Reading this message shows
that Summer's problem seems to be different than ours although they could be
connected.
Yesterday our application stopped responding. After a little investigation
we found that 1 process on the db box was using 100% CPU. Via the ps command
we could see that there were 2 processes running continuously, a 'vacuum'
and a 'query'. Unfortunately I failed to note which was using 100% CPU.
At the time of discovery the run-a-way process had used almost 1 hour CPU
time. Since the app wasn't responding I'm assuming one or more other
processes were blocked. I can only guess that the vacuum process was the
long one because under typical usage no queries in the application run for
more than a few seconds.
I have since restarted the db with debug output turned on in order to track
down the queries. Any other ideas or suggestions? BTW, we're running with PG
7.1.3.
Thanks,
--Rainer
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Summer S. Wilson
> Sent: Tuesday, October 09, 2001 10:20 PM
> To: pgsql-admin@postgresql.org
> Cc: tgl@sss.pgh.pa.us
> Subject: Re: [ADMIN] Vacuum
>
>
> Hi Tom,
>
> It does not appear to be consuming any CPU time, its just sitting there.
> I'm not sure what would be connected to it because we have nothing live
> right now, and none of the pages related to that table have been designed
> yet. Is there a safe way to end the vacuum?
>
>
> Summer S. Wilson ICQ 26835530
> Programmer/Analyst I, EIT TCE
> Webmaster, An Eclectic World http://eclectic-world.com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Vacuum
@ 2001-11-29 16:06 bangh <banghe@baileylink.net>
2001-11-29 18:30 ` Re: Vacuum Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 33+ messages in thread
From: bangh @ 2001-11-29 16:06 UTC (permalink / raw)
To: pgsql-admin
Hello,
Using vacuum a table is very usefull to compress the space of a table .
However I noticed the index grows also very fast. Does anyone have a way to
compress the index? I know if I dump the database, and destroy the orginal
database, then create new one using the same database, and restory the dump
file, that will dramatically compress the space the database has taken. This
is not a good way for a live database that is driven by the web application
that means I have to shut down the web application service during this
clearup.
Thanks,
Bangh
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2001-11-29 16:06 Vacuum bangh <banghe@baileylink.net>
@ 2001-11-29 18:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-29 20:13 ` Re: Vacuum Brian McCane <bmccane@mccons.net>
0 siblings, 1 reply; 33+ messages in thread
From: Tom Lane @ 2001-11-29 18:30 UTC (permalink / raw)
To: bangh <banghe@baileylink.net>; +Cc: pgsql-admin
bangh <banghe@baileylink.net> writes:
> However I noticed the index grows also very fast. Does anyone have a way to
> compress the index?
REINDEX, or just drop and recreate the indexes.
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2001-11-29 16:06 Vacuum bangh <banghe@baileylink.net>
2001-11-29 18:30 ` Re: Vacuum Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-11-29 20:13 ` Brian McCane <bmccane@mccons.net>
0 siblings, 0 replies; 33+ messages in thread
From: Brian McCane @ 2001-11-29 20:13 UTC (permalink / raw)
To: pgsql-admin
On Thu, 29 Nov 2001, Tom Lane wrote:
> bangh <banghe@baileylink.net> writes:
> > However I noticed the index grows also very fast. Does anyone have a way to
> > compress the index?
>
> REINDEX, or just drop and recreate the indexes.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Okay, after some more testing and cussing/discussing with myself, I have
decided that my "reindex" perl script is ready for public consumption.
To use the attached script, you will need to put your username, password
and hostname into the program (just look for the comment). If you run it
without any parameters, it will give you some simple instructions. I use
it something like:
$ fixtable.pl -I -t relevance kids
The program will then dump out SQL code to re-create ALL indexes (-I) for
the 'relevance' table (-t) in my 'kids' database. You can feed the output
of the script to 'psql', or look at it first (I am paranoid) and then
cut-n-paste it to 'psql' yourself.
The SQL code creates a new index for each index on a table, drops the
original index, renames the new index to the old name, and then repeats
for the next index on the table. This means that the user you login as
when you run 'psql' must own the indexes (otherwise the DROP INDEX fails).
If the drop fails for any reason, you will end up with TWO (2) identical
indexes which will almost certainly hurt performance on inserts. Also,
you need to have enough free space to have a second copy of your largest
index on the table or the CREATE INDEX will fail.
NOTE: This script DOES NOT DO ANYTHING with permissions because I don't
use them. If someone would like to give me some SQL code that will return
the old permissions, and set them on the new index, I would be happy to
add this functionality.
Finally. I am providing this script because I am a nice guy. If your
machine explodes or any table gets injured by your use of this script I
cannot and will not be held responsible. I wrote this script for my own
use, and the only time it has ever failed for me was when I had duplicated
values in a UNIQUE index field. Once I corrected the data corruption, the
script worked correctly. So, at least at first, I recommend that you look
at the SQL that is generated, and make sure you understand what it is
trying to do BEFORE you use it.
- brian
Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
#! /usr/bin/perl
require 'getopts.pl' ;
my $allIndexes = 0 ;
my $allTriggers = 0 ;
my $table = "" ;
my $index = "" ;
my $notreally = 0 ;
Getopts('ITt:i:n') ;
$allIndexes = $opt_I if $opt_I ;
$allTriggers = $opt_T if $opt_T ;
$table = $opt_t if $opt_t ;
$index = $opt_i if $opt_i ;
$notreally = $opt_n if $opt_n ;
use strict ;
my $USAGE = <<USAGE ;
$0 - Postgresql table tool
$0 -t <table> [-i <index>] [-I] [-T] <database>
Options:
-t <table> Table to work on
-i <index> Index to rebuild
-I Rebuild all triggers
-T Do all triggers
You must choose at least one of -i, -I and -T
USAGE
die $USAGE if ($table eq "") ;
die "You can only use one of '-I' and '-i' options"
if ($index ne "" && $allIndexes) ;
die $USAGE if ($index eq "" && ! $allIndexes && ! $allTriggers) ;
use DBI ;
my $dbname = shift @ARGV ;
$$$$$$$$$ DELETE THIS LINE AND FIX NEXT THREE VALUES $$$$$$$$$
my $user = 'USERNAME' ;
my $passwd = 'PASSWORD' ;
my $hostname = 'HOSTNAME' ;
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$hostname",$user,$passwd) ;
my %indexes ;
my @triggers ;
my %unique ;
open PGDUMP, "pg_dump -s -t $table $dbname |" ;
while (<PGDUMP>)
{
chomp ;
next if ! m/^CREATE/ ;
if (m/^CREATE\s+(.*)\s*INDEX\s+"([^"]*)".*(\(.*\))/)
{
$indexes{$2} = $3 ;
$indexes{$2 => "UNIQUE"} = ($1 ne "") ;
next ;
}
if (! defined $unique{$_})
{
$unique{$_} = 1 ;
if (m/^CREATE\s+CONSTRAINT\s+TRIGGER\s+"([^"]*)"/)
{
push (@triggers, $_) ;
next ;
}
}
else
{
next ;
}
}
close PGDUMP ;
if ($allTriggers)
{
my $cmd = "SELECT oid from pg_class where relname = '$table'" ;
my $relid ;
if (my $sth = $dbh->prepare($cmd))
{
if ($sth->execute)
{
if (my $row = $sth->fetchrow_arrayref)
{
$relid = $$row[0] ;
}
}
}
$cmd = "SELECT tgname FROM pg_trigger where tgrelid = $relid" ;
if (my $sth = $dbh->prepare($cmd))
{
if ($sth->execute)
{
while (my $row = $sth->fetchrow_arrayref)
{
$relid = $$row[0] ;
print "DROP TRIGGER $$row[0] ON $table;\n" ;
}
}
}
for my $trigger (@triggers)
{
print $trigger . "\n" ;
}
}
die "Index $index does not exist on table $table"
if ($index ne "" && ! defined $indexes{$index}) ;
if ($index)
{
my $cmd = "CREATE" ;
$cmd.= " UNIQUE" if $indexes{$index => "UNIQUE"} == 1 ;
$cmd.= " INDEX $index ON $table $indexes{$index};" ;
print "$cmd\n" ;
my $cmd = "DROP INDEX $index;" ;
print "$cmd\n" ;
$cmd = "ALTER TABLE ".$index."_new" ;
$cmd.= " RENAME TO $index;" ;
print "$cmd\n" ;
}
elsif ($allIndexes)
{
for my $index (keys %indexes)
{
next if ! defined $indexes{$index => "UNIQUE"} ;
my $cmd = "CREATE" ;
$cmd.= " UNIQUE" if $indexes{$index => "UNIQUE"} == 1 ;
$cmd.= " INDEX ".$index."_new ON $table $indexes{$index};" ;
print "$cmd\n" ;
$cmd = "DROP INDEX $index;" ;
print "$cmd\n" ;
$cmd = "ALTER TABLE ".$index."_new" ;
$cmd.= " RENAME TO $index;" ;
print "$cmd\n" ;
}
}
$dbh->disconnect ;
__END__
Attachments:
[text/plain] fixtable.pl (3.1K, ../../20011129134155.W29836-200000@fw.mccons.net/2-fixtable.pl)
download | inline:
#! /usr/bin/perl
require 'getopts.pl' ;
my $allIndexes = 0 ;
my $allTriggers = 0 ;
my $table = "" ;
my $index = "" ;
my $notreally = 0 ;
Getopts('ITt:i:n') ;
$allIndexes = $opt_I if $opt_I ;
$allTriggers = $opt_T if $opt_T ;
$table = $opt_t if $opt_t ;
$index = $opt_i if $opt_i ;
$notreally = $opt_n if $opt_n ;
use strict ;
my $USAGE = <<USAGE ;
$0 - Postgresql table tool
$0 -t <table> [-i <index>] [-I] [-T] <database>
Options:
-t <table> Table to work on
-i <index> Index to rebuild
-I Rebuild all triggers
-T Do all triggers
You must choose at least one of -i, -I and -T
USAGE
die $USAGE if ($table eq "") ;
die "You can only use one of '-I' and '-i' options"
if ($index ne "" && $allIndexes) ;
die $USAGE if ($index eq "" && ! $allIndexes && ! $allTriggers) ;
use DBI ;
my $dbname = shift @ARGV ;
$$$$$$$$$ DELETE THIS LINE AND FIX NEXT THREE VALUES $$$$$$$$$
my $user = 'USERNAME' ;
my $passwd = 'PASSWORD' ;
my $hostname = 'HOSTNAME' ;
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$hostname",$user,$passwd) ;
my %indexes ;
my @triggers ;
my %unique ;
open PGDUMP, "pg_dump -s -t $table $dbname |" ;
while (<PGDUMP>)
{
chomp ;
next if ! m/^CREATE/ ;
if (m/^CREATE\s+(.*)\s*INDEX\s+"([^"]*)".*(\(.*\))/)
{
$indexes{$2} = $3 ;
$indexes{$2 => "UNIQUE"} = ($1 ne "") ;
next ;
}
if (! defined $unique{$_})
{
$unique{$_} = 1 ;
if (m/^CREATE\s+CONSTRAINT\s+TRIGGER\s+"([^"]*)"/)
{
push (@triggers, $_) ;
next ;
}
}
else
{
next ;
}
}
close PGDUMP ;
if ($allTriggers)
{
my $cmd = "SELECT oid from pg_class where relname = '$table'" ;
my $relid ;
if (my $sth = $dbh->prepare($cmd))
{
if ($sth->execute)
{
if (my $row = $sth->fetchrow_arrayref)
{
$relid = $$row[0] ;
}
}
}
$cmd = "SELECT tgname FROM pg_trigger where tgrelid = $relid" ;
if (my $sth = $dbh->prepare($cmd))
{
if ($sth->execute)
{
while (my $row = $sth->fetchrow_arrayref)
{
$relid = $$row[0] ;
print "DROP TRIGGER $$row[0] ON $table;\n" ;
}
}
}
for my $trigger (@triggers)
{
print $trigger . "\n" ;
}
}
die "Index $index does not exist on table $table"
if ($index ne "" && ! defined $indexes{$index}) ;
if ($index)
{
my $cmd = "CREATE" ;
$cmd.= " UNIQUE" if $indexes{$index => "UNIQUE"} == 1 ;
$cmd.= " INDEX $index ON $table $indexes{$index};" ;
print "$cmd\n" ;
my $cmd = "DROP INDEX $index;" ;
print "$cmd\n" ;
$cmd = "ALTER TABLE ".$index."_new" ;
$cmd.= " RENAME TO $index;" ;
print "$cmd\n" ;
}
elsif ($allIndexes)
{
for my $index (keys %indexes)
{
next if ! defined $indexes{$index => "UNIQUE"} ;
my $cmd = "CREATE" ;
$cmd.= " UNIQUE" if $indexes{$index => "UNIQUE"} == 1 ;
$cmd.= " INDEX ".$index."_new ON $table $indexes{$index};" ;
print "$cmd\n" ;
$cmd = "DROP INDEX $index;" ;
print "$cmd\n" ;
$cmd = "ALTER TABLE ".$index."_new" ;
$cmd.= " RENAME TO $index;" ;
print "$cmd\n" ;
}
}
$dbh->disconnect ;
__END__
^ permalink raw reply [nested|flat] 33+ messages in thread
* VACUUM
@ 2002-11-14 20:39 Jodi Kanter <jkanter@virginia.edu>
2002-11-14 21:44 ` Re: VACUUM Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 1 reply; 33+ messages in thread
From: Jodi Kanter @ 2002-11-14 20:39 UTC (permalink / raw)
To: pgsql-admin
I am trying to set up my cron job to run the appropriate vacuums on our database. I understand from reading the documentation that regular vacuums, vacuum full, and vacuum analyze are all important. Can someone assist in telling me what is the most efficient order? I would assume that analyze should be done once the inaccessible rows are released within the table and then to the OS?
We currently have a small amount of data in our database so my plan was to do all vacuums on the entire database.
Also, I read that reindexing was important. Does this need to be done as frequently as the vacuums? Is it only done on a per table basis? or can you reindex the entire database?
Thanks
Jodi
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: VACUUM
2002-11-14 20:39 VACUUM Jodi Kanter <jkanter@virginia.edu>
@ 2002-11-14 21:44 ` Robert Treat <xzilla@users.sourceforge.net>
2002-11-14 22:18 ` Re: VACUUM Tom Lane <tgl@sss.pgh.pa.us>
2002-11-15 13:27 ` Re: VACUUM Jodi Kanter <jkanter@virginia.edu>
0 siblings, 2 replies; 33+ messages in thread
From: Robert Treat @ 2002-11-14 21:44 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-admin
Honestly this is very dependent on the type of schema you have and what
your application does with it. If you have tables that get a lot of
updates (or both inserts and deletes) you will want to run vacuum
analyze on those tables more often. How often is debatable, but as a
rule of thumb it good to run them vacuum analyze as least once per
turnover of your table (though running it more wont hurt). As for vacuum
full, with frequently enough "lazy" vacuuming you wont need to vacuum
full very often. If you can afford to lock your tables, then I'd say do
it once a night. If not, you need to decide when is a good time, but I
think the docs recommend doing it at least once every 1 billion
transactions, though more often is definitely better here too. As far
as sequences go, search the archives for pgsql-general, tom lane just
posted a great explanation of why sequences need to be recreated within
the last day or two. Read it and if it applies work that into your crons
accordingly. One final note is to research the free space map setting in
the postgresql.conf. This helps tracks dead tuples in the database, so
you'll want to make this large enough to store your tuple counts between
vacuums. Hope this helps,
Robert Treat
On Thu, 2002-11-14 at 15:39, Jodi Kanter wrote:
> I am trying to set up my cron job to run the appropriate vacuums on our
> database. I understand from reading the documentation that regular
> vacuums, vacuum full, and vacuum analyze are all important. Can someone
> assist in telling me what is the most efficient order? I would assume
> that analyze should be done once the inaccessible rows are released
> within the table and then to the OS?
>
> We currently have a small amount of data in our database so my plan was
> to do all vacuums on the entire database.
>
> Also, I read that reindexing was important. Does this need to be done as
> frequently as the vacuums? Is it only done on a per table basis? or can
> you reindex the entire database?
>
> Thanks
> Jodi
>
> _______________________________
> Jodi L Kanter
> BioInformatics Database Administrator
> University of Virginia
> (434) 924-2846
> jkanter@virginia.edu <mailto:jkanter@virginia.edu>
>
>
>
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: VACUUM
2002-11-14 20:39 VACUUM Jodi Kanter <jkanter@virginia.edu>
2002-11-14 21:44 ` Re: VACUUM Robert Treat <xzilla@users.sourceforge.net>
@ 2002-11-14 22:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-14 22:55 ` Re: VACUUM Robert Treat <xzilla@users.sourceforge.net>
2002-11-20 18:04 ` Re: VACUUM Andrew Sullivan <andrew@libertyrms.info>
1 sibling, 2 replies; 33+ messages in thread
From: Tom Lane @ 2002-11-14 22:18 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Jodi Kanter <jkanter@virginia.edu>; pgsql-admin
Robert Treat <xzilla@users.sourceforge.net> writes:
> ... As for vacuum
> full, with frequently enough "lazy" vacuuming you wont need to vacuum
> full very often. If you can afford to lock your tables, then I'd say do
> it once a night. If not, you need to decide when is a good time, but I
> think the docs recommend doing it at least once every 1 billion
> transactions, though more often is definitely better here too.
The intention is that if you do plain ("lazy") vacuum often enough to
keep your tables from bloating unreasonably, you shouldn't ever have to
do a "full" vacuum. How much is "unreasonable" is up to you to decide.
For instance, if under peak load you might update 10% of the rows in
a particular table every hour, then hourly lazy vacuums would keep you
at a steady state of about 10% wasted space in that table. That seems
pretty workable to me. If you waited 100 hours then the table would
have bloated to about 10X its minimum size, and at that point you'd
most likely want to do a vacuum full to reclaim the wasted space.
(Right now the only way to set up an appropriate vacuuming schedule is
for the DBA to hand-craft a cron script. I'd like to see the database
able to launch automatic background vacuum runs by itself.)
The business about "you must vacuum at least every billion transactions"
is a different issue. Either plain or full vacuum will satisfy that
requirement.
There is more discussion in the Admin Guide ---
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/maintenance.html
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: VACUUM
2002-11-14 20:39 VACUUM Jodi Kanter <jkanter@virginia.edu>
2002-11-14 21:44 ` Re: VACUUM Robert Treat <xzilla@users.sourceforge.net>
2002-11-14 22:18 ` Re: VACUUM Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-11-14 22:55 ` Robert Treat <xzilla@users.sourceforge.net>
1 sibling, 0 replies; 33+ messages in thread
From: Robert Treat @ 2002-11-14 22:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jodi Kanter <jkanter@virginia.edu>; pgsql-admin
On Thu, 2002-11-14 at 17:18, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
>
> (Right now the only way to set up an appropriate vacuuming schedule is
> for the DBA to hand-craft a cron script. I'd like to see the database
> able to launch automatic background vacuum runs by itself.)
>
There is also an "automatic vacuum daemon" on gborg. I've looked at the
code and it looks nifty, though I haven't used it myself.
Robert Treat
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: VACUUM
2002-11-14 20:39 VACUUM Jodi Kanter <jkanter@virginia.edu>
2002-11-14 21:44 ` Re: VACUUM Robert Treat <xzilla@users.sourceforge.net>
2002-11-14 22:18 ` Re: VACUUM Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-11-20 18:04 ` Andrew Sullivan <andrew@libertyrms.info>
1 sibling, 0 replies; 33+ messages in thread
From: Andrew Sullivan @ 2002-11-20 18:04 UTC (permalink / raw)
To: pgsql-admin
On Thu, Nov 14, 2002 at 05:18:06PM -0500, Tom Lane wrote:
>
> The business about "you must vacuum at least every billion transactions"
> is a different issue. Either plain or full vacuum will satisfy that
> requirement.
Note, also, that it is _every_ table needs to be vacuumed in that
billion transaction period, but it is nevertheless probable that some
of your tables will not need to be vacuumed as often as others. For
efficiency's sake, therefore, you may want to vacuum different tables
at different times.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: VACUUM
2002-11-14 20:39 VACUUM Jodi Kanter <jkanter@virginia.edu>
2002-11-14 21:44 ` Re: VACUUM Robert Treat <xzilla@users.sourceforge.net>
@ 2002-11-15 13:27 ` Jodi Kanter <jkanter@virginia.edu>
2002-11-15 14:12 ` Re: VACUUM Robert Treat <xzilla@users.sourceforge.net>
1 sibling, 1 reply; 33+ messages in thread
From: Jodi Kanter @ 2002-11-15 13:27 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-admin
Do you have a URL for that document you mention? I get nothing when I search
on pgsql-general.
Thanks for the help.
Jodi
----- Original Message -----
From: "Robert Treat" <xzilla@users.sourceforge.net>
To: "Jodi Kanter" <jkanter@virginia.edu>
Cc: "Postgres Admin List" <pgsql-admin@postgresql.org>
Sent: Thursday, November 14, 2002 4:44 PM
Subject: Re: [ADMIN] VACUUM
> Honestly this is very dependent on the type of schema you have and what
> your application does with it. If you have tables that get a lot of
> updates (or both inserts and deletes) you will want to run vacuum
> analyze on those tables more often. How often is debatable, but as a
> rule of thumb it good to run them vacuum analyze as least once per
> turnover of your table (though running it more wont hurt). As for vacuum
> full, with frequently enough "lazy" vacuuming you wont need to vacuum
> full very often. If you can afford to lock your tables, then I'd say do
> it once a night. If not, you need to decide when is a good time, but I
> think the docs recommend doing it at least once every 1 billion
> transactions, though more often is definitely better here too. As far
> as sequences go, search the archives for pgsql-general, tom lane just
> posted a great explanation of why sequences need to be recreated within
> the last day or two. Read it and if it applies work that into your crons
> accordingly. One final note is to research the free space map setting in
> the postgresql.conf. This helps tracks dead tuples in the database, so
> you'll want to make this large enough to store your tuple counts between
> vacuums. Hope this helps,
>
> Robert Treat
>
> On Thu, 2002-11-14 at 15:39, Jodi Kanter wrote:
> > I am trying to set up my cron job to run the appropriate vacuums on our
> > database. I understand from reading the documentation that regular
> > vacuums, vacuum full, and vacuum analyze are all important. Can someone
> > assist in telling me what is the most efficient order? I would assume
> > that analyze should be done once the inaccessible rows are released
> > within the table and then to the OS?
> >
> > We currently have a small amount of data in our database so my plan was
> > to do all vacuums on the entire database.
> >
> > Also, I read that reindexing was important. Does this need to be done as
> > frequently as the vacuums? Is it only done on a per table basis? or can
> > you reindex the entire database?
> >
> > Thanks
> > Jodi
> >
> > _______________________________
> > Jodi L Kanter
> > BioInformatics Database Administrator
> > University of Virginia
> > (434) 924-2846
> > jkanter@virginia.edu <mailto:jkanter@virginia.edu>
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: VACUUM
2002-11-14 20:39 VACUUM Jodi Kanter <jkanter@virginia.edu>
2002-11-14 21:44 ` Re: VACUUM Robert Treat <xzilla@users.sourceforge.net>
2002-11-15 13:27 ` Re: VACUUM Jodi Kanter <jkanter@virginia.edu>
@ 2002-11-15 14:12 ` Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 33+ messages in thread
From: Robert Treat @ 2002-11-15 14:12 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-admin
http://groups.google.com/groups?q=tom+lane+index+group:comp.databases.postgresql.general&hl=en&a...
hows that for a URL?
Robert Treat
On Fri, 2002-11-15 at 08:27, Jodi Kanter wrote:
> Do you have a URL for that document you mention? I get nothing when I search
> on pgsql-general.
> Thanks for the help.
> Jodi
>
> ----- Original Message -----
> From: "Robert Treat" <xzilla@users.sourceforge.net>
> To: "Jodi Kanter" <jkanter@virginia.edu>
> Cc: "Postgres Admin List" <pgsql-admin@postgresql.org>
> Sent: Thursday, November 14, 2002 4:44 PM
> Subject: Re: [ADMIN] VACUUM
>
>
> > Honestly this is very dependent on the type of schema you have and what
> > your application does with it. If you have tables that get a lot of
> > updates (or both inserts and deletes) you will want to run vacuum
> > analyze on those tables more often. How often is debatable, but as a
> > rule of thumb it good to run them vacuum analyze as least once per
> > turnover of your table (though running it more wont hurt). As for vacuum
> > full, with frequently enough "lazy" vacuuming you wont need to vacuum
> > full very often. If you can afford to lock your tables, then I'd say do
> > it once a night. If not, you need to decide when is a good time, but I
> > think the docs recommend doing it at least once every 1 billion
> > transactions, though more often is definitely better here too. As far
> > as sequences go, search the archives for pgsql-general, tom lane just
> > posted a great explanation of why sequences need to be recreated within
> > the last day or two. Read it and if it applies work that into your crons
> > accordingly. One final note is to research the free space map setting in
> > the postgresql.conf. This helps tracks dead tuples in the database, so
> > you'll want to make this large enough to store your tuple counts between
> > vacuums. Hope this helps,
> >
> > Robert Treat
> >
> > On Thu, 2002-11-14 at 15:39, Jodi Kanter wrote:
> > > I am trying to set up my cron job to run the appropriate vacuums on our
> > > database. I understand from reading the documentation that regular
> > > vacuums, vacuum full, and vacuum analyze are all important. Can someone
> > > assist in telling me what is the most efficient order? I would assume
> > > that analyze should be done once the inaccessible rows are released
> > > within the table and then to the OS?
> > >
> > > We currently have a small amount of data in our database so my plan was
> > > to do all vacuums on the entire database.
> > >
> > > Also, I read that reindexing was important. Does this need to be done as
> > > frequently as the vacuums? Is it only done on a per table basis? or can
> > > you reindex the entire database?
> > >
> > > Thanks
> > > Jodi
> > >
> > > _______________________________
> > > Jodi L Kanter
> > > BioInformatics Database Administrator
> > > University of Virginia
> > > (434) 924-2846
> > > jkanter@virginia.edu <mailto:jkanter@virginia.edu>
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 33+ messages in thread
* vacuum
@ 2009-03-17 14:36 Ygor Degani <ygordegani@gmail.com>
2009-03-20 12:19 ` Re: vacuum itishree sukla <itishree.sukla@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Ygor Degani @ 2009-03-17 14:36 UTC (permalink / raw)
To: pgsql-admin
People,
I have three databases in different machines: a pgsql server version 8.2.4,
a pgsql server version 8.1.4 and another pgsql client version 8.3.5.
Is there a problem to make vacuum with client newer than server? For
example: To use vacuumdb of client version 8.3.5 to make vacuum in server
8.2.4 or in server 8.1.4.
Regards,
--
Ygor Degani
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuum
2009-03-17 14:36 vacuum Ygor Degani <ygordegani@gmail.com>
@ 2009-03-20 12:19 ` itishree sukla <itishree.sukla@gmail.com>
0 siblings, 0 replies; 33+ messages in thread
From: itishree sukla @ 2009-03-20 12:19 UTC (permalink / raw)
To: Ygor Degani <ygordegani@gmail.com>; +Cc: pgsql-admin
Hi Ygor,
There is no problem with this .You can do the vacuum.
Regards,
Itishree
On Tue, Mar 17, 2009 at 8:06 PM, Ygor Degani <ygordegani@gmail.com> wrote:
> People,
>
> I have three databases in different machines: a pgsql server version 8.2.4,
> a pgsql server version 8.1.4 and another pgsql client version 8.3.5.
> Is there a problem to make vacuum with client newer than server? For
> example: To use vacuumdb of client version 8.3.5 to make vacuum in server
> 8.2.4 or in server 8.1.4.
>
> Regards,
>
> --
> Ygor Degani
>
^ permalink raw reply [nested|flat] 33+ messages in thread
* Vacuum
@ 2024-01-02 15:06 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-02 15:33 ` Re: Vacuum SOzcn <selahattinozcnma@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Rajesh Kumar @ 2024-01-02 15:06 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Postgres 15
I am creating a new replica, and after the data is caught up and
replication lag is 0, I am doing a switchover and making a new replica as
master.
Now, in this case, do I have so vacuum analyze in master?
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2024-01-02 15:06 Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-01-02 15:33 ` SOzcn <selahattinozcnma@gmail.com>
2024-01-02 19:34 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 33+ messages in thread
From: SOzcn @ 2024-01-02 15:33 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello, I've tried the same thing, you need to analyze your databases in any
case. Don't need a Full vacuum.
Rajesh Kumar <rajeshkumar.dba09@gmail.com>, 2 Oca 2024 Sal, 18:07 tarihinde
şunu yazdı:
> Postgres 15
>
> I am creating a new replica, and after the data is caught up and
> replication lag is 0, I am doing a switchover and making a new replica as
> master.
>
> Now, in this case, do I have so vacuum analyze in master?
>
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2024-01-02 15:06 Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-02 15:33 ` Re: Vacuum SOzcn <selahattinozcnma@gmail.com>
@ 2024-01-02 19:34 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-02 19:52 ` Re: Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Laurenz Albe @ 2024-01-02 19:34 UTC (permalink / raw)
To: SOzcn <selahattinozcnma@gmail.com>; Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, 2024-01-02 at 18:33 +0300, SOzcn wrote:
> Rajesh Kumar <rajeshkumar.dba09@gmail.com>, 2 Oca 2024 Sal, 18:07 tarihinde şunu yazdı:
> > Postgres 15
> >
> > I am creating a new replica, and after the data is caught up and replication lag is 0,
> > I am doing a switchover and making a new replica as master.
> >
> > Now, in this case, do I have so vacuum analyze in master?
>
> Hello, I've tried the same thing, you need to analyze your databases in any case.
> Don't need a Full vacuum.
I don't think that is true.
Both table statistics and the effects of VACUUM are replicated.
What is not replicated are the statistics in "pg_stat_all_tables", so autovacuum
loses its brain during a switchover.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2024-01-02 15:06 Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-02 15:33 ` Re: Vacuum SOzcn <selahattinozcnma@gmail.com>
2024-01-02 19:34 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-01-02 19:52 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-03 10:23 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 33+ messages in thread
From: Rajesh Kumar @ 2024-01-02 19:52 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: SOzcn <selahattinozcnma@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Vacuum operation happens only in primary. How is that replicated?
On Wed, 3 Jan 2024, 01:04 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> On Tue, 2024-01-02 at 18:33 +0300, SOzcn wrote:
> > Rajesh Kumar <rajeshkumar.dba09@gmail.com>, 2 Oca 2024 Sal, 18:07
> tarihinde şunu yazdı:
> > > Postgres 15
> > >
> > > I am creating a new replica, and after the data is caught up and
> replication lag is 0,
> > > I am doing a switchover and making a new replica as master.
> > >
> > > Now, in this case, do I have so vacuum analyze in master?
> >
> > Hello, I've tried the same thing, you need to analyze your databases in
> any case.
> > Don't need a Full vacuum.
>
> I don't think that is true.
> Both table statistics and the effects of VACUUM are replicated.
>
> What is not replicated are the statistics in "pg_stat_all_tables", so
> autovacuum
> loses its brain during a switchover.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2024-01-02 15:06 Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-02 15:33 ` Re: Vacuum SOzcn <selahattinozcnma@gmail.com>
2024-01-02 19:34 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-02 19:52 ` Re: Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-01-03 10:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-03 11:08 ` Re: Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Laurenz Albe @ 2024-01-03 10:23 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: SOzcn <selahattinozcnma@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, 2024-01-03 at 01:22 +0530, Rajesh Kumar wrote:
> Vacuum operation happens only in primary. How is that replicated?
VACUUM modifies data, data modifications are WAL logged, the WAL
is replayed on the standby.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2024-01-02 15:06 Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-02 15:33 ` Re: Vacuum SOzcn <selahattinozcnma@gmail.com>
2024-01-02 19:34 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-02 19:52 ` Re: Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-03 10:23 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-01-03 11:08 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-03 15:08 ` Re: Vacuum Ron Johnson <ronljohnsonjr@gmail.com>
2024-01-03 15:11 ` Re: Vacuum David G. Johnston <david.g.johnston@gmail.com>
2024-01-03 18:36 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 33+ messages in thread
From: Rajesh Kumar @ 2024-01-03 11:08 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: SOzcn <selahattinozcnma@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Vacuum removes dead tuples in primary. Are you saying, dead tuples are
removed in replica?
On Wed, 3 Jan 2024, 15:53 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> On Wed, 2024-01-03 at 01:22 +0530, Rajesh Kumar wrote:
> > Vacuum operation happens only in primary. How is that replicated?
>
> VACUUM modifies data, data modifications are WAL logged, the WAL
> is replayed on the standby.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2024-01-02 15:06 Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-02 15:33 ` Re: Vacuum SOzcn <selahattinozcnma@gmail.com>
2024-01-02 19:34 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-02 19:52 ` Re: Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-03 10:23 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-03 11:08 ` Re: Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-01-03 15:08 ` Ron Johnson <ronljohnsonjr@gmail.com>
2 siblings, 0 replies; 33+ messages in thread
From: Ron Johnson @ 2024-01-03 15:08 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; SOzcn <selahattinozcnma@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Streaming Replication is file-level replication. Therefore, if a file in
$PGDATA is modified by a Postgresql program, then the file will be
replicated.
VACUUM is a Postgresql "program" that modifies files.
On Wed, Jan 3, 2024 at 6:08 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:
> Vacuum removes dead tuples in primary. Are you saying, dead tuples are
> removed in replica?
>
> On Wed, 3 Jan 2024, 15:53 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
>
>> On Wed, 2024-01-03 at 01:22 +0530, Rajesh Kumar wrote:
>> > Vacuum operation happens only in primary. How is that replicated?
>>
>> VACUUM modifies data, data modifications are WAL logged, the WAL
>> is replayed on the standby.
>>
>> Yours,
>> Laurenz Albe
>>
>
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2024-01-02 15:06 Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-02 15:33 ` Re: Vacuum SOzcn <selahattinozcnma@gmail.com>
2024-01-02 19:34 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-02 19:52 ` Re: Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-03 10:23 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-03 11:08 ` Re: Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-01-03 15:11 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 33+ messages in thread
From: David G. Johnston @ 2024-01-03 15:11 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; SOzcn <selahattinozcnma@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Jan 3, 2024 at 4:08 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:
> Vacuum removes dead tuples in primary. Are you saying, dead tuples are
> removed in replica?
>
>>
>>
The fact that the space those removed tuples occupied gets reused when
creating new tuples should be enough knowledge to figure out the answer to
that question.
David J.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: Vacuum
2024-01-02 15:06 Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-02 15:33 ` Re: Vacuum SOzcn <selahattinozcnma@gmail.com>
2024-01-02 19:34 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-02 19:52 ` Re: Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-03 10:23 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-03 11:08 ` Re: Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-01-03 18:36 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 33+ messages in thread
From: Laurenz Albe @ 2024-01-03 18:36 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: SOzcn <selahattinozcnma@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, 2024-01-03 at 16:38 +0530, Rajesh Kumar wrote:
> Vacuum removes dead tuples in primary. Are you saying, dead tuples are removed in replica?
Yes.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 33+ messages in thread
end of thread, other threads:[~2024-01-03 18:36 UTC | newest]
Thread overview: 33+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-13 23:47 vacuum Rainer Mager <rmager@vgkk.com>
2000-07-13 23:48 RE: vacuum Neil Toronto <NToronto@Dentrix.com>
2000-07-14 01:55 ` Rainer Mager <rmager@vgkk.com>
2000-07-14 01:59 ` Jean-Marc Pigeon <jmp@safe.ca>
2001-03-01 01:26 Vacuum. Grant <grant@conprojan.com.au>
2001-07-16 19:26 Vacuum Chris Ruprecht <chrup999@yahoo.com>
2001-07-16 22:59 ` Re: Vacuum Tom Lane <tgl@sss.pgh.pa.us>
2001-10-08 14:37 Vacuum Summer S. Wilson <collectonian@eclectic-world.com>
2001-10-08 16:03 ` Re: Vacuum Tom Lane <tgl@sss.pgh.pa.us>
2001-10-09 07:45 ` Re: Vacuum Ariunbold Gerelt-Od <gereltod@kikakuya.com>
2001-10-09 13:19 Re: Vacuum Summer S. Wilson <collectonian@eclectic-world.com>
2001-10-09 23:00 ` Re: Vacuum Rainer Mager <rmager@vgkk.com>
2001-11-29 16:06 Vacuum bangh <banghe@baileylink.net>
2001-11-29 18:30 ` Re: Vacuum Tom Lane <tgl@sss.pgh.pa.us>
2001-11-29 20:13 ` Re: Vacuum Brian McCane <bmccane@mccons.net>
2002-11-14 20:39 VACUUM Jodi Kanter <jkanter@virginia.edu>
2002-11-14 21:44 ` Re: VACUUM Robert Treat <xzilla@users.sourceforge.net>
2002-11-14 22:18 ` Re: VACUUM Tom Lane <tgl@sss.pgh.pa.us>
2002-11-14 22:55 ` Re: VACUUM Robert Treat <xzilla@users.sourceforge.net>
2002-11-20 18:04 ` Re: VACUUM Andrew Sullivan <andrew@libertyrms.info>
2002-11-15 13:27 ` Re: VACUUM Jodi Kanter <jkanter@virginia.edu>
2002-11-15 14:12 ` Re: VACUUM Robert Treat <xzilla@users.sourceforge.net>
2009-03-17 14:36 vacuum Ygor Degani <ygordegani@gmail.com>
2009-03-20 12:19 ` itishree sukla <itishree.sukla@gmail.com>
2024-01-02 15:06 Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-02 15:33 ` Re: Vacuum SOzcn <selahattinozcnma@gmail.com>
2024-01-02 19:34 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-02 19:52 ` Re: Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-03 10:23 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-03 11:08 ` Re: Vacuum Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-01-03 15:08 ` Re: Vacuum Ron Johnson <ronljohnsonjr@gmail.com>
2024-01-03 15:11 ` Re: Vacuum David G. Johnston <david.g.johnston@gmail.com>
2024-01-03 18:36 ` Re: Vacuum Laurenz Albe <laurenz.albe@cybertec.at>
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