pg_dump / pg_restore version confusion

[Zwettler Markus (OIZ)]

Hi,

An external supplier had a postgres v9.5 database which he dumped with a 
pg_dump v12 client in custom format using PgAdmin4.

Would a pg_restore with a v12 client into a postgres v9.6 database work and be 
officially supported?

Thanks, Markus

Re: pg_dump / pg_restore version confusion

[Bruce Momjian]

On Wed, Jul 29, 2020 at 10:53:34AM +, Zwettler Markus (OIZ) wrote:
> Hi,
> 
>  
> 
> An external supplier had a postgres v9.5 database which he dumped with a
> pg_dump v12 client in custom format using PgAdmin4.
> 
>  
> 
> Would a pg_restore with a v12 client into a postgres v9.6 database work and be
> officially supported?

Yes, you can always use a newer pg_dump on an older database, though the
reverse is not recommended.  In fact, if you are upgrading to PG 12, it
is recommended to use pg_dump v12 to dump a Postgres database from an
earlier version.

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

  The usefulness of a cup is in its emptiness, Bruce Lee

AW: pg_dump / pg_restore version confusion

[Zwettler Markus (OIZ)]

And I can also do this restore:


Would a pg_restore with a v12 client into a postgres v9.6 database work and be 
officially supported?


-Markus



> -Ursprüngliche Nachricht-
> Von: Bruce Momjian 
> Gesendet: Mittwoch, 29. Juli 2020 13:49
> An: Zwettler Markus (OIZ) 
> Cc: pgsql-general 
> Betreff: Re: pg_dump / pg_restore version confusion
> 
> On Wed, Jul 29, 2020 at 10:53:34AM +, Zwettler Markus (OIZ) wrote:
> > Hi,
> >
> >
> >
> > An external supplier had a postgres v9.5 database which he dumped with
> > a pg_dump v12 client in custom format using PgAdmin4.
> >
> >
> >
> > Would a pg_restore with a v12 client into a postgres v9.6 database
> > work and be officially supported?
> 
> Yes, you can always use a newer pg_dump on an older database, though the
> reverse is not recommended.  In fact, if you are upgrading to PG 12, it is
> recommended to use pg_dump v12 to dump a Postgres database from an earlier
> version.
> 
> --
>   Bruce Momjian  https://momjian.us
>   EnterpriseDB https://enterprisedb.com
> 
>   The usefulness of a cup is in its emptiness, Bruce Lee

Re: pg_dump / pg_restore version confusion

[Bruce Momjian]

On Wed, Jul 29, 2020 at 12:33:56PM +, Zwettler Markus (OIZ) wrote:
> And I can also do this restore:
> 
> 
> Would a pg_restore with a v12 client into a postgres v9.6 database work and 
> be officially supported?
> 

Uh, good question.  You should still use the version of pg_restore that
you are loading _into_, not what you dumped from.

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

  The usefulness of a cup is in its emptiness, Bruce Lee

AW: pg_dump / pg_restore version confusion

[Zwettler Markus (OIZ)]

I cannot use pg_restore v9.6 on a pg_dump v12 because otherwise: pg_restore: 
[archiver] unsupported version (1.14) in file header

The external supplier did PG v9.5 database + pg_dump v12. 
I would have to do pg_restore v12 (as of pg_dump v12) into my PG v9.6. 
The version chain would be PG v9.5 => pg_dump v12 => pg_restore v12 => PG v9.6.

That's why my question has been: would the whole chain work and is it supported?



> -Ursprüngliche Nachricht-
> Von: Bruce Momjian 
> Gesendet: Mittwoch, 29. Juli 2020 14:55
> An: Zwettler Markus (OIZ) 
> Cc: pgsql-general 
> Betreff: Re: pg_dump / pg_restore version confusion
> 
> On Wed, Jul 29, 2020 at 12:33:56PM +, Zwettler Markus (OIZ) wrote:
> > And I can also do this restore:
> >
> > 
> > Would a pg_restore with a v12 client into a postgres v9.6 database work and 
> > be
> officially supported?
> > 
> 
> Uh, good question.  You should still use the version of pg_restore that you 
> are
> loading _into_, not what you dumped from.
> 
> --
>   Bruce Momjian  https://momjian.us
>   EnterpriseDB https://enterprisedb.com
> 
>   The usefulness of a cup is in its emptiness, Bruce Lee

Re: pg_dump / pg_restore version confusion

[Adrian Klaver]

On 7/29/20 3:53 AM, Zwettler Markus (OIZ) wrote:

Hi,

An external supplier had a postgres v9.5 database which he dumped with a 
pg_dump v12 client in custom format using PgAdmin4.


Would a pg_restore with a v12 client into a postgres v9.6 database work 
and be officially supported?


The best that could be said is it may work.

Officially supported :

https://www.postgresql.org/docs/12/app-pgdump.html

"Because pg_dump is used to transfer data to newer versions of 
PostgreSQL, the output of pg_dump can be expected to load into 
PostgreSQL server versions newer than pg_dump's version. pg_dump can 
also dump from PostgreSQL servers older than its own version. 
(Currently, servers back to version 8.0 are supported.) However, pg_dump 
cannot dump from PostgreSQL servers newer than its own major version; it 
will refuse to even try, rather than risk making an invalid dump. Also, 
it is not guaranteed that pg_dump's output can be loaded into a server 
of an older major version — not even if the dump was taken from a server 
of that version. Loading a dump file into an older server may require 
manual editing of the dump file to remove syntax not understood by the 
older server. "







Thanks, Markus




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

Re: AW: pg_dump / pg_restore version confusion

[Adrian Klaver]

On 7/29/20 6:38 AM, Zwettler Markus (OIZ) wrote:

I cannot use pg_restore v9.6 on a pg_dump v12 because otherwise: pg_restore: 
[archiver] unsupported version (1.14) in file header

The external supplier did PG v9.5 database + pg_dump v12.
I would have to do pg_restore v12 (as of pg_dump v12) into my PG v9.6.
The version chain would be PG v9.5 => pg_dump v12 => pg_restore v12 => PG v9.6.

That's why my question has been: would the whole chain work and is it supported?


That is one way of attempting it, though it is not supported. You could 
also try pg_restore v12 on pg_dump file v9.6 to Postgres v9.6 database 
as well. With the caveats that I quoted in my previous post. Ideally you 
would have the supplier redo the dump of the database with a 9.6 version 
of pg_dump.







-Ursprüngliche Nachricht-
Von: Bruce Momjian 
Gesendet: Mittwoch, 29. Juli 2020 14:55
An: Zwettler Markus (OIZ) 
Cc: pgsql-general 
Betreff: Re: pg_dump / pg_restore version confusion

On Wed, Jul 29, 2020 at 12:33:56PM +, Zwettler Markus (OIZ) wrote:

And I can also do this restore:


Would a pg_restore with a v12 client into a postgres v9.6 database work and be

officially supported?




Uh, good question.  You should still use the version of pg_restore that you are
loading _into_, not what you dumped from.

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

   The usefulness of a cup is in its emptiness, Bruce Lee







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

Re: AW: pg_dump / pg_restore version confusion

[Tom Lane]

"Zwettler Markus (OIZ)"  writes:
> And I can also do this restore:
> 
> Would a pg_restore with a v12 client into a postgres v9.6 database work and 
> be officially supported?
> 

It might work, but it's not completely guaranteed.  Sometimes a new
pg_dump will use DDL syntax that doesn't exist in older versions,
even to describe constructs that do exist in older versions.  This
isn't really common though.

I'd say try it and see.  If the dump loads into 9.6 without errors then
it's probably fine (but you could dump again, with the same pg_dump
version, and diff the outputs to confirm).

regards, tom lane

Re: Out of memory with "create extension postgis"

[Daniel Westermann (DWE)]

 "Daniel Westermann (DWE)"  writes:
> The process eats all the available memory and finally dies:
> # create extension postgis;
> ERROR:  out of memory
> DETAIL:  Failed on request of size 8265691 in memory context 
> "PortalContext".
> Time: 773569.877 ms (12:53.570)

>>> There is nothing in the log file except these (not from today, but the 
>>> messages are always the same):
>>> 2020-07-03 16:52:16 CEST 53617 LOG:  server process (PID 54070) was 
>>> terminated by signal 9: Killed

>>A process that was killed by the OOM killer would not have managed to
>>produce an "out of memory" ERROR report, so these two are different
>>symptoms.  You need to reproduce the first case, or you won't have
>>any luck setting an error breakpoint either.

>You're right, that was before we adjusted the oom behavior. Will get back once 
>I have more information.

So this is what we got today. In the log file there is this:

2020-07-29 16:33:23 CEST 101995 ERROR:  out of memory
2020-07-29 16:33:23 CEST 101995 DETAIL:  Failed on request of size 8265691 in 
memory context "PortalContext".
2020-07-29 16:33:23 CEST 101995 STATEMENT:  create extension postgis;
2020-07-29 16:35:00 CEST 106695 LOG:  disconnection: session time: 0:04:45.200 
user=monitor database=pcl_l800 host=127.0.0.1 port=52160
2020-07-29 16:35:00 CEST 107063 LOG:  connection received: host=127.0.0.1 
port=52212
2020-07-29 16:35:00 CEST 107063 LOG:  connection authorized: user=monitor 
database=pdb_l80_oiz application_name=pg_statsinfod
2020-07-29 16:35:00 CEST 53630 ALERT:  pg_statsinfo: memory swap size exceeds 
threshold in snapshot '2020-07-29 16:35:00' --- 17383584 KiB (threshold = 
100 KiB)
2020-07-29 16:35:15 CEST 107063 LOG:  disconnection: session time: 0:00:14.770 
user=monitor database=pdb_l80_oiz host=127.0.0.1 port=52212
2020-07-29 16:35:15 CEST 107269 LOG:  connection received: host=127.0.0.1 
port=52220

The GDB session shows this:
(gdb) set pagination off
(gdb) set logging file postgis.log
(gdb)
(gdb) set logging on
Copying output to postgis.log.
(gdb) b errfinish
Breakpoint 1 at 0x87e210: file elog.c, line 411.
(gdb) cont
Continuing.
Breakpoint 1, errfinish (dummy=dummy@entry=0) at elog.c:411
411     elog.c: No such file or directory.
(gdb) cont
Continuing.
Breakpoint 1, errfinish (dummy=dummy@entry=0) at elog.c:411
411     in elog.c
Missing separate debuginfos, use: debuginfo-install CGAL-4.7-1.rhel7.1.x86_64 
boost-date-time-1.53.0-27.el7.x86_64 boost-serialization-1.53.0-27.el7.x86_64 
boost-system-1.53.0-27.el7.x86_64 boost-thread-1.53.0-27.el7.x86_64 
gmp-6.0.0-15.el7.x86_64 jbigkit-libs-2.0-11.el7.x86_64 
json-c-0.11-4.el7_0.x86_64 libcurl-7.29.0-54.el7_7.2.x86_64 
libidn-1.28-4.el7.x86_64 libjpeg-turbo-1.2.90-8.el7.x86_64 
libssh2-1.8.0-3.el7.x86_64 libtiff-4.0.3-32.el7.x86_64 mpfr-3.1.1-4.el7.x86_64 
postgis30_12-3.0.1-5.rhel7.x86_64 protobuf-c-1.0.2-3.el7.x86_64 
sqlite33-libs-3.30.1-1.rhel7.x86_64
(gdb)
Continuing.


The GDB logfile shows this:
[root@tstm49012 ~]# cat postgis.log
Breakpoint 1 at 0x87e210: file elog.c, line 411.
Continuing.
Breakpoint 1, errfinish (dummy=dummy@entry=0) at elog.c:411
411     elog.c: No such file or directory.
Continuing.
Breakpoint 1, errfinish (dummy=dummy@entry=0) at elog.c:411
411     in elog.c
Missing separate debuginfos, use: debuginfo-install CGAL-4.7-1.rhel7.1.x86_64 
boost-date-time-1.53.0-27.el7.x86_64 boost-serialization-1.53.0-27.el7.x86_64 
boost-system-1.53.0-27.el7.x86_64 boost-thread-1.53.0-27.el7.x86_64 
gmp-6.0.0-15.el7.x86_64 jbigkit-libs-2.0-11.el7.x86_64 
json-c-0.11-4.el7_0.x86_64 libcurl-7.29.0-54.el7_7.2.x86_64 
libidn-1.28-4.el7.x86_64 libjpeg-turbo-1.2.90-8.el7.x86_64 
libssh2-1.8.0-3.el7.x86_64 libtiff-4.0.3-32.el7.x86_64 mpfr-3.1.1-4.el7.x86_64 
postgis30_12-3.0.1-5.rhel7.x86_64 protobuf-c-1.0.2-3.el7.x86_64 
sqlite33-libs-3.30.1-1.rhel7.x86_64
Continuing.
Program received signal SIGINT, Interrupt.
0x7f94f4af2e93 in __epoll_wait_nocancel () from /lib64/libc.so.6
Detaching from program: /usr/pgsql-12/bin/postgres, process 101995
[root@tstm49012 ~]#

Is that of any help?

Regards
Daniel

Re: Out of memory with "create extension postgis"

[Tom Lane]

"Daniel Westermann (DWE)"  writes:
> So this is what we got today. In the log file there is this:

> 2020-07-29 16:33:23 CEST 101995 ERROR:  out of memory
> 2020-07-29 16:33:23 CEST 101995 DETAIL:  Failed on request of size 8265691 in 
> memory context "PortalContext".
> 2020-07-29 16:33:23 CEST 101995 STATEMENT:  create extension postgis;

Is there not a dump of memory context sizes just before the "ERROR: out of
memory" line?  It should look something like

TopMemoryContext: 68720 total in 5 blocks; 17040 free (15 chunks); 51680 used
  MessageContext: 8192 total in 1 blocks; 6880 free (1 chunks); 1312 used
...
  ErrorContext: 8192 total in 1 blocks; 7928 free (3 chunks); 264 used
Grand total: 1063328 bytes in 190 blocks; 312376 free (159 chunks); 750952 used

(this taken from an idle backend, so numbers from a backend that's hit
OOM would be a lot larger).  If you don't see that then you must be
using some logging mechanism that fails to capture the postmaster's
stderr output, such as syslog.  If your postmaster start script doesn't
actually send stderr to /dev/null, you might find the context map in some
other log file.

> The GDB session shows this:

Umm ... you didn't issue a "bt" when you got to errfinish, so there's
no useful info here.

> Missing separate debuginfos, use: debuginfo-install CGAL-4.7-1.rhel7.1.x86_64 
> boost-date-time-1.53.0-27.el7.x86_64 boost-serialization-1.53.0-27.el7.x86_64 
> boost-system-1.53.0-27.el7.x86_64 boost-thread-1.53.0-27.el7.x86_64 
> gmp-6.0.0-15.el7.x86_64 jbigkit-libs-2.0-11.el7.x86_64 
> json-c-0.11-4.el7_0.x86_64 libcurl-7.29.0-54.el7_7.2.x86_64 
> libidn-1.28-4.el7.x86_64 libjpeg-turbo-1.2.90-8.el7.x86_64 
> libssh2-1.8.0-3.el7.x86_64 libtiff-4.0.3-32.el7.x86_64 
> mpfr-3.1.1-4.el7.x86_64 postgis30_12-3.0.1-5.rhel7.x86_64 
> protobuf-c-1.0.2-3.el7.x86_64 sqlite33-libs-3.30.1-1.rhel7.x86_64

This is a little weird and scary; I would not expect most of those
libraries to have gotten linked into a Postgres backend.  What
extensions are you using?  (And what the devil would any of them
want with sqlite or libcurl?  boost-thread is even scarier, because
we absolutely do not support multithreading in a backend process.)

regards, tom lane

Re: How to create function returning numeric from string containing percent character

[Ben Madin]

I suspect it will depend on your localisation whether you need to account
for different decimal separators, but just in case:

SELECT replace(substring('-1,2%' from '^-?\d*[.,]?\d*'), ',', '.')::numeric;

On Wed, 22 Jul 2020 at 18:50, Andrus  wrote:

> val function should return numeric value from string up to first non-digit
> character, considering first decimal point also:
>
> val('1,2TEST')  should return 1.2
> val('1,2,3')  should return 1.2
> val('-1,2,3')  should return -1.2
>
> I tried
>
> CREATE OR REPLACE FUNCTION public.VAL(value text)
>   RETURNS numeric AS
> $BODY$
> SELECT coalesce(nullif('0'||substring(Translate($1,',','.'),
> '^-?[0-9]+\.?[0-9]*$'),''),'0')::numeric;
> $BODY$ language sql immutable;
>
> but if string contains % character,
>
> select val('1,2%')
>
> returns 0.
>
> How to force it to return 1.2 ?
>
> It should work starting from Postgres 9.0
>
> Posted also in
>
>
> https://stackoverflow.com/questions/63032072/how-to-create-function-returning-value-up-to-first-non-digit-decimal-charcater#63032126
>
> Andrus.
>
>
>
>

-- 

[image: Ausvet Logo] 

Dr Ben Madin

BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Mobile:
+61 448 887 220 <+61448887220>
E-mail:
b...@ausvet.com.au
Website:
www.ausvet.com.au
Skype: benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia

Track pgsql steps

[Olivier Leprêtre]

Hi,



I have a rather long pgsql procedure and I would like to detect which step
is currently executing (subscript 1,2,3…). Due to transaction isolation,
it’s not possible to make it write in a table or get nexval from a sequence
because values become available only after the complete end of the
procedure.



Do you see any solution in this purpose ?



Thanks,



Olivier







--
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel 
antivirus Avast.
https://www.avast.com/antivirus

Re: Track pgsql steps

[Adrian Klaver]

On 7/29/20 8:44 AM, Olivier Leprêtre wrote:

Hi,

I have a rather long pgsql procedure and I would like to detect which 
step is currently executing (subscript 1,2,3…). Due to transaction 
isolation, it’s not possible to make it write in a table or get nexval 
from a sequence because values become available only after the complete 
end of the procedure.


Do you see any solution in this purpose ?


RAISE NOTICE?:

https://www.postgresql.org/docs/12/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE



Thanks,

Olivier


 
	Garanti sans virus. www.avast.com 
 



<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>



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

Transaction prevention

[Steve Baldwin]

Hi,

If I have a user that is restricted to select access (only) on a single
table, is there any way to prevent that user from starting a transaction?

The reason for the question is that the select-only user can block another
session trying to run an alter table on that table if the select-only
user runs their query in a transaction.

This happened to us recently where a migration script that was being run
during a ci deployment 'hung' until the select-only user happened to
disconnect. I'm wondering if we need to terminate any sessions from
select-only users at the start of the migration, or if there is a better
way.

Thanks,

Steve

Re: Transaction prevention

[Tom Lane]

Steve Baldwin  writes:
> If I have a user that is restricted to select access (only) on a single
> table, is there any way to prevent that user from starting a transaction?

No, but maybe setting statement_timeout and/or
idle_in_transaction_session_timeout for that user would be helpful
(cf ALTER USER ... SET ...).

regards, tom lane

Re: Transaction prevention

[Steve Baldwin]

Thanks Tom. The idle_in_transaction_session_timeout could work well, but it
seems to be just a default that can be overridden by a user post-login (or
am I missing something?). I'm thinking of setting lock_timeout as part of
the migration process so it will fail if it is unable to obtain a lock in a
'reasonable' amount of time. I wonder what other folks do?

Cheers,

Steve

On Thu, Jul 30, 2020 at 10:23 AM Tom Lane  wrote:

> Steve Baldwin  writes:
> > If I have a user that is restricted to select access (only) on a single
> > table, is there any way to prevent that user from starting a transaction?
>
> No, but maybe setting statement_timeout and/or
> idle_in_transaction_session_timeout for that user would be helpful
> (cf ALTER USER ... SET ...).
>
> regards, tom lane
>

Re: Transaction prevention

[Tom Lane]

Steve Baldwin  writes:
> Thanks Tom. The idle_in_transaction_session_timeout could work well, but it
> seems to be just a default that can be overridden by a user post-login (or
> am I missing something?).

It is that, but if you have an actively malicious user then you need to
keep them from issuing SQL directly at all.  There are far too many ways
to cause effective denial-of-service, eg a single query that runs
"forever".

> I'm thinking of setting lock_timeout as part of
> the migration process so it will fail if it is unable to obtain a lock in a
> 'reasonable' amount of time. I wonder what other folks do?

If you'd rather fail the migration process, sure.

regards, tom lane