From: | Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Cc: | "Hilbert, Sebastian" <Sebastian(dot)Hilbert(at)gmx(dot)net> |
Subject: | pg_upgrade ?deficiency |
Date: | 2013-11-19 10:22:47 |
Message-ID: | 20131119102247.GA4557@hermes.hilbert.loc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
Hello all,
I am upgrading a 8.4 cluster to 9.1 and am seeing the following:
SQL command failed
CREATE TEMPORARY TABLE info_rels (reloid) AS
SELECT c.oid
FROM
pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
LEFT OUTER JOIN pg_catalog.pg_index i ON c.oid = i.indexrelid
WHERE
relkind IN ('r', 'm', 'i', 'S')
AND
i.indisvalid IS DISTINCT FROM false
AND
i.indisready IS DISTINCT FROM false
AND
((n.nspname !~ '^pg_temp_'
AND
n.nspname !~ '^ pg_toast_temp_'
AND
n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast')
AND
c.oid >= 16384
)
OR
(n.nspname = 'pg_catalog'
AND
relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index')
));
ERROR: transaction is read-only
Now, this is quite understandable since one of the databases
is set to
ALTER DATABASE ... SET DEFAULT_TRANSACTION_READ_ONLY TO ON;
However, since the above setting is something which can
be expected every so often in any odd PostgreSQL cluster
(and not some weird coincidence no one really knows how
they got into in the first place) I would think pg_upgrade
really should be able to handle.
Technically that's pretty easy - make sure transactions are
set to readwrite for the pg_upgrade run by any number of
means:
- ALTER DATABASE before/after pg_upgrade
- ALTER USER running the pg_upgrade
- SET TRANSACTION READ WRITE at the appropriate times
- ...
Or at least this limitation of pg_upgrade (requiring
DB write access) should get a mention in the docs and/or
man page.
What is the informed opinion on this ?
Thanks,
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2013-11-19 10:42:10 | Re: expression index not used within function |
Previous Message | Stefan Keller | 2013-11-19 07:16:52 | Re: Postgres as In-Memory Database? |
From | Date | Subject | |
---|---|---|---|
Next Message | Kyotaro HORIGUCHI | 2013-11-19 10:24:15 | Re: Using indices for UNION. |
Previous Message | Rohit Goyal | 2013-11-19 10:22:45 | Call flow of btinsert(PG_FUNCTION_ARGS) |