Re: pg_dump save command in output

[Daniel Gustafsson]

> On 3 Oct 2021, at 18:56, Joao Miguel Ferreira 
>  wrote:

> is there a way to ask pg_dump to save a comment in the output file with the 
> full command it is executing ?

I think this idea has some merit.  Given the amount of switches for tailoring
the dump that pg_dump has there is a point to knowing just which ones were
used.  Connection level parameters should probably be scrubbed, but
object-level filtering etc could be handy.

Would you like to try and propose a patch for pg_dump to implement this?

--
Daniel Gustafsson   https://vmware.com/

Re: pg_dump save command in output

[Joao Miguel Ferreira]

On Wed, Oct 6, 2021 at 10:33 AM Daniel Gustafsson  wrote:

> > On 3 Oct 2021, at 18:56, Joao Miguel Ferreira <
> joao.miguel.c.ferre...@gmail.com> wrote:
>
> > is there a way to ask pg_dump to save a comment in the output file with
> the full command it is executing ?
>
> I think this idea has some merit.  Given the amount of switches for
> tailoring
> the dump that pg_dump has there is a point to knowing just which ones were
> used.  Connection level parameters should probably be scrubbed, but
> object-level filtering etc could be handy.
>
> Would you like to try and propose a patch for pg_dump to implement this?
>
Hi Daniel, thank you for the feedback!

I would like to try that yes, it would be a pleasure but I have zero
knowledge on the dev tools, setups and processes required for that.

If someone gives me some pointers I would love to try it. I'm a software
developer so I should be able to at least try.

Can you point me to some hands-on docs on setting up my env ?

I use Debian Linux 10 and 11 VMs, if that is relevant, I could copy a VM
for this purpose and start right away

Thank you
Joao (pg user for many many years)






>
> --
> Daniel Gustafsson   https://vmware.com/
>
>

how to merge two postgresql server instances into one

[Marco Lechner]

Hi,

how to merge two postgresql server instances into one? I have a postgresql-11 
and -12 instance on my server and want to upgrade both (pg_upgrade?) into one 
postgresql-14 instance.
The postgresql-14 instance does not exist yet (fresh install of postgresql-14 
server packages). Using pg_upgrade to upgrade the larger one and 
pg_dumpall/pg_restore for the smaller one? Or are there any other nice 
recommended workflows?

i.A. Dr. Marco Lechner
Leiter Fachgebiet RN 1 │ Head RN 1

--
Bundesamt für Strahlenschutz │ Federal Office for Radiation Protection
Koordination Notfallschutzsysteme │ Coordination Emergency Systems │ RN 1

Re: pg_dump save command in output

[Laurenz Albe]

On Wed, 2021-10-06 at 10:44 +0100, Joao Miguel Ferreira wrote:
> I would like to try that yes, it would be a pleasure but I have zero 
> knowledge on the dev tools, setups and processes required for that. 
> 
> If someone gives me some pointers I would love to try it. I'm a software 
> developer so I should be able to at least try.
> 
> Can you point me to some hands-on docs on setting up my env ?

Here is an introductory article from the Wiki:

https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: pg_dump save command in output

[Daniel Gustafsson]

> On 6 Oct 2021, at 12:22, Laurenz Albe  wrote:
> 
> On Wed, 2021-10-06 at 10:44 +0100, Joao Miguel Ferreira wrote:
>> I would like to try that yes, it would be a pleasure but I have zero 
>> knowledge on the dev tools, setups and processes required for that. 
>> 
>> If someone gives me some pointers I would love to try it. I'm a software 
>> developer so I should be able to at least try.
>> 
>> Can you point me to some hands-on docs on setting up my env ?
> 
> Here is an introductory article from the Wiki:
> 
> https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F

There is also the section in the docs for how to install a postgres instance
for source which will be handy:

https://www.postgresql.org/docs/devel/installation.html

For an environment to hack in I would recommend enabling debig and TAP style
tests, and personally I always install to a specific folder (see the section on
developer options):

./configure --enable-debug --enable-tap-tests --prefix=path/to/dev/install

Feel free to reach out here if you get stuck on anything, so we can make the
Wiki page better.

--
Daniel Gustafsson   https://vmware.com/

streaming replication different versions

[Marc Millas]

Hi,

on release 10,  I remember reading something like: streaming replication is
NOW upward compatible.
which could be understood as: its possible to have a master in rel 10 and a
slave in rel 11.

Can someone confirm (or not !) this ?

if true, is the following scheme possible:
a master and a slave in same rel (10/11/12/13/14?)
1) stopping the slave
2) upgrading the slave to rel+1
3) restarting the slave
4) stopping the master and promoting the slave
5) upgrading the ex master
6) restarting it as slave
bingo version upgrade without stopping production.

what about the time line of the upgraded slave (step 2 ) ??

thanks,



Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

Re: streaming replication different versions

[Thomas Kellerer]

Marc Millas schrieb am 06.10.2021 um 13:43:
> on release 10,  I remember reading something like: streaming replication is 
> NOW upward compatible.
> which could be understood as: its possible to have a master in rel 10 and a 
> slave in rel 11.

No, that's not possible.

For streaming replication both servers must run the same major versions.

Re: pg_dump save command in output

[Joao Miguel Ferreira]

On Wed, Oct 6, 2021 at 11:40 AM Daniel Gustafsson  wrote:

> > On 6 Oct 2021, at 12:22, Laurenz Albe  wrote:
> >
> > On Wed, 2021-10-06 at 10:44 +0100, Joao Miguel Ferreira wrote:
> >> I would like to try that yes, it would be a pleasure but I have zero
> knowledge on the dev tools, setups and processes required for that.
> >>
> >> If someone gives me some pointers I would love to try it. I'm a
> software developer so I should be able to at least try.
> >>
> >> Can you point me to some hands-on docs on setting up my env ?
> >
> > Here is an introductory article from the Wiki:
> >
> > https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F
>
> There is also the section in the docs for how to install a postgres
> instance
> for source which will be handy:
>
> https://www.postgresql.org/docs/devel/installation.html
>
> For an environment to hack in I would recommend enabling debig and TAP
> style
> tests, and personally I always install to a specific folder (see the
> section on
> developer options):
>
> ./configure --enable-debug --enable-tap-tests
> --prefix=path/to/dev/install
>

Hi again, thank you both for the guidance. I will start reading and setting
things up in the next few days


>
> Feel free to reach out here if you get stuck on anything, so we can make
> the
> Wiki page better.
>

sure


>
> --
> Daniel Gustafsson   https://vmware.com/
>
>

idle_in_transaction_session_timeout

[Philippe Doussot]

Hi,


Just want to say THANK YOU all (Robert Haas, Vik Fearing, ..) for these feature 
I just discover available since 9.6 !


although I am still in version 9.5


Philippe

Re: streaming replication different versions

[Guillaume Lelarge]

Le mer. 6 oct. 2021 à 13:46, Thomas Kellerer  a écrit :

> Marc Millas schrieb am 06.10.2021 um 13:43:
> > on release 10,  I remember reading something like: streaming replication
> is NOW upward compatible.
> > which could be understood as: its possible to have a master in rel 10
> and a slave in rel 11.
>
> No, that's not possible.
>
> For streaming replication both servers must run the same major versions.
>
>
You're right for physical replication. But it works for logical
replication, which was released in version 10.


-- 
Guillaume.

Re: [Ext:] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

[Allie Crawford]

Troy,
I am running into the same problem where I am not able to deploy the pgaudit 
parameter setting into the postgresql.conf file because every time I do that 
and restart the postgresql cluster, it  is just fail, I cannot get the pgaudit 
setting on the postgresql.conf file. Have you find a solution to this problem? 
Would you be kind enough to share with me the implementation of the solution to 
allow the postgresql.conf file to have the pgaudit parameter settings so that 
they can be applied to all the databases in the cluster?

Thank you in advance for your help on this.

Regards,
Allie Crawford

From: pgsql-general-ow...@postgresql.org  
on behalf of Troy Hardin 
Date: Wednesday, October 6, 2021 at 8:57 AM
To: pgsql-gene...@postgresql.org 
Subject: [Ext:] Configuration of pgaudit settings in postgreSQL.conf causes 
postgreSQL to fail to start
[External Email]

I support an existing product that utilizes postgreSQL in a Windows 
environment.  Recently we've been asked to make a series of security/auditing 
changes to the product that require pgaudit.  We built pgaudit.dll and have 
made the majority of the configuration changes and those seem to be working as 
designed.  The pgaudit extension does what it is being required to do, but we 
are running into issues with configuring some settings globally in the 
postgresql.conf.



The two lines we need to run in the .conf file that are causing postgresql to 
fail to start are, for example:



pgaudit.log = 'read, write, ddl, role'

pgaudit.log_level = 'log'



Putting either of these two lines in the .conf file cause it to fail to start.  
However, after starting postgreSQL, I can go to command line and run the SQL to 
set these and they run fine.  After running them, pgaudit performs as designed 
and configured so I believe we've compiled pgaudit.dll correctly.  At any rate, 
I'm at wits end right now and need to either fix the above or find someone with 
more db experience to assist in creating triggers that would run the SQL SET 
commands after startup.



Are there known reasons why I might be having this issue?  Anyone have 
experience getting past something similar?



v/r

Troy R. Hardin

Re: how to merge two postgresql server instances into one

[Adrian Klaver]

On 10/6/21 2:58 AM, Marco Lechner wrote:

Hi,

how to merge two postgresql server instances into one? I have a 
postgresql-11 and -12 instance on my server and want to upgrade both 
(pg_upgrade?) into one postgresql-14 instance.


The bigger issue will be the merge. Define what that means in this context?



The postgresql-14 instance does not exist yet (fresh install of 
postgresql-14 server packages). Using pg_upgrade to upgrade the larger 
one and pg_dumpall/pg_restore for the smaller one? Or are there any 
other nice recommended workflows?


i.A. Dr. Marco Lechner

Leiter Fachgebiet RN 1 │ Head RN 1

--

Bundesamt für Strahlenschutz │ Federal Office for Radiation Protection

Koordination Notfallschutzsysteme │ Coordination Emergency Systems │ RN 1




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

Re: streaming replication different versions

[Marc Millas]

it does work well for logical replication.
still its sad that it doesnt work for physical replication as, then, how to
ensure continuous  availability for generic apps ??

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Wed, Oct 6, 2021 at 5:03 PM Guillaume Lelarge 
wrote:

> Le mer. 6 oct. 2021 à 13:46, Thomas Kellerer  a écrit :
>
>> Marc Millas schrieb am 06.10.2021 um 13:43:
>> > on release 10,  I remember reading something like: streaming
>> replication is NOW upward compatible.
>> > which could be understood as: its possible to have a master in rel 10
>> and a slave in rel 11.
>>
>> No, that's not possible.
>>
>> For streaming replication both servers must run the same major versions.
>>
>>
> You're right for physical replication. But it works for logical
> replication, which was released in version 10.
>
>
> --
> Guillaume.
>

Re: [Ext:] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

[Troy Hardin]

The engineers rebuilt the pgaudit extension and the issue went away on our end.  Note that pgaudit is version specific and would need to be built for each version.  I'd suggest if you're having a similar issue to ours that you rebuild pgaudit from source and try again.  On Oct 6, 2021 10:06, Allie Crawford  wrote:

Troy,
I am running into the same problem where I am not able to deploy the pgaudit parameter setting into the postgresql.conf file because every time I do that and restart the postgresql cluster, it  is just fail, I cannot get the pgaudit setting
 on the postgresql.conf file. Have you find a solution to this problem? Would you be kind enough to share with me the implementation of the solution to allow the postgresql.conf file to have the pgaudit parameter settings so that they can be applied to all
 the databases in the cluster?
 
Thank you in advance for your help on this.
 
Regards,
Allie Crawford
 

From:
pgsql-general-ow...@postgresql.org  on behalf of Troy Hardin 
Date: Wednesday, October 6, 2021 at 8:57 AM
To: pgsql-gene...@postgresql.org 
Subject: [Ext:] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

[External Email]


I support an existing product that utilizes postgreSQL in a Windows environment.  Recently we've been asked to make a series of security/auditing changes to the product that require pgaudit.  We built pgaudit.dll
 and have made the majority of the configuration changes and those seem to be working as designed.  The pgaudit extension does what it is being required to do, but we are running into issues with configuring some settings globally in the postgresql.conf.
 
The two lines we need to run in the .conf file that are causing postgresql to fail to start are, for example:
 
pgaudit.log = 'read, write, ddl, role'
pgaudit.log_level = 'log'
 
Putting either of these two lines in the .conf file cause it to fail to start.  However, after starting postgreSQL, I can go to command line and run the SQL to set these and they run fine.  After running them, pgaudit
 performs as designed and configured so I believe we've compiled pgaudit.dll correctly.  At any rate, I'm at wits end right now and need to either fix the above or find someone with more db experience to assist in creating triggers that would run the SQL SET
 commands after startup.
 
Are there known reasons why I might be having this issue?  Anyone have experience getting past something similar?
 
v/r

Troy R. Hardin

Using make_timestamp() to create a BC value

[Bryn Llewellyn]

Everything that I say here applies to "make_timestamptz()", too. But my code 
examples need less typing when I use plain "make_timestamp()".

It would seem to me that ordinary principles of good software design let one 
expect that the rule that this procedure tests ought to hold:

create procedure assert_make_ts_from_extracted_fields_from_ts_ok(ts in 
timestamp)
  language plpgsql
as $body$
declare
  year   constant int  not null := extract(year  from ts);
  month  constant int  not null := extract(month from ts);
  mday   constant int  not null := extract(day   from ts);
  hour   constant int  not null := extract(hour  from ts);
  minconstant int  not null := extract(min   from ts);
  secconstant double precision not null := extract(sec   from ts);
begin
  raise info 'year: %', year;

  declare
ts_from_extracted_fields constant timestamp not null :=
  make_timestamp(year, month, mday, hour, min, sec);
  begin
assert (ts_from_extracted_fields = ts), 'assert failed';
  end;
end;
$body$;

Indeed, it does hold—for AD timestamps:

call assert_make_ts_from_extracted_fields_from_ts_ok('0001-01-01 00:00:00 AD');
call assert_make_ts_from_extracted_fields_from_ts_ok(localtimestamp);

The assertion holds for each invocation. And the expected "year" values, "1" 
and "2021", are reported.

But this invocation makes the assertion fail:

call assert_make_ts_from_extracted_fields_from_ts_ok('0001-01-01 00:00:00 BC');

The 22008 error is reported thus:

date field value out of range: -1-01-01

(And sure enough, "raise info 'year: %', year" reports "-1".)

The doc for "make_timestamp()" here:

www.postgresql.org/docs/14/functions-datetime.html

is totally silent about the limitation that a positive year argument must be 
used.

This means that I need to implement my own baroque version thus:

create function my_make_timestamp(
  year int, month int, mday int, hour int, min int, sec double precision)
  returns timestamp
  language plpgsql
as $body$
declare
  bc  constant boolean   not null := year < 0 ;
  t   constant timestamp not null := make_timestamp(abs(year), month, mday, 
hour, min, sec);
begin
  return case bc
   when true then (t::text||' BC')::timestamp
   else   t
 end;
end;
$body$;

If I replace "make_timestamp()" in 
"assert_make_ts_from_extracted_fields_from_ts_ok()" with "my_make_timestamp()"  
then (of course) I get (what I regard as) the proper behavior.

Why must I do this? Am I missing something?

Re: Using make_timestamp() to create a BC value

[Adrian Klaver]

On 10/6/21 2:59 PM, Bryn Llewellyn wrote:
Everything that I say here applies to "make_timestamptz()", too. But my 
code examples need less typing when I use plain "make_timestamp()".





(And sure enough, "raise info 'year: %', year" reports "-1".)

The doc for "make_timestamp()" here:

www.postgresql.org/docs/14/functions-datetime.html




What version of Postgres are you using?

Postgres 13:

select extract(year from '0001-01-01 00:00:00 BC'::timestamp);
 date_part
---
-1

select make_timestamp(-1,1,1,2,30,0);
ERROR:  date field value out of range: -1-01-01


Postgres 14:

select extract(year from '0001-01-01 00:00:00 BC'::timestamp);
 date_part
---
-1

select make_timestamp(-1,1,1,2,30,0);
 make_timestamp

 0001-01-01 02:30:00 BC

Postgres release notes


https://www.postgresql.org/docs/current/release-14.html#id-1.11.6.5.3

Allow make_timestamp()/make_timestamptz() to accept negative years 
(Peter Eisentraut)


Negative values are interpreted as BC years.



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

Re: Using make_timestamp() to create a BC value

[Tom Lane]

Bryn Llewellyn  writes:
> But this invocation makes the assertion fail:
> call assert_make_ts_from_extracted_fields_from_ts_ok('0001-01-01 00:00:00 
> BC');
> The 22008 error is reported thus:
> date field value out of range: -1-01-01

As of v14, make_timestamp[tz] will accept negative year values as
meaning BC.  Before that, they threw errors.

regards, tom lane

Re: Using make_timestamp() to create a BC value

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
>> Bryn wrote:
>> 
>> But this invocation makes the assertion fail:
>> call assert_make_ts_from_extracted_fields_from_ts_ok('0001-01-01 00:00:00 
>> BC');
>> The 22008 error is reported thus:
>> date field value out of range: -1-01-01
> 
> As of v14, make_timestamp[tz] will accept negative year values as meaning BC. 
> Before that, they threw errors.

Ah… that's excellent news. Thanks for the lightening fast response, Tom. 
Thanks, too, to Adrian who passed on similar news, and this link:

www.postgresql.org/docs/current/release-14.html#id-1.11.6.5.3

I did my test using PostgreSQL 13.4. Forgive me for not mentioning this.

Misplaced double quotes in error message

[Thomas Kellerer]

Hello,

consider the following table, query and error message:

create table t
(
  "someColumn" int
);

select t.someColumn
from t;


ERROR: column t.somecolumn does not exist
  Hint: Perhaps you meant to reference the column "t.someColumn".


For someone proficient in SQL it's pretty clear what the error message means,
but the quotes can be misleading for someone who doesn't really understand
what's going on.

I think if the error message quoted the column name the way it should actually
be used, it would make things easier for someone not used to this, e.g.:

  Hint: Perhaps you meant to reference the column t."someColumn".

Because when the user takes the error message literally they might be tempted
to use "t.someColumn" just to be confronted with the same error message again
which then seems even more confusing.