PostgreSQL migration

Post Reply
germie
Posts: 24
Joined: 27 Apr 15 22:43

PostgreSQL migration

Post by germie » 18 Sep 19 17:31

Hi all,

The new psu 5 offers support for postgresql 11.5.
What is the way to migrate my existing postgresql 10 database to 11.5 pkease?
Any help is very welcome

Kind regards from Holland

Gern Wind

Hert
Posts: 16000
Joined: 13 Sep 03 7:24

Re: PostgreSQL migration

Post by Hert » 19 Sep 19 12:02

Hi Gern, best to start here;
https://www.postgresql.org/docs/11/upgr ... PG-UPGRADE

But if you're using Postgresql only for PSU then I suggest;
1. Make a backup of your catalog database using pdAdmin4 (don't use the backup in PSU)
2. Make a backup of your thumbs database using pdAdmin4 (don't use the backup in PSU)
3. Close/End the Postgresql 10 service
4. Install Postgresql 11 using the PSU instructions
https://manualsu.idimager.com/version5/ ... upreme.pdf
5. Start PSU while holding down Ctrl+Alt+Win (Ctrl+Alt+Cmd on macOS)
6. Let PSU create a new database in your Postgresql 11 installation
7. Close PSU
8. Start pgAdmin4
9. Right click on the photosupreme database and select Restore and point to the file created in step 1
10. Right click on the photosupreme_thumbs database and select Restore and point to the file created in step 2

Once finished, you should be good to go.

PS> While step 6 seems redundant, you can not ignore that step.
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

germie
Posts: 24
Joined: 27 Apr 15 22:43

Re: PostgreSQL migration

Post by germie » 19 Sep 19 14:24

Thanks for the reply Hert.
I did as instructed and with the photosupreme database. The the log by pgamin showed 291 ignored errors.
I show the beginning and the end of the logfile below.
Questions: Is this normal?

pg_restore: connecting to database for restore
pg_restore: creating EXTENSION "pg_trgm"
pg_restore: creating COMMENT "EXTENSION pg_trgm"
pg_restore: creating FUNCTION "public.process_idsearchdataupdatefts()"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 254; 1255 18564 FUNCTION process_idsearchdataupdatefts() idimager_main
pg_restore: [archiver (db)] could not execute query: ERROR: function "process_idsearchdataupdatefts" already exists with same argument types
Command was: CREATE FUNCTION public.process_idsearchdataupdatefts() RETURNS trigger
LANGUAGE plpgsql
AS $$ begin new.ContentTypeIdx := to_tsvector('dutch', new.ContentType); new.ContentGroupIdx := to_tsvector('dutch', new.ContentGroup); new.ContentValueIdx := to_tsvector('dutch', new.ContentValue); return new; end; $$;



pg_restore: creating TABLE "public.idcatalogitem"
pg_restore: [archiver (db)] Error from TOC entry 214; 1259 17957 TABLE idcatalogitem postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation "idcatalogitem" already exists
Command was: CREATE TABLE public.idcatalogitem (
guid character varying(50) NOT NULL,
filename character varying(1024),
datetimestamp timestamp without time zone,
filesize integer,
filestamp timestamp without time zone,
rating integer,
imagename character varying(1024),
imagedescription text,
udf1 character varying(255),
udf2 character varying(255),
udf3 character varying(255),
udf4 character varying(255),
udf5 character varying(255),
userguid character varying(50),
autocataloged integer DEFAULT 0,
lastupdate timestamp without time zone,
idcreated timestamp without time zone DEFAULT now(),
idcounter integer DEFAULT 0,
idmarked integer DEFAULT 0,
idlabel character varying(50) DEFAULT ''::character varying,
idinsync integer DEFAULT 0,
idbookmark integer DEFAULT 0,
idfilenamelen integer DEFAULT 0,
pathguid character varying(50),
idfiletype character varying(50) DEFAULT ''::character varying,
groupguid character varying(50),
grouporder integer DEFAULT 0,
idgpslon double precision,
idgpslat double precision,
idgpsalt double precision,
idhasrecipe integer DEFAULT 0,
idsignature character varying(50) DEFAULT ''::character varying,
idsimilaritycode character varying(50),
idyear integer DEFAULT 0,
idmonth integer DEFAULT 0,
idday integer DEFAULT 0,
idweek integer DEFAULT 0,
idweekday integer DEFAULT 0
);



pg_restore: creating TABLE "public.idcatalogitemdefinition"
pg_restore: [archiver (db)] Error from TOC entry 223; 1259 18052 TABLE idcatalogitemdefinition postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation "idcatalogitemdefinition" already exists
Command was: CREATE TABLE public.idcatalogitemdefinition (
guid character varying(50) NOT NULL,
catalogitemguid character varying(50) NOT NULL,
idassigned timestamp without time zone
);



pg_restore: creating TABLE "public.idcollectioninfo"
pg_restore: [archiver (db)] Error from TOC entry 203; 1259 17856 TABLE idcollectioninfo postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation "idcollectioninfo" already exists
Command was: CREATE TABLE public.idcollectioninfo (
guid character varying(50) NOT NULL,
collectionsource character varying(255),
collectionname character varying(255),
collectiondescription text,
collectionhotitemguid character varying(50),
lastsort character varying(2),
viewstyle character varying(2),
lastsortorder character varying(2),
lastusedtemplate character varying(255),
lastusedftpdir character varying(255),
lastusedtargetdir character varying(255)
);



pg_restore: creating TABLE "public.idfilepath"
pg_restore: [archiver (db)] Error from TOC entry 202; 1259 17846 TABLE idfilepath postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation "idfilepath" already exists
Command was: CREATE TABLE public.idfilepath (
guid character varying(50) NOT NULL,
mediumtype character varying(2),
mediumname character varying(255),
mediumserial double precision,
filepath character varying(1024),
rootname character varying(512) DEFAULT ''::character varying,
pathstate integer,
idsignaturedate timestamp without time zone,
idignored integer DEFAULT 0,
foldername character varying(512)
);






pg_restore: creating FK CONSTRAINT "public.idversionplaceholder idplaceholderversion_placeholder"
pg_restore: [archiver (db)] Error from TOC entry 3356; 2606 18541 FK CONSTRAINT idversionplaceholder idplaceholderversion_placeholder postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "idplaceholderversion_placeholder" for relation "idversionplaceholder" already exists
Command was: ALTER TABLE ONLY public.idversionplaceholder
ADD CONSTRAINT idplaceholderversion_placeholder FOREIGN KEY (placeholderguid) REFERENCES public.idplaceholder(guid) ON UPDATE CASCADE ON DELETE CASCADE;



pg_restore: creating FK CONSTRAINT "public.idversionplaceholder idplaceholderversion_version"
pg_restore: [archiver (db)] Error from TOC entry 3357; 2606 18546 FK CONSTRAINT idversionplaceholder idplaceholderversion_version postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "idplaceholderversion_version" for relation "idversionplaceholder" already exists
Command was: ALTER TABLE ONLY public.idversionplaceholder
ADD CONSTRAINT idplaceholderversion_version FOREIGN KEY (itemguid) REFERENCES public.idimageversion(guid) ON UPDATE CASCADE ON DELETE CASCADE;



pg_restore: creating FK CONSTRAINT "public.idprop idprop_user"
pg_restore: [archiver (db)] Error from TOC entry 3324; 2606 18471 FK CONSTRAINT idprop idprop_user postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "idprop_user" for relation "idprop" already exists
Command was: ALTER TABLE ONLY public.idprop
ADD CONSTRAINT idprop_user FOREIGN KEY (userguid) REFERENCES public.iduser(guid);



pg_restore: creating FK CONSTRAINT "public.idpropgroupcontent idpropgroupcontent_group"
pg_restore: [archiver (db)] Error from TOC entry 3349; 2606 18481 FK CONSTRAINT idpropgroupcontent idpropgroupcontent_group postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "idpropgroupcontent_group" for relation "idpropgroupcontent" already exists
Command was: ALTER TABLE ONLY public.idpropgroupcontent
ADD CONSTRAINT idpropgroupcontent_group FOREIGN KEY (guid) REFERENCES public.idpropgroup(guid) ON UPDATE CASCADE ON DELETE CASCADE;



pg_restore: creating FK CONSTRAINT "public.idpropgroupcontent idpropgroupcontent_prop"
pg_restore: [archiver (db)] Error from TOC entry 3350; 2606 18486 FK CONSTRAINT idpropgroupcontent idpropgroupcontent_prop postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "idpropgroupcontent_prop" for relation "idpropgroupcontent" already exists
Command was: ALTER TABLE ONLY public.idpropgroupcontent
ADD CONSTRAINT idpropgroupcontent_prop FOREIGN KEY (propguid) REFERENCES public.idprop(guid) ON UPDATE CASCADE ON DELETE CASCADE;



pg_restore: creating FK CONSTRAINT "public.idpropgrouppathcontent idpropgrouppathcontent_group"
pg_restore: [archiver (db)] Error from TOC entry 3348; 2606 18491 FK CONSTRAINT idpropgrouppathcontent idpropgrouppathcontent_group postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "idpropgrouppathcontent_group" for relation "idpropgrouppathcontent" already exists
Command was: ALTER TABLE ONLY public.idpropgrouppathcontent
ADD CONSTRAINT idpropgrouppathcontent_group FOREIGN KEY (guid) REFERENCES public.idpropgroup(guid) ON UPDATE CASCADE ON DELETE CASCADE;



pg_restore: creating FK CONSTRAINT "public.idpropgroupperiodcontent idpropgroupperiodcontent_group"
pg_restore: [archiver (db)] Error from TOC entry 3347; 2606 18496 FK CONSTRAINT idpropgroupperiodcontent idpropgroupperiodcontent_group postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "idpropgroupperiodcontent_group" for relation "idpropgroupperiodcontent" already exists
Command was: ALTER TABLE ONLY public.idpropgroupperiodcontent
ADD CONSTRAINT idpropgroupperiodcontent_group FOREIGN KEY (guid) REFERENCES public.idpropgroup(guid) ON UPDATE CASCADE ON DELETE CASCADE;



pg_restore: creating FK CONSTRAINT "public.idpropgroup idpropgroupuser"
pg_restore: [archiver (db)] Error from TOC entry 3332; 2606 18476 FK CONSTRAINT idpropgroup idpropgroupuser postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "idpropgroupuser" for relation "idpropgroup" already exists
Command was: ALTER TABLE ONLY public.idpropgroup
ADD CONSTRAINT idpropgroupuser FOREIGN KEY (userguid) REFERENCES public.iduser(guid) ON UPDATE CASCADE ON DELETE CASCADE;



pg_restore: creating FK CONSTRAINT "public.idproprelation idproprelation_idpropparent"
pg_restore: [archiver (db)] Error from TOC entry 3346; 2606 18501 FK CONSTRAINT idproprelation idproprelation_idpropparent postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "idproprelation_idpropparent" for relation "idproprelation" already exists
Command was: ALTER TABLE ONLY public.idproprelation
ADD CONSTRAINT idproprelation_idpropparent FOREIGN KEY (parentguid) REFERENCES public.idprop(guid) ON UPDATE CASCADE ON DELETE CASCADE;



pg_restore: creating FK CONSTRAINT "public.idimagescore idscore_image"
pg_restore: [archiver (db)] Error from TOC entry 3360; 2606 18436 FK CONSTRAINT idimagescore idscore_image postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "idscore_image" for relation "idimagescore" already exists
Command was: ALTER TABLE ONLY public.idimagescore
ADD CONSTRAINT idscore_image FOREIGN KEY (contestguid, imageguid) REFERENCES public.idimagecollectionitem(guid, itemguid) ON UPDATE CASCADE ON DELETE CASCADE;



pg_restore: creating FK CONSTRAINT "public.idimagescore idscore_user"
pg_restore: [archiver (db)] Error from TOC entry 3361; 2606 18441 FK CONSTRAINT idimagescore idscore_user postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "idscore_user" for relation "idimagescore" already exists
Command was: ALTER TABLE ONLY public.idimagescore
ADD CONSTRAINT idscore_user FOREIGN KEY (userguid) REFERENCES public.iduser(guid);



pg_restore: creating FK CONSTRAINT "public.iduserfavorite iduser_favorite"
pg_restore: [archiver (db)] Error from TOC entry 3339; 2606 18511 FK CONSTRAINT iduserfavorite iduser_favorite postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "iduser_favorite" for relation "iduserfavorite" already exists
Command was: ALTER TABLE ONLY public.iduserfavorite
ADD CONSTRAINT iduser_favorite FOREIGN KEY (userguid) REFERENCES public.iduser(guid);



pg_restore: creating FK CONSTRAINT "public.iduserusage iduser_usage_user"
pg_restore: [archiver (db)] Error from TOC entry 3331; 2606 18526 FK CONSTRAINT iduserusage iduser_usage_user postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "iduser_usage_user" for relation "iduserusage" already exists
Command was: ALTER TABLE ONLY public.iduserusage
ADD CONSTRAINT iduser_usage_user FOREIGN KEY (userguid) REFERENCES public.iduser(guid) ON UPDATE CASCADE ON DELETE CASCADE;



pg_restore: creating FK CONSTRAINT "public.iduseruser iduser_user"
pg_restore: [archiver (db)] Error from TOC entry 3329; 2606 18531 FK CONSTRAINT iduseruser iduser_user postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "iduser_user" for relation "iduseruser" already exists
Command was: ALTER TABLE ONLY public.iduseruser
ADD CONSTRAINT iduser_user FOREIGN KEY (userguid) REFERENCES public.iduser(guid) ON UPDATE CASCADE ON DELETE CASCADE;



pg_restore: creating FK CONSTRAINT "public.iduseruser iduser_user2"
pg_restore: [archiver (db)] Error from TOC entry 3330; 2606 18536 FK CONSTRAINT iduseruser iduser_user2 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "iduser_user2" for relation "iduseruser" already exists
Command was: ALTER TABLE ONLY public.iduseruser
ADD CONSTRAINT iduser_user2 FOREIGN KEY (userguid2) REFERENCES public.iduser(guid);



pg_restore: creating FK CONSTRAINT "public.iduserprop iduserprop_prop"
pg_restore: [archiver (db)] Error from TOC entry 3336; 2606 18516 FK CONSTRAINT iduserprop iduserprop_prop postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "iduserprop_prop" for relation "iduserprop" already exists
Command was: ALTER TABLE ONLY public.iduserprop
ADD CONSTRAINT iduserprop_prop FOREIGN KEY (guid) REFERENCES public.idprop(guid) ON UPDATE CASCADE ON DELETE CASCADE;



pg_restore: creating FK CONSTRAINT "public.iduserprop iduserprop_user"
pg_restore: [archiver (db)] Error from TOC entry 3337; 2606 18521 FK CONSTRAINT iduserprop iduserprop_user postgres
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "iduserprop_user" for relation "iduserprop" already exists
Command was: ALTER TABLE ONLY public.iduserprop
ADD CONSTRAINT iduserprop_user FOREIGN KEY (userguid) REFERENCES public.iduser(guid) ON UPDATE CASCADE ON DELETE CASCADE;



WARNING: errors ignored on restore: 291

Hert
Posts: 16000
Joined: 13 Sep 03 7:24

Re: PostgreSQL migration

Post by Hert » 19 Sep 19 14:49

Try to enable the restore option in pgAdmin to clean before restore.
pgAdminRestore.png
pgAdminRestore.png (103.99 KiB) Viewed 192 times
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

germie
Posts: 24
Joined: 27 Apr 15 22:43

Re: PostgreSQL migration

Post by germie » 19 Sep 19 17:50

Hi Hert,

That worked!
First of all I made the backup in .sql format, not in .backup format.
With the clean before restore option checked it restored photosupreme database without error.
Now moving to the thumbs database.
I'll keep you posted.

Kind regards from Holland
Germ Wind

germie
Posts: 24
Joined: 27 Apr 15 22:43

Re: PostgreSQL migration

Post by germie » 20 Sep 19 11:54

Hi Hert,

Thumbs database restored without error.
PSu opens just fine with the new database.
Thanks!

Kind regards from Holland
Germ Wind

Post Reply