Re: [GENERAL] pg_upgrade?: Upgrade method from/to any version on random OS?

[David G. Johnston]

On Wed, Sep 27, 2017 at 12:48 PM, Hans Schou  wrote:

> I have looked through
> https://www.postgresql.org/docs/9.6/static/pgupgrade.html
> but it seems more complicated than necessary.
>

​[perform dump/restore]​

It went very good but took 100 minutes - where we had downtime - not so
> good.
>

​There is a correlation between the decreased downtime that pg_upgrade
facilitates and its complexity.​

If you cannot afford the 2 hours of downtime for dump/restore then likely
learning the pg_upgrade process would be the better choice for you.

David J.

[GENERAL] pg_upgrade?: Upgrade method from/to any version on random OS?

[Hans Schou]

If I ask this question without mention the PG version I upgrade from and
to, and don't mention the operating system, and don't mention replication,
would the standard answer be:
  Use pg_upgrade!

I have looked through
https://www.postgresql.org/docs/9.6/static/pgupgrade.html
but it seems more complicated than necessary.

My actual situation was PG version from 9.3 to 9.6, on the same server, no
replication, and OS was Windows.

I did:
* SET PGUSER=postgres
* SET /P PGPASSWORD=
* change port number in 9.3 and restart
* 9.3\bin\pg_dumpall.exe --port=5431 | 9.6\bin\psql.exe --port=5433
* change port number on 9.6 to 5432 and restart, done

It went very good but took 100 minutes - where we had downtime - not so
good.

Would it have been:
* better
* faster
* safer
* easier
with pg_upgrade?

The way I did it is similar to what I have done with Oracle and MySQL. It
feels right.

best regards
Hans

[GENERAL] pg_upgrade fails right after printing "Running in verbose mode"

[Юрий Нелепко]

Hello.

Lately we upgraded pg version in our app to 9.6.1 from 9.2.
However one of the customers reported, that upgrade process failed on
Windows Server 2008 R2.

Our installer's logs showed that pg_upgrade returned -1073741515 which
translates to 0xC135, which in turn means binary is missing some dll
dependencies.
I ran pg_upgrade in customer's environment with dependency walker profiler
on. After that I did the same on 2008r2 in our lab.

The only difference in the logs that I have found was following:

Log from customer which resulted in error:
...
00:00:02.652: DllMain(0x00018000, DLL_PROCESS_ATTACH,
0x0042FB00) in "e:\prod_e\rfp\application\
depends-full\test\engine-new\bin\LIBPQ.DLL" called.
00:00:02.652: DllMain(0x00018000, DLL_PROCESS_ATTACH,
0x0042FB00) in "e:\prod_e\rfp\application\
depends-full\test\engine-new\bin\LIBPQ.DLL" returned 1 (0x1).
00:00:02.652: LoadLibraryExA("SspiCli.dll", 0x, 0x)
called from "c:\windows\system32\ADVAPI32.DLL" at address
0x07FEFEC5D995.
00:00:02.652: LoadLibraryExA("SspiCli.dll", 0x, 0x)
returned 0x07FEFD02.
00:00:02.667: GetProcAddress(0x07FEFD02
[c:\windows\system32\SSPICLI.DLL], "GetUserNameExA") called from
"c:\windows\system32\ADVAPI32.DLL" at address 0x07FEFEC5DA01 and
returned 0x07FEFD021640.
00:00:02.667: DllMain(0x07FEFF84, DLL_PROCESS_DETACH,
0x0001) in "c:\windows\system32\IMM32.DLL" called.
00:00:02.667: DllMain(0x07FEFF84, DLL_PROCESS_DETACH,
0x0001) in "c:\windows\system32\IMM32.DLL" returned 1 (0x1).
00:00:02.667: DllMain(0x07FEFF32, DLL_PROCESS_DETACH,
0x0001) in "c:\windows\system32\MSCTF.DLL" called.
00:00:02.667: DllMain(0x07FEFF32, DLL_PROCESS_DETACH,
0x0001) in "c:\windows\system32\MSCTF.DLL" returned 1 (0x1).
00:00:02.667: DllMain(0x00018000, DLL_PROCESS_DETACH,
0x0001) in "e:\prod_e\rfp\application\
depends-full\test\engine-new\bin\LIBPQ.DLL" called.
00:00:02.667: DllMain(0x00018000, DLL_PROCESS_DETACH,
0x0001) in "e:\prod_e\rfp\application\
depends-full\test\engine-new\bin\LIBPQ.DLL" returned 1 (0x1).
...
00:00:02.714: DllMain(0x07FEFD2F, DLL_PROCESS_DETACH,
0x0001) in "c:\windows\system32\KERNELBASE.DLL" returned
4294828033 (0xFFFDE001).
00:00:02.714: Exited "e:\prod_e\rfp\application\
depends-full\test\engine-new\bin\PG_UPGRADE.EXE" (process 0x1980) with code
1 (0x1).


Log from our lab:
...

00:00:00.578: DllMain(0x00018000, DLL_PROCESS_ATTACH,
0x0042FB00) in
"c:\users\administrator\desktop\depends-full\test\engine-new\bin\LIBPQ.DLL"
called.
00:00:00.578: DllMain(0x00018000, DLL_PROCESS_ATTACH,
0x0042FB00) in
"c:\users\administrator\desktop\depends-full\test\engine-new\bin\LIBPQ.DLL"
returned 1 (0x1).
00:00:00.578: LoadLibraryExA("SspiCli.dll", 0x, 0x)
called from "c:\windows\system32\ADVAPI32.DLL" at address
0x07FEFD95D995.
00:00:00.578: LoadLibraryExA("SspiCli.dll", 0x, 0x)
returned 0x07FEFCF5.
00:00:00.578: GetProcAddress(0x07FEFCF5
[c:\windows\system32\SSPICLI.DLL], "GetUserNameExA") called from
"c:\windows\system32\ADVAPI32.DLL" at address 0x07FEFD95DA01 and
returned 0x07FEFCF51640.
00:00:00.593: GetProcAddress(0x07FEFE50
[c:\windows\system32\MSVCRT.DLL],
"_putenv") called from
"c:\users\administrator\desktop\depends-full\test\engine-new\bin\PG_UPGRADE.EXE"
at address 0x000140012672 and returned 0x07FEFE542E54.
00:00:00.624: GetProcAddress(0x07FEF6A2
[c:\windows\system32\MSVCR120.DLL], "_putenv") called from
"c:\users\administrator\desktop\depends-full\test\engine-new\bin\PG_UPGRADE.EXE"
at address 0x000140012672 and returned 0x07FEF6A94380.
00:00:00.624: LoadLibraryA("ADVAPI32.DLL") called from
"c:\users\administrator\desktop\depends-full\test\engine-new\bin\PG_UPGRADE.EXE"
at address 0x00014000D84C.
00:00:00.640: LoadLibraryA("ADVAPI32.DLL") returned 0x07FEFD94.
00:00:00.640: GetProcAddress(0x07FEFD94
[c:\windows\system32\ADVAPI32.DLL], "CreateRestrictedToken") called from
"c:\users\administrator\desktop\depends-full\test\engine-new\bin\PG_UPGRADE.EXE"
at address 0x00014000D868 and returned 0x07FEFD97A510.
00:00:00.656: Loaded "c:\windows\system32\APPHELP.DLL" at address
0x07FEFCF8.  Successfully hooked module.
00:00:00.687: DllMain(0x07FEFCF8, DLL_PROCESS_ATTACH,
0x) in "c:\windows\system32\APPHELP.DLL" called.
00:00:00.687: DllMain(0x07FEFCF8, DLL_PROCESS_ATTACH,
0x) in "c:\windows\system32\APPHELP.DLL" returned 1 (0x1).
00:00:21.809: DllMain(0x07FEFCF8, DLL_PROCESS_DETACH,
0x0001) in "c:\windows\system32\APPHELP.DLL" called.
00:00:21.809: DllMain(0x07FEFCF8, DLL_PROCESS_DETACH,
0x0001) in "c:\windows\system32\APPHELP.DLL" 

Re: [DOCS] [GENERAL] pg_upgrade --link on Windows

[Bruce Momjian]

On Tue, Jun 13, 2017 at 04:07:48PM -0400, Bruce Momjian wrote:
> On Fri, Jun  9, 2017 at 07:55:55AM -0700, Adrian Klaver wrote:
> I apologize for not being smarter on this thread.  When I helped with
> the Windows port, I was told Windows didn't have hard links for use by
> tablespace directories, so I got it into my head that Windows didn't
> have hard links.  Therefore, when I was writing the docs, I called them
> junction points.
> 
> Looking back to Postgres 9.0 where pg_upgrade was added to the tree, I
> see that the code even at that time used hard links on Windows.  I have
> created the attached patch which I will apply to all current Postgres
> versions to fix this error.
> 
> Thanks for the report and the research.  :-)

Patch applied all the way back to 9.3, where the junction point mention
first appeared.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade --link on Windows

[Bruce Momjian]

On Wed, Jun 14, 2017 at 09:59:04AM +0200, Klaus P. Pieper wrote:
> > -Ursprüngliche Nachricht-
> > 
> > I apologize for not being smarter on this thread.  When I helped with the
> > Windows port, I was told Windows didn't have hard links for use by
> tablespace
> > directories, so I got it into my head that Windows didn't have hard links.
> > Therefore, when I was writing the docs, I called them junction points.
> 
> It's actually not "Windows" providing hard links, it is the file system
> NTFS. FAT and its modern cousins don't provide hard links - but this will
> rarely be used for databases these days. 
> However, ReFS (introduced with server 2012 and providing some new features
> like automatic integrity checks, clustering etc.) does no longer provide
> hard links. 
> Are hard links used anywhere else but in pg_upgrade?

Nope, it is used only by transfer_relfile() calling linkFile().

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade --link on Windows

[Klaus P. Pieper]

> -Ursprüngliche Nachricht-
> 
> I apologize for not being smarter on this thread.  When I helped with the
> Windows port, I was told Windows didn't have hard links for use by
tablespace
> directories, so I got it into my head that Windows didn't have hard links.
> Therefore, when I was writing the docs, I called them junction points.

It's actually not "Windows" providing hard links, it is the file system
NTFS. FAT and its modern cousins don't provide hard links - but this will
rarely be used for databases these days. 
However, ReFS (introduced with server 2012 and providing some new features
like automatic integrity checks, clustering etc.) does no longer provide
hard links. 
Are hard links used anywhere else but in pg_upgrade?

Klaus



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade --link on Windows

[Bruce Momjian]

On Fri, Jun  9, 2017 at 07:55:55AM -0700, Adrian Klaver wrote:
> On 06/09/2017 07:39 AM, Arnaud L. wrote:
> >See this page for more details :
> >http://cects.com/overview-to-understanding-hard-links-junction-points-and-symbolic-links-in-windows/
> >
> >
> >Under "Hard Link (Linking for individual files)" :
> >"If the target is deleted, its content is still available through the hard
> >link"
> >
> >Junction Point (Directory Hard Link):
> >"If the target is moved, renamed or deleted, the Junction Point still
> >exists, but points to a non-existing directory"
> >
> >BUT, when I try to "pg_upgrade --link --check" with old-data-dir and
> >new-data-dir on different volumes, I get an error saying that both
> >directories must be on the same volume if --link is used.
> >So maybe pg_upgrade uses hard-links (i.e. to files), and only the
> >documentation is wrong by calling them junctions (i.e. soft links to
> >files) ?
> 
> Looks that way. In file.c in ~/src/bin/pg_upgrade I see:
> 
> #ifdef WIN32
>  300 /* implementation of pg_link_file() on Windows */
>  301 static int
>  302 win32_pghardlink(const char *src, const char *dst)
>  303 {
>  304 /*
>  305  * CreateHardLinkA returns zero for failure
>  306  * http://msdn.microsoft.com/en-us/library/aa363860(VS.85).aspx
>  307  */
>  308 if (CreateHardLinkA(dst, src, NULL) == 0)
>  309 {
>  310 _dosmaperr(GetLastError());
>  311 return -1;
>  312 }
>  313 else
>  314 return 0;
>  315 }
>  316 #endif

[docs list added]

I apologize for not being smarter on this thread.  When I helped with
the Windows port, I was told Windows didn't have hard links for use by
tablespace directories, so I got it into my head that Windows didn't
have hard links.  Therefore, when I was writing the docs, I called them
junction points.

Looking back to Postgres 9.0 where pg_upgrade was added to the tree, I
see that the code even at that time used hard links on Windows.  I have
created the attached patch which I will apply to all current Postgres
versions to fix this error.

Thanks for the report and the research.  :-)

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
new file mode 100644
index bf58a0a..4e27112
*** a/doc/src/sgml/ref/pgupgrade.sgml
--- b/doc/src/sgml/ref/pgupgrade.sgml
***
*** 123,129 
-k
--link
use hard links instead of copying files to the new
!   cluster (use junction points on Windows)
   
  
   
--- 123,129 
-k
--link
use hard links instead of copying files to the new
!   cluster
   
  
   

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade --link on Windows

[George Neuner]

On Fri, 9 Jun 2017 07:24:03 -0700, Adrian Klaver
 wrote:


>https://msdn.microsoft.com/en-us/library/windows/desktop/aa365006(v=vs.85).aspx
>
>Seems to me the difference is hard links point to file, junctions to 
>directories.

You can make either hard links or symlinks to files.  Junctions are
distinct from normal symlinks in that junctions can cross filesystems.
Microsoft's cmdline tools complain if you try to make a junction to a
file, because Microsoft intended junctions for mount points ... but
you can do it programmatically, or trick the tool by creating the link
and then replacing the target, and in most cases it will work the same
as a normal symlink.

I have seen cases where a junction to a file didn't work, but they
seemed to be application related rather than an OS issue.  Prior to
Vista, the mklink utility was not available, so people wanting to
create symlinks were forced to use the sysinternals junction utility.
https://technet.microsoft.com/en-us/sysinternals/bb545021.aspx

George



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade --link on Windows

[George Neuner]

On Fri, 9 Jun 2017 10:07:24 -0400, Bruce Momjian 
wrote:

>On Fri, Jun  9, 2017 at 12:00:56PM +0200, Arnaud L. wrote:
>> Hi
>> 
>> The pg_upgrade documentation for PostgreSQL 9.6 states that --link will use
>> junction points on Windows.
>> Shouldn't it rather user hard-links ?
>> If I'm not mistaken, with junction points (i.e. soft-links to directories),
>> the old data dir cannot be removed.
>> With hard-links to file, we can get rid of the old data dir once we are sure
>> that the upgrade is fine.
>
>I was told junction points on Windows were hard links and no one has
>ever complained about not being able to remove them.


NTFS junctions are a distinct type of symbolic link which is meant for
filesystem mount points.  In NTFS "normal" symlinks are restricted to
targets within the same filesystem.

You can use a junction anywhere you want a symlink, but not the
reverse.  The downside is that pathname parsing is slower with
junctions than with symlinks because of the possibility that the path
may cross into a different filesystem.


The documentation is not very clear, IMO.

https://msdn.microsoft.com/en-us/library/windows/desktop/aa365006(v=vs.85).aspx
https://msdn.microsoft.com/en-us/library/windows/desktop/aa363878(v=vs.85).aspx
https://msdn.microsoft.com/en-us/library/windows/desktop/aa365503(v=vs.85).aspx


The mklink utility can create any of these types of links.  Its
documentation does not describe the differences, but is shows that
hard links, symlinks, and junctions all are distinct concepts in
Windows.

https://technet.microsoft.com/en-us/library/cc753194(v=ws.11).aspx


George



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade --link on Windows

[Arnaud L.]

Le 9/06/2017 à 17:02, Arnaud L. a écrit :

Le 9/06/2017 à 16:55, Adrian Klaver a écrit :

On 06/09/2017 07:39 AM, Arnaud L. wrote:
So maybe pg_upgrade uses hard-links (i.e. to files), and only the 
documentation is wrong by calling them junctions (i.e. soft links to 
files) ?


Looks that way. In file.c in ~/src/bin/pg_upgrade I see:

#ifdef WIN32
   300 /* implementation of pg_link_file() on Windows */
   301 static int
   302 win32_pghardlink(const char *src, const char *dst)
   303 {
   304 /*
   305  * CreateHardLinkA returns zero for failure
   306  * http://msdn.microsoft.com/en-us/library/aa363860(VS.85).aspx
   307  */
   308 if (CreateHardLinkA(dst, src, NULL) == 0)
   309 {
   310 _dosmaperr(GetLastError());
   311 return -1;
   312 }
   313 else
   314 return 0;
   315 }
   316 #endif


Great !
So I did a full upgrade for nothing (just for safety), but that's good
to know for next time !
Should this be submitted to postgresql-bugs, or is there something more
specific to the documentation ?


I just found the pgsql-d...@postgresql.org list, for for the spam.

--
Arnaud



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade --link on Windows

[Arnaud L.]

Le 9/06/2017 à 16:55, Adrian Klaver a écrit :

On 06/09/2017 07:39 AM, Arnaud L. wrote:
So maybe pg_upgrade uses hard-links (i.e. to files), and only the 
documentation is wrong by calling them junctions (i.e. soft links to 
files) ?


Looks that way. In file.c in ~/src/bin/pg_upgrade I see:

#ifdef WIN32
   300 /* implementation of pg_link_file() on Windows */
   301 static int
   302 win32_pghardlink(const char *src, const char *dst)
   303 {
   304 /*
   305  * CreateHardLinkA returns zero for failure
   306  * http://msdn.microsoft.com/en-us/library/aa363860(VS.85).aspx
   307  */
   308 if (CreateHardLinkA(dst, src, NULL) == 0)
   309 {
   310 _dosmaperr(GetLastError());
   311 return -1;
   312 }
   313 else
   314 return 0;
   315 }
   316 #endif


Great !
So I did a full upgrade for nothing (just for safety), but that's good 
to know for next time !
Should this be submitted to postgresql-bugs, or is there something more 
specific to the documentation ?


Regards
--
Arnaud




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade --link on Windows

[Adrian Klaver]

On 06/09/2017 07:39 AM, Arnaud L. wrote:

Le 9/06/2017 à 16:07, Bruce Momjian a écrit :

I was told junction points on Windows were hard links and no one has
ever complained about not being able to remove them.


Sorry, I think my explanation was not very clear.
You can remove the link, but the point is to remove the target (i.e. the 
old-data-dir).
You can do this with a hard link (there still exists a hardlink pointing 
to the inode so it remains), but with a soft link you end up with a link 
to nothing.
Deleting a junction target in Windows will work, but you'll have an 
error trying to access the junction directory (directory not found).


See this page for more details :
http://cects.com/overview-to-understanding-hard-links-junction-points-and-symbolic-links-in-windows/ 



Under "Hard Link (Linking for individual files)" :
"If the target is deleted, its content is still available through the 
hard link"


Junction Point (Directory Hard Link):
"If the target is moved, renamed or deleted, the Junction Point still 
exists, but points to a non-existing directory"


BUT, when I try to "pg_upgrade --link --check" with old-data-dir and 
new-data-dir on different volumes, I get an error saying that both 
directories must be on the same volume if --link is used.
So maybe pg_upgrade uses hard-links (i.e. to files), and only the 
documentation is wrong by calling them junctions (i.e. soft links to 
files) ?


Looks that way. In file.c in ~/src/bin/pg_upgrade I see:

#ifdef WIN32
 300 /* implementation of pg_link_file() on Windows */
 301 static int
 302 win32_pghardlink(const char *src, const char *dst)
 303 {
 304 /*
 305  * CreateHardLinkA returns zero for failure
 306  * http://msdn.microsoft.com/en-us/library/aa363860(VS.85).aspx
 307  */
 308 if (CreateHardLinkA(dst, src, NULL) == 0)
 309 {
 310 _dosmaperr(GetLastError());
 311 return -1;
 312 }
 313 else
 314 return 0;
 315 }
 316 #endif




Regards
--
Arnaud





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade --link on Windows

[Arnaud L.]

Le 9/06/2017 à 16:07, Bruce Momjian a écrit :

I was told junction points on Windows were hard links and no one has
ever complained about not being able to remove them.


Sorry, I think my explanation was not very clear.
You can remove the link, but the point is to remove the target (i.e. the 
old-data-dir).
You can do this with a hard link (there still exists a hardlink pointing 
to the inode so it remains), but with a soft link you end up with a link 
to nothing.
Deleting a junction target in Windows will work, but you'll have an 
error trying to access the junction directory (directory not found).


See this page for more details :
http://cects.com/overview-to-understanding-hard-links-junction-points-and-symbolic-links-in-windows/

Under "Hard Link (Linking for individual files)" :
"If the target is deleted, its content is still available through the 
hard link"


Junction Point (Directory Hard Link):
"If the target is moved, renamed or deleted, the Junction Point still 
exists, but points to a non-existing directory"


BUT, when I try to "pg_upgrade --link --check" with old-data-dir and 
new-data-dir on different volumes, I get an error saying that both 
directories must be on the same volume if --link is used.
So maybe pg_upgrade uses hard-links (i.e. to files), and only the 
documentation is wrong by calling them junctions (i.e. soft links to 
files) ?


Regards
--
Arnaud


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade --link on Windows

[Adrian Klaver]

On 06/09/2017 07:07 AM, Bruce Momjian wrote:

On Fri, Jun  9, 2017 at 12:00:56PM +0200, Arnaud L. wrote:

Hi

The pg_upgrade documentation for PostgreSQL 9.6 states that --link will use
junction points on Windows.
Shouldn't it rather user hard-links ?
If I'm not mistaken, with junction points (i.e. soft-links to directories),
the old data dir cannot be removed.
With hard-links to file, we can get rid of the old data dir once we are sure
that the upgrade is fine.


I was told junction points on Windows were hard links and no one has
ever complained about not being able to remove them.



https://msdn.microsoft.com/en-us/library/windows/desktop/aa365006(v=vs.85).aspx

Seems to me the difference is hard links point to file, junctions to 
directories.


So if I am following:

https://en.wikipedia.org/wiki/NTFS_junction_point#Creating_or_deleting_a_junction_point

You remove the junction and then the directory it points to.

--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade --link on Windows

[Bruce Momjian]

On Fri, Jun  9, 2017 at 12:00:56PM +0200, Arnaud L. wrote:
> Hi
> 
> The pg_upgrade documentation for PostgreSQL 9.6 states that --link will use
> junction points on Windows.
> Shouldn't it rather user hard-links ?
> If I'm not mistaken, with junction points (i.e. soft-links to directories),
> the old data dir cannot be removed.
> With hard-links to file, we can get rid of the old data dir once we are sure
> that the upgrade is fine.

I was told junction points on Windows were hard links and no one has
ever complained about not being able to remove them.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_upgrade --link on Windows

[Arnaud L.]

Hi

The pg_upgrade documentation for PostgreSQL 9.6 states that --link will 
use junction points on Windows.

Shouldn't it rather user hard-links ?
If I'm not mistaken, with junction points (i.e. soft-links to 
directories), the old data dir cannot be removed.
With hard-links to file, we can get rid of the old data dir once we are 
sure that the upgrade is fine.


Regards

--
Arnaud


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade error on FreeBSD (9.1 -> 9.5)

[Amitabh Kant]

On Sat, Jan 21, 2017 at 9:53 PM, Tom Lane  wrote:

> Amitabh Kant  writes:
> > command: "/var/tmp/pgbin.SPOsRj4D/bin/pg_ctl" -w -l
> "pg_upgrade_server.log"
> > -D "/usr/local/pgsql/data91" -o "-p 50432 -b  -c listen_addresses='' -c
> > unix_socket_permissions=0700 -c unix_socket_directory='/usr/
> local/pgsql'"
>
> Note the unix_socket_directory parameter, which is indeed being applied
> because we can see it again in the ps output:
>
> > pgsql 26636   0.0  1.4 66960 14512  -  Is4:08AM   0:00.06
> > /var/tmp/pgbin.SPOsRj4D/bin/postgres -D /usr/local/pgsql/data91 -p
> 50432 -b
> > -c listen_addresses= -c unix_socket_permissions=0700 -c
> > unix_socket_directory=/usr/local/pgsql
>
> However, your psql is looking for the socket in /tmp:
>
> > $ psql -p 50432 -d template1
> > psql: could not connect to server: No such file or directory
> > Is the server running locally and accepting
> > connections on Unix domain socket "/tmp/.s.PGSQL.50432"?
>
> You could successfully connect to that server with
> "psql -p 50432 -h /usr/local/pgsql ...", I expect.
>
> The question is why pg_upgrade issued that option and then failed to
> cope with the consequences.  I suspect it has something to do with one
> installation being configured with different default socket directory
> than the other, but I don't have enough facts.
>
> regards, tom lane
>

Yes, it does connect  using Unix domain socket as you suggested. PG 9.5 is
the stock install as present on FreeBSD. I will have to check the script
that installs PG 9.1  in an alternate location for any changes from the
default.

regards

Amitabh

Re: [GENERAL] pg_upgrade error on FreeBSD (9.1 -> 9.5)

[Tom Lane]

Amitabh Kant  writes:
> command: "/var/tmp/pgbin.SPOsRj4D/bin/pg_ctl" -w -l "pg_upgrade_server.log"
> -D "/usr/local/pgsql/data91" -o "-p 50432 -b  -c listen_addresses='' -c
> unix_socket_permissions=0700 -c unix_socket_directory='/usr/local/pgsql'"

Note the unix_socket_directory parameter, which is indeed being applied
because we can see it again in the ps output:

> pgsql 26636   0.0  1.4 66960 14512  -  Is4:08AM   0:00.06
> /var/tmp/pgbin.SPOsRj4D/bin/postgres -D /usr/local/pgsql/data91 -p 50432 -b
> -c listen_addresses= -c unix_socket_permissions=0700 -c
> unix_socket_directory=/usr/local/pgsql

However, your psql is looking for the socket in /tmp:

> $ psql -p 50432 -d template1
> psql: could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket "/tmp/.s.PGSQL.50432"?

You could successfully connect to that server with
"psql -p 50432 -h /usr/local/pgsql ...", I expect.

The question is why pg_upgrade issued that option and then failed to
cope with the consequences.  I suspect it has something to do with one
installation being configured with different default socket directory
than the other, but I don't have enough facts.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_upgrade error on FreeBSD (9.1 -> 9.5)

[Amitabh Kant]

Hi

I am trying to upgrade an exisitng Postgresql cluster from  9.1 to 9.5 on
FreeBSD. pg_upgrade fails with the following error:
[Verbose Output: http://pastebin.com/YhR8vD03]
==
$ /usr/local/bin/pg_upgrade -k  -d /usr/local/pgsql/data91 -D
/usr/local/pgsql/data --old-bindir=/var/tmp/pgbin.SPOsRj4D/bin
--new-bindir=/usr/local/bin --check

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

pg_ctl failed to start the old server, or connection failed
Failure, exiting


$ cat pg_upgrade_server.log

-
  pg_upgrade run on Sat Jan 21 04:08:13 2017
-

command: "/var/tmp/pgbin.SPOsRj4D/bin/pg_ctl" -w -l "pg_upgrade_server.log"
-D "/usr/local/pgsql/data91" -o "-p 50432 -b  -c listen_addresses='' -c
unix_socket_permissions=0700 -c unix_socket_directory='/usr/local/pgsql'"
start >> "pg_upgrade_server.log" 2>&1
waiting for server to start stopped waiting
pg_ctl: could not start server
Examine the log output.
==

On checking the process list, the old postgres server seems to have
started, but I am unable to connect to it using psql.
==
$ ps auwwx |grep post
pgsql 26636   0.0  1.4 66960 14512  -  Is4:08AM   0:00.06
/var/tmp/pgbin.SPOsRj4D/bin/postgres -D /usr/local/pgsql/data91 -p 50432 -b
-c listen_addresses= -c unix_socket_permissions=0700 -c
unix_socket_directory=/usr/local/pgsql
pgsql 26638   0.0  1.4 66960 14516  -  Ss4:08AM   0:00.23 postgres:
writer process(postgres)
pgsql 26639   0.0  1.4 66960 14516  -  Ss4:08AM   0:00.15 postgres: wal
writer process(postgres)
pgsql 26640   0.0  0.9 26720  9568  -  Ss4:08AM   0:00.02 postgres:
stats collector process(postgres)

$ psql -p 50432 -d template1
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.50432"?
==


If I run the pg_ctl command directly with just the minimum parameters, it
still gives me an error, but seems to work correctly.
==
$ /var/tmp/pgbin.SPOsRj4D/bin/pg_ctl -o "-p 50432 " -w -l
"pg_upgrade_server.log" -D "/usr/local/pgsql/data91"  start
waiting for server to start stopped waiting
pg_ctl: could not start server
Examine the log output.

$ cat pg_upgrade_server.log

$ psql -p 50432 -d template1
psql (9.5.5, server 9.1.20)
Type "help" for help.

template1=#
==


Where am I going wrong?


Amitabh


Detailed steps that I followed for upgrade:

Since postgresql installed on FreeBSD through its ports/packages doesn't
have version specific binaries or data directory till  PG 9.5 (9.6 does
have version specific data directory), use of pg_upgrade requires a little
bit of work:

i) Stopped the current PG 9.1 instance. Renamed the data directory to
data91 (under /usr/local/pgsql).
ii) Installed PG 9.1 binaries in a different location
(/var/tmp/pgbin.SPOsRj4D/bin). Set owner and permission for pgsql
user(default superuser in FreeBSD)
iii) Removed PG 9.1 installation from server & installed PG 9.5 in it's
default location (under /usr/local/bin)
iv) Initiaized DB instance using initdb command (PG 9.5 data location is
/usr/local/pgsql/data)
v) Ran the following command for upgrade check:
/usr/local/bin/pg_upgrade -k  -d /usr/local/pgsql/data91 -D
/usr/local/pgsql/data --old-bindir=/var/tmp/pgbin.SPOsRj4D/bin
--new-bindir=/usr/local/bin --check --verbose

Output: http://pastebin.com/YhR8vD03


OS: FreeBSD 10.3 (running as virtualbox guest OS, host is Mac OS)

Re: [GENERAL] pg_upgrade 9.0 to 9.6

[Thomas Kellerer]

Mikhail schrieb am 13.12.2016 um 10:57:
> Should i check all the production environments for the problems,
> mentioned in all interim versions release notes, is it enough only to
> check the last minor upgrade release note (9.6 --> 9.6.1) or there is
> another quick way to check if i should apply some additional actions
> to standard pg_upgrade+stats_collect?

There is a nice page which consolidates all release notes:

   https://bucardo.org/postgres_all_versions.html

The nice thing about that is, that for each change in one release, it also 
mentions if that was a change in other releases too, e.g.: 

   https://bucardo.org/postgres_all_versions.html#version_9.6.1

Thomas






-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re[2]: [GENERAL] pg_upgrade 9.0 to 9.6

[Mikhail]

John, thanks! Your approach significantly reduces the number of checks.


>Вторник, 13 декабря 2016, 13:34 +03:00 от John R Pierce :
>
>On 12/13/2016 1:57 AM, Mikhail wrote:
>> Should i check all the production environments for the problems, 
>> mentioned in all interim versions release notes, is it enough only to 
>> check the last minor upgrade release note (9.6 --> 9.6.1) or there is 
>> another quick way to check if i should apply some additional actions 
>> to standard pg_upgrade+stats_collect?
>
>if you're upgrading directly from 9.0.x to 9.6.1, I would only check the 
>release notes for 9.0.latest (and upgrade to that, following those notes 
>and any 9.0.x release newer than your current which are referenced in 
>hte 9.0.latest notes), then do the pg_upgrade directly to 9.6.1
>
>now, there could be application compatibility issues going from 9.0 to 
>9.6, those would best be addressed by 9.1.0, 9.2.0, ... release notes as 
>9.6 will include all those functional changes.
>
>
>-- 
>john r pierce, recycling bits in santa cruz
>
>
>
>-- 
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.0 to 9.6

[John R Pierce]

On 12/13/2016 1:57 AM, Mikhail wrote:
Should i check all the production environments for the problems, 
mentioned in all interim versions release notes, is it enough only to 
check the last minor upgrade release note (9.6 --> 9.6.1) or there is 
another quick way to check if i should apply some additional actions 
to standard pg_upgrade+stats_collect?


if you're upgrading directly from 9.0.x to 9.6.1, I would only check the 
release notes for 9.0.latest (and upgrade to that, following those notes 
and any 9.0.x release newer than your current which are referenced in 
hte 9.0.latest notes), then do the pg_upgrade directly to 9.6.1


now, there could be application compatibility issues going from 9.0 to 
9.6, those would best be addressed by 9.1.0, 9.2.0, ... release notes as 
9.6 will include all those functional changes.



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_upgrade 9.0 to 9.6

[Mikhail]

Hi all,
We've tested upgrading from postgresql 9.0.x to 9.6.1 on the test environment 
and everything passed fluently. But there are concerns about the production 
environments upgrade because of notes to some interim updates that recommends 
reindex and/or vacuuming to exclude data corruption, index results and etc.
Some examples:
9.6.0 to 9.6.1  https://www.postgresql.org/docs/9.6/static/release-9-6-1.html
  if your installation has been affected by the bugs described in the first two 
changelog entries below, then after updating you may need to take action to 
repair corrupted free space maps and/or visibility maps.
9.5.1 to 9.5.2  https://www.postgresql.org/docs/9.6/static/release-9-5-2.html  
  you may need to REINDEX some indexes after applying the update, as per the 
first changelog entry below
9.4.5 to 9.4.6  https://www.postgresql.org/docs/9.6/static/release-9-4-6.html  
  if you are upgrading an installation that contains any GIN indexes that use 
the (non-default) jsonb_path_ops operator class, see the first changelog entry 
below.
9.4.1 to 9.4.2  https://www.postgresql.org/docs/9.6/static/release-9-4-2.html  
  if you use contrib/citext's regexp_matches() functions, see the changelog 
entry below about that
9.3.4 to 9.3.5  https://www.postgresql.org/docs/9.6/static/release-9-3-5.html  
  this release corrects a logic error in pg_upgrade, as well as an index 
corruption problem in some GiST indexes. See the first two changelog entries 
below to find out whether your installation has been affected and what steps 
you should take if so
... and so on.

Should i check all the production environments for the problems, mentioned in 
all interim versions release notes, is it enough only to check the last minor 
upgrade release note (9.6 --> 9.6.1) or there is another quick way to check if 
i should apply some additional actions to standard pg_upgrade+stats_collect?

Regards, Mikhail Balayan

Re: [GENERAL] pg_upgrade not able to cope with pg_largeobject being in a different tablespace

[Bruce Momjian]

On Thu, Oct 13, 2016 at 04:35:35PM +0200, Andreas Joseph Krogh wrote:
> På torsdag 13. oktober 2016 kl. 16:09:34, skrev Bruce Momjian 
>  >:
> 
> On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote:
> > I would assume that having pg_largeobject in a separate tablespace is
> more and
> > more common these days, having real-cheap SAN vs. fast-SSD for normal
> tables/
> > indexes/wal.
> 
> So common that no one has ever asked for this feature before?
> 
> Sometimes one gets the feeling that one is the only one in the universe doing
> something one considers "quite common":-)

Yes, I often feel the same way.  :-)  Like, why am I the only person who
thinks this is a natural thing to do.  I find a lot of bugs that way.  :-)

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade not able to cope with pg_largeobject being in a different tablespace

[Andreas Joseph Krogh]

På tirsdag 18. oktober 2016 kl. 12:39:03, skrev Magnus Hagander <
mag...@hagander.net >:
    On Thu, Oct 13, 2016 at 7:35 AM, Andreas Joseph Krogh > wrote: På torsdag 13. oktober 2016 kl. 16:09:34, 
skrev Bruce Momjian >:
On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote:
 > I would assume that having pg_largeobject in a separate tablespace is more 
and
 > more common these days, having real-cheap SAN vs. fast-SSD for normal 
tables/
 > indexes/wal.

 So common that no one has ever asked for this feature before?
 
 
Sometimes one gets the feeling that one is the only one in the universe doing 
something one considers "quite common":-)
 
> So - I'm wondering if we can fund development of pg_upgrade to cope with this
 > configuration or somehow motivate to getting this issue fixed?
 >  
 > Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this?
 >  
 > Any feedback welcome, thanks.

 You would need to get buy-in that that community wants the relocation of
 pg_largeobject to be supported via an SQL command, and at that point
 pg_upgrade would be modified to support that.  It is unlikely pg_upgrade
 is going to be modified to support something that isn't supported at the
 SQL level.  Of course, you can create a custom version of pg_upgrade to
 do that.
 
Doesn't "ALTER TABLE pg_largeobject SET TABLESPACE myspace_lo" count as being 
"at the SQL-level"?
 
Well, it requires that you set allow_system_table_mods on, which is documented 
as a developer option. It's documented with things like "The following 
parameters are intended for work on the PostgreSQL source code, and in some 
cases to assist with recovery of severely damaged databases. There should be no 
reason to use them on a production database.".

 Perhaps we should add another disclaimer there saying that if you make 
changes in this mode, tools like pg_upgrade (or for that matter, pg_dump or 
pretty much anything at all) may not work anymore?
 
 
The whole problem seems to come from the fact that BLOBs are stored in 
pg_largeobject which for some reason is implemented as a system-catalogue in 
PG, which imposes all kinds of weird problems, from a DBA-perspective.
 
Yes, there are several issues related to how lo style large objects work. I've 
often gone to similar implementations but in userspace on top of custom tables 
to work around those.
 
 
Can we pay you at EDB for making such a custom version of pg_upgrade for 9.6?
 
 
You're assuming pg_upgrade is the only potential problem. If you are willing 
to spend towards it, it would probably be better to spend towards the "upper 
layer" problem which would be to make it possible to move pg_largeobject to a 
different tablespace *without* turning on system_table_mods.
 
That said, I cannot comment to the complexity of either doing that *or* doing 
a custom pg_upgrade that would support it. But solving a long-term problem 
seems better than solving a one-off one.



 
I totally agree that investing in a long-term solution is the best. However, I 
need (would like very much) to upgrade a 9.5 cluster to 9.6 and would rather 
not wait for a solution to land in 10.x.
 
IIRC there was a discussion on -hackers not too long ago about pg_largeobject 
and releasing it from being a "system catalogue", but i think it stranded and 
got nowhere.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

 

Re: [GENERAL] pg_upgrade not able to cope with pg_largeobject being in a different tablespace

[Magnus Hagander]

On Thu, Oct 13, 2016 at 7:35 AM, Andreas Joseph Krogh 
wrote:

> På torsdag 13. oktober 2016 kl. 16:09:34, skrev Bruce Momjian <
> br...@momjian.us>:
>
> On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote:
> > I would assume that having pg_largeobject in a separate tablespace is
> more and
> > more common these days, having real-cheap SAN vs. fast-SSD for normal
> tables/
> > indexes/wal.
>
> So common that no one has ever asked for this feature before?
>
>
>
> Sometimes one gets the feeling that one is the only one in the universe
> doing something one considers "quite common":-)
>
>
> > So - I'm wondering if we can fund development of pg_upgrade to cope with
> this
> > configuration or somehow motivate to getting this issue fixed?
> >
> > Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this?
> >
> > Any feedback welcome, thanks.
>
> You would need to get buy-in that that community wants the relocation of
> pg_largeobject to be supported via an SQL command, and at that point
> pg_upgrade would be modified to support that.  It is unlikely pg_upgrade
> is going to be modified to support something that isn't supported at the
> SQL level.  Of course, you can create a custom version of pg_upgrade to
> do that.
>
>
> Doesn't "ALTER TABLE pg_largeobject SET TABLESPACE myspace_lo" count as
> being "at the SQL-level"?
>

Well, it requires that you set allow_system_table_mods on, which is
documented as a developer option. It's documented with things like "The
following parameters are intended for work on the PostgreSQL source code,
and in some cases to assist with recovery of severely damaged databases. There
should be no reason to use them on a production database.".

Perhaps we should add another disclaimer there saying that if you make
changes in this mode, tools like pg_upgrade (or for that matter, pg_dump or
pretty much anything at all) may not work anymore?



> The whole problem seems to come from the fact that BLOBs are stored in
> pg_largeobject which for some reason is implemented as a system-catalogue
> in PG, which imposes all kinds of weird problems, from a DBA-perspective.
>

Yes, there are several issues related to how lo style large objects work.
I've often gone to similar implementations but in userspace on top of
custom tables to work around those.



> Can we pay you at EDB for making such a custom version of pg_upgrade for
> 9.6?
>
>
You're assuming pg_upgrade is the only potential problem. If you are
willing to spend towards it, it would probably be better to spend towards
the "upper layer" problem which would be to make it possible to move
pg_largeobject to a different tablespace *without* turning on
system_table_mods.

That said, I cannot comment to the complexity of either doing that *or*
doing a custom pg_upgrade that would support it. But solving a long-term
problem seems better than solving a one-off one.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] pg_upgrade not able to cope with pg_largeobject being in a different tablespace

[Andreas Joseph Krogh]

På torsdag 13. oktober 2016 kl. 16:09:34, skrev Bruce Momjian >:
On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote:
 > I would assume that having pg_largeobject in a separate tablespace is more 
and
 > more common these days, having real-cheap SAN vs. fast-SSD for normal 
tables/
 > indexes/wal.

 So common that no one has ever asked for this feature before?
 
 
Sometimes one gets the feeling that one is the only one in the universe doing 
something one considers "quite common":-)
 
> So - I'm wondering if we can fund development of pg_upgrade to cope with this
 > configuration or somehow motivate to getting this issue fixed?
 >  
 > Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this?
 >  
 > Any feedback welcome, thanks.

 You would need to get buy-in that that community wants the relocation of
 pg_largeobject to be supported via an SQL command, and at that point
 pg_upgrade would be modified to support that.  It is unlikely pg_upgrade
 is going to be modified to support something that isn't supported at the
 SQL level.  Of course, you can create a custom version of pg_upgrade to
 do that.
 
Doesn't "ALTER TABLE pg_largeobject SET TABLESPACE myspace_lo" count as being 
"at the SQL-level"?
 
The whole problem seems to come from the fact that BLOBs are stored in 
pg_largeobject which for some reason is implemented as a system-catalogue in 
PG, which imposes all kinds of weird problems, from a DBA-perspective.
 
Can we pay you at EDB for making such a custom version of pg_upgrade for 9.6?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] pg_upgrade not able to cope with pg_largeobject being in a different tablespace

[Bruce Momjian]

On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote:
> I would assume that having pg_largeobject in a separate tablespace is more and
> more common these days, having real-cheap SAN vs. fast-SSD for normal tables/
> indexes/wal.

So common that no one has ever asked for this feature before?

> So - I'm wondering if we can fund development of pg_upgrade to cope with this
> configuration or somehow motivate to getting this issue fixed?
>  
> Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this?
>  
> Any feedback welcome, thanks.

You would need to get buy-in that that community wants the relocation of
pg_largeobject to be supported via an SQL command, and at that point
pg_upgrade would be modified to support that.  It is unlikely pg_upgrade
is going to be modified to support something that isn't supported at the
SQL level.  Of course, you can create a custom version of pg_upgrade to
do that.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_upgrade not able to cope with pg_largeobject being in a different tablespace

[Andreas Joseph Krogh]

Hi all.
 
(I tried raising this on -hackers but got "this is not supported"-answer, 
which is quite dissatisfactory..)
 
I have an issue with pg_upgrade upgrading 9.5 to 9.6. (my system is 
Ubuntu-16.04 and packages from http://apt.postgresql.org/) 

 
In short pg_upgrade fails with:
 
Linking user relation files
 No match found in new cluster for old relation with OID 16388 in database 
"andreak": "pg_toast.pg_toast_2613" which is the TOAST table for 
"pg_catalog.pg_largeobject"
 No match found in new cluster for old relation with OID 16390 in database 
"andreak": "pg_toast.pg_toast_2613_index" which is an index on 
"pg_toast.pg_toast_2613" which is the TOAST table for 
"pg_catalog.pg_largeobject"
Failed to match up old and new tables in database "andreak"
 Failure, exiting
 
I issued the following command:
PG_NEW_VERSION=9.6
 PG_OLD_VERSION=9.5
 /usr/lib/postgresql/$PG_NEW_VERSION/bin/pg_upgrade \
           --old-bindir=/usr/lib/postgresql/$PG_OLD_VERSION/bin/ \
           --new-bindir=/usr/lib/postgresql/$PG_NEW_VERSION/bin/ \
           --old-datadir=/var/lib/postgresql/$PG_OLD_VERSION/main \
           --new-datadir=/var/lib/postgresql/$PG_NEW_VERSION/main \
           -o " -c 
config_file=/etc/postgresql/$PG_OLD_VERSION/main/postgresql.conf" \
           -O " -c 
config_file=/etc/postgresql/$PG_NEW_VERSION/main/postgresql.conf" \
           --link

 
 
My database and tablespaces are created like this:
 I have tablespaces configured outside PGDATA:
mkdir /var/lib/postgresql/9.5/tablespaces/andreak
 mkdir /var/lib/postgresql/9.5/tablespaces_lo/andreak
psql -c "create tablespace andreak OWNER andreak location 
'/var/lib/postgresql/9.5/tablespaces/andreak'" postgres;
 psql -c "create tablespace andreak_lo OWNER andreak location 
'/var/lib/postgresql/9.5/tablespaces_lo/andreak'" postgres;
 createdb --tablespace=andreak -O andreak andreak


psql -U postgres -c "alter table pg_largeobject set tablespace andreak_lo" -d 
andreak


(I've set allow_system_table_mods=on in postgresql.conf)
 
These are symlinked:
ln -s /storage/wal/9.5/pg_xlog /var/lib/postgresql/9.5/main/pg_xlog
 ln -s /storage/fast_ssd/9.5/tablespaces /var/lib/postgresql/9.5/tablespaces
 ln -s /storage/archive_disk/9.5/tablespaces_lo 
/var/lib/postgresql/9.5/tablespaces_lo

 
I would assume that having pg_largeobject in a separate tablespace is more and 
more common these days, having real-cheap SAN vs. fast-SSD for normal 
tables/indexes/wal.
 
So - I'm wondering if we can fund development of pg_upgrade to cope with this 
configuration or somehow motivate to getting this issue fixed?
 
Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this?
 
Any feedback welcome, thanks.
 

-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

[Masahiko Sawada]

On Fri, Sep 30, 2016 at 6:40 PM, Thomas Kellerer  wrote:
> Tom Lane schrieb am 29.09.2016 um 23:10:
>> Thomas Kellerer  writes:
>>> for some reason pg_upgrade failed on Windows 10 for me, with an error 
>>> message that one specifc _vm file couldn't be copied.
>>
>> Hmm ... a _vm file would go through rewriteVisibilityMap(), which is new
>> code for 9.6 and hasn't really gotten that much testing.  Its error
>> reporting is shamefully bad --- you can't tell which step failed, and
>> I wouldn't even put a lot of faith in the errno being meaningful,
>> considering that it does close() calls before capturing the errno.
>>
>> But what gets my attention in this connection is that it doesn't
>> seem to be taking the trouble to open the files in binary mode.
>> Could that lead to the reported failure?  Not sure, but it seems
>> like at the least it could result in corrupted VM files.
>
> I did this on two different computers, one with Windows 10 the other with 
> Windows 7.
> (only test-databases, so no real issue anyway)
>
> In both cases running a "vacuum full" for the table in question fixed the 
> problem and pg_upgrade finished without problems.

Because vacuum full removes the _vm file, pg_upgrade completed job successfully.
If you still have the _vm file
("d:/Daten/db/pgdata95/base/16410/85358_vm") that lead an error, is it
possible that you check if there is '\r\n' [0d 0a] character in that
_vm file or share that _vm file with us?

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

[Thomas Kellerer]

Tom Lane schrieb am 29.09.2016 um 23:10:
> Thomas Kellerer  writes:
>> for some reason pg_upgrade failed on Windows 10 for me, with an error 
>> message that one specifc _vm file couldn't be copied.
> 
> Hmm ... a _vm file would go through rewriteVisibilityMap(), which is new
> code for 9.6 and hasn't really gotten that much testing.  Its error
> reporting is shamefully bad --- you can't tell which step failed, and
> I wouldn't even put a lot of faith in the errno being meaningful,
> considering that it does close() calls before capturing the errno.
> 
> But what gets my attention in this connection is that it doesn't
> seem to be taking the trouble to open the files in binary mode.
> Could that lead to the reported failure?  Not sure, but it seems
> like at the least it could result in corrupted VM files.

I did this on two different computers, one with Windows 10 the other with 
Windows 7. 
(only test-databases, so no real issue anyway)

In both cases running a "vacuum full" for the table in question fixed the 
problem and pg_upgrade finished without problems.




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

[Tom Lane]

Alvaro Herrera  writes:
> Moreover I think getErrorText() as a whole is misconceived and should be
> removed altogether (why pstrdup the string?).

Indeed.  I think bouncing the error back to the caller is misguided
to start with, seeing that the caller is just going to do pg_fatal
anyway.  We should rewrite these functions to just error out internally,
which will make it much easier to provide decent error reporting
indicating which call failed.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

[Alvaro Herrera]

Tom Lane wrote:
> Thomas Kellerer  writes:
> > for some reason pg_upgrade failed on Windows 10 for me, with an error 
> > message that one specifc _vm file couldn't be copied.
> 
> Hmm ... a _vm file would go through rewriteVisibilityMap(), which is new
> code for 9.6 and hasn't really gotten that much testing.  Its error
> reporting is shamefully bad --- you can't tell which step failed, and
> I wouldn't even put a lot of faith in the errno being meaningful,
> considering that it does close() calls before capturing the errno.

So we do close() in a bunch of places while closing shop, which calls
_close() on Windows; this function sets errno.  Then we call
getErrorText(), which calls _dosmaperr() on the result of
GetLastError().  But the last-error stuff is not set by _close; I suppose
GetLastError() returns 0 in that case, which promps _doserrmap to set errno to 
0.
http://stackoverflow.com/questions/20056851/getlasterror-errno-formatmessagea-and-strerror-s
So this wouldn't quite have the effect you say; I think it'd say
"Failure while copying ...: Success" instead.

However surely we should have errno save/restore.

Other than that, I think the _dosmaperr() call should go entirely.
Moreover I think getErrorText() as a whole is misconceived and should be
removed altogether (why pstrdup the string?).  There are very few places
in pg_upgrade that require _dosmaperr; I can see only copyFile and
linkFile.  All the others should just be doing strerror() only, at least
according to the manual.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

[Tom Lane]

Thomas Kellerer  writes:
> for some reason pg_upgrade failed on Windows 10 for me, with an error message 
> that one specifc _vm file couldn't be copied.

Hmm ... a _vm file would go through rewriteVisibilityMap(), which is new
code for 9.6 and hasn't really gotten that much testing.  Its error
reporting is shamefully bad --- you can't tell which step failed, and
I wouldn't even put a lot of faith in the errno being meaningful,
considering that it does close() calls before capturing the errno.

But what gets my attention in this connection is that it doesn't
seem to be taking the trouble to open the files in binary mode.
Could that lead to the reported failure?  Not sure, but it seems
like at the least it could result in corrupted VM files.

Has anyone tested vismap upgrades on Windows, and made an effort
to validate that the output wasn't garbage?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

[Thomas Kellerer]

Adrian Klaver schrieb am 29.09.2016 um 22:55:

After running a "vacuum full" on the table in question the upgrade goes
through.


Assuming you did that on old cluster?


Yes, correct. I did that on the 9.5 cluster


Where both clusters installed the same way?


Yes.

I always download the ZIP Archive from 
http://www.enterprisedb.com/products-services-training/pgbindownload then run 
initdb manually.

Both were initialized using:

   initdb -D "..."  --lc-messages=English -U postgres --pwfile=pwfile.txt -E 
UTF8 -A md5


What was the complete command line invocation of pg_upgrade?


That was in a batch file:

set LC_MESSAGES=English

set oldbin=c:\Programme\PostgreSQL\9.5\bin
set newbin=c:\Programme\PostgreSQL\9.6\bin
"%newbin%\pg_upgrade" ^
  --username=postgres ^
  --old-bindir="%oldbin%" ^
  --new-bindir="%newbin%" ^
  --old-datadir=d:/Daten/db/pgdata95 ^
  --new-datadir=d:/Daten/db/pgdata96 ^
  --retain ^
  --verbose ^
  --old-port=5432 ^
  --new-port=5433

 





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

[Adrian Klaver]

On 09/29/2016 12:50 PM, Thomas Kellerer wrote:

Hello,

for some reason pg_upgrade failed on Windows 10 for me, with an error
message that one specifc _vm file couldn't be copied.

When I try to copy that file manually everything works fine.

After running a "vacuum full" on the table in question the upgrade goes
through.


Assuming you did that on old cluster?

Upgrading to 9.6?

Where both clusters installed the same way?



One thing I noticed in the --verbose output of pg_upgrade is that the
old cluster - despite being a 9.5 one - has the "pg_control version
number 942"


Which is correct:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/pg_control.h;h=0b8bea74a891831bc3cbe8fd4d4233475a8329c4;hb=ba37ac217791dfdf2b327c4b75e7083b6b03a2f5


What was the complete command line invocation of pg_upgrade?



Here is the part of the pg_upgrade output:

.

copying "d:/Daten/db/pgdata95/base/16410/85351" to
"d:/Daten/db/pgdata96/base/16411/85351"
  d:/Daten/db/pgdata95/base/16410/85351_fsm
copying "d:/Daten/db/pgdata95/base/16410/85351_fsm" to
"d:/Daten/db/pgdata96/base/16411/85351_fsm"
  d:/Daten/db/pgdata95/base/16410/85351_vm
copying "d:/Daten/db/pgdata95/base/16410/85351_vm" to
"d:/Daten/db/pgdata96/base/16411/85351_vm"
  d:/Daten/db/pgdata95/base/16410/85358
copying "d:/Daten/db/pgdata95/base/16410/85358" to
"d:/Daten/db/pgdata96/base/16411/85358"
  d:/Daten/db/pgdata95/base/16410/85358.1
copying "d:/Daten/db/pgdata95/base/16410/85358.1" to
"d:/Daten/db/pgdata96/base/16411/85358.1"
  d:/Daten/db/pgdata95/base/16410/85358.2
copying "d:/Daten/db/pgdata95/base/16410/85358.2" to
"d:/Daten/db/pgdata96/base/16411/85358.2"
  d:/Daten/db/pgdata95/base/16410/85358.3
copying "d:/Daten/db/pgdata95/base/16410/85358.3" to
"d:/Daten/db/pgdata96/base/16411/85358.3"
  d:/Daten/db/pgdata95/base/16410/85358_fsm
copying "d:/Daten/db/pgdata95/base/16410/85358_fsm" to
"d:/Daten/db/pgdata96/base/16411/85358_fsm"
  d:/Daten/db/pgdata95/base/16410/85358_vm
copying "d:/Daten/db/pgdata95/base/16410/85358_vm" to
"d:/Daten/db/pgdata96/base/16411/85358_vm"

error while copying relation "public.wb_downloads"
("d:/Daten/db/pgdata95/base/16410/85358_vm" to
"d:/Daten/db/pgdata96/base/16411/85358_vm"): Invalid argument
Failure, exiting

The file in question is 65.536 bytes in size.

I saved all log files and the complete output from the failed run, so if
you are interested I can supply them (I ran pg_upgrade with the --retain
option).

Regards
Thomas







--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

[Thomas Kellerer]

Hello,

for some reason pg_upgrade failed on Windows 10 for me, with an error message 
that one specifc _vm file couldn't be copied.

When I try to copy that file manually everything works fine.

After running a "vacuum full" on the table in question the upgrade goes through.

One thing I noticed in the --verbose output of pg_upgrade is that the old cluster - 
despite being a 9.5 one - has the "pg_control version number 942"

Here is the part of the pg_upgrade output:

.

copying "d:/Daten/db/pgdata95/base/16410/85351" to 
"d:/Daten/db/pgdata96/base/16411/85351"
  d:/Daten/db/pgdata95/base/16410/85351_fsm
copying "d:/Daten/db/pgdata95/base/16410/85351_fsm" to 
"d:/Daten/db/pgdata96/base/16411/85351_fsm"
  d:/Daten/db/pgdata95/base/16410/85351_vm
copying "d:/Daten/db/pgdata95/base/16410/85351_vm" to 
"d:/Daten/db/pgdata96/base/16411/85351_vm"
  d:/Daten/db/pgdata95/base/16410/85358
copying "d:/Daten/db/pgdata95/base/16410/85358" to 
"d:/Daten/db/pgdata96/base/16411/85358"
  d:/Daten/db/pgdata95/base/16410/85358.1
copying "d:/Daten/db/pgdata95/base/16410/85358.1" to 
"d:/Daten/db/pgdata96/base/16411/85358.1"
  d:/Daten/db/pgdata95/base/16410/85358.2
copying "d:/Daten/db/pgdata95/base/16410/85358.2" to 
"d:/Daten/db/pgdata96/base/16411/85358.2"
  d:/Daten/db/pgdata95/base/16410/85358.3
copying "d:/Daten/db/pgdata95/base/16410/85358.3" to 
"d:/Daten/db/pgdata96/base/16411/85358.3"
  d:/Daten/db/pgdata95/base/16410/85358_fsm
copying "d:/Daten/db/pgdata95/base/16410/85358_fsm" to 
"d:/Daten/db/pgdata96/base/16411/85358_fsm"
  d:/Daten/db/pgdata95/base/16410/85358_vm
copying "d:/Daten/db/pgdata95/base/16410/85358_vm" to 
"d:/Daten/db/pgdata96/base/16411/85358_vm"

error while copying relation "public.wb_downloads" 
("d:/Daten/db/pgdata95/base/16410/85358_vm" to 
"d:/Daten/db/pgdata96/base/16411/85358_vm"): Invalid argument
Failure, exiting

The file in question is 65.536 bytes in size.

I saved all log files and the complete output from the failed run, so if you 
are interested I can supply them (I ran pg_upgrade with the --retain option).

Regards
Thomas
 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade error regarding hstore operator

[Tom Lane]

parihaaraka  writes:
> I have the same issue after pg_upgrade from 9.3 to 9.5.
> pg_dump generates excess commands like
>   CREATE OPERATOR FAMILY bit_ops USING gin;
>   ...
> while all of this is done during CREATE EXTENSION

This is fixed in the latest round of minor releases, but not in a way that
will help you if you already pg_upgrade'd such an extension:

http://git.postgresql.org/gitweb/?p=postgresql.git=commitdiff=6cead413b

Per the comments there, you could manually add that operator family back
to its extension, or just ignore the duplicate-opfamily complaints you'd
get during a restore.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade error regarding hstore operator

[parihaaraka]

Hello, All
I have the same issue after pg_upgrade from 9.3 to 9.5.
pg_dump generates excess commands like
  CREATE OPERATOR FAMILY bit_ops USING gin;
  ...
while all of this is done during CREATE EXTENSION
(i have only btree_gin and plpgsql installed)



--
View this message in context: 
http://postgresql.nabble.com/pg-upgrade-error-regarding-hstore-operator-tp5891425p5904776.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade with an extension name change

[Bruce Momjian]

On Fri, Apr 8, 2016 at 12:15:27PM -0700, Christophe Pettus wrote:
> I'm attempting to upgrade a database from 9.2 to 9.5 using pg_upgrade.
> The 9.2 database has the "orafunc" extension installed, which appears
> to have changed names to "orafce". pg_upgrade complains that it can't
> find "orafunc" on 9.5, which is true.  Is there a standard way of
> handling this situation?

Uh, I would uninstall the extension on the old cluster and reinstall it
on the new one.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade error regarding hstore operator

[Feld, Michael (IMS)]

)
 function hstore(text[],text[])
 function hstore_cmp(hstore,hstore)
 function hstore_eq(hstore,hstore)
 function hstore_ge(hstore,hstore)
 function hstore_gt(hstore,hstore)
 function hstore_hash(hstore)
 function hstore_in(cstring)
 function hstore_le(hstore,hstore)
 function hstore_lt(hstore,hstore)
 function hstore_ne(hstore,hstore)
 function hstore_out(hstore)
 function hstore_recv(internal)
 function hstore_send(hstore)
 function hstore_to_array(hstore)
 function hstore_to_matrix(hstore)
 function hstore_version_diag(hstore)
 function isdefined(hstore,text)
 function isexists(hstore,text)
 function populate_record(anyelement,hstore)
 function skeys(hstore)
 function slice(hstore,text[])
 function slice_array(hstore,text[])
 function svals(hstore)
 function tconvert(text,text)
 operator #<#(hstore,hstore)
 operator #<=#(hstore,hstore)
 operator #=(anyelement,hstore)
 operator #>#(hstore,hstore)
 operator #>=#(hstore,hstore)
 operator %#(NONE,hstore)
 operator %%(NONE,hstore)
 operator -(hstore,hstore)
 operator -(hstore,text)
 operator -(hstore,text[])
 operator ->(hstore,text)
 operator ->(hstore,text[])
 operator <>(hstore,hstore)
 operator <@(hstore,hstore)
 operator =(hstore,hstore)
 operator =>(text,text)
 operator ?&(hstore,text[])
 operator ?(hstore,text)
 operator ?|(hstore,text[])
 operator @(hstore,hstore)
 operator @>(hstore,hstore)
 operator class btree_hstore_ops for access method btree
 operator class gin_hstore_ops for access method gin
 operator class gist_hstore_ops for access method gist
 operator class hash_hstore_ops for access method hash
 operator family btree_hstore_ops for access method btree
 operator family gin_hstore_ops for access method gin
 operator family gist_hstore_ops for access method gist
 operator family hash_hstore_ops for access method hash
 operator ||(hstore,hstore)
 operator ~(hstore,hstore)
 type ghstore
 type hstore

9.5:
cast from text[] to hstore
 function akeys(hstore)
 function avals(hstore)
 function defined(hstore,text)
 function delete(hstore,hstore)
 function delete(hstore,text)
 function delete(hstore,text[])
 function each(hstore)
 function exist(hstore,text)
 function exists_all(hstore,text[])
 function exists_any(hstore,text[])
 function fetchval(hstore,text)
 function ghstore_compress(internal)
 function ghstore_consistent(internal,internal,integer,oid,internal)
 function ghstore_decompress(internal)
 function ghstore_in(cstring)
 function ghstore_out(ghstore)
 function ghstore_penalty(internal,internal,internal)
 function ghstore_picksplit(internal,internal)
 function ghstore_same(internal,internal,internal)
 function ghstore_union(internal,internal)
 function 
gin_consistent_hstore(internal,smallint,internal,integer,internal,internal)
 function gin_extract_hstore(internal,internal)
 function gin_extract_hstore_query(internal,internal,smallint,internal,internal)
 function hs_concat(hstore,hstore)
 function hs_contained(hstore,hstore)
 function hs_contains(hstore,hstore)
 function hstore(record)
 function hstore(text,text)
 function hstore(text[])
 function hstore(text[],text[])
 function hstore_cmp(hstore,hstore)
 function hstore_eq(hstore,hstore)
 function hstore_ge(hstore,hstore)
 function hstore_gt(hstore,hstore)
 function hstore_hash(hstore)
 function hstore_in(cstring)
 function hstore_le(hstore,hstore)
 function hstore_lt(hstore,hstore)
 function hstore_ne(hstore,hstore)
 function hstore_out(hstore)
 function hstore_recv(internal)
 function hstore_send(hstore)
 function hstore_to_array(hstore)
 function hstore_to_matrix(hstore)
 function hstore_version_diag(hstore)
 function isdefined(hstore,text)
 function isexists(hstore,text)
 function populate_record(anyelement,hstore)
 function skeys(hstore)
 function slice(hstore,text[])
 function slice_array(hstore,text[])
 function svals(hstore)
 function tconvert(text,text)
 operator #<#(hstore,hstore)
 operator #<=#(hstore,hstore)
 operator #=(anyelement,hstore)
 operator #>#(hstore,hstore)
 operator #>=#(hstore,hstore)
 operator %#(NONE,hstore)
 operator %%(NONE,hstore)
 operator -(hstore,hstore)
 operator -(hstore,text)
 operator -(hstore,text[])
 operator ->(hstore,text)
 operator ->(hstore,text[])
 operator <>(hstore,hstore)
 operator <@(hstore,hstore)
 operator =(hstore,hstore)
 operator ?&(hstore,text[])
 operator ?(hstore,text)
 operator ?|(hstore,text[])
 operator @(hstore,hstore)
 operator @>(hstore,hstore)
 operator class btree_hstore_ops for access method btree
 operator class gin_hstore_ops for access method gin
 operator class gist_hstore_ops for access method gist
 operator class hash_hstore_ops for access method hash
 operator ||(hstore,hstore)
 operator ~(hstore,hstore)
 type ghstore
 type hstore
(82 rows)

Thanks again for you time and assistance,
Mike

-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Tuesday, April 12, 2016 5:40 PM
To: Feld, Michael (IMS) <fe...@imsweb.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_upgrade error regarding hstore operator

"Feld, Michael (IMS)" <fe...@imsweb.com> writes:
> Thanks for the reply Tom. template1 is definitely empty and does not contain 
> any hstore objects. I did a little debugging and placed the below SQL before 
> and after the hstore creation in the file produced by the pg_dump and 
> determined that these operator objects only become present immediately after 
> the creation of the hstore extension, and not before. Then, much later down 
> the pg_dump file, it attempts to create the operator family for these 4 items 
> producing the errors. I did a pg_dump of the same database on a 9.1 instance 
> and it does not produce the operator creation objects SQL outside of the 
> extension. This seems to be something that happened as part of the 
> pg_upgrade. Any idea why these have showed up outside the extension? Is there 
> anything I can do to fix this? Thanks for all of your help.

I got some time today to try to reproduce this problem, and failed.
What I did was:
* install hstore 1.0 extension in a 9.1 database
* upgrade the extension to 1.1 (as per instructions upthread)
* pg_upgrade the database to HEAD
* pg_dump the database

The pg_dump output shows the expected CREATE EXTENSION command and no other 
extraneous objects.  So the procedure is fine.  I have to conclude there was 
something weird about the initial state of your
9.1 database.  I have too little info to say what exactly.

> SELECT am.amname AS index_method,
>opf.opfname AS opfamily_name,
>amop.amopopr::regoperator AS opfamily_operator
> FROM pg_am am, pg_opfamily opf, pg_amop amop
> WHERE opf.opfmethod = am.oid AND
>   amop.amopfamily = opf.oid and opf.opfname like '%hstore%'
> ORDER BY index_method, opfamily_name, opfamily_operator;

Uh, what did you get from that query?  Might also be useful to see

select * from pg_opclass where opcintype = 'hstore'::regtype;

and the output of "\dx+ hstore" in psql.

regards, tom lane



Information in this e-mail may be confidential. It is intended only for the 
addressee(s) identified above. If you are not the addressee(s), or an employee 
or agent of the addressee(s), please note that any dissemination, distribution, 
or copying of this communication is strictly prohibited. If you have received 
this e-mail in error, please notify the sender of the error.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade error regarding hstore operator

[Tom Lane]

"Feld, Michael (IMS)"  writes:
> Thanks for the reply Tom. template1 is definitely empty and does not contain 
> any hstore objects. I did a little debugging and placed the below SQL before 
> and after the hstore creation in the file produced by the pg_dump and 
> determined that these operator objects only become present immediately after 
> the creation of the hstore extension, and not before. Then, much later down 
> the pg_dump file, it attempts to create the operator family for these 4 items 
> producing the errors. I did a pg_dump of the same database on a 9.1 instance 
> and it does not produce the operator creation objects SQL outside of the 
> extension. This seems to be something that happened as part of the 
> pg_upgrade. Any idea why these have showed up outside the extension? Is there 
> anything I can do to fix this? Thanks for all of your help.

I got some time today to try to reproduce this problem, and failed.
What I did was:
* install hstore 1.0 extension in a 9.1 database
* upgrade the extension to 1.1 (as per instructions upthread)
* pg_upgrade the database to HEAD
* pg_dump the database

The pg_dump output shows the expected CREATE EXTENSION command and
no other extraneous objects.  So the procedure is fine.  I have to
conclude there was something weird about the initial state of your
9.1 database.  I have too little info to say what exactly.

> SELECT am.amname AS index_method,
>opf.opfname AS opfamily_name,
>amop.amopopr::regoperator AS opfamily_operator
> FROM pg_am am, pg_opfamily opf, pg_amop amop
> WHERE opf.opfmethod = am.oid AND
>   amop.amopfamily = opf.oid and opf.opfname like '%hstore%'
> ORDER BY index_method, opfamily_name, opfamily_operator;

Uh, what did you get from that query?  Might also be useful to see

select * from pg_opclass where opcintype = 'hstore'::regtype;

and the output of "\dx+ hstore" in psql.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_upgrade with an extension name change

[Christophe Pettus]

I'm attempting to upgrade a database from 9.2 to 9.5 using pg_upgrade.  The 9.2 
database has the "orafunc" extension installed, which appears to have changed 
names to "orafce".  pg_upgrade complains that it can't find "orafunc" on 9.5, 
which is true.  Is there a standard way of handling this situation?

--
-- Christophe Pettus
   x...@thebuild.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade error regarding hstore operator

[Feld, Michael (IMS)]

Thanks for the reply Tom. template1 is definitely empty and does not contain 
any hstore objects. I did a little debugging and placed the below SQL before 
and after the hstore creation in the file produced by the pg_dump and 
determined that these operator objects only become present immediately after 
the creation of the hstore extension, and not before. Then, much later down the 
pg_dump file, it attempts to create the operator family for these 4 items 
producing the errors. I did a pg_dump of the same database on a 9.1 instance 
and it does not produce the operator creation objects SQL outside of the 
extension. This seems to be something that happened as part of the pg_upgrade. 
Any idea why these have showed up outside the extension? Is there anything I 
can do to fix this? Thanks for all of your help.

Mike

SELECT am.amname AS index_method,
   opf.opfname AS opfamily_name,
   amop.amopopr::regoperator AS opfamily_operator
FROM pg_am am, pg_opfamily opf, pg_amop amop
WHERE opf.opfmethod = am.oid AND
  amop.amopfamily = opf.oid and opf.opfname like '%hstore%'
ORDER BY index_method, opfamily_name, opfamily_operator;

-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Wednesday, April 06, 2016 7:01 PM
To: Feld, Michael (IMS) <fe...@imsweb.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_upgrade error regarding hstore operator

"Feld, Michael (IMS)" <fe...@imsweb.com> writes:
> Thanks for the assist Tom. That worked for us. Noticing a different
> issue following the pg_upgrade. If we take a pg_dump of a database on
> this upgraded instance with the hstore extension and try to pg_restore
> it back up to the same instance we get the following errors

Those are the *only* errors you get?  That seems rather odd.  I could believe 
something like this happening if, say, you had an "unpackaged"
(that is, pre-extensions) version of hstore lying about.  But then you'd 
probably get conflicts on all the hstore-related objects, not only the 
opclasses.

In any case, by far the most likely explanation is that you're trying to 
restore into a non-empty database, probably because you've put stuff into
template1 and are cloning the new database from there.

regards, tom lane



Information in this e-mail may be confidential. It is intended only for the 
addressee(s) identified above. If you are not the addressee(s), or an employee 
or agent of the addressee(s), please note that any dissemination, distribution, 
or copying of this communication is strictly prohibited. If you have received 
this e-mail in error, please notify the sender of the error.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade error regarding hstore operator

[Tom Lane]

"Feld, Michael (IMS)"  writes:
> Thanks for the assist Tom. That worked for us. Noticing a different
> issue following the pg_upgrade. If we take a pg_dump of a database on
> this upgraded instance with the hstore extension and try to pg_restore
> it back up to the same instance we get the following errors

Those are the *only* errors you get?  That seems rather odd.  I could
believe something like this happening if, say, you had an "unpackaged"
(that is, pre-extensions) version of hstore lying about.  But then
you'd probably get conflicts on all the hstore-related objects, not
only the opclasses.

In any case, by far the most likely explanation is that you're trying to
restore into a non-empty database, probably because you've put stuff into
template1 and are cloning the new database from there.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade error regarding hstore operator

[Feld, Michael (IMS)]

Thanks for the assist Tom. That worked for us. Noticing a different issue 
following the pg_upgrade. If we take a pg_dump of a database on this upgraded 
instance with the hstore extension and try to pg_restore it back up to the same 
instance we get the following errors (ignore the likeness to your name, 
apparently when this cluster was created years ago they chose to set it up in 
your honor):

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3879; 2753 745119 OPERATOR 
FAMILY btree_hstore_ops tomlane
pg_restore: [archiver (db)] could not execute query: ERROR:  operator family 
"btree_hstore_ops" for access method "btree" already exists
Command was: CREATE OPERATOR FAMILY btree_hstore_ops USING btree;

pg_restore: [archiver (db)] Error from TOC entry 3880; 2753 745127 OPERATOR 
FAMILY gin_hstore_ops tomlane
pg_restore: [archiver (db)] could not execute query: ERROR:  operator family 
"gin_hstore_ops" for access method "gin" already exists
Command was: CREATE OPERATOR FAMILY gin_hstore_ops USING gin;

pg_restore: [archiver (db)] Error from TOC entry 3881; 2753 745137 OPERATOR 
FAMILY gist_hstore_ops tomlane
pg_restore: [archiver (db)] could not execute query: ERROR:  operator family 
"gist_hstore_ops" for access method "gist" already exists
Command was: CREATE OPERATOR FAMILY gist_hstore_ops USING gist;

pg_restore: [archiver (db)] Error from TOC entry 3882; 2753 745151 OPERATOR 
FAMILY hash_hstore_ops tomlane
pg_restore: [archiver (db)] could not execute query: ERROR:  operator family 
"hash_hstore_ops" for access method "hash" already exists
Command was: CREATE OPERATOR FAMILY hash_hstore_ops USING hash;

We do not have this issue for any new databases created following the 
pg_upgrade. I noticed that new databases have the hstore 1.3 version while the 
originals still have the 1.1 version. I updated the extension on one of these 
to see if that would resolve the issue and it did not. Thanks for any help you 
can offer.

-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Tuesday, March 08, 2016 6:22 PM
To: Feld, Michael (IMS) <fe...@imsweb.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_upgrade error regarding hstore operator

"Feld, Michael (IMS)" <fe...@imsweb.com> writes:
> I am attempting to upgrade my organization's database cluster from
> 9.1.19 to 9.5.1 using the pg_upgrade utility.

That's kind of a big jump :-( ... you missed the versions where => was 
deprecated as an operator name.

> I tried dropping the operator before doing the upgrade but it's dependent on 
> the existence of the hstore extension. Ideas?

The clean solution would be to copy share/extension/hstore--1.0--1.1.sql
from the 9.5 installation into the 9.1 installation and then do

ALTER EXTENSION hstore UPDATE TO '1.1';

Under the hood that's just doing

ALTER EXTENSION hstore DROP OPERATOR => (text, text); DROP OPERATOR => (text, 
text);

but if you did that manually, you'd have a problem when you want to update 
hstore to current versions later.  If you do what I suggest, the extension will 
properly look like it's 1.1 after pg_upgrade'ing.

regards, tom lane



Information in this e-mail may be confidential. It is intended only for the 
addressee(s) identified above. If you are not the addressee(s), or an employee 
or agent of the addressee(s), please note that any dissemination, distribution, 
or copying of this communication is strictly prohibited. If you have received 
this e-mail in error, please notify the sender of the error.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade error regarding hstore operator

[Tom Lane]

"Feld, Michael (IMS)"  writes:
> I am attempting to upgrade my organization's database cluster from
> 9.1.19 to 9.5.1 using the pg_upgrade utility.

That's kind of a big jump :-( ... you missed the versions where =>
was deprecated as an operator name.

> I tried dropping the operator before doing the upgrade but it's dependent on 
> the existence of the hstore extension. Ideas?

The clean solution would be to copy share/extension/hstore--1.0--1.1.sql
from the 9.5 installation into the 9.1 installation and then do

ALTER EXTENSION hstore UPDATE TO '1.1';

Under the hood that's just doing

ALTER EXTENSION hstore DROP OPERATOR => (text, text);
DROP OPERATOR => (text, text);

but if you did that manually, you'd have a problem when you want to update
hstore to current versions later.  If you do what I suggest, the extension
will properly look like it's 1.1 after pg_upgrade'ing.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade error regarding hstore operator

[Adrian Klaver]

On 03/08/2016 10:27 AM, Feld, Michael (IMS) wrote:

I am attempting to upgrade my organization's database cluster from 9.1.19 to 
9.5.1 using the pg_upgrade utility. After some processing, the tool bails out 
with the following error in the log:

pg_restore: creating OPERATOR "public.=>"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5660; 2617 5655672 OPERATOR => 
postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or near 
"=>"
LINE 1: CREATE OPERATOR => (
 ^
 Command was: CREATE OPERATOR => (
 PROCEDURE = "hstore",
 LEFTARG = "text",
 RIGHTARG = "text"
);

-- For binary upgrade, handle...

I tried dropping the operator before doing the upgrade but it's dependent on 
the existence of the hstore extension. Ideas?


Believe it has to do with this:

http://www.postgresql.org/docs/9.5/interactive/release-9-5.html

"
Allow => to specify named parameters in function calls (Pavel Stehule)

E.2.3.7. Functions

Previously only := could be used. This requires removing the possibility 
for => to be a user-defined operator. Creation of user-defined => 
operators has been issuing warnings since PostgreSQL 9.0."


and the removal of => as operator from here:

http://www.postgresql.org/docs/9.5/interactive/hstore.html

I remember a similar problem with just a regular dump/restore. If I 
remember correctly I solved it by doing:


\dx in psql to see what extensions where installed and when I did not 
see hstore doing:


http://www.postgresql.org/docs/9.1/interactive/sql-createextension.html
"
CREATE EXTENSION hstore SCHEMA public FROM unpackaged;

Be careful to specify the schema in which you installed the existing 
hstore objects."


on the older Postgres version and then repeating the dump/restore.







Information in this e-mail may be confidential. It is intended only for the 
addressee(s) identified above. If you are not the addressee(s), or an employee 
or agent of the addressee(s), please note that any dissemination, distribution, 
or copying of this communication is strictly prohibited. If you have received 
this e-mail in error, please notify the sender of the error.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_upgrade error regarding hstore operator

[Feld, Michael (IMS)]

I am attempting to upgrade my organization's database cluster from 9.1.19 to 
9.5.1 using the pg_upgrade utility. After some processing, the tool bails out 
with the following error in the log:

pg_restore: creating OPERATOR "public.=>"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5660; 2617 5655672 OPERATOR => 
postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or 
near "=>"
LINE 1: CREATE OPERATOR => (
^
Command was: CREATE OPERATOR => (
PROCEDURE = "hstore",
LEFTARG = "text",
RIGHTARG = "text"
);

-- For binary upgrade, handle...

I tried dropping the operator before doing the upgrade but it's dependent on 
the existence of the hstore extension. Ideas?



Information in this e-mail may be confidential. It is intended only for the 
addressee(s) identified above. If you are not the addressee(s), or an employee 
or agent of the addressee(s), please note that any dissemination, distribution, 
or copying of this communication is strictly prohibited. If you have received 
this e-mail in error, please notify the sender of the error.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.5.1: pg_upgrade_support missing

[schoetbi]

Hello,

I tried to delete all binary_upgrade schematas using this:

$ psql -tc "SELECT datname FROM pg_database" -U postgres -p 5433 | xargs -I
{} psql -d {} -U postgres -p 5433 -tc "drop schema if exists binary_upgrade
cascade;"

But got this:
output is not a tty

So I got the dblist from pgadmin and saved to file:
That worked:
$ cat dblist.txt | xargs -I {} psql -d {} -U postgres -p 5433 -tc "drop
schema if exists binary_upgrade cascade;"

pg_upgrade succeeded with the step:
Checking for presence of required libraries ok

But then I got: 
Checking database user is the install user
Only the install user can be defined in the new cluster.
Failure, exiting

But this is the next story ;-) I can assure that deleting the schematas from
the source db solves my initial problem.

Thanks for your help,
Tobias Schönit




--
View this message in context: 
http://postgresql.nabble.com/pg-upgrade-9-5-1-pg-upgrade-support-missing-tp5890202p5890380.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.5.1: pg_upgrade_support missing

[Tom Lane]

schoetbi schoetbi  writes:
> i tried to migrate a database cluster from pg 9.4.1 to 9.5.1 with
> pg_upgrade. I got the follwing error:

>> Could not load library "$libdir/pg_upgrade_support"

Hmm, pg_upgrade_support isn't a separate library anymore; it's been merged
into core.  I would not have expected that to cause any problems, because
the functions in that library shouldn't have been referenced in your old
database in the first place.  They were supposed to be installed and then
removed again during the process of (prior versions of) pg_upgrade.

I'm guessing you had leftovers from old failed pg_upgrade attempts that
you never completed.

What you want to do is simply manually drop the functions referencing
$libdir/pg_upgrade_support.  A look into the pg_upgrade sources suggests
that this ought to do it:

DROP SCHEMA IF EXISTS binary_upgrade CASCADE;

though you'll probably have to do that in every database of the
installation.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.5.1: pg_upgrade_support missing

[Adrian Klaver]

On 03/02/2016 01:42 AM, schoetbi schoetbi wrote:

Hello,

i tried to migrate a database cluster from pg 9.4.1 to 9.5.1 with
pg_upgrade. I got the follwing error:

c:\Temp>"C:\Program Files\PostgreSQL\9.5\bin\pg_upgrade" -b
"C:\Program Files\PostgreSQL\9.4\bin" -B "C:\Program
Files\PostgreSQL\9.5\bin" -
d "C:\Program Files\PostgreSQL\9.4\data" -D "C:\Program
Files\PostgreSQL\9.5\data" -p 5433 -P 5432 -U postgres
Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings   ok
Checking for prepared transactions  ok
Checking for reg* system OID user data typesok
Checking for contrib/isn with bigint-passing mismatch   ok
Creating dump of global objects ok
Creating dump of database schemas
 ok
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing
from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
 loadable_libraries.txt
Failure, exiting


The file  loadable_libraries.txt  contains:


Could not load library "$libdir/pg_upgrade_support"
FEHLER: konnte nicht auf Datei „$libdir/pg_upgrade_support“
zugreifen: No such file or directory



I tried to copy the dll from the 9.4 version but the interface seems not
to be compatible.

I use postgres under Windows 7 64 Bit.


So how did you install Postgres(and from where) and was it different for 
9.4 versus 9.5?




Note: This is a question first asked here:
https://www.pg-forum.de/viewtopic.php?f=54=7495

Thanks,
Tobias



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_upgrade 9.5.1: pg_upgrade_support missing

[schoetbi schoetbi]

Hello,

i tried to migrate a database cluster from pg 9.4.1 to 9.5.1 with
pg_upgrade. I got the follwing error:

c:\Temp>"C:\Program Files\PostgreSQL\9.5\bin\pg_upgrade" -b "C:\Program
> Files\PostgreSQL\9.4\bin" -B "C:\Program Files\PostgreSQL\9.5\bin" -
> d "C:\Program Files\PostgreSQL\9.4\data" -D "C:\Program
> Files\PostgreSQL\9.5\data" -p 5433 -P 5432 -U postgres
> Performing Consistency Checks
> -
> Checking cluster versions   ok
> Checking database user is the install user  ok
> Checking database connection settings   ok
> Checking for prepared transactions  ok
> Checking for reg* system OID user data typesok
> Checking for contrib/isn with bigint-passing mismatch   ok
> Creating dump of global objects ok
> Creating dump of database schemas
> ok
> Checking for presence of required libraries fatal
> Your installation references loadable libraries that are missing from the
> new installation.  You can add these libraries to the new installation,
> or remove the functions using them from the old installation.  A list of
> problem libraries is in the file:
> loadable_libraries.txt
> Failure, exiting


The file  loadable_libraries.txt  contains:
>
>
> Could not load library "$libdir/pg_upgrade_support"
> FEHLER: konnte nicht auf Datei „$libdir/pg_upgrade_support“ zugreifen: No
> such file or directory



I tried to copy the dll from the 9.4 version but the interface seems not to
be compatible.

I use postgres under Windows 7 64 Bit.

Note: This is a question first asked here:
https://www.pg-forum.de/viewtopic.php?f=54=7495

Thanks,
Tobias

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Tom Lane]

Karsten Hilbert  writes:
>> and here is the function that leads to the schema having a
>> dependancy on table data:

Hm.  So, by having installed this function as a check constraint, you have
created a data dependency that pg_dump has no way to know about.  It's
going to load the tables in some order that's chosen without regard to the
need for dem.staff to be populated first.  This is not a pg_dump bug.

In general, embedding lookups of other tables into CHECK constraints
is going to cause you all kinds of grief quite aside from pg_dump
not understanding it, because the backend doesn't really understand it
either.  If the other table changes, causing the CHECK expression to
fail, that will *not* cause anything to happen to the table with the
CHECK constraint.  It could well be that pg_dump is loading the tables
in the right order by chance, and the reason you're seeing a failure
is that one or more rows have modified_by values corresponding to
people who no longer are in the staff table.

Can you get rid of dem.staff in favor of something like creating a
"staff" role and GRANT'ing that to appropriate users?

Alternatively, maybe you can make the modified_by column be a foreign
key referencing a table of users (it probably couldn't be defined
quite like "staff", but you get the idea).  The presence of the foreign
key would be enough to cue pg_dump about load order.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 10:45:27AM -0500, Tom Lane wrote:

> > (For what it's worth, I have also tried the --method=dump way 
> > of using Debian's pg_upgradecluster which internally uses a
> > dump/restore cycle rather than calling pg_upgrade. That
> > failed due to ordering problems with table data vs table
> > constraints.)
> 
> That seems like an independent bug.  Can you provide specifics?

Attached the log of

pg_upgradecluster -v 9.5 9.4 main &> pg-upgrade-9_4-9_5-dump_restore.log

and here is the function that leads to the schema having a
dependancy on table data:

create or replace function gm.account_is_dbowner_or_staff(_account name)
returns boolean
language plpgsql
as '
DECLARE
_is_owner boolean;
BEGIN
-- is _account member of current db group ?
--  PERFORM 1 FROM pg_auth_members
--  WHERE
--  roleid = (SELECT oid FROM pg_roles WHERE rolname = 
current_database())
--  AND
--  member = (SELECT oid FROM pg_roles WHERE rolname = 
_account)
--  ;
--  IF FOUND THEN
--  -- should catch people on staff, gm-dbo, and postgres
--  RETURN TRUE;
--  END IF;

-- postgres
IF _account = ''postgres'' THEN
RETURN TRUE;
END IF;

-- on staff list
PERFORM 1 FROM dem.staff WHERE db_user = _account;
IF FOUND THEN
RETURN TRUE;
END IF;

-- owner
SELECT pg_catalog.pg_get_userbyid(datdba) = _account INTO 
STRICT _is_owner FROM pg_catalog.pg_database WHERE datname = current_database();
IF _is_owner IS TRUE THEN
RETURN TRUE;
END IF;

-- neither
RAISE EXCEPTION
''gm.account_is_dbowner_or_staff(NAME): <%> is neither 
database owner, nor , nor on staff'', _account
USING ERRCODE = ''integrity_constraint_violation''
;
RETURN FALSE;
END;';

The function is used on audit tables:

alter table audit.audit_fields
drop constraint if exists
audit_audit_fields_sane_modified_by cascade;

alter table audit.audit_fields
add constraint audit_audit_fields_sane_modified_by check
(gm.account_is_dbowner_or_staff(modified_by) IS TRUE)
;

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
Stopping old cluster...
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Creating new cluster 9.5/main ...
  config /etc/postgresql/9.5/main
  data   /var/lib/postgresql/9.5/main
  locale de_DE.UTF-8
  port   5433
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs...
WARNING:  column "brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "atc_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "external_code_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "external_code_type_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "fake_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "pk_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "pk_data_source" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "pk_drug_component" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "src_table" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "atc_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "external_code_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "external_code_type_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "fake_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "pk_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "pk_data_source" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "pk_drug_component" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
Fixing hardcoded library paths for stored procedures...
Upgrading database 

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 12:38:47PM -0500, Tom Lane wrote:

> After digging through this, I figured out the problem: you'd installed
> pg_trgm into the pg_catalog schema, whereas when I was testing I'd just
> dropped it into the public schema.  That confuses pg_dump into not
> emitting the shell type that it should emit.  It's an easy fix now
> that I see the problem.
> 
> This bug does *not*, AFAICT, explain any problem you might have with
> "dump" transfers, only with pg_upgrade.

I realize that. Thank you for looking into this issue.

I'll rethink the foreign key / staff / check constraint issue meanwhile.

Thanks,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 04:03:51PM -0500, Tom Lane wrote:

> BTW, the one-liner fix that I'd had in mind when I wrote that does indeed
> fix this particular problem, but after studying the code I realized that
> there's a whole bunch of related problems; for instance I believe
> pg_upgrade would lose domain constraints on a domain type that's in an
> extension installed into pg_catalog :-(.

Does this warrant adding a few words to the documentation
warning against installing extensions into pg_catalog. ?

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 12:53:24PM -0500, Tom Lane wrote:

> >> and here is the function that leads to the schema having a
> >> dependancy on table data:
> 
> Hm.  So, by having installed this function as a check constraint, you have
> created a data dependency that pg_dump has no way to know about.  It's
> going to load the tables in some order that's chosen without regard to the
> need for dem.staff to be populated first.  This is not a pg_dump bug.

Yes, I agree.

> In general, embedding lookups of other tables into CHECK constraints
> is going to cause you all kinds of grief quite aside from pg_dump
> not understanding it, because the backend doesn't really understand it
> either.  If the other table changes, causing the CHECK expression to
> fail, that will *not* cause anything to happen to the table with the
> CHECK constraint.  It could well be that pg_dump is loading the tables
> in the right order by chance, and the reason you're seeing a failure
> is that one or more rows have modified_by values corresponding to
> people who no longer are in the staff table.

Not really but for that I need to deliver more information.
The audit.audit_fields table is part of GNUmed's homegrown,
trigger based audit solution:

- tables have audit tables w/o constraints in the audit. schema
- triggers on tables log UPDATEs/DELETEs into the audit tables
- tables being audited (such as dem.staff) INHERIT from audit.audit_fields
- audit.audit_fields is never inserted into directly (only into child tables)
- audit.audit_fields carries the constraint based on gm.is_dbowner_or_staff()

(so, yes, it is even worse: since dem.staff is audited, and
 therefore inherits the check constraint, it depends on itself :-o


   Table "dem.staff"
Column |   Type   |   
Modifiers   | Storage  | Stats target | 
   Description
---+--+---+--+--+---
 pk_audit  | integer  | not null default 
nextval('audit.audit_fields_pk_audit_seq'::regclass) | plain|  
| 
 row_version   | integer  | not null default 0  
  | plain|  | 
 modified_when | timestamp with time zone | not null default now()  
  | plain|  | 
 modified_by   | name | not null default "current_user"()   
  | plain|  | 
 pk| integer  | not null default 
nextval('dem.staff_pk_seq'::regclass)| plain|  
| 
 fk_identity   | integer  | not null
  | plain|  | 
 db_user   | name | not null default "current_user"()   
  | plain|  | 
 short_alias   | text | not null
  | extended |  | a short signature 
unique to this staff member+
   |  | 
  |  |  |  to be 
used in the GUI, actually this is somewhat+
   |  | 
  |  |  |  
redundant with ext_person_id...
 comment   | text | 
  | extended |  | 
 is_active | boolean  | not null default true   
  | plain|  | 
Indexes:
"staff_pkey" PRIMARY KEY, btree (pk)
"staff_db_user_key" UNIQUE CONSTRAINT, btree (db_user)
"staff_short_alias_key" UNIQUE CONSTRAINT, btree (short_alias)
Foreign-key constraints:
"staff_fk_identity_fkey" FOREIGN KEY (fk_identity) REFERENCES 
dem.identity(pk) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
TABLE "bill.bill_item" CONSTRAINT "bill_item_fk_provider_fkey" FOREIGN KEY 
(fk_provider) REFERENCES dem.staff(pk) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "blobs.doc_obj" CONSTRAINT "doc_obj_fk_intended_reviewer_fkey" 
FOREIGN KEY (fk_intended_reviewer) REFERENCES dem.staff(pk) ON UPDATE CASCADE 
ON DELETE RESTRICT
TABLE "dem.identity" CONSTRAINT "identity_fk_primary_provider_fkey" FOREIGN 
KEY (fk_primary_provider) REFERENCES dem.staff(pk) ON UPDATE CASCADE ON DELETE 
RESTRICT
TABLE 

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 12:38:47PM -0500, Tom Lane wrote:

>>> dumps of your 9.4 installation's databases.  If you'd be willing to send
>>> those to me off-list, maybe I could figure out what's happening.
> 
>> The list stalled the attachment so here as PM.
> 
> Well, you shouldn't have tried to send it to the list; there's no need
> to memorialize half a megabyte of transient data in the archives.

I hadn't realized that

>>> If you'd be willing to send those to me off-list

was to be understood as "if so you *should* send them *off*-list". Sorry.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Tom Lane]

Karsten Hilbert  writes:
> On Fri, Jan 08, 2016 at 12:38:47PM -0500, Tom Lane wrote:
>> After digging through this, I figured out the problem: you'd installed
>> pg_trgm into the pg_catalog schema, whereas when I was testing I'd just
>> dropped it into the public schema.  That confuses pg_dump into not
>> emitting the shell type that it should emit.  It's an easy fix now
>> that I see the problem.
>> This bug does *not*, AFAICT, explain any problem you might have with
>> "dump" transfers, only with pg_upgrade.

> I realize that. Thank you for looking into this issue.

BTW, the one-liner fix that I'd had in mind when I wrote that does indeed
fix this particular problem, but after studying the code I realized that
there's a whole bunch of related problems; for instance I believe
pg_upgrade would lose domain constraints on a domain type that's in an
extension installed into pg_catalog :-(.  See
http://www.postgresql.org/message-id/19767.1452279...@sss.pgh.pa.us

So a fix might take a bit more time than I thought, but hopefully we'll
have something in time for next month's update releases.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Tom Lane]

Karsten Hilbert  writes:
> On Fri, Jan 08, 2016 at 04:03:51PM -0500, Tom Lane wrote:
>> BTW, the one-liner fix that I'd had in mind when I wrote that does indeed
>> fix this particular problem, but after studying the code I realized that
>> there's a whole bunch of related problems; for instance I believe
>> pg_upgrade would lose domain constraints on a domain type that's in an
>> extension installed into pg_catalog :-(.

> Does this warrant adding a few words to the documentation
> warning against installing extensions into pg_catalog. ?

No, it's just a bug.  Although apparently not many people do that, or
we'd have heard complaints before.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Adrian Klaver]

On 01/08/2016 01:26 PM, Tom Lane wrote:
> Karsten Hilbert  writes:
>> On Fri, Jan 08, 2016 at 04:03:51PM -0500, Tom Lane wrote:
>>> BTW, the one-liner fix that I'd had in mind when I wrote that does indeed
>>> fix this particular problem, but after studying the code I realized that
>>> there's a whole bunch of related problems; for instance I believe
>>> pg_upgrade would lose domain constraints on a domain type that's in an
>>> extension installed into pg_catalog :-(.
> 
>> Does this warrant adding a few words to the documentation
>> warning against installing extensions into pg_catalog. ?
> 
> No, it's just a bug.  Although apparently not many people do that, or
> we'd have heard complaints before.

That dredged up a memory from way back:

http://www.postgresql.org/message-id/200411251906.43881.akla...@comcast.net

in particular:

http://www.postgresql.org/message-id/20077.1101510...@sss.pgh.pa.us

> 
>   regards, tom lane
> 
> 


-- 
Adrian Klaver
adrian.kla...@aklaver.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Tom Lane]

Adrian Klaver  writes:
> On 01/08/2016 01:26 PM, Tom Lane wrote:
>> No, it's just a bug.  Although apparently not many people do that, or
>> we'd have heard complaints before.

> That dredged up a memory from way back:
> http://www.postgresql.org/message-id/200411251906.43881.akla...@comcast.net
> in particular:
> http://www.postgresql.org/message-id/20077.1101510...@sss.pgh.pa.us

Well, that was a long time ago.  Now that we have extensions, it should
be possible for pg_dump to do the right thing with an extension's members
whether they're in pg_catalog or not.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Tom Lane]

Karsten Hilbert  writes:
> Just a crazy thought:
> If I create a foreign key from *.*.modified_by towards
> dem.staff.db_user but then DISABLE that FK -- would that still
> cue in pg_dump to order the tables appropriately ?

Hmm, probably.  Sounds like a kluge but ...

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 12:53:24PM -0500, Tom Lane wrote:

> Hm.  So, by having installed this function as a check constraint, you have
> created a data dependency that pg_dump has no way to know about.  It's
> going to load the tables in some order that's chosen without regard to the
> need for dem.staff to be populated first.  This is not a pg_dump bug.
> 
> In general, embedding lookups of other tables into CHECK constraints
> is going to cause you all kinds of grief quite aside from pg_dump
> not understanding it, because the backend doesn't really understand it
> either.  If the other table changes, causing the CHECK expression to
> fail, that will *not* cause anything to happen to the table with the
> CHECK constraint.  It could well be that pg_dump is loading the tables
> in the right order by chance, and the reason you're seeing a failure
> is that one or more rows have modified_by values corresponding to
> people who no longer are in the staff table.
> 
> Can you get rid of dem.staff in favor of something like creating a
> "staff" role and GRANT'ing that to appropriate users?
> 
> Alternatively, maybe you can make the modified_by column be a foreign
> key referencing a table of users (it probably couldn't be defined
> quite like "staff", but you get the idea).  The presence of the foreign
> key would be enough to cue pg_dump about load order.

Just a crazy thought:

If I create a foreign key from *.*.modified_by towards
dem.staff.db_user but then DISABLE that FK -- would that still
cue in pg_dump to order the tables appropriately ?

Thanks,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

SOLVED: Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 04:26:25PM -0500, Tom Lane wrote:

> Karsten Hilbert  writes:
> > On Fri, Jan 08, 2016 at 04:03:51PM -0500, Tom Lane wrote:
> >> BTW, the one-liner fix that I'd had in mind when I wrote that does indeed
> >> fix this particular problem, but after studying the code I realized that
> >> there's a whole bunch of related problems; for instance I believe
> >> pg_upgrade would lose domain constraints on a domain type that's in an
> >> extension installed into pg_catalog :-(.
> 
> > Does this warrant adding a few words to the documentation
> > warning against installing extensions into pg_catalog. ?
> 
> No, it's just a bug.  Although apparently not many people do that, or
> we'd have heard complaints before.

For the record, apart from the aforementioned bug, I can
confirm that pg_upgrade will work fine when pg_trgm is
relocated to another schema (I chose "pgtrgm").

Caveats:

1)
One can't use "pg_trgm" as the schema name - PG will tell us
that the pg_ prefix is reserved for system schemata.

2)
One can't (easily ?) use

alter extension ... set schema ...

to relocate pg_trgm from pg_catalog to some other schema
because PG will inform us that pg_catalog is a system catalog:

gnumed_v21=# alter extension pg_trgm set schema pg_catalog;
ALTER EXTENSION
gnumed_v21=# alter extension pg_trgm set schema pgtrgm;
ERROR:  cannot remove dependency on schema pg_catalog because it is a 
system object
gnumed_v21=#

Relocating from pg_catalog requires a

drop extension ... cascade
create extension ... with schema

cycle, followed by recreating GIN indexes as needed (in my case).

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"partial" data constraint - trigger or CONSTRAINT ? was: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 12:53:24PM -0500, Tom Lane wrote:

> In general, embedding lookups of other tables into CHECK constraints
> is going to cause you all kinds of grief quite aside from pg_dump
> not understanding it, because the backend doesn't really understand it
> either.  If the other table changes, causing the CHECK expression to
> fail, that will *not* cause anything to happen to the table with the
> CHECK constraint.  It could well be that pg_dump is loading the tables
> in the right order by chance, and the reason you're seeing a failure
> is that one or more rows have modified_by values corresponding to
> people who no longer are in the staff table.

That has pretty much been the very intent of the constraint function:

Allowing only "postgres", the owner of the database, or
people _currently_ on staff to insert/update table data.

There may well be database accounts which used to be
associated with staff rows but are no longer listed as staff
(because they aren't). There will still be table data
associated with those accounts - their former staff entries
can be gotten from the audit system (that's why dem.staff
itself is being audited).

I realize that being able to foreign key into system tables
would not have helped with the part where only _current_
staff is to insert into/update data tables. That's why I
haven't moaned about it but rather written my own
(misguided?) attempt at enforcing such a constraint.

Would I be better of rewriting the constraint as an ON INSERT
OR UPDATE trigger ?

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 12:45:29PM +0100, Karsten Hilbert wrote:

>   pg_restore: erstelle EXTENSION „pg_trgm“
>   pg_restore: erstelle COMMENT „EXTENSION "pg_trgm"“
>   pg_restore: erstelle FUNCTION „pg_catalog.gtrgm_in("cstring")“
>   pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
>   pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 893; 
> 1255 511230 FUNCTION gtrgm_in("cstring") postgres
>   pg_restore: [Archivierer (DB)] could not execute query: ERROR:  pg_type 
> OID value not set when in binary upgrade mode
>   Die Anweisung war: CREATE FUNCTION "gtrgm_in"("cstring") RETURNS 
> "gtrgm"
>   LANGUAGE "c" IMMUTABLE STRICT
>   AS '$libdir/pg_trgm', 'gtrgm_in'...
> 
> For one thing - does it seem odd that the function would be
> named "gtrgm_in" rather than "pgtrgm_in" ?

A bit of searching shows that that seems to be normal.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 12:45:29PM +0100, Karsten Hilbert wrote:

>   pg_restore: erstelle EXTENSION „pg_trgm“
>   pg_restore: erstelle COMMENT „EXTENSION "pg_trgm"“
>   pg_restore: erstelle FUNCTION „pg_catalog.gtrgm_in("cstring")“
>   pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
>   pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 893; 
> 1255 511230 FUNCTION gtrgm_in("cstring") postgres
>   pg_restore: [Archivierer (DB)] could not execute query: ERROR:  pg_type 
> OID value not set when in binary upgrade mode
>   Die Anweisung war: CREATE FUNCTION "gtrgm_in"("cstring") RETURNS 
> "gtrgm"
>   LANGUAGE "c" IMMUTABLE STRICT
>   AS '$libdir/pg_trgm', 'gtrgm_in'...

It does sound similar to


http://postgresql.nabble.com/BUG-5942-pg-trgm-sql-has-cyclic-dependency-on-type-gtrgm-creation-td4259677.html

which, however, wouldn't help me in solving the problem.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

Hi,

I have attempted a pg_upgrade on Debian using the Debian
wrapper scripts like so:

pg_upgradecluster -v 9.5 9.4 main

(meaning to upgrade a cluster named "main" from 9.4 to 9.5)

which resulted in this:

-
  pg_upgrade run on Fri Jan  8 11:47:32 2016
-

Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings   ok
Checking for prepared transactions  ok
Checking for reg* system OID user data typesok
Checking for contrib/isn with bigint-passing mismatch   ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user  ok
Checking for prepared transactions  ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
--
Analyzing all rows in the new cluster   ok
Freezing all rows on the new clusterok
Deleting files from new pg_clog ok
Copying old pg_clog to new server   ok
Setting next transaction ID and epoch for new cluster   ok
Deleting files from new pg_multixact/offsetsok
Copying old pg_multixact/offsets to new server  ok
Deleting files from new pg_multixact/membersok
Copying old pg_multixact/members to new server  ok
Setting next multixact ID and offset for new clusterok
Resetting WAL archives  ok
Setting frozenxid and minmxid counters in new cluster   ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster

*failure*
Consult the last few lines of "pg_upgrade_dump_512600.log" for
the probable cause of the failure.

-

The pg_upgrade_dump_512600.log shows:

command: "/usr/lib/postgresql/9.5/bin/pg_dump" --host 
"/var/log/postgresql/pg_upgradecluster-9.4-9.5-main.nb6n" --port 5432 
--username "postgres" --schema-only --quote-all-identifiers --binary-upgrade 
--format=custom  --file="pg_upgrade_dump_512600.custom" "gnumed_v20" >> 
"pg_upgrade_dump_512600.log" 2>&1

command: "/usr/lib/postgresql/9.5/bin/pg_restore" --host 
"/var/log/postgresql/pg_upgradecluster-9.4-9.5-main.nb6n" --port 5433 
--username "postgres" --exit-on-error --verbose --dbname "gnumed_v20" 
"pg_upgrade_dump_512600.custom" >> "pg_upgrade_dump_512600.log" 2>&1

pg_restore: verbinde mit der Datenbank zur Wiederherstellung
pg_restore: erstelle pg_largeobject „pg_largeobject“
pg_restore: erstelle pg_largeobject_metadata „pg_largeobject_metadata“
pg_restore: erstelle SCHEMA „au“
pg_restore: erstelle SCHEMA „audit“
pg_restore: erstelle SCHEMA „bill“
pg_restore: erstelle COMMENT „SCHEMA "bill"“
pg_restore: erstelle SCHEMA „blobs“
pg_restore: erstelle SCHEMA „cfg“
pg_restore: erstelle COMMENT „SCHEMA "cfg"“
pg_restore: erstelle SCHEMA „clin“
pg_restore: erstelle SCHEMA „de_de“
pg_restore: erstelle SCHEMA „dem“
pg_restore: erstelle SCHEMA „gm“
pg_restore: erstelle SCHEMA „i18n“
pg_restore: erstelle SCHEMA „public“
pg_restore: erstelle COMMENT „SCHEMA "public"“
pg_restore: erstelle SCHEMA „ref“
pg_restore: erstelle COMMENT „SCHEMA "ref"“
pg_restore: erstelle SCHEMA „staging“
pg_restore: erstelle COMMENT „SCHEMA "staging"“
pg_restore: erstelle EXTENSION „pg_trgm“
pg_restore: erstelle COMMENT „EXTENSION "pg_trgm"“
pg_restore: erstelle FUNCTION „pg_catalog.gtrgm_in("cstring")“
pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 893; 
1255 511230 FUNCTION gtrgm_in("cstring") postgres
pg_restore: [Archivierer (DB)] could not execute query: ERROR:  pg_type 
OID value not set when in binary upgrade mode
Die Anweisung war: CREATE FUNCTION "gtrgm_in"("cstring") RETURNS 
"gtrgm"
LANGUAGE "c" IMMUTABLE STRICT
AS 

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Adrian Klaver]

On 01/08/2016 07:28 AM, Karsten Hilbert wrote:
Ccing list

On Fri, Jan 08, 2016 at 07:18:24AM -0800, Adrian Klaver wrote:


I thought --method=dump was the default, so this:

pg_upgradecluster -v 9.5 9.4 main

was using that?


True enough. I did specify the "-m upgrade" though, as
witnessed by the log snippet.


Alright then. Just trying to match the output with the command and quiet 
the nagging voice in the head:)




Karsten




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 07:18:24AM -0800, Adrian Klaver wrote:

> http://www.postgresql.org/docs/9.5/interactive/pgupgrade.html
> 
> "If an error occurs while restoring the database schema, pg_upgrade will
> exit and you will have to revert to the old cluster as outlined in step 16
> below.

Thanks for pointing this out. Debian made it fairly easy to
pick up the old cluster (they provide quite nice wrappers). I
am firmly seated on 9.4 again, which in itself has been doing
excellent duty.

> To try pg_upgrade again, you will need to modify the old cluster so
> the pg_upgrade schema restore succeeds. If the problem is a contrib module,
> you might need to uninstall the contrib module from the old cluster and
> install it in the new cluster after the upgrade, assuming the module is not
> being used to store user data."

I am, indeed, using pg_trgm for an index on patients' names
so I will likely have to DROP / CREATE EXTENSION for
upgrading the cluster, and re-create the index after the
upgrade :-(

No problem for me but will need meticulous documentation and
instructions to end users (GPs, physical therapists ... ;-)

Thanks for answering,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Tom Lane]

Karsten Hilbert  writes:
> On Fri, Jan 08, 2016 at 11:23:21AM -0500, Tom Lane wrote:
>> A suggestion is to run the pg_upgrade with -r switch, which will leave a
>> litter of files in your working directory.  Some of them will be named
>> like pg_upgrade_dump_NNN.custom and should be custom-format, schema-only
>> dumps of your 9.4 installation's databases.  If you'd be willing to send
>> those to me off-list, maybe I could figure out what's happening.

> The list stalled the attachment so here as PM.

Well, you shouldn't have tried to send it to the list; there's no need
to memorialize half a megabyte of transient data in the archives.

After digging through this, I figured out the problem: you'd installed
pg_trgm into the pg_catalog schema, whereas when I was testing I'd just
dropped it into the public schema.  That confuses pg_dump into not
emitting the shell type that it should emit.  It's an easy fix now
that I see the problem.

This bug does *not*, AFAICT, explain any problem you might have with
"dump" transfers, only with pg_upgrade.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Adrian Klaver]

On 01/08/2016 07:41 AM, Karsten Hilbert wrote:

On Fri, Jan 08, 2016 at 07:18:24AM -0800, Adrian Klaver wrote:


http://www.postgresql.org/docs/9.5/interactive/pgupgrade.html

"If an error occurs while restoring the database schema, pg_upgrade will
exit and you will have to revert to the old cluster as outlined in step 16
below.


Thanks for pointing this out. Debian made it fairly easy to
pick up the old cluster (they provide quite nice wrappers). I
am firmly seated on 9.4 again, which in itself has been doing
excellent duty.


To try pg_upgrade again, you will need to modify the old cluster so
the pg_upgrade schema restore succeeds. If the problem is a contrib module,
you might need to uninstall the contrib module from the old cluster and
install it in the new cluster after the upgrade, assuming the module is not
being used to store user data."


I am, indeed, using pg_trgm for an index on patients' names
so I will likely have to DROP / CREATE EXTENSION for
upgrading the cluster, and re-create the index after the
upgrade :-(


I do not use pg_trgm, so I have not had occasion to upgrade it. Maybe 
someone who has can provide a better method.




No problem for me but will need meticulous documentation and
instructions to end users (GPs, physical therapists ... ;-)

Thanks for answering,
Karsten




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 10:45:27AM -0500, Tom Lane wrote:

> Karsten Hilbert  writes:

> > (For what it's worth, I have also tried the --method=dump way 
> > of using Debian's pg_upgradecluster which internally uses a
> > dump/restore cycle rather than calling pg_upgrade. That
> > failed due to ordering problems with table data vs table
> > constraints.)
> 
> That seems like an independent bug.  Can you provide specifics?

I will, please bear with me as I'll have to rerun the upgrade
to get logs.

Thanks,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 10:45:27AM -0500, Tom Lane wrote:

> Karsten Hilbert  writes:
> > [ pg_upgrade failed on pg_trgm ]
> 
> Just for completeness, can you tell us which pg_trgm version (1.0
> or 1.1) is installed in the 9.4 database?

Sure:

(pg_trgm,1.1,"text similarity measurement and index searching based on 
trigrams")

taken from:

   pg_available_extensions  
  

--
 (moddatetime,1.0,"functions for tracking last modification time")
 (pg_freespacemap,1.0,"examine the free space map (FSM)")
 (earthdistance,1.0,"calculate great-circle distances on the surface of 
the Earth")
 (test_shm_mq,1.0,"Test code for shared memory message queues")
 (tablefunc,1.0,"functions that manipulate whole tables, including 
crosstab")
 (uuid-ossp,1.0,"generate universally unique identifiers (UUIDs)")
 (pageinspect,1.2,"inspect the contents of database pages at a low 
level")
 (isn,1.0,"data types for international product numbering standards")
 (pgrowlocks,1.1,"show row-level locking information")
 (pgagent,3.4,"A PostgreSQL job scheduler")
 (tcn,1.0,"Triggered change notifications")
 (unaccent,1.0,"text search dictionary that removes accents")
 (pg_stat_statements,1.2,"track execution statistics of all SQL 
statements executed")
 (dblink,1.1,"connect to other PostgreSQL databases from within a 
database")
 (insert_username,1.0,"functions for tracking who changed a table")
 (fuzzystrmatch,1.0,"determine similarities and distance between 
strings")
 (pg_buffercache,1.0,"examine the shared buffer cache")
 (timetravel,1.0,"functions for implementing time travel")
 (cube,1.0,"data type for multidimensional cubes")
 (pg_trgm,1.1,"text similarity measurement and index searching based on 
trigrams")
 (dict_int,1.0,"text search dictionary template for integers")
 (xml2,1.0,"XPath querying and XSLT")
 (sslinfo,1.0,"information about SSL certificates")
 (btree_gin,1.0,"support for indexing common datatypes in GIN")
 (btree_gist,1.0,"support for indexing common datatypes in GiST")
 (tsearch2,1.0,"compatibility package for pre-8.3 text search 
functions")
 (test_parser,1.0,"example of a custom parser for full-text search")
 (seg,1.0,"data type for representing line segments or floating-point 
intervals")
 (citext,1.0,"data type for case-insensitive character strings")
 (intarray,1.0,"functions, operators, and index support for 1-D arrays 
of integers")
 (worker_spi,1.0,"Sample background worker")
 (file_fdw,1.0,"foreign-data wrapper for flat file access")
 (dict_xsyn,1.0,"text search dictionary template for extended synonym 
processing")
 (intagg,1.0,"integer aggregator and enumerator (obsolete)")
 (pgstattuple,1.2,"show tuple-level statistics")
 (autoinc,1.0,"functions for autoincrementing fields")
 (pg_prewarm,1.0,"prewarm relation data")
 (chkpass,1.0,"data type for auto-encrypted passwords")
 (pgcrypto,1.1,"cryptographic functions")
 (plpgsql,1.0,"PL/pgSQL procedural language")
 (postgres_fdw,1.0,"foreign-data wrapper for remote PostgreSQL servers")
 (adminpack,1.0,"administrative functions for PostgreSQL")
 (hstore,1.3,"data type for storing sets of (key, value) pairs")
 (ltree,1.0,"data type for hierarchical tree-like structures")
 (lo,1.0,"Large Object maintenance")
 (refint,1.0,"functions for implementing referential integrity 
(obsolete)")
(46 Zeilen)

Diving into the postgresql-contrib-9.5 package shows that it
seems to install the same version (1.1, that is).

4c5dc5fb5743dd4534cc0ad082c075d8  
usr/share/postgresql/9.5/extension/pg_trgm--1.0--1.1.sql
5222fd4cbbc5049b8e1bc64817443d7b  
usr/share/postgresql/9.5/extension/pg_trgm--1.1.sql
aceed02fc9730e6d34000869e6dfa308  
usr/share/postgresql/9.5/extension/pg_trgm--unpackaged--1.0.sql
f81af8d3825cb3a1762b9a27d0899b38  
usr/share/postgresql/9.5/extension/pg_trgm.control

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Tom Lane]

Karsten Hilbert  writes:
> On Fri, Jan 08, 2016 at 11:12:09AM -0500, Tom Lane wrote:
>> Hm.  I just tried running a pg_upgrade here on a 9.4 database containing
>> pg_trgm 1.1, and didn't see any particular problem, so there's some
>> additional factor needed to cause your result.  Hard to tell what.
>> Can you think of anything unusual about the history of your installation?

> No, other than that that cluster has been upgraded all the
> way from, I think, 8.4 over several Debian releases ;)

A suggestion is to run the pg_upgrade with -r switch, which will leave a
litter of files in your working directory.  Some of them will be named
like pg_upgrade_dump_NNN.custom and should be custom-format, schema-only
dumps of your 9.4 installation's databases.  If you'd be willing to send
those to me off-list, maybe I could figure out what's happening.

It occurs to me that this might actually be related to the issue you
saw in "dump" mode --- if there's some unresolved circular dependency,
it could cause pg_dump to dump things in an unexpected order, which
could possibly explain the message we're seeing.  But that's just a
guess.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Tom Lane]

Karsten Hilbert  writes:
> On Fri, Jan 08, 2016 at 10:45:27AM -0500, Tom Lane wrote:
>> Just for completeness, can you tell us which pg_trgm version (1.0
>> or 1.1) is installed in the 9.4 database?

> Sure:
>   (pg_trgm,1.1,"text similarity measurement and index searching based on 
> trigrams")

Hm.  I just tried running a pg_upgrade here on a 9.4 database containing
pg_trgm 1.1, and didn't see any particular problem, so there's some
additional factor needed to cause your result.  Hard to tell what.
Can you think of anything unusual about the history of your installation?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Adrian Klaver]

On 01/08/2016 03:45 AM, Karsten Hilbert wrote:

Hi,

I have attempted a pg_upgrade on Debian using the Debian
wrapper scripts like so:

pg_upgradecluster -v 9.5 9.4 main

(meaning to upgrade a cluster named "main" from 9.4 to 9.5)

which resulted in this:

-
  pg_upgrade run on Fri Jan  8 11:47:32 2016
-

Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings   ok
Checking for prepared transactions  ok
Checking for reg* system OID user data typesok
Checking for contrib/isn with bigint-passing mismatch   ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user  ok
Checking for prepared transactions  ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
--
Analyzing all rows in the new cluster   ok
Freezing all rows on the new clusterok
Deleting files from new pg_clog ok
Copying old pg_clog to new server   ok
Setting next transaction ID and epoch for new cluster   ok
Deleting files from new pg_multixact/offsetsok
Copying old pg_multixact/offsets to new server  ok
Deleting files from new pg_multixact/membersok
Copying old pg_multixact/members to new server  ok
Setting next multixact ID and offset for new clusterok
Resetting WAL archives  ok
Setting frozenxid and minmxid counters in new cluster   ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster

*failure*
Consult the last few lines of "pg_upgrade_dump_512600.log" for
the probable cause of the failure.

-

The pg_upgrade_dump_512600.log shows:

command: "/usr/lib/postgresql/9.5/bin/pg_dump" --host "/var/log/postgresql/pg_upgradecluster-9.4-9.5-main.nb6n" --port 5432 
--username "postgres" --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="pg_upgrade_dump_512600.custom" 
"gnumed_v20" >> "pg_upgrade_dump_512600.log" 2>&1

command: "/usr/lib/postgresql/9.5/bin/pg_restore" --host "/var/log/postgresql/pg_upgradecluster-9.4-9.5-main.nb6n" --port 
5433 --username "postgres" --exit-on-error --verbose --dbname "gnumed_v20" "pg_upgrade_dump_512600.custom" >> 
"pg_upgrade_dump_512600.log" 2>&1

pg_restore: verbinde mit der Datenbank zur Wiederherstellung
pg_restore: erstelle pg_largeobject „pg_largeobject“
pg_restore: erstelle pg_largeobject_metadata „pg_largeobject_metadata“
pg_restore: erstelle SCHEMA „au“
pg_restore: erstelle SCHEMA „audit“
pg_restore: erstelle SCHEMA „bill“
pg_restore: erstelle COMMENT „SCHEMA "bill"“
pg_restore: erstelle SCHEMA „blobs“
pg_restore: erstelle SCHEMA „cfg“
pg_restore: erstelle COMMENT „SCHEMA "cfg"“
pg_restore: erstelle SCHEMA „clin“
pg_restore: erstelle SCHEMA „de_de“
pg_restore: erstelle SCHEMA „dem“
pg_restore: erstelle SCHEMA „gm“
pg_restore: erstelle SCHEMA „i18n“
pg_restore: erstelle SCHEMA „public“
pg_restore: erstelle COMMENT „SCHEMA "public"“
pg_restore: erstelle SCHEMA „ref“
pg_restore: erstelle COMMENT „SCHEMA "ref"“
pg_restore: erstelle SCHEMA „staging“
pg_restore: erstelle COMMENT „SCHEMA "staging"“
pg_restore: erstelle EXTENSION „pg_trgm“
pg_restore: erstelle COMMENT „EXTENSION "pg_trgm"“
pg_restore: erstelle FUNCTION „pg_catalog.gtrgm_in("cstring")“
pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 893; 1255 
511230 FUNCTION gtrgm_in("cstring") postgres
pg_restore: [Archivierer (DB)] could not execute query: ERROR:  pg_type 
OID value not set when in binary upgrade mode
Die Anweisung war: CREATE FUNCTION "gtrgm_in"("cstring") RETURNS 
"gtrgm"

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Tom Lane]

I wrote:
> A suggestion is to run the pg_upgrade with -r switch, which will leave a
> litter of files in your working directory.  Some of them will be named
> like pg_upgrade_dump_NNN.custom and should be custom-format, schema-only
> dumps of your 9.4 installation's databases.  If you'd be willing to send
> those to me off-list, maybe I could figure out what's happening.

> It occurs to me that this might actually be related to the issue you
> saw in "dump" mode --- if there's some unresolved circular dependency,
> it could cause pg_dump to dump things in an unexpected order, which
> could possibly explain the message we're seeing.  But that's just a
> guess.

BTW, there will also be .log files, which might contain useful information
as well, especially if any of it is bleats from pg_dump about being unable
to break a circular dependency.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

Again, as the list software doesn't like "config" at the
start of a line.

Karsten

On Fri, Jan 08, 2016 at 05:34:00PM +0100, Karsten Hilbert wrote:

> > > (For what it's worth, I have also tried the --method=dump way 
> > > of using Debian's pg_upgradecluster which internally uses a
> > > dump/restore cycle rather than calling pg_upgrade. That
> > > failed due to ordering problems with table data vs table
> > > constraints.)
> > 
> > That seems like an independent bug.  Can you provide specifics?
> 
> Attached the log of
> 
>   pg_upgradecluster -v 9.5 9.4 main &> pg-upgrade-9_4-9_5-dump_restore.log
> 
> and here is the function that leads to the schema having a
> dependancy on table data:
> 
>   create or replace function gm.account_is_dbowner_or_staff(_account name)
>   returns boolean
>   language plpgsql
>   as '
>   DECLARE
>   _is_owner boolean;
>   BEGIN
>   -- is _account member of current db group ?
>   --  PERFORM 1 FROM pg_auth_members
>   --  WHERE
>   --  roleid = (SELECT oid FROM pg_roles WHERE rolname = 
> current_database())
>   --  AND
>   --  member = (SELECT oid FROM pg_roles WHERE rolname = 
> _account)
>   --  ;
>   --  IF FOUND THEN
>   --  -- should catch people on staff, gm-dbo, and postgres
>   --  RETURN TRUE;
>   --  END IF;
> 
>   -- postgres
>   IF _account = ''postgres'' THEN
>   RETURN TRUE;
>   END IF;
> 
>   -- on staff list
>   PERFORM 1 FROM dem.staff WHERE db_user = _account;
>   IF FOUND THEN
>   RETURN TRUE;
>   END IF;
> 
>   -- owner
>   SELECT pg_catalog.pg_get_userbyid(datdba) = _account INTO 
> STRICT _is_owner FROM pg_catalog.pg_database WHERE datname = 
> current_database();
>   IF _is_owner IS TRUE THEN
>   RETURN TRUE;
>   END IF;
> 
>   -- neither
>   RAISE EXCEPTION
>   ''gm.account_is_dbowner_or_staff(NAME): <%> is neither 
> database owner, nor , nor on staff'', _account
>   USING ERRCODE = ''integrity_constraint_violation''
>   ;
>   RETURN FALSE;
>   END;';
> 
> The function is used on audit tables:
> 
>   alter table audit.audit_fields
>   drop constraint if exists
>   audit_audit_fields_sane_modified_by cascade;
> 
>   alter table audit.audit_fields
>   add constraint audit_audit_fields_sane_modified_by check
>   (gm.account_is_dbowner_or_staff(modified_by) IS TRUE)
>   ;
> 
> Karsten
> -- 
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
Stopping old cluster...
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Creating new cluster 9.5/main ...
  _DEFANG_ config /etc/postgresql/9.5/main
  data   /var/lib/postgresql/9.5/main
  locale de_DE.UTF-8
  port   5433
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs...
WARNING:  column "brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "atc_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "external_code_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "external_code_type_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "fake_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "pk_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "pk_data_source" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "pk_drug_component" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "src_table" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "atc_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "external_code_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "external_code_type_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "fake_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "pk_brand" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column 

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Tom Lane]

Karsten Hilbert  writes:
> [ pg_upgrade failed on pg_trgm ]

Just for completeness, can you tell us which pg_trgm version (1.0
or 1.1) is installed in the 9.4 database?

> (For what it's worth, I have also tried the --method=dump way 
> of using Debian's pg_upgradecluster which internally uses a
> dump/restore cycle rather than calling pg_upgrade. That
> failed due to ordering problems with table data vs table
> constraints.)

That seems like an independent bug.  Can you provide specifics?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 07:41:09AM -0800, Adrian Klaver wrote:

> >>I thought --method=dump was the default, so this:
> >>
> >>pg_upgradecluster -v 9.5 9.4 main
> >>
> >>was using that?
> >
> >True enough. I did specify the "-m upgrade" though, as
> >witnessed by the log snippet.
> 
> Alright then. Just trying to match the output with the command and quiet the
> nagging voice in the head:)

For completeness, here's the actual command run

pg_upgradecluster -m upgrade -v 9.5 9.4 main &> pg-upgrade-9_4-9_5.log

(not that that would help along any, I suppose)

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 07:41:09AM -0800, Adrian Klaver wrote:

> >On Fri, Jan 08, 2016 at 07:18:24AM -0800, Adrian Klaver wrote:
> >
> >>I thought --method=dump was the default, so this:
> >>
> >>pg_upgradecluster -v 9.5 9.4 main
> >>
> >>was using that?
> >
> >True enough. I did specify the "-m upgrade" though, as
> >witnessed by the log snippet.
> 
> Alright then. Just trying to match the output with the command and quiet the
> nagging voice in the head:)

Absolutely. I should have paid that extra second of
*re*-checking before I sent the initial question. Sorry.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

[Karsten Hilbert]

On Fri, Jan 08, 2016 at 11:12:09AM -0500, Tom Lane wrote:

> > Sure:
> > (pg_trgm,1.1,"text similarity measurement and index searching based on 
> > trigrams")
> 
> Hm.  I just tried running a pg_upgrade here on a 9.4 database containing
> pg_trgm 1.1, and didn't see any particular problem, so there's some
> additional factor needed to cause your result.  Hard to tell what.
> Can you think of anything unusual about the history of your installation?

No, other than that that cluster has been upgraded all the
way from, I think, 8.4 over several Debian releases ;)

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_upgrade on Windows

[Igal @ Lucee.org]

hi guys,

I am having issues with pg_upgrade on Windows.  I have posted a question 
on StackOverflow -- at 
http://stackoverflow.com/questions/34664236/pg-upgrade-on-windows-cannot-write-to-log-file-pg-upgrade-internal-log 
-- copied below for convenience:


I'm trying to run pg_upgrade on Windows, but I'm getting the error:

   cannot write to log file pg_upgrade_internal.log Failure, exiting

I saw a similar question for Linux at23216734 
which 
explains that the issue is with permissions, but it doesn't help with 
Windows as I do not have a user named|postgres|


Same goes for thepg_upgrade docs 
, which 
mention a|postgres|user:


   RUNAS /USER:postgres "CMD.EXE"

But again, I do not have such a user, and am trying to run this command 
as Administrator so I don't understand why I would have no permission. I 
even tried to do


|RUNAS /USER:Administrator "CMD.EXE"|

And run pg_upgrade in the new command prompt, but am getting the same error.

Also, I am not sure which directory needs permissions? Where is the 
process trying to write|pg_upgrade_internal.log|to?


any ideas?  TIA!

--

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: [GENERAL] pg_upgrade on Windows

[John R Pierce]

On 1/7/2016 12:32 PM, Igal @ Lucee.org wrote:


I'm trying to run pg_upgrade on Windows, but I'm getting the error:

cannot write to log file pg_upgrade_internal.log Failure, exiting

I saw a similar question for Linux at23216734 
which 
explains that the issue is with permissions, but it doesn't help with 
Windows as I do not have a user named|postgres|


Same goes for thepg_upgrade docs 
, which 
mention a|postgres|user:


RUNAS /USER:postgres "CMD.EXE"

But again, I do not have such a user, and am trying to run this 
command as Administrator so I don't understand why I would have no 
permission. I even tried to do


|RUNAS /USER:Administrator "CMD.EXE"|

And run pg_upgrade in the new command prompt, but am getting the same 
error.


Also, I am not sure which directory needs permissions? Where is the 
process trying to write|pg_upgrade_internal.log|to?


any ideas?  TIA!



how was your postgresql installed ?   is the existing version of 
postgres running as a system service or what? what versions are you 
upgrading from and to ?


note, btw, just being Administrator does NOT automatically override file 
permissions... an administrator is allowed to change file ownership.


--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] pg_upgrade problem

[Jerry Sievers]

Melvin Davidson melvin6...@gmail.com writes:

 Using pg_upgrade in 9.4 CentOS release 6.6 (Final) (from PostgreSQL 9.1.15) 
 it fails when GRANTING permits to roles.

 Checking pg_upgrade_dump_globals.sql, I see the point of failure is caused by 
 the -- Role memberships section.

 In there I see the following troublesome lines.
 -- Role memberships
 ...
 ...
 
 GRANT supers TO pgpoolad GRANTED BY postgres;
 GRANT  TO ;
 GRANT  TO ;
 GRANT  TO ;
 GRANT  TO ;
 GRANT  TO ;
 GRANT  TO ;
 GRANT  TO ;
 GRANT  TO ;
 GRANT  TO  GRANTED BY postgres;
 GRANT  TO ;
 GRANT  TO ;

 Doing a pg_dumpall -g on the database produces the same result.

Well then I don't presume this is a pg_upgrade issue.

Inspect your pg_auth_members catalog for entries referring to rows
absent from pg_authid.

Did someone manually remove rows from pg_authid?

 I am pretty sure this is catalog corruption.

 Can anyone else confirm and/or suggest a recommended fix?

 --
 Melvin Davidson


-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_upgrade problem

[Melvin Davidson]

Using pg_upgrade in 9.4 CentOS release 6.6 (Final) (from PostgreSQL 9.1.15)
it fails when GRANTING permits to roles.


Checking pg_upgrade_dump_globals.sql, I see the point of failure is caused
by the -- Role memberships section.

In there I see the following troublesome lines.
-- Role memberships
...
...

GRANT supers TO pgpoolad GRANTED BY postgres;
GRANT  TO ;
GRANT  TO ;
GRANT  TO ;
GRANT  TO ;
GRANT  TO ;
GRANT  TO ;
GRANT  TO ;
GRANT  TO ;
GRANT  TO  GRANTED BY postgres;
GRANT  TO ;
GRANT  TO ;

Doing a pg_dumpall -g on the database produces the same result.

I am pretty sure this is catalog corruption.

Can anyone else confirm and/or suggest a recommended fix?


-- 
*Melvin Davidson*

Re: [GENERAL] pg_upgrade problem

[Melvin Davidson]

Yup, that looks like the problem.
Doing:
postgres=# SELECT * FROM pg_auth_members
postgres-# WHERE roleid NOT IN (SELECT oid FROM pg_authid);

Yields, 11 Rows, so for sure someone must have been messing around. Thanks.


On Tue, Jun 16, 2015 at 11:58 AM, Jerry Sievers gsiever...@comcast.net
wrote:

 Melvin Davidson melvin6...@gmail.com writes:

  Using pg_upgrade in 9.4 CentOS release 6.6 (Final) (from PostgreSQL
 9.1.15) it fails when GRANTING permits to roles.
 
  Checking pg_upgrade_dump_globals.sql, I see the point of failure is
 caused by the -- Role memberships section.
 
  In there I see the following troublesome lines.
  -- Role memberships
  ...
  ...
  
  GRANT supers TO pgpoolad GRANTED BY postgres;
  GRANT  TO ;
  GRANT  TO ;
  GRANT  TO ;
  GRANT  TO ;
  GRANT  TO ;
  GRANT  TO ;
  GRANT  TO ;
  GRANT  TO ;
  GRANT  TO  GRANTED BY postgres;
  GRANT  TO ;
  GRANT  TO ;
 
  Doing a pg_dumpall -g on the database produces the same result.

 Well then I don't presume this is a pg_upgrade issue.

 Inspect your pg_auth_members catalog for entries referring to rows
 absent from pg_authid.

 Did someone manually remove rows from pg_authid?

  I am pretty sure this is catalog corruption.
 
  Can anyone else confirm and/or suggest a recommended fix?
 
  --
  Melvin Davidson
 

 --
 Jerry Sievers
 Postgres DBA/Development Consulting
 e: postgres.consult...@comcast.net
 p: 312.241.7800




-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

[Stephen Frost]

Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
 On Mon, Mar  9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote:
  On Fri, Mar  6, 2015 at 06:10:15PM -0500, Stephen Frost wrote:
   The first is required or anyone who has done that will get the funny
   error that started this thread and things won't work anyway, but I
   believe the latter is also necessary to patch and back-patch as it could
   lead to data loss.  It's not a high potential as, hopefully, people will
   check first, but I can imagine a hosting provider or environments where
   there are lots of independent clusters not catching this issue in their
   testing, only to discover someone set their database to 'datallowconn =
   false' for whatever reason and now that database is gone...
  
  Agreed. I will work on a patch for this.
 
 Attached is a patch that implements this, and it should be backpatch to
 all versions.

Excellent and agreed.  Just looked through the patch and didn't do a
full review, but it looks good to me.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

[Bruce Momjian]

On Fri, May 15, 2015 at 10:49:43AM -0400, Stephen Frost wrote:
 Bruce,
 
 * Bruce Momjian (br...@momjian.us) wrote:
  On Mon, Mar  9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote:
   On Fri, Mar  6, 2015 at 06:10:15PM -0500, Stephen Frost wrote:
The first is required or anyone who has done that will get the funny
error that started this thread and things won't work anyway, but I
believe the latter is also necessary to patch and back-patch as it could
lead to data loss.  It's not a high potential as, hopefully, people will
check first, but I can imagine a hosting provider or environments where
there are lots of independent clusters not catching this issue in their
testing, only to discover someone set their database to 'datallowconn =
false' for whatever reason and now that database is gone...
   
   Agreed. I will work on a patch for this.
  
  Attached is a patch that implements this, and it should be backpatch to
  all versions.
 
 Excellent and agreed.  Just looked through the patch and didn't do a
 full review, but it looks good to me.

OK, thanks.  I will apply it all branches later today as it is a data
loss bug.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

[Bruce Momjian]

On Fri, May 15, 2015 at 10:51:15AM -0400, Bruce Momjian wrote:
 On Fri, May 15, 2015 at 10:49:43AM -0400, Stephen Frost wrote:
  Bruce,
  
  * Bruce Momjian (br...@momjian.us) wrote:
   On Mon, Mar  9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote:
On Fri, Mar  6, 2015 at 06:10:15PM -0500, Stephen Frost wrote:
 The first is required or anyone who has done that will get the funny
 error that started this thread and things won't work anyway, but I
 believe the latter is also necessary to patch and back-patch as it 
 could
 lead to data loss.  It's not a high potential as, hopefully, people 
 will
 check first, but I can imagine a hosting provider or environments 
 where
 there are lots of independent clusters not catching this issue in 
 their
 testing, only to discover someone set their database to 'datallowconn 
 =
 false' for whatever reason and now that database is gone...

Agreed. I will work on a patch for this.
   
   Attached is a patch that implements this, and it should be backpatch to
   all versions.
  
  Excellent and agreed.  Just looked through the patch and didn't do a
  full review, but it looks good to me.
 
 OK, thanks.  I will apply it all branches later today as it is a data
 loss bug.

Patch applied back through 9.0.  Thanks for the report and analysis.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

[Bruce Momjian]

On Mon, Mar  9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote:
 On Fri, Mar  6, 2015 at 06:10:15PM -0500, Stephen Frost wrote:
  Technically, there haven't been any complaints about either pg_dumpall's
  behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade
  scripts would happily remove any databases which were marked as
  'datallowconn = false' and that scares the daylights out of me.  To that
  end, I'd suggest patching (and back-patching) pg_upgrade to check early
  on that:
  
  template0 is set to 'datallowconn = false'
  
  AND
  
  all databases except template0 are set to 'datallowconn = true'
  
  The first is required or anyone who has done that will get the funny
  error that started this thread and things won't work anyway, but I
  believe the latter is also necessary to patch and back-patch as it could
  lead to data loss.  It's not a high potential as, hopefully, people will
  check first, but I can imagine a hosting provider or environments where
  there are lots of independent clusters not catching this issue in their
  testing, only to discover someone set their database to 'datallowconn =
  false' for whatever reason and now that database is gone...
 
 Agreed. I will work on a patch for this.

Attached is a patch that implements this, and it should be backpatch to
all versions.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
new file mode 100644
index be66b24..5eaa67b
*** a/src/bin/pg_upgrade/check.c
--- b/src/bin/pg_upgrade/check.c
*** static void check_databases_are_compatib
*** 19,24 
--- 19,25 
  static void check_locale_and_encoding(DbInfo *olddb, DbInfo *newdb);
  static bool equivalent_locale(int category, const char *loca, const char *locb);
  static void check_is_install_user(ClusterInfo *cluster);
+ static void check_proper_datallowconn(ClusterInfo *cluster);
  static void check_for_prepared_transactions(ClusterInfo *cluster);
  static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
  static void check_for_reg_data_type_usage(ClusterInfo *cluster);
*** check_and_dump_old_cluster(bool live_che
*** 93,98 
--- 94,100 
  	 * Check for various failure cases
  	 */
  	check_is_install_user(old_cluster);
+ 	check_proper_datallowconn(old_cluster);
  	check_for_prepared_transactions(old_cluster);
  	check_for_reg_data_type_usage(old_cluster);
  	check_for_isn_and_int8_passing_mismatch(old_cluster);
*** check_is_install_user(ClusterInfo *clust
*** 640,645 
--- 642,699 
  
  	check_ok();
  }
+ 
+ 
+ static void
+ check_proper_datallowconn(ClusterInfo *cluster)
+ {
+ 	int			dbnum;
+ 	PGconn	   *conn_template1;
+ 	PGresult   *dbres;
+ 	int			ntups;
+ 	int			i_datname;
+ 	int			i_datallowconn;
+ 
+ 	prep_status(Checking for proper database connection permissions);
+ 
+ 	conn_template1 = connectToServer(cluster, template1);
+ 
+ 	/* get database names */
+ 	dbres = executeQueryOrDie(conn_template1,
+ 			  SELECT	datname, datallowconn 
+ 			  FROM	pg_catalog.pg_database);
+ 
+ 	i_datname = PQfnumber(dbres, datname);
+ 	i_datallowconn = PQfnumber(dbres, datallowconn);
+ 
+ 	ntups = PQntuples(dbres);
+ 	for (dbnum = 0; dbnum  ntups; dbnum++)
+ 	{
+ 		char	   *datname = PQgetvalue(dbres, dbnum, i_datname);
+ 		char	   *datallowconn = PQgetvalue(dbres, dbnum, i_datallowconn);
+ 
+ 		if (strcmp(datname, template0) == 0)
+ 		{
+ 			/* avoid restore failure when pg_dumpall tries to create template0 */
+ 			if (strcmp(datallowconn, t) == 0)
+ pg_fatal(template0 must not allow connections,\n
+ 		 i.e. its pg_database.datallowconn must be false\n);
+ 		}
+ 		else
+ 		{
+ 			/* avoid datallowconn == false databases from being skipped on restore */
+ 			if (strcmp(datallowconn, f) == 0)
+ pg_fatal(All non-template0 databases must allow connections,\n
+ 		 i.e. their pg_database.datallowconn must be true\n);
+ 		}
+ 	}
+ 
+ 	PQclear(dbres);
+ 
+ 	PQfinish(conn_template1);
+ 
+ 	check_ok();
+ }
  
  
  /*

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

[Bruce Momjian]

On Fri, Mar  6, 2015 at 06:10:15PM -0500, Stephen Frost wrote:
 Technically, there haven't been any complaints about either pg_dumpall's
 behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade
 scripts would happily remove any databases which were marked as
 'datallowconn = false' and that scares the daylights out of me.  To that
 end, I'd suggest patching (and back-patching) pg_upgrade to check early
 on that:
 
 template0 is set to 'datallowconn = false'
 
 AND
 
 all databases except template0 are set to 'datallowconn = true'
 
 The first is required or anyone who has done that will get the funny
 error that started this thread and things won't work anyway, but I
 believe the latter is also necessary to patch and back-patch as it could
 lead to data loss.  It's not a high potential as, hopefully, people will
 check first, but I can imagine a hosting provider or environments where
 there are lots of independent clusters not catching this issue in their
 testing, only to discover someone set their database to 'datallowconn =
 false' for whatever reason and now that database is gone...

Agreed. I will work on a patch for this.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

[Bruce Momjian]

On Mon, Mar  9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote:
 On Fri, Mar  6, 2015 at 06:10:15PM -0500, Stephen Frost wrote:
  Technically, there haven't been any complaints about either pg_dumpall's
  behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade
  scripts would happily remove any databases which were marked as
  'datallowconn = false' and that scares the daylights out of me.  To that
  end, I'd suggest patching (and back-patching) pg_upgrade to check early
  on that:
  
  template0 is set to 'datallowconn = false'
  
  AND
  
  all databases except template0 are set to 'datallowconn = true'
  
  The first is required or anyone who has done that will get the funny
  error that started this thread and things won't work anyway, but I
  believe the latter is also necessary to patch and back-patch as it could
  lead to data loss.  It's not a high potential as, hopefully, people will
  check first, but I can imagine a hosting provider or environments where
  there are lots of independent clusters not catching this issue in their
  testing, only to discover someone set their database to 'datallowconn =
  false' for whatever reason and now that database is gone...
 
 Agreed. I will work on a patch for this.

Oh, also, thanks for the analysis on this --- you are spot-on.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

[Stephen Frost]

* Tom Lane (t...@sss.pgh.pa.us) wrote:
 Stephen Frost sfr...@snowman.net writes:
  * Tom Lane (t...@sss.pgh.pa.us) wrote:
  Perhaps pg_upgrade should deliberately ignore template0 regardless of
  datallowconn?  And/or we should hard-wire that into pg_dumpall?
 
  My thinking would be that pg_dumpall should be hard-wired for template0
  (just like it is for template1..) and that we should *not* be excluding
  databases that are marked as datallowconn = false..  That said, it's not
  clear to me what to do there instead.  Maybe throw an error or a
  warning?  The point of pg_dumpall is to dump *all* the databases and at
  least the manpage doesn't appear to say anything about but ignores
  databases with datallowconn = false.
 
 I think pg_upgrade and pg_dumpall may be two different use-cases.

Perhaps..

 pg_upgrade should definitely throw a hard error if there are any
 non-template0 databases that it can't connect to, because the alternative
 is losing such databases during the upgrade.  I'm not sure that the
 argument is so black-and-white for pg_dumpall, though.  Nobody's ever
 complained about it skipping unconnectable databases, and that behavior
 has been there since we invented datallowconn (cf commit 2cf48ca04bf599).

Technically, there haven't been any complaints about either pg_dumpall's
behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade
scripts would happily remove any databases which were marked as
'datallowconn = false' and that scares the daylights out of me.  To that
end, I'd suggest patching (and back-patching) pg_upgrade to check early
on that:

template0 is set to 'datallowconn = false'

AND

all databases except template0 are set to 'datallowconn = true'

The first is required or anyone who has done that will get the funny
error that started this thread and things won't work anyway, but I
believe the latter is also necessary to patch and back-patch as it could
lead to data loss.  It's not a high potential as, hopefully, people will
check first, but I can imagine a hosting provider or environments where
there are lots of independent clusters not catching this issue in their
testing, only to discover someone set their database to 'datallowconn =
false' for whatever reason and now that database is gone...

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

[Jerry Sievers]

Stephen Frost sfr...@snowman.net writes:

 * Tom Lane (t...@sss.pgh.pa.us) wrote:

 Stephen Frost sfr...@snowman.net writes:
  * Tom Lane (t...@sss.pgh.pa.us) wrote:
  Perhaps pg_upgrade should deliberately ignore template0 regardless of
  datallowconn?  And/or we should hard-wire that into pg_dumpall?
 
  My thinking would be that pg_dumpall should be hard-wired for template0
  (just like it is for template1..) and that we should *not* be excluding
  databases that are marked as datallowconn = false..  That said, it's not
  clear to me what to do there instead.  Maybe throw an error or a
  warning?  The point of pg_dumpall is to dump *all* the databases and at
  least the manpage doesn't appear to say anything about but ignores
  databases with datallowconn = false.
 
 I think pg_upgrade and pg_dumpall may be two different use-cases.

 Perhaps..

 pg_upgrade should definitely throw a hard error if there are any
 non-template0 databases that it can't connect to, because the alternative
 is losing such databases during the upgrade.  I'm not sure that the
 argument is so black-and-white for pg_dumpall, though.  Nobody's ever
 complained about it skipping unconnectable databases, and that behavior
 has been there since we invented datallowconn (cf commit 2cf48ca04bf599).

 Technically, there haven't been any complaints about either pg_dumpall's
 behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade
 scripts would happily remove any databases which were marked as
 'datallowconn = false' and that scares the daylights out of me.  To that
 end, I'd suggest patching (and back-patching) pg_upgrade to check early
 on that:

 template0 is set to 'datallowconn = false'

 AND

 all databases except template0 are set to 'datallowconn = true'

 The first is required or anyone who has done that will get the funny
 error that started this thread and things won't work anyway, but I
 believe the latter is also necessary to patch and back-patch as it could
 lead to data loss.  It's not a high potential as, hopefully, people will
 check first, but I can imagine a hosting provider or environments where
 there are lots of independent clusters not catching this issue in their
 testing, only to discover someone set their database to 'datallowconn =
 false' for whatever reason and now that database is gone...

Good thinking!

Not lately but several times in the past I've done just that to lock
down some odd DB that  was to be untouched but preserved  nonetheless.

   Thanks!

   Stephen

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

[Adrian Klaver]

On 03/06/2015 10:11 AM, Matt Landry wrote:

Attempting to upgrade a large (3TB) postgressql database from 9.3 to
9.4 on Ubuntu 14.04 LTS, but the process fails fairly early on. The
error message instructs me to look at the last few lines of
pg_upgrade_utility.log for more info, and the last two lines there (the
only ones that don't succeed) are:

CREATE DATABASE template0 WITH TEMPLATE = template0 OWNER = postgres;
psql:pg_upgrade_dump_globals.sql:44: ERROR:  database template0
already exists


Looks to me like someone created their own template0 database in the 
original cluster. You might do in psql a \l in the original cluster to 
see if there is more than one template0.




For reference, the pg_upgrade command as I'm running it is:

/usr/lib/postgresql/9.4/bin/pg_upgrade -k -b /usr/lib/postgresql/9.3/bin
-B /usr/lib/postgresql/9.4/bin -d /DB/postgres/data/9.3/main -D
/DB/postgres/data/9.4/main -o '-c
config_file=/etc/postgresql/9.3/main/postgresql.conf' -O '-c
config_file=/etc/postgresql/9.4/main/postgresql.conf'

Searching google for useful advice yields...little. (Mostly articles
about problems upgrading from 8.4 to 9.2, whose solutions -- when
they're described -- don't help here. Also, there are a number of
sources saying I should just do a pg_dumpall and then a
pg_restore...which would be fine, probably, if I could afford to take my
production database offline for a week.)

I do have a pg_dumpall backup done, and at the moment I'm doing all this
on a test instance, so I can afford to experiment without risking data
corruption or excessive downtime on the production server, but the whole
point of the experimenting is to come up with a procedure that will work
in production when it's time to do it there. Dump-and-restore definitely
does not meet that requirement, even if it does eventually recreate a
working database.

Any ideas? I'm sure I'm missing something obvious, here, but I can't
seem to find any reference that tells me what it would be.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

[Adrian Klaver]

On 03/06/2015 10:35 AM, Stephen Frost wrote:

Adrian,

* Adrian Klaver (adrian.kla...@aklaver.com) wrote:

On 03/06/2015 10:11 AM, Matt Landry wrote:

Attempting to upgrade a large (3TB) postgressql database from 9.3 to
9.4 on Ubuntu 14.04 LTS, but the process fails fairly early on. The
error message instructs me to look at the last few lines of
pg_upgrade_utility.log for more info, and the last two lines there (the
only ones that don't succeed) are:

CREATE DATABASE template0 WITH TEMPLATE = template0 OWNER = postgres;
psql:pg_upgrade_dump_globals.sql:44: ERROR:  database template0
already exists


Looks to me like someone created their own template0 database in the
original cluster. You might do in psql a \l in the original cluster
to see if there is more than one template0.


template0 is created during initdb and it shouldn't be possible to have
more than one database named 'template0' in a given cluster (though if
there is, that could certainly be a problem...).


Agreed, I am just trying to figure out how you get:

CREATE DATABASE template0 WITH TEMPLATE = template0 ..

Seems to be a snake eating its tail:)



Now, if someone renamed the original template0 and created a new one,
that could be an issue but I think that's more because the newer
template0 would be dumped by pg_dumpall as it'd be set to allow
connections (which is the default).

If I'm reading the code correctly, I think we have an entirely different
issue here which is that databases with 'datallowconn' set to false
won't be included in the upgrade and, while all the files will be there,
the catalog won't be.  That's a serious problem as people do actually
modify that flag in some environments and if it happens to be set
incorrectly when the pg_dumpall from pg_upgrade runs then those
databases will disappear across the upgrade..  I've not checked to see
if something else in the pg_upgrade process will catch this, but if not,
we should definitely add something.

Thanks,

Stephen




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general