correcting tablespaces inside data folder

Hello. Yes I'm one of those guys who only recently realized the mess of having tablespaces inside the data directory... now I want to use pg_upgrade and it will make things even worse... Does anyone have a best approach to this problem? Fastest/safest? pg 9.3.x on win Thanks, Duarte

Re: correcting tablespaces inside data folder

Magnus, You mean changing the symlinks inside pg_tblspc? On Fri, Jul 6, 2018 at 5:49 PM Magnus Hagander wrote: > On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira > wrote: > >> Hello. >> >> Yes I'm one of those guys who only recently realized the mess of havin

Re: correcting tablespaces inside data folder

On Fri, Jul 6, 2018 at 6:15 PM Magnus Hagander wrote: > (please don't top-post. It makes it hard to follow discussions) > > On Fri, Jul 6, 2018 at 7:05 PM, Duarte Carreira > wrote: > >> Magnus, >> You mean changing the symlinks inside pg_tblspc? >> >&g

Re: correcting tablespaces inside data folder

Hi. On Fri, Jul 6, 2018 at 6:30 PM Duarte Carreira wrote: > > > On Fri, Jul 6, 2018 at 6:15 PM Magnus Hagander > wrote: > >> (please don't top-post. It makes it hard to follow discussions) >> >> On Fri, Jul 6, 2018 at 7:05 PM, Duarte Carreira >> wro

duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

ng FUNCTION "public.pgis_abs_out("public"."pgis_abs")" pg_restore: creating TYPE "public.pgis_abs" pg_restore: creating TYPE "public.rastbandarg" pg_restore: creating COMMENT "public.TYPE "rastbandarg"" pg_restore: creating TYPE "public.reclassarg" pg_restore: creating COMMENT "public.TYPE "reclassarg"" pg_restore: creating SHELL TYPE "public.spheroid" pg_restore: creating FUNCTION "public.spheroid_in("cstring")" pg_restore: creating FUNCTION "public.spheroid_out("public"."spheroid")" pg_restore: creating TYPE "public.spheroid" pg_restore: creating TYPE "public.summarystats" pg_restore: creating COMMENT "public.TYPE "summarystats"" pg_restore: creating TYPE "public.tablefunc_crosstab_2" pg_restore: creating TYPE "public.tablefunc_crosstab_3" pg_restore: creating TYPE "public.tablefunc_crosstab_4" pg_restore: creating TYPE "public.unionarg" pg_restore: creating COMMENT "public.TYPE "unionarg"" pg_restore: creating TYPE "public.valid_detail" pg_restore: creating SHELL TYPE "sde.se_coord" pg_restore: creating FUNCTION "sde.st_coord_in("cstring")" pg_restore: creating FUNCTION "sde.st_coord_out("sde"."se_coord")" pg_restore: creating FUNCTION "sde.st_coord_recv("internal")" pg_restore: creating FUNCTION "sde.st_coord_send("sde"."se_coord")" pg_restore: creating TYPE "sde.se_coord" pg_restore: creating SHELL TYPE "sde.se_extent" pg_restore: creating FUNCTION "sde.st_extent_in("cstring")" pg_restore: creating FUNCTION "sde.st_extent_out("sde"."se_extent")" pg_restore: creating FUNCTION "sde.st_extent_recv("internal")" pg_restore: creating FUNCTION "sde.st_extent_send("sde"."se_extent")" pg_restore: creating TYPE "sde.se_extent" pg_restore: creating FUNCTION "sde.st_envelope_in("cstring")" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 4712; 1255 1141726 FUNCTION st_envelope_in("cstring") sde pg_restore: [archiver (db)] could not execute query: ERROR: duplicate key value violates unique constraint "pg_type_oid_index" DETAIL: Key (oid)=(1142573) already exists. Command was: CREATE FUNCTION "sde"."st_envelope_in"("cstring") RETURNS "sde"."st_envelope" LANGUAGE "c" IMMUTABLE STRICT AS 'st_g... command: "D:\Program Files\PostgreSQL\9.5\bin/pg_restore" --port 50432 --username ^"postgres^" --exit-on-error --verbose --dbname ^"dbname^=postgis^" "pg_upgrade_dump_16393.custom" >> "pg_upgrade_dump_16393.log" 2>&1 Duarte Carreira Diretor | Dep. Informa??o Geogr?fica e Cartografia www.edia.pt<http://www.edia.pt> www.alqueva.com.pt<http://www.alqueva.com.pt> Tel. +351 284315100 [http://www.edia.pt/edia/images/rodape.jpg]<http://www.edia.pt>

Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Hi Tom. It's 9.5 latest. I'll try pgdump tomorrow. Obter o Outlook para Android<https://aka.ms/ghei36> From: Tom Lane Sent: Thursday, January 24, 2019 9:02:25 PM To: Duarte Carreira Cc: pgsql-gene...@postgresql.org Subject: Re: duplicate OID

RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

no warnings. I do a pg_dump full backup every week with no incidents. Versions are: Windows x64 9.3.22 9.5.15 Thanks for taking a look. Duarte De: Duarte Carreira Enviada: 24 de janeiro de 2019 21:52 Para: Tom Lane Cc: pgsql-gene...@postgresql.org Assunto: Re: duplicate OID issue when using

RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Sure, I'll be sending you the dump shortly off-list. Duarte -Mensagem original- De: Tom Lane Enviada: 25 de janeiro de 2019 14:28 Para: Duarte Carreira Cc: pgsql-gene...@postgresql.org Assunto: Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5 Duarte Car

RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

t;;"n" "function st_envelope_equal(st_envelope,st_envelope)";"n" "function st_envelope_equal(st_envelope,st_envelope)";"n" "function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n" "function st_geo_gist_equal(st_

RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Just a footnote. This is not a postgis object, it's an esri object. Belongs to the sde schema that is installed in every database where esri software is installed in, namely arcgis server. -Mensagem original- De: Duarte Carreira Enviada: 25 de janeiro de 2019 18:03 Para: 'Tom

RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

equal(st_envelope,st_envelope)";"n" "function st_envelope_equal(st_envelope,st_envelope)";"n" "function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n" "function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n" I'

RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

ource rocks! Duarte -Mensagem original- De: Duarte Carreira Enviada: 25 de janeiro de 2019 18:24 Para: Tom Lane Cc: pgsql-gene...@postgresql.org Assunto: RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5 Tom, can't thank you enough. Now, the 2nd query and result

Re: Query on postgres_fdw extension

Hello everyone. I got here after encountering the same difficulty, although on a much more mundane scenario. I'm used to fdw on a read-only basis. I was just inserting a new record on a foreign table and got blocked... and after much searching got here. Not to rant or anything, but I am complete

Re: Query on postgres_fdw extension

e to human error but as long as nothing changes and no one deletes it thinking it's garbage... Thanks. Vijaykumar Jain escreveu no dia quinta, 20/01/2022 à(s) 17:39: > On Thu, 20 Jan 2022 at 21:29, Duarte Carreira > wrote: > >> Hello everyone. >> >> I don't

Re: Query on postgres_fdw extension

eed for trigger and view. If I understood correctly. I have this 2 table setup working. It's a workaround that quickly escalates out of hand though... with little added value. Thanks. Laurenz Albe escreveu no dia sexta, 21/01/2022 à(s) 13:59: > On Thu, 2022-01-20 at 15:59 +, Duart