Thread: 7.1 Upgrade Failure
To Whom It May Concern: Just wanted to share with you the errors I keep getting trying to upgrade my current version 6.5 of Postgresql to 7.1. 1.) Installed 6.5 via the Red Hat linux install of 6.2 2.) Have plenty of hardware (dual 600's, 2Gig RAM, 18G HD). 3.) Downloaded 7.1 tar file. 4.) Unzipped and untarred the file. 5.) Stopped all the current postgres services, etc,. 6.) Ran the install version off postgresql.org site. 7.) One note that might be of interest is Red Hat's install put the pgsql directory in /var/lib/pgsql 8.) The install for 7.1 is /usr/local/pgsql. 9.) Tried to even rename the /var/lib/pgsql and install new in that directory, but got corruption errors looking for files in /usr/local/pgsql. If anyone has any help, I would greatly appreciate it. Thanks.
Schroeder, Steve writes: > 9.) Tried to even rename the /var/lib/pgsql and install new in that > directory, but got corruption errors looking for files in /usr/local/pgsql. Precise details please, preferably copied from the screen. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tuesday 15 May 2001 11:25, Schroeder, Steve wrote: > To Whom It May Concern: > 5.) Stopped all the current postgres services, etc,. > 6.) Ran the install version off postgresql.org site. > 7.) One note that might be of interest is Red Hat's install put the > pgsql directory in /var/lib/pgsql > 8.) The install for 7.1 is /usr/local/pgsql. > 9.) Tried to even rename the /var/lib/pgsql and install new in that > directory, but got corruption errors looking for files in /usr/local/pgsql. You have to do a 'dump/initdb/restore' cycle, as inconvenient as it may be. Restore your 6.5 installation. Then, run pg_dumpall and place the resulting ASCII dump somewhere you can find it. Now, install 7.1, either from RPM (which is available) or from source. You will need to go through the initdb portion of the installation document. Now, follow the instructions on upgrading, beginning with the restore process - -- this is part of the documentation. - -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7AuW05kGGI8vV9eERAt9eAJ9sbvYmzbQxc8RZ5yGPdv9rkY1hggCgkLNj V1Ktud47qR0sb0N5YcVgadE= =Z1eF -----END PGP SIGNATURE-----
This is in 7.1.1:
I was playing around with a table called user_comments and then dropped it.
One of its columns referenced the user_id column of another table, users.
Now whenever I try to update users, I get results like this:
bf2-new=# update users set last_visit=sysdate() where user_id=4; ERROR:
Relation 'user_comments' does not exist
Somehow a constraint trigger that should have been dropped wasn't.
I looked at the dump file and this is the only reference to user_comments:
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "users" NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"
('<unnamed>', 'user_comments', 'users', 'UNSPECIFIED', 'user_id',
'user_id');
but I am not out of the woods here because it won't let me drop it:
bf2-new=# drop trigger RI_ConstraintTrigger_44349 on users; ERROR:
DropTrigger: there is no trigger ri_constrainttrigger_44349 on relation
users
It's in pg_trigger all right -- and with tgrelid = (select relfilenode from
pg_class where relname = 'users') -- but it won't drop. Would manually
removing it from pg_trigger cause Bad Things to happen?
-Jonathan
On Wed, 16 May 2001, Jonathan Ellis wrote:
> This is in 7.1.1:
>
> I was playing around with a table called user_comments and then dropped it.
> One of its columns referenced the user_id column of another table, users.
> Now whenever I try to update users, I get results like this:
> bf2-new=# update users set last_visit=sysdate() where user_id=4; ERROR:
> Relation 'user_comments' does not exist
>
> Somehow a constraint trigger that should have been dropped wasn't.
>
> I looked at the dump file and this is the only reference to user_comments:
>
> CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "users" NOT DEFERRABLE
> INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"
> ('<unnamed>', 'user_comments', 'users', 'UNSPECIFIED', 'user_id',
> 'user_id');
>
> but I am not out of the woods here because it won't let me drop it:
>
> bf2-new=# drop trigger RI_ConstraintTrigger_44349 on users; ERROR:
> DropTrigger: there is no trigger ri_constrainttrigger_44349 on relation
> users
>
> It's in pg_trigger all right -- and with tgrelid = (select relfilenode from
> pg_class where relname = 'users') -- but it won't drop. Would manually
> removing it from pg_trigger cause Bad Things to happen?
Yes, and you'll need to double quote the trigger name.
try
drop trigger "RI_ConstraintTrigger_44349" on users;
How did you drop the table? Did you reload a dump file (like the one that
referenced it)?
> > Would manually > > removing it from pg_trigger cause Bad Things to happen? > > Yes, and you'll need to double quote the trigger name. > try > drop trigger "RI_ConstraintTrigger_44349" on users; That worked. Why was that? > How did you drop the table? Did you reload a dump file (like the one that > referenced it)? I'm afraid I don't understand the question. I dropped it with drop table. Although thinking back on it, the drop may have been on 7.0.2. Can't remember whether that was before or after upgrading... -Jonathan
On Wed, 16 May 2001, Jonathan Ellis wrote: > > > Would manually > > > removing it from pg_trigger cause Bad Things to happen? > > > > Yes, and you'll need to double quote the trigger name. > > try > > drop trigger "RI_ConstraintTrigger_44349" on users; > > That worked. Why was that? It's a mixed case thing. The name of the constraint is actually RI_ConstraintTrigger_44349 (with that casing). When you specify it without the quotes, postgres lowercases it to ri_constrainttrigger_44349 and fails to find it. > > How did you drop the table? Did you reload a dump file (like the one that > > referenced it)? > > I'm afraid I don't understand the question. I dropped it with drop table. > Although thinking back on it, the drop may have been on 7.0.2. Can't > remember whether that was before or after upgrading... Wait, okay, I think I know what happened. There was a problem with 7.0.? dumps not having the information about the "other" table in the references constraint written out so when you dropped the table it didn't realize it needed to drop the trigger. You may want to drop the constraint triggers and recreate them with alter table add constraint.