Re: merge with view

[Adrian Klaver]

On 3/11/24 13:25, Lorusso Domenico wrote:

Hello guys,
Merge isn't work on view; anyone know why?
I mean, merge could be performed in many way, but essentially is:

  * join sets
  * if matched update or delete
  * if not matched insert

it doesn't seem to be relevant if set is a table or a view.

Moreover also "insert + on conflict" doesn't work with view (in my case 
is a view on a single table with an instead of trigger)


Reply with:

Postgres version

View definition

Example Merge query.

Definition of doesn't work, with actual complete error message.



There is a way to workaround to this issue?

--
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]


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

merge with view

[Lorusso Domenico]

Hello guys,
Merge isn't work on view; anyone know why?
I mean, merge could be performed in many way, but essentially is:

   - join sets
   - if matched update or delete
   - if not matched insert

it doesn't seem to be relevant if set is a table or a view.

Moreover also "insert + on conflict" doesn't work with view (in my case is
a view on a single table with an instead of trigger)

There is a way to workaround to this issue?

-- 
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

RE: alter table xxx alter column yyy set (n_distinct=nnnn );

[Tefft, Michael J]

OK, thanks to both of you, it is now working for me. Many thanks.

This is what I determined was the missing ingredient from the secret sauce…

One of the things I had tried (before posting here) was
Alter table xxx alter column p_id set statistics [varying values];

Including zero.

When I first tried the suggestion of also setting n_distinct_inherited, these 
columns were still set to “statistics 0”.

When I altered them back to the default value i.e.
Alter table xxx alter column p_id set statistics -1;
(or, in fact, any nonzero value), the desired n_distinct values appeared after 
ANALYZE.

So, to set n_distinct for column col1 in a partitioned table sss.xxx:

  1.  Make sure column col1 has “statistics” != 0
  2.  alter table sss.xxx alter column p_id set 
(n_distinct_inherited=[desired_value] );
  3.  analyze sss.xxx;
  4.  verify with: select attname, n_distinct from pg_stats where 
schemaname='sss' and tablename='xxx' and attname = ‘col1’;

So setting n_distinct on the column at the parent/partitioned-table level is 
not relevant for this.

Thanks again.

Mike Tefft




From: Greg Sabino Mullane 
Sent: Monday, March 11, 2024 12:23 PM
To: Laurenz Albe 
Cc: Tefft, Michael J ; 
pgsql-general@lists.postgresql.org
Subject: Re: alter table xxx alter column yyy set (n_distinct= );

Works for me on Postgres 14 as well. Maybe you are looking at the wrong table? 
Might help to show the exact steps you typed in. ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ 
‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ 
‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ 
‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍

Works for me on Postgres 14 as well. Maybe you are looking at the wrong table? 
Might help to show the exact steps you typed in.

Re: Pgxs - How to reference another extension

[Michał Kłeczek]

> On 11 Mar 2024, at 15:00, Pavel Stehule  wrote:
> 
> 
> The advantage of OidFunctionCall is fact, it is working on MacOS. My 
> extension plpgsql_check has a lot of dependencies on plpgsql. 
> 
> The linking on MacOS required special section in Makefile
> 
> ifeq ($(PORTNAME), darwin)
> override CFLAGS += -undefined dynamic_lookup
> endif

Thanks! That worked (I am on MacOS indeed).

> 
> And there was another problem with loading dependencies.

Right - the problem seems to be:
ERROR:  could not load library 
"/opt/homebrew/opt/postgresql@16/lib/postgresql/btree_gist_extra.dylib": 
dlopen(/opt/homebrew/opt/postgresql@16/lib/postgresql/btree_gist_extra.dylib, 
0x000A): symbol not found in flat namespace '_gbt_text_consistent'

That only happens when btree_gist is _already_ loaded earlier.

When btree_gist is not loaded yet and I perform:

CREATE EXTENSION btree_gist_extra CASCADE;

all works fine.

> So now, I use only indirect methods.

I would like to avoid it but maybe it is going to be necessary.

BTW - the extension is https://github.com/mkleczek/btree_gist_extra

Thanks!

—
Michal

Re: alter table xxx alter column yyy set (n_distinct=nnnn );

[Greg Sabino Mullane]

Works for me on Postgres 14 as well. Maybe you are looking at the wrong
table? Might help to show the exact steps you typed in.

Re: could not open file "global/pg_filenode.map": Operation not permitted

[Adrian Klaver]

On 3/11/24 03:11, Nick Renders wrote:

Thank you for your reply Laurenz.
I don't think it is related to any third party security software. We have 
several other machines with a similar setup, but this is the only server that 
has this issue.

The one thing different about this machine however, is that it runs 2 instances 
of Postgres:
- cluster A on port 165
- cluster B on port 164
Cluster A is actually a backup from another Postgres server that is restored on 
a daily basis via Barman. This means that we login remotely from the Barman 
server over SSH, stop cluster A's service (port 165), clear the Data folder, 
restore the latest back into the Data folder, and start up the service again.
Cluster B's Data and service (port 164) remain untouched during all this time. This is 
the cluster that experiences the intermittent "operation not permitted" issue.

Over the past 2 weeks, I have suspended our restore script and the issue did 
not occur.
I have just performed another restore on cluster A and now cluster B is 
throwing errors in the log again.


Since it seems to be the trigger, what are the contents of the restore 
script?




Any idea why this is happening? It does not occur with every restore, but it 
seems to be related anyway.

Thanks,

Nick Renders




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

Re: alter table xxx alter column yyy set (n_distinct=nnnn );

[Laurenz Albe]

On Mon, 2024-03-11 at 14:26 +, Tefft, Michael J wrote:
> I tried setting 
> 
> alter table ctrg.xxx alter column p_id set (n_distinct_inherited=-0.033 );
> alter table ctrg.xxx alter column pi_id set (n_distinct_inherited=-0.045 );
> alter table ctrg.xxx alter column i_id set (n_distinct_inherited=-0.0002 );
> 
> I then ran 
> analyze xxx;
> but saw no change in pg_stats.n_distinct for xxx.

Well, it works on my PostgreSQL v16.

Yours,
Laurenz Albe

RE: alter table xxx alter column yyy set (n_distinct=nnnn );

[Tefft, Michael J]

Thanks very much for the reply.
I tried setting 

alter table ctrg.xxx alter column p_id set (n_distinct_inherited=-0.033 );
alter table ctrg.xxx alter column pi_id set (n_distinct_inherited=-0.045 );
alter table ctrg.xxx alter column i_id set (n_distinct_inherited=-0.0002 );

I then ran 
analyze xxx;
but saw no change in pg_stats.n_distinct for xxx.
Reviewing the values for pg_stats.n_distinct in the partitions of xxx, 
- values for i_id are all positive
- values for p_id are a mix - mostly positive, a few negative, but none carry 
the value I attempted to set
- values for pi_id are all negative, but none carry the value I attempted to 
set 

Thanks,
Mike Tefft

-Original Message-
From: Laurenz Albe  
Sent: Monday, March 11, 2024 9:21 AM
To: Tefft, Michael J ; 
pgsql-general@lists.postgresql.org
Subject: Re: alter table xxx alter column yyy set (n_distinct= );

On Mon, 2024-03-11 at 12:36 +, Tefft, Michael J wrote:
> I have been struggling to set n_distinct on a few table columns, and confirm 
> that my changes have actually been accepted.
>  
> I have a 400-million row table with 81 partitions. PostgreSQL version is 
> 14.11.
> Column p_id has 13 million distinct values but pg_stats says n_distinct is 
> only 82k.
> Column pi_id has 18 million distinct values but pg_stats says n_distinct is 
> only 29k.
> Column i_id has 70k distinct values but pg_stats says n_distinct is only 1800.
>  
> I tried:
> alter table xxx alter column p_id set (n_distinct=-0.033 ); alter 
> table xxx alter column pi_id set (n_distinct=-0.045 ); alter table xxx 
> alter column i_id set (n_distinct=-0.0002 );
>  
> All ran without error, but pg_stats shows no changes. I tried:
>  
> alter table xxx alter column p_id set (n_distinct=13118955 ); alter 
> table xxx alter column pi_id set (n_distinct=18059179 ); alter table 
> xxx alter column i_id set (n_distinct=69911 );
>  
> Again, all ran without error, but pg_stats shows no changes.
>  
> I saw somewhere (but can’t locate the reference today) that setting 
> n_distinct takes effect after the next ANALYZE. I tried ANALYZE xxx and 
> VACUUM ANALYZE xxx but the settings never appeared to take effect.
>  
> I tried increasing “statistics” on the columns to 1000 and running ANALYZE; 
> this produced somewhat-higher n_distinct values but still far from accurate 
> and my manually-set values still did not appear.
>  
> How can I get these values to take effect?

If it is a partitioned table, set "n_distinct_inherited" on the column, then 
run ANALYZE.

Yours,
Laurenz Albe

Re: Create a standby server

[Stephen Frost]

Greetings,

On Mon, Mar 11, 2024 at 13:33 normandavis1990 
wrote:

> > On Monday, March 11th, 2024 at 3:43 PM, Stephen Frost <
> sfr...@snowman.net> wrote:
>
> > Greetings,
> >
> > * normandavis1990 (normandavis1...@proton.me) wrote:
> >
> > > I have a master and tow standby servers. I want to create another
> one. These servers are made by someone else and I am a newbie in PostgreSQL.
> > > I found the following two tutorials:
> >
> >
> > [...]
> >
> > > A) Which on is better and easier?
> >
> >
> > One referred to 9.2, which is extremly old and no longer supported, and
> > the other said it was archived ... so I'm not sure either is really
> > great to be used today.
> >
> > > B) In these articles, to create a Standby server, a user is created in
> the database. Because there are already two Standby servers, this user is
> probably created. How can I find it? Can I use that user to build a third
> server?
> >
> >
> > If those systems are connected to the primary, you can query the view
> > pg_stat_replication and see what user they are connected with:
> >
> > SELECT * FROM pg_stat_replication;
> >
> > You should be able to use the existing user to create a new standby.
> > I'd recommend using pg_basebackup to create it with a command along
> > these lines:
> >
> > pg_basebackup -h existing.server.com -U username -D
> /destination/directory -c fast -C -S standbyslotname -R -P -v
> >
> > Running pg_basebackup this way will:
> > - Have pg_basebackup connect to 'existing.server.com' (should be your
> > primary)
> > - Connect as user 'username' (pull this from the 'usename' field in
> > pg_stat_replication)
> > - Store the data files for the new system into /destination/directory on
> > the system where pg_basebackup is run
> > - Start the backup immediately by doing a 'fast' checkpoint
> > - Create a replication slot to use to make sure the WAL is kept on the
> > primary until the new standby system collects it (you should monitor
> > this though- if you destroy this new system, WAL could build up on the
> > primary).
> > - Use 'standbyslotname' as the name of the slot that's created
> > - Instructs pg_basebackup to write out the connection information to
> > connect to the primary and start streaming when it starts up.
> > - Enabled progress reporting from pg_basebackup
> > - Enables verbose mode of pg_basebackup
> >
> > Full documentation of pg_basebackup is here:
> >
> > https://www.postgresql.org/docs/current/app-pgbasebackup.html
> >
> > Thanks!
> >
> > Stephen
>
> Hi,
> Thank you.
> Shoudd I run the following command on the mater?
>
> $ pg_basebackup -h "Master_IP_Address" -U username -D
> /destination/directory -c fast -C -S standbyslotname -R -P -v


No, on the system you wish to bring up as another standby.

Thanks,

Stephen

>

Re: Pgxs - How to reference another extension

[Pavel Stehule]

Hi

po 11. 3. 2024 v 14:48 odesílatel Michał Kłeczek 
napsal:

>
>
> On 11 Mar 2024, at 14:08, Artur Zakirov  wrote:
>
> On Mon, 11 Mar 2024 at 13:26, Michał Kłeczek  wrote:
>
>
>
> On 11 Mar 2024, at 11:41, Michał Kłeczek  wrote:
>
> Hi,
>
> I am trying to create an extension that delegates some calls to btree_gist
> functions:
>
> DirectFunctionCall5Coll(
>   gbt_text_consistent, …other arguments);
>
> Basic PGXS Makefile does not work - I get linker error:
>
> Undefined symbols for architecture arm64:
> "_gbt_text_consistent", referenced from:
>
>
> Anyone could provide me with some hints?
>
>
> I’ve added:
> PG_LDFLAGS += -L$(shell $(PG_CONFIG) --pkglibdir) -lbtree_gist
>
>
> You can try FunctionCall5Coll() or OidFunctionCall5Coll() functions.
>
> OidFunctionCall5Coll() calls fmgr_info() and FunctionCall5Coll(). What
> you only need is Oid of the target function.
>
>
> What I am trying to do is wrapping and decoration of gbt_text_consistent
> function.
> The reason I want to use DirectFunctionCall5Col is that other variants
> require catalog lookup
> as I don’t have old of the wrapped function.
> The lookup itself is problematic as the only piece of information I have
> is the strategy number.
> What’s more - the result of the lookup should be cached in fn_extra and
> that makes things even more complex.
>
> Is there any way to simply link against another extension library?
>

The advantage of OidFunctionCall is fact, it is working on MacOS. My
extension plpgsql_check has a lot of dependencies on plpgsql.

The linking on MacOS required special section in Makefile

ifeq ($(PORTNAME), darwin)
override CFLAGS += -undefined dynamic_lookup
endif

And there was another problem with loading dependencies. So now, I use only
indirect methods.

DirectFunctionCall is ok just for buildin functions.

Regards

Pavel


>
> —
> Thanks
>
>

Re: Pgxs - How to reference another extension

[Joe Conway]

On 3/11/24 09:48, Michał Kłeczek wrote:




On 11 Mar 2024, at 14:08, Artur Zakirov  wrote:

On Mon, 11 Mar 2024 at 13:26, Michał Kłeczek > wrote:




On 11 Mar 2024, at 11:41, Michał Kłeczek  wrote:

Hi,

I am trying to create an extension that delegates some calls to 
btree_gist functions:


DirectFunctionCall5Coll(
  gbt_text_consistent, …other arguments);

Basic PGXS Makefile does not work - I get linker error:

Undefined symbols for architecture arm64:
"_gbt_text_consistent", referenced from:


Anyone could provide me with some hints?


I’ve added:
PG_LDFLAGS += -L$(shell $(PG_CONFIG) --pkglibdir) -lbtree_gist


You can try FunctionCall5Coll() or OidFunctionCall5Coll() functions.

OidFunctionCall5Coll() calls fmgr_info() and FunctionCall5Coll(). What
you only need is Oid of the target function.


What I am trying to do is wrapping and decoration of gbt_text_consistent 
function.
The reason I want to use DirectFunctionCall5Col is that other variants 
require catalog lookup

as I don’t have old of the wrapped function.
The lookup itself is problematic as the only piece of information I have 
is the strategy number.
What’s more - the result of the lookup should be cached in fn_extra and 
that makes things even more complex.


Is there any way to simply link against another extension library?



I used this successfully in the past with postgis:

postgis_libdir := $(shell pg_config --pkglibdir)
postgis_libver := $(shell ls -1 $(postgis_libdir) | grep "^postgis")
SHLIB_LINK += -L$(postgis_libdir) -l:$(postgis_libver) -llwgeom

HTH,

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: Pgxs - How to reference another extension

[Michał Kłeczek]

> On 11 Mar 2024, at 14:08, Artur Zakirov  wrote:
> 
> On Mon, 11 Mar 2024 at 13:26, Michał Kłeczek  > wrote:
>> 
>> 
>>> On 11 Mar 2024, at 11:41, Michał Kłeczek  wrote:
>>> 
>>> Hi,
>>> 
>>> I am trying to create an extension that delegates some calls to btree_gist 
>>> functions:
>>> 
>>> DirectFunctionCall5Coll(
>>>   gbt_text_consistent, …other arguments);
>>> 
>>> Basic PGXS Makefile does not work - I get linker error:
>>> 
>>> Undefined symbols for architecture arm64:
>>> "_gbt_text_consistent", referenced from:
>>> 
>>> 
>>> Anyone could provide me with some hints?
>> 
>> I’ve added:
>> PG_LDFLAGS += -L$(shell $(PG_CONFIG) --pkglibdir) -lbtree_gist
> 
> You can try FunctionCall5Coll() or OidFunctionCall5Coll() functions.
> 
> OidFunctionCall5Coll() calls fmgr_info() and FunctionCall5Coll(). What
> you only need is Oid of the target function.

What I am trying to do is wrapping and decoration of gbt_text_consistent 
function.
The reason I want to use DirectFunctionCall5Col is that other variants require 
catalog lookup
as I don’t have old of the wrapped function.
The lookup itself is problematic as the only piece of information I have is the 
strategy number.
What’s more - the result of the lookup should be cached in fn_extra and that 
makes things even more complex.

Is there any way to simply link against another extension library?

—
Thanks

Re: alter table xxx alter column yyy set (n_distinct=nnnn );

[Laurenz Albe]

On Mon, 2024-03-11 at 12:36 +, Tefft, Michael J wrote:
> I have been struggling to set n_distinct on a few table columns, and confirm 
> that my changes have actually been accepted.
>  
> I have a 400-million row table with 81 partitions. PostgreSQL version is 
> 14.11.
> Column p_id has 13 million distinct values but pg_stats says n_distinct is 
> only 82k.
> Column pi_id has 18 million distinct values but pg_stats says n_distinct is 
> only 29k.
> Column i_id has 70k distinct values but pg_stats says n_distinct is only 1800.
>  
> I tried:
> alter table xxx alter column p_id set (n_distinct=-0.033 );
> alter table xxx alter column pi_id set (n_distinct=-0.045 );
> alter table xxx alter column i_id set (n_distinct=-0.0002 );
>  
> All ran without error, but pg_stats shows no changes. I tried:
>  
> alter table xxx alter column p_id set (n_distinct=13118955 );
> alter table xxx alter column pi_id set (n_distinct=18059179 );
> alter table xxx alter column i_id set (n_distinct=69911 );
>  
> Again, all ran without error, but pg_stats shows no changes.
>  
> I saw somewhere (but can’t locate the reference today) that setting 
> n_distinct takes effect after the next ANALYZE. I tried ANALYZE xxx and 
> VACUUM ANALYZE xxx but the settings never appeared to take effect.
>  
> I tried increasing “statistics” on the columns to 1000 and running ANALYZE; 
> this produced somewhat-higher n_distinct values but still far from accurate 
> and my manually-set values still did not appear.
>  
> How can I get these values to take effect?

If it is a partitioned table, set "n_distinct_inherited" on the column,
then run ANALYZE.

Yours,
Laurenz Albe

Re: Pgxs - How to reference another extension

[Artur Zakirov]

On Mon, 11 Mar 2024 at 13:26, Michał Kłeczek  wrote:
>
>
> > On 11 Mar 2024, at 11:41, Michał Kłeczek  wrote:
> >
> > Hi,
> >
> > I am trying to create an extension that delegates some calls to btree_gist 
> > functions:
> >
> > DirectFunctionCall5Coll(
> >gbt_text_consistent, …other arguments);
> >
> > Basic PGXS Makefile does not work - I get linker error:
> >
> > Undefined symbols for architecture arm64:
> >  "_gbt_text_consistent", referenced from:
> >
> >
> > Anyone could provide me with some hints?
>
> I’ve added:
> PG_LDFLAGS += -L$(shell $(PG_CONFIG) --pkglibdir) -lbtree_gist

You can try FunctionCall5Coll() or OidFunctionCall5Coll() functions.

OidFunctionCall5Coll() calls fmgr_info() and FunctionCall5Coll(). What
you only need is Oid of the target function.

FunctionCall5Coll() can be useful and used when you have a cache
variable of the function, where you store previously calculated
FmgrInfo.

-- 
Artur

alter table xxx alter column yyy set (n_distinct=nnnn );

[Tefft, Michael J]

I have been struggling to set n_distinct on a few table columns, and confirm 
that my changes have actually been accepted.

I have a 400-million row table with 81 partitions. PostgreSQL version is 14.11.
Column p_id has 13 million distinct values but pg_stats says n_distinct is only 
82k.
Column pi_id has 18 million distinct values but pg_stats says n_distinct is 
only 29k.
Column i_id has 70k distinct values but pg_stats says n_distinct is only 1800.

I tried:
alter table xxx alter column p_id set (n_distinct=-0.033 );
alter table xxx alter column pi_id set (n_distinct=-0.045 );
alter table xxx alter column i_id set (n_distinct=-0.0002 );

All ran without error, but pg_stats shows no changes. I tried:

alter table xxx alter column p_id set (n_distinct=13118955 );
alter table xxx alter column pi_id set (n_distinct=18059179 );
alter table xxx alter column i_id set (n_distinct=69911 );

Again, all ran without error, but pg_stats shows no changes.

I saw somewhere (but can't locate the reference today) that setting n_distinct 
takes effect after the next ANALYZE. I tried ANALYZE xxx and VACUUM ANALYZE xxx 
but the settings never appeared to take effect.

I tried increasing "statistics" on the columns to 1000 and running ANALYZE; 
this produced somewhat-higher n_distinct values but still far from accurate and 
my manually-set values still did not appear.

How can I get these values to take effect?

Thanks,
Mike Tefft

Re: Create a standby server

[normandavis1990]

> On Monday, March 11th, 2024 at 3:39 PM, Mateusz Henicz 
>  wrote:

> Hey,
> Check your parameter primary_conninfo on any standby server, you should find 
> here information about the user used for replication and its password or path 
> to .pgpass file, where the password is stored. If there is no password or 
> .pgpass file defined, then you do not need any password most likely, and you 
> are likely using the "trust" authentication method in your pg_hba.conf for 
> replication.
>
> To create a replica pretty much all you have to do is to add your new standby 
> server to pg_hba.conf, so you are allowed to connect and run on your new 
> standby:
> pg_basebackup -h  -U  -R -D /data_pg -X 
> stream
> and then start it using
> pg_ctl -D /data_pg start
>
> There can be some parameters that need to be adjusted, like listen_addresses 
> for example, unless you are using "*" for it. And maybe 
> max_wal_senders/max_replication_slots can be too low and you may have to 
> increase it, but if there is any problem and you will try to start your new 
> replica it will just fail and you will get information about what was wrong 
> to your logfile, so it is easy to find.
>
> By adding -R to pg_basebackup you will get your replication configuration 
> generated automatically to postgresql.auto.conf and -X will stream all WAL 
> files generated during pg_basebackup execution to your new replica server.
> If you prefer to use replication slots you may also add -C -S  to 
> get a replication slot created automatically by pg_basebackup.
>
> Good luck!
>
> Cheers,
> Mateusz
>
> pon., 11 mar 2024 o 12:51 normandavis1990  
> napisał(a):
>
>> Hello,
>> I have a master and tow standby servers. I want to create another one. These 
>> servers are made by someone else and I am a newbie in PostgreSQL.
>> I found the following two tutorials:
>>
>> https://linuxconfig.org/how-to-create-a-hot-standby-with-postgresql
>>
>> https://github.com/GoogleCloudPlatform/community/blob/master/archived/setting-up-postgres-hot-standby.md
>>
>> A) Which on is better and easier?
>>
>> B) In these articles, to create a Standby server, a user is created in the 
>> database. Because there are already two Standby servers, this user is 
>> probably created. How can I find it? Can I use that user to build a third 
>> server?
>>
>> Cheers.

Hi,
Should I run those commands on the standby server?

Re: Create a standby server

[normandavis1990]

> On Monday, March 11th, 2024 at 3:43 PM, Stephen Frost  
> wrote:

> Greetings,
> 
> * normandavis1990 (normandavis1...@proton.me) wrote:
> 
> > I have a master and tow standby servers. I want to create another one. 
> > These servers are made by someone else and I am a newbie in PostgreSQL.
> > I found the following two tutorials:
> 
> 
> [...]
> 
> > A) Which on is better and easier?
> 
> 
> One referred to 9.2, which is extremly old and no longer supported, and
> the other said it was archived ... so I'm not sure either is really
> great to be used today.
> 
> > B) In these articles, to create a Standby server, a user is created in the 
> > database. Because there are already two Standby servers, this user is 
> > probably created. How can I find it? Can I use that user to build a third 
> > server?
> 
> 
> If those systems are connected to the primary, you can query the view
> pg_stat_replication and see what user they are connected with:
> 
> SELECT * FROM pg_stat_replication;
> 
> You should be able to use the existing user to create a new standby.
> I'd recommend using pg_basebackup to create it with a command along
> these lines:
> 
> pg_basebackup -h existing.server.com -U username -D /destination/directory -c 
> fast -C -S standbyslotname -R -P -v
> 
> Running pg_basebackup this way will:
> - Have pg_basebackup connect to 'existing.server.com' (should be your
> primary)
> - Connect as user 'username' (pull this from the 'usename' field in
> pg_stat_replication)
> - Store the data files for the new system into /destination/directory on
> the system where pg_basebackup is run
> - Start the backup immediately by doing a 'fast' checkpoint
> - Create a replication slot to use to make sure the WAL is kept on the
> primary until the new standby system collects it (you should monitor
> this though- if you destroy this new system, WAL could build up on the
> primary).
> - Use 'standbyslotname' as the name of the slot that's created
> - Instructs pg_basebackup to write out the connection information to
> connect to the primary and start streaming when it starts up.
> - Enabled progress reporting from pg_basebackup
> - Enables verbose mode of pg_basebackup
> 
> Full documentation of pg_basebackup is here:
> 
> https://www.postgresql.org/docs/current/app-pgbasebackup.html
> 
> Thanks!
> 
> Stephen

Hi,
Thank you.
Shoudd I run the following command on the mater?

$ pg_basebackup -h "Master_IP_Address" -U username -D /destination/directory -c 
fast -C -S standbyslotname -R -P -v

Re: Pgxs - How to reference another extension

[Michał Kłeczek]

> On 11 Mar 2024, at 11:41, Michał Kłeczek  wrote:
> 
> Hi,
> 
> I am trying to create an extension that delegates some calls to btree_gist 
> functions:
> 
> DirectFunctionCall5Coll(
>gbt_text_consistent, …other arguments);
> 
> Basic PGXS Makefile does not work - I get linker error: 
> 
> Undefined symbols for architecture arm64:
>  "_gbt_text_consistent", referenced from:
> 
> 
> Anyone could provide me with some hints?

I’ve added:
PG_LDFLAGS += -L$(shell $(PG_CONFIG) --pkglibdir) -lbtree_gist

to Makefile and I get the following:

ld: library 'btree_gist' not found
clang: error: linker command failed with exit code 1 (use -v to see invocation)

Note that I am on Mac and btree_gist.dylib is present in $(pg_config —pkglibdir)

—
Michal

Re: Create a standby server

[Stephen Frost]

Greetings,

* normandavis1990 (normandavis1...@proton.me) wrote:
> I have a master and tow standby servers. I want to create another one. These 
> servers are made by someone else and I am a newbie in PostgreSQL.
> I found the following two tutorials:

[...]

> A) Which on is better and easier?

One referred to 9.2, which is extremly old and no longer supported, and
the other said it was archived ... so I'm not sure either is really
great to be used today.

> B) In these articles, to create a Standby server, a user is created in the 
> database. Because there are already two Standby servers, this user is 
> probably created. How can I find it? Can I use that user to build a third 
> server?

If those systems are connected to the primary, you can query the view
pg_stat_replication and see what user they are connected with:

SELECT * FROM pg_stat_replication;

You should be able to use the existing user to create a new standby.
I'd recommend using pg_basebackup to create it with a command along
these lines:

pg_basebackup -h existing.server.com -U username -D /destination/directory -c 
fast -C -S standbyslotname -R -P -v

Running pg_basebackup this way will:
- Have pg_basebackup connect to 'existing.server.com' (should be your
  primary)
- Connect as user 'username' (pull this from the 'usename' field in
  pg_stat_replication)
- Store the data files for the new system into /destination/directory on
  the system where pg_basebackup is run
- Start the backup immediately by doing a 'fast' checkpoint
- Create a replication slot to use to make sure the WAL is kept on the
  primary until the new standby system collects it (you should monitor
  this though- if you destroy this new system, WAL could build up on the
  primary).
- Use 'standbyslotname' as the name of the slot that's created
- Instructs pg_basebackup to write out the connection information to
  connect to the primary and start streaming when it starts up.
- Enabled progress reporting from pg_basebackup
- Enables verbose mode of pg_basebackup

Full documentation of pg_basebackup is here:

https://www.postgresql.org/docs/current/app-pgbasebackup.html

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Create a standby server

[Mateusz Henicz]

Hey,
Check your parameter primary_conninfo on any standby server, you should
find here information about the user used for replication and its password
or path to .pgpass file, where the password is stored. If there is no
password or .pgpass file defined, then you do not need any password most
likely, and you are likely using the "trust" authentication method in your
pg_hba.conf for replication.

To create a replica pretty much all you have to do is to add your new
standby server to pg_hba.conf, so you are allowed to connect and run on
your new standby:
pg_basebackup -h  -U  -R -D /data_pg
-X stream
and then start it using
pg_ctl -D /data_pg start

There can be some parameters that need to be adjusted, like
listen_addresses for example, unless you are using "*" for it. And maybe
max_wal_senders/max_replication_slots can be too low and you may have to
increase it, but if there is any problem and you will try to start your new
replica it will just fail and you will get information about what was wrong
to your logfile, so it is easy to find.

By adding -R to pg_basebackup you will get your replication configuration
generated automatically to postgresql.auto.conf and -X will stream all WAL
files generated during pg_basebackup execution to your new replica server.
If you prefer to use replication slots you may also add -C -S 
to get a replication slot created automatically by pg_basebackup.

Good luck!

Cheers,
Mateusz

pon., 11 mar 2024 o 12:51 normandavis1990 
napisał(a):

> Hello,
> I have a master and tow standby servers. I want to create another
> one. These servers are made by someone else and I am a newbie in PostgreSQL.
> I found the following two tutorials:
>
> https://linuxconfig.org/how-to-create-a-hot-standby-with-postgresql
>
>
> https://github.com/GoogleCloudPlatform/community/blob/master/archived/setting-up-postgres-hot-standby.md
>
> A) Which on is better and easier?
>
> B) In these articles, to create a Standby server, a user is created in the
> database. Because there are already two Standby servers, this user is
> probably created. How can I find it? Can I use that user to build a third
> server?
>
>
> Cheers.
>
>
>

Re: could not open file "global/pg_filenode.map": Operation not permitted

[Stephen Frost]

Greetings,

* Nick Renders (postg...@arcict.com) wrote:
> The one thing different about this machine however, is that it runs 2 
> instances of Postgres:
> - cluster A on port 165
> - cluster B on port 164
> Cluster A is actually a backup from another Postgres server that is restored 
> on a daily basis via Barman. This means that we login remotely from the 
> Barman server over SSH, stop cluster A's service (port 165), clear the Data 
> folder, restore the latest back into the Data folder, and start up the 
> service again.
> Cluster B's Data and service (port 164) remain untouched during all this 
> time. This is the cluster that experiences the intermittent "operation not 
> permitted" issue.
> 
> Over the past 2 weeks, I have suspended our restore script and the issue did 
> not occur.
> I have just performed another restore on cluster A and now cluster B is 
> throwing errors in the log again.
> 
> Any idea why this is happening? It does not occur with every restore, but it 
> seems to be related anyway.

Not sure why it's happening but they certainly sound related based on
the correlation.

One thing that I suggest doing when you're running multiple independent
PG clusters on the same host is to run them under different users on the
system.  Perhaps if you move cluster B to a distinct user account,
you'll have better luck figuring out what's going on since something
will presumably start throwing permission denied errors.

Not sure if it's an option or not, but you might also consider using
Linux instead of MacOS..

Thanks,

Stephen


signature.asc
Description: PGP signature

Create a standby server

[normandavis1990]

Hello,
I have a master and tow standby servers. I want to create another one. These 
servers are made by someone else and I am a newbie in PostgreSQL.
I found the following two tutorials:

https://linuxconfig.org/how-to-create-a-hot-standby-with-postgresql

https://github.com/GoogleCloudPlatform/community/blob/master/archived/setting-up-postgres-hot-standby.md

A) Which on is better and easier?

B) In these articles, to create a Standby server, a user is created in the 
database. Because there are already two Standby servers, this user is probably 
created. How can I find it? Can I use that user to build a third server?


Cheers.

Re: could not open file "global/pg_filenode.map": Operation not permitted

[Laurenz Albe]

On Mon, 2024-03-11 at 11:11 +0100, Nick Renders wrote:
> We have several other machines with a similar setup, but this is the only 
> server that has this issue.
> 
> [...] Cluster A is actually a backup from another Postgres server that is 
> restored on a
> daily basis via Barman. This means that we login remotely from the Barman 
> server over SSH,
> stop cluster A's service (port 165), clear the Data folder, restore the 
> latest back into
> the Data folder, and start up the service again.
> Cluster B's Data and service (port 164) remain untouched during all this 
> time. This is
> the cluster that experiences the intermittent "operation not permitted" issue.
> 
> Over the past 2 weeks, I have suspended our restore script and the issue did 
> not occur.
> I have just performed another restore on cluster A and now cluster B is 
> throwing errors in the log again.
> 
> Any idea why this is happening? It does not occur with every restore, but it 
> seems to be related anyway.

I don't know Barman, but with that incomplete description anybody will have
problems determining the cause.  For example, how are A and B connected?

Yours,
Laurenz Albe

Pgxs - How to reference another extension

[Michał Kłeczek]

Hi,

I am trying to create an extension that delegates some calls to btree_gist 
functions:

DirectFunctionCall5Coll(
gbt_text_consistent, …other arguments);

Basic PGXS Makefile does not work - I get linker error: 

Undefined symbols for architecture arm64:
  "_gbt_text_consistent", referenced from:


Anyone could provide me with some hints?

—
Michal

Re: About rsync

[Stephen Frost]

Greetings,

* Rama Krishnan (raghuld...@gmail.com) wrote:
> I have primary and stand by setup in that customer using wal_keep_segment
> very low number and database size were also high so that cilent asking with
> me in order use pgbase backup can't we use rsyn
> 
> Here my question is if the wal is is presented on primary and not in
> standby then rsync will work but if the wal files were missed at primary if
> I am using rsync to the db will be work here please explain me

Not really following what's being asked here, but one thing that I can
certainly say is that rsync, by itself, is not an acceptable solution
for performing online backups of PostgreSQL.

I'd strongly suggest you continue to use pg_basebackup or look into a
more advanced tool like pgBackRest if you need a backup solution for
PostgreSQL.

Thanks,

Stephen


signature.asc
Description: PGP signature

About rsync

[Rama Krishnan]

Hi Team,


I have primary and stand by setup in that customer using wal_keep_segment
very low number and database size were also high so that cilent asking with
me in order use pgbase backup can't we use rsyn

Here my question is if the wal is is presented on primary and not in
standby then rsync will work but if the wal files were missed at primary if
I am using rsync to the db will be work here please explain me

Thanks
RK

Re: could not open file "global/pg_filenode.map": Operation not permitted

[Nick Renders]

Thank you for your reply Laurenz.
I don't think it is related to any third party security software. We have 
several other machines with a similar setup, but this is the only server that 
has this issue.

The one thing different about this machine however, is that it runs 2 instances 
of Postgres:
- cluster A on port 165
- cluster B on port 164
Cluster A is actually a backup from another Postgres server that is restored on 
a daily basis via Barman. This means that we login remotely from the Barman 
server over SSH, stop cluster A's service (port 165), clear the Data folder, 
restore the latest back into the Data folder, and start up the service again.
Cluster B's Data and service (port 164) remain untouched during all this time. 
This is the cluster that experiences the intermittent "operation not permitted" 
issue.

Over the past 2 weeks, I have suspended our restore script and the issue did 
not occur.
I have just performed another restore on cluster A and now cluster B is 
throwing errors in the log again.

Any idea why this is happening? It does not occur with every restore, but it 
seems to be related anyway.

Thanks,

Nick Renders


On 26 Feb 2024, at 16:29, Laurenz Albe wrote:

> On Mon, 2024-02-26 at 15:14 +0100, Nick Renders wrote:
>> We have a Postgres server that intermittently logs the following:
>>
>> 2024-02-26 10:29:41.580 CET [63962] FATAL:  could not open file 
>> "global/pg_filenode.map": Operation not permitted
>> 2024-02-26 10:30:11.147 CET [90610] LOG:  could not open file 
>> "postmaster.pid": Operation not permitted; continuing anyway
>>
>> This has happened 3 times over the last 2 weeks now, without any indication 
>> what caused it.
>> The privileges of those 2 files are all in order.
>> When this happens, the server is no longer accessible, and we need to 
>> restart the service (pg_ctl restart).
>> Once restarted, Popstgres runs fine again for a couple of days.
>>
>> We are running PostgreSQL 16.2 on macOS 14.3.1.
>
> Perhaps that is some kind of virus checker or something else that locks files.
>
> Yours,
> Laurenz Albe

Performance (and general) considerations between views and functions

[Wiwwo Staff]

Hi Community!
I am in the process of re-writing SQLs in app code to a more DB side
approach.

My first idea was to write views, and let the app query those views; i'd
need to rewrite some queries to expose the "parameters" (where condition
fields) of those queries in the select part, to expose them and allow app
to filter on them. Now a huge issue.

But, i might also create functions (language sql) returning "RETURNS
TABLE", and pass the where conditions as parameter; i would have to bite
the bullet and write all the "RETURNS TABLE (... ... ...)" definitions, but
meh. :-D

 My question would be: in terms of performance, the select on the function
looks way faster, but I am afraid PG optimized does not really
"understands" the complexity of the query.
For example, the explain on the view understands that the query returns
always 1 row; the explain on the proc. does not.
On the other side, functions can be tuned with many many "hints" (COST,
ROWS, etc...)

In my shoes, what would you do?

Thanks a ton!

Implementing product-aggregate

[Jan Kohnert]

Hi,

we need a product aggregate and used to implement this as 

exp(sum(ln([COLUMN])))

While using the sum of logarithms is working RDBMS-independently, we'd like to 
switch to a more PostgreSQL native way of doing this and implement an 
aggregate to be used. Currently the implementation is

create aggregate prod(numeric) (sfunc = numeric_mul, stype = numeric)

This is simply calling the implementation funtion of the *-Operator for the 
numeric 
datatype. Since I could not find any documentation of this implementation 
function, I am wondering, if using a possibly internal function might be a bad 
idea.

Are there any recommendations on this?

Thanks for any input!

-- 
MfG Jan

Re: walsender RAM increases by 500 MB while data is 80 MB

[Masahiko Sawada]

On Mon, Mar 11, 2024 at 12:33 AM Avi Weinberg  wrote:
>
> Hi Experts,
>
>
>
> Your input is most welcome!
>
>
>
> We are using Postgres 13 (and plan to upgrade to 15 soon).  We have logical 
> replication with about 40 servers subscribing to one publisher.  40 Walsender 
> processes are running on the publisher server.  When we insert a row into a 
> table holding binary data the walsender RAM usage increases by 500MB although 
> the row binary data is only 80MB.  We see this increase in all walsender 
> processes.  At some point we got OOM and the process was killed.

This sounds like similar reports[1][2] we got before. Were there any
long-running transactions at that time when the 80MB data change was
made? And is it accessible to the core dump of the walsender process
who was killed due to OOM?

> Why does the walsender increases by 500MB when the data change was only 80MB
> Is some of the 500MB increase due to shared memory or each walsender has its 
> own 500MB increase.  I assume that if it was only in shared memory we would 
> not have gotten OOM…
> Why when logical_decoding_work_mem = 64MB the RAM is 15 times that size?  
> Shouldn't any additional space be used from disk and not RAM?
> Will adding streaming = on to publication "PUBLICATION pub WITH (streaming = 
> on)" can alleviate the issue?
> Are there configuration options that can resolve the RAM issue.  It can be 
> also in version 15 since we plan to upgrade soon.

If you're facing a similar issue I shared above, temporarily setting
logical_decoding_work_mem a *lower* value could alleviate the
situation.  Setting a lower value would lead to more evictions in
logical decoding, and it probably can avoid using much memory and OOM
(note that the logical decoding gets slower instead). I think there is
a memory accounting issue in logical decoding, which could end up
using memory much more than the logical_decoding_work_mem limit in
some scenarios. This issue is not fixed yet, and setting "streaming =
on" doesn't help.

Regards,

[1] 
https://www.postgresql.org/message-id/CAMnUB3oYugXCBLSkih%2BqNsWQPciEwos6g_AMbnz_peNoxfHwyw%40mail.gmail.com
[2] 
https://www.postgresql.org/message-id/17974-f8c9d353a62f414d%40postgresql.org

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Re: Seeing high query planning time on Azure Postgres Single Server version 11.

[hassan rafi]

Thanks all. Will try upgrading the postgres version.

On Sun, Mar 10, 2024 at 11:44 PM Ron Johnson 
wrote:

> On Sun, Mar 10, 2024 at 1:34 PM Greg Sabino Mullane 
> wrote:
>
>>
>> On Sat, Mar 9, 2024 at 1:57 PM hassan rafi 
>> wrote:
>>
>>> Would upgrading to the latest version of Postgres potentially solve the
>>> issue?
>>>
>>
>> Potentially, yes, but the only one who can answer that for sure is you.
>> Upgrade to 11.22 and re-run the query. Worst case scenario, it runs the
>> same speed but you gained yourself a bunch of bugfixes and CVE resolutions.
>> If the problem persists on 11.22, spin up a Postgres 16, load the data, and
>> test it there.
>>
>
> We have a similar situation with 9.6.24.  14.10 is
> noticeably faster (between 10% and 80%, depending on the query.
>
>