Database upgrade Pg11 to Pg12 issues
Hi I have upgraded many Pg databases from 9 to 10 to 11 using the Ubuntu pg_upgradecluster command (wrapper to pg_update) without issues, however today I upgraded a DB from 11 to 12. Using the syntax */pg_upgradecluser -k -m upgrade 11 main/* using the latest 12.5 binaries all appeared to work well, and for a 330G DB this took 2 minutes. On starting the DB using systemctl start postgresql@12.service the DB started, but the screen showed the default directory was *//var/lib/postgresql/11/main/*. Checking the open database with /*show data_directory; */gave /*/var/lib/postgresql/12/main*/ Then using /*lsof | grep '11/main'*/ gave no files, but/*lsof | grep '12/main'*/ gave over 30K files, Checking the directory sizes suggests that 11/main is 300G and 12/main is 40G So all appears OK, with files hard linked between 11/main and 12/main, and I assume the issue relates to the PGDATA/postgresql.auto.conf file being just copied from the original 11/main with data_directory being set to /var/lib/postgresql/11/main. If I where to run /*pg_dropcluster 11 main*/ to remove the old database and conf files, will this destroy my running Pg12 database with hard linked files in 11/main and 12/main? In theory it shouldn't and files from 11/main and 12/main will all be under 12/main, I just have doubts. Is there an easy way to tidy this up? Simon -- Simon Windsor Eml: simon.wind...@cornfield.me.uk “There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.”
Re: Error XX000 After pg11 upgrade
Hi Thanks for all the help, and a couple of offlist suggestions. We have fixed the problem by copying all of the data (160GB) to a partitioned table, replacing the trigger with table column defaults for timestamp and sequence values. As a result, all is working ok. Thank you, once again Simon On Fri, 16 Aug 2019 at 01:28, Tom Lane wrote: > Simon Windsor writes: > > Since then, large bulk inserts of configuration changes are failing with > > this Error, but adhoc and small changes are working ok. > > Might it be that things work as long as the trigger is only tasked with > redirecting to the same child table (or limited set of child tables) > within a particular insertion command? > > I'm wondering if this could be related to bug #15913 --- which I just > fixed today, so maybe I just have it on the brain too much. The > manifestation doesn't look quite the same, but given the way your > trigger is written, something about NEW.* changing type from one > call to the next might have something to do with it. > > I also wonder how often you create/delete child tables. > > regards, tom lane > -- Simon Simon Windsor Eml: simon.wind...@cornfield.org.uk Tel: 01454 617689 Mob: 07960 321599
Re: Error XX000 After pg11 upgrade
Hi
The Full system used to in an Oracle DB and was ported to Postgres 9.5
about 2+ years ago, and partitioned using inheritance tables.
Since then pg_upgrade has been used to upgrade to pg10 (with apt upgrade
to take to 10.5 occasionally).
Last week, pg_upgrade was againn used to upgrade to pg11.4.
Since then, large bulk inserts of configuration changes are failing with
this Error, but adhoc and small changes are working ok.
The actual error is reported by a Java process
Caused by: org.postgresql.util.PSQLException: ERROR: cache lookup failed
for type 22079
Where: SQL statement "insert into configObjectsFull_2019 values (new.*)"
PL/pgSQL function configobjectsfull_insert_trigger() line 28 at SQL
statement
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2310)
at
The DB Tableand Trigger are attached.
As you can see, the id and timestamp are set on insert, and are used to
determine the partition used.
This issue started after the upgrade to pg11, pg10 and pg9 had no problems.
Any ideas would be appreciated.
Simon
On 15/08/2019 16:31, Tom Lane wrote:
Simon Windsor writes:
I have just upgraded a Db from pg 10.5 to pg11.4 and almost immediately we
are seeing errors like
ERROR,XX000,"cache lookup failed for type 22079"
When inserting into a a partitioned table. About 30% of inserts are
reporting this error.
Hmm ... can you show the full schema (eg, psql \d+ output) for the
partitioned table and its children? Is there any pattern to the
failing inserts, eg do they all resolve as inserts to the same
partition(s)?
... And an insert trigger uses the current date to dertmine where the
object is stored.
In other words, this isn't actual partitioning as introduced in v10,
but a hand-rolled equivalent?
How do I determine what type 22079 refers to?
Presumably, the problem is that that type OID *doesn't* refer to anything
any more. You should be asking "where is this dangling reference coming
from?". It's possibly hiding in the partitioning expression(s) of
this partitioned table, but there's no way to tell with this amount
of info.
How did you do the upgrade exactly?
regards, tom lane
--
Simon Windsor
Eml: simon.wind...@cornfield.me.uk
Tel: 01454 617689
Mob: 0755 197 9733
“There is nothing in the world that some man cannot make a little worse and
sell a little cheaper, and he who considers price only is that man's lawful
prey.”
objects.sql
Description: application/sql
Error XX000 After pg11 upgrade
Hi I have just upgraded a Db from pg 10.5 to pg11.4 and almost immediately we are seeing errors like ERROR,XX000,"cache lookup failed for type 22079" When inserting into a a partitioned table. About 30% of inserts are reporting this error. How do I determine what type 22079 refers to? I have checked pg_attributes and pg_classes without success? What would be causing this error? The basic table layout is objects objects_2010 objects_2011 - objects_2018 objects_2019 And an insert trigger uses the current date to dertmine where the object is stored. Any ideas or suggestions would be appreciated. Simon -- Simon Simon Windsor Eml: simon.wind...@cornfield.org.uk Tel: 01454 617689 Mob: 07960 321599
Postgres 10 and auto vacuum
Hi We have a busy postgres 10 Db with a principal table that holds about 15 million active rows and has about 90M Inserts|Updates|Deletes a day. All performs well, except a small number of monitoring SQL statements of the nature select code, max(timestamp) from mainTable group by code; After running ANALYZE or VACUUM ANALYZE on the table, the Query Plan uses the an index on columns (code, timestamp) and takes less than 1s to report the latest value for each code. However, after about 20 minutes, the Query Plan has changed to be a full table scan mainTable and this takes about 30s. This is very puzzling because the nature of the table and data has not changed, although many values and 5M changes may have happened. The only possible cause can be auto vacuum. Playing with autovacuum_analyze_threshold autovacuum_analyze_scale_factor default_statistics_target What is the best option to ensure that the table statistics stay upto data and that the best Query Plan is generated.One option would be to use CRON and regenerate the table statistics every 15 minutes, or disable auto vacuum ANALYZE function, but neither of these options feel write. How does the auto vacuum analyze function work? Does it use a subset of the whole table to regenerate the table statistics use a subset of the rows changed to regenerate the table statistics use a subset of the rows changed to update the table statistics or something else Any help or suggestions with this would be appreciated Simon Simon Windsor Eml: simon.wind...@cornfield.org.uk <mailto:simon.wind...@cornfield.org.uk> “There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.”
