From: | Carl von Clausewitz <clausewitz45(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Vacuumdb error |
Date: | 2011-04-14 16:36:55 |
Message-ID: | BANLkTin-dsQWj9ddyoWr6Y0JFL0iFT760A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
see the two scripts attached. First one is the postgres_maintenance.sh, and
the second is the postgres_backup.sh. I've attached it, and copied, because
of the antivirus filters :-)
regards,
Carl
Maintenance:
#!/bin/sh
date >> /var/log/postgresql_maintenance.log
/usr/local/bin/reindexdb --all --username=cvc >>
/var/log/postgresql_maintenance.log
echo "Reindex done" >> /var/log/postgresql_maintenance.log
/usr/local/bin/vacuumdb --all --full --analyze --username=cvc >>
/var/log/postgresql_maintenance.log
echo "Vacuum done" >> /var/log/postgresql_maintenance.log
Backup:
#!/bin/sh
date >> /var/log/postgresql_backup.log
/usr/local/bin/pg_dump --create --file=/usr/NAS/mentesek/postgres/$(date
+%Y%m%d).backup --format=c --no-owner --username=cvc ktv_migracio_access
echo "Backup done" >> /var/log/postgresql_backup.log
2011/4/14 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Gipsz Jakab <clausewitz45(at)gmail(dot)com> writes:
> > Today morning at 01:00 AM in our PostgreSQL 9.0.3 server a routine
> > maintenance script has started (vacuumdb --all --full --analyze), and
> > stopped with this error:
>
> > sqlstate=23505ERROR: duplicate key value violates unique constraint
> > "pg_index_indexrelid_index"
> > sqlstate=23505DETAIL: Key (indexrelid)=(2678) already exists.
>
> Maybe you had better show us *all* of your "routine maintenance" steps,
> because there are some fairly unexplainable things in that pg_index dump.
> VACUUM FULL alone wouldn't have resulted in the pg_index and pg_class
> indexes' entries having updated xmins:
>
> > "(1,40)";"48741";"0";2678;2610;1;false;false;true;false;true;false;true
> > "(1,41)";"48741";"0";2679;2610;1;true;false;true;false;true;true;true
> > "(1,42)";"49791";"0";2662;1259;1;true;false;true;false;true;true;true
> > "(1,43)";"49791";"0";2663;1259;2;true;false;true;false;true;true;true
>
> and I'm wondering how come three of them have indcheckxmin true too.
> I think you must be whacking the system catalogs around harder than
> you've admitted to.
>
> regards, tom lane
>
Attachment | Content-Type | Size |
---|---|---|
postgre_backup.sh | application/x-sh | 254 bytes |
postgre_maintenance.sh | application/x-sh | 370 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-04-14 16:46:15 | Re: Vacuumdb error |
Previous Message | Tom Lane | 2011-04-14 16:25:05 | Re: Vacuumdb error |