slightly unexpected result

[Torsten Förtsch]

Hi,

imagine a simple table with 1 row

=# table tf;
 i | x
---+
 1 | xx
(1 row)

And this query:

with x as (update tf set i=i+1 returning *)
, y as (update tf set x=x||'yy' returning *)
select * from x,y;

My PG14 gives this result

 i | x | i | x
---+---+---+---
(0 rows)

To me that was a bit surprising. I would have expected it to fail with
something like "can't update the same row twice in the same command".

If I check the table content after the query I see the i=i+1 part was
executed.

Is this expected behavior?

Thanks,
Torsten

failed to setup barman backup when Posgres is running in Podman Container

[duc hiep ha]

Dear All,

I am trying to use Barman to back up PostgreSQL, which is running in a
Podman container. However, I encounter the error "WAL archive: FAILED," as
shown below:

#barman check testdb
Server testdb:
WAL archive: FAILED (please make sure WAL shipping is set up)
PostgreSQL: OK
superuser: OK
PostgreSQL streaming: OK
wal_level: OK
replication slot: OK
directories: OK
retention policy settings: OK
backup maximum age: OK (no last_backup_maximum_age provided)
compression settings: OK
failed backups: OK (there are 0 failed backups)
minimum redundancy requirements: OK (have 0 backups, expected at least 0)
pg_basebackup: OK
pg_basebackup compatible: OK
pg_basebackup supports tablespaces mapping: OK
pg_receivexlog: OK
pg_receivexlog compatible: OK
receive-wal running: OK
archiver errors: OK

I have identified that normally, when the PostgreSQL database is installed
on a regular VM (not in a container), the archive_command parameter under
postgresql.conf looks like this:

archive_command = 'rsync -a %p barman@barman-backup-server-ip
:/data/barman/main-db-server/incoming/%f'

However, when PostgreSQL is installed on a Podman container, I cannot use
the above archive_command because within the PostgreSQL container, it
doesn't understand rsync, ssh, and the barman user. That's why I have to
use a normal copy command like:

  archive_command = 'cp -i %p /srv/archive/%f'

I then mounted this wal-files folder on
'/data/barman/main-db-server/incoming/%f'. As a result, all these wal_files
have the PostgreSQL ownership and not the barman ownership. The barman user
has no privileges to change these wal-files, which is why the WAL archive
is marked as FAILED.

Do you know how to solve this issue? do I have to install Barman and
PostgreSQL in the same Pod container in order to backup  PostgreSQL
container? Or is there another solution to this problem?

Thank you for your support in advance.

Best,
Ha

Re: slightly unexpected result

[Bruce Momjian]

On Wed, Jan 10, 2024 at 12:29:54PM +0100, Torsten Förtsch wrote:
> Hi,
> 
> imagine a simple table with 1 row
> 
> =# table tf;
>  i | x  
> ---+
>  1 | xx
> (1 row)
> 
> And this query:
> 
> with x as (update tf set i=i+1 returning *)
> , y as (update tf set x=x||'yy' returning *)
> select * from x,y;
> 
> My PG14 gives this result
> 
>  i | x | i | x
> ---+---+---+---
> (0 rows)
> 
> To me that was a bit surprising. I would have expected it to fail with
> something like "can't update the same row twice in the same command".
> 
> If I check the table content after the query I see the i=i+1 part was 
> executed.
> 
> Is this expected behavior?

Yes, this surprised me too.  Here is a reproducible case:

CREATE TABLE tf (i INT, x TEXT);
INSERT INTO tf VALUES (1, 'x');

WITHx AS (UPDATE tf SET i=i+1 RETURNING *),
y AS (UPDATE tf SET x=x||'yy' RETURNING *)
SELECT * FROM x,y;
 i | x | i | x
---+---+---+---


TABLE tf;
 i | x
---+---
 2 | x

I know you can cascade the returning of one table into the update of
another table, but maybe it doesn't work into the same table.

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

  Only you can decide what is important to you.

Re: failed to setup barman backup when Posgres is running in Podman Container

[kaido vaikla]

Hi,

It's a typical error for brand new setup. After new backup setup do
$ barman switch-xlog --force --archive  testdb

br
Kaido

On Wed, 10 Jan 2024 at 15:58, duc hiep ha  wrote:

> Dear All,
>
> I am trying to use Barman to back up PostgreSQL, which is running in a
> Podman container. However, I encounter the error "WAL archive: FAILED," as
> shown below:
>
> #barman check testdb
> Server testdb:
> WAL archive: FAILED (please make sure WAL shipping is set up)
> PostgreSQL: OK
> superuser: OK
> PostgreSQL streaming: OK
> wal_level: OK
> replication slot: OK
> directories: OK
> retention policy settings: OK
> backup maximum age: OK (no last_backup_maximum_age provided)
> compression settings: OK
> failed backups: OK (there are 0 failed backups)
> minimum redundancy requirements: OK (have 0 backups, expected at least 0)
> pg_basebackup: OK
> pg_basebackup compatible: OK
> pg_basebackup supports tablespaces mapping: OK
> pg_receivexlog: OK
> pg_receivexlog compatible: OK
> receive-wal running: OK
> archiver errors: OK
>
> I have identified that normally, when the PostgreSQL database is installed
> on a regular VM (not in a container), the archive_command parameter under
> postgresql.conf looks like this:
>
> archive_command = 'rsync -a %p barman@barman-backup-server-ip
> :/data/barman/main-db-server/incoming/%f'
>
> However, when PostgreSQL is installed on a Podman container, I cannot use
> the above archive_command because within the PostgreSQL container, it
> doesn't understand rsync, ssh, and the barman user. That's why I have to
> use a normal copy command like:
>
>   archive_command = 'cp -i %p /srv/archive/%f'
>
> I then mounted this wal-files folder on
> '/data/barman/main-db-server/incoming/%f'. As a result, all these wal_files
> have the PostgreSQL ownership and not the barman ownership. The barman user
> has no privileges to change these wal-files, which is why the WAL archive
> is marked as FAILED.
>
> Do you know how to solve this issue? do I have to install Barman and
> PostgreSQL in the same Pod container in order to backup  PostgreSQL
> container? Or is there another solution to this problem?
>
> Thank you for your support in advance.
>
> Best,
> Ha
>

Re: slightly unexpected result

[David G. Johnston]

On Wed, Jan 10, 2024 at 8:46 AM Bruce Momjian  wrote:

> On Wed, Jan 10, 2024 at 12:29:54PM +0100, Torsten Förtsch wrote:
>
> >
> > To me that was a bit surprising. I would have expected it to fail with
> > something like "can't update the same row twice in the same command".
> >
> > If I check the table content after the query I see the i=i+1 part was
> executed.
> >
> > Is this expected behavior?
>
> Yes, this surprised me too.


It is mostly documented.

https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING
"""
Only one of the modifications takes place, but it is not easy (and
sometimes not possible) to reliably predict which one.
...
In particular avoid writing WITH sub-statements that could affect the same
rows changed by the main statement or a sibling sub-statement. The effects
of such a statement will not be predictable.
"""

Yes, an error would be nice, but the effort put forth stops at
unpredictable, and saying just don't do it.

David J.

Re: slightly unexpected result

[Bruce Momjian]

On Wed, Jan 10, 2024 at 09:06:31AM -0700, David G. Johnston wrote:
> It is mostly documented.
> 
> https://www.postgresql.org/docs/current/queries-with.html#
> QUERIES-WITH-MODIFYING
> """
> Only one of the modifications takes place, but it is not easy (and sometimes
> not possible) to reliably predict which one.
> ...
> In particular avoid writing WITH sub-statements that could affect the same 
> rows
> changed by the main statement or a sibling sub-statement. The effects of such 
> a
> statement will not be predictable.
> """
> 
> Yes, an error would be nice, but the effort put forth stops at unpredictable,
> and saying just don't do it.

Oh, good to know.  :-)

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

  Only you can decide what is important to you.

pgAdmin 8.1 install

[David Barron]

Is there a problem with the installation of 8.1?  I've installed it 3 times but 
when I bring it up it still says I'm at 7.8.  The Help > About menu shows 7.8 
as well.

I am hesitant to uninstall and install from scratch because I don't want to 
lose my connections, etc.

Thanks

architects of vision and strategy for business intelligence
David Barron
Senior Consultant
Zencos Consulting: david.bar...@zencos.com
[www.zencos.com]
mobile:
919-995-2356
fax:
919-287-2335
The information in this e-mail and any attached files is confidential. It is 
intended solely for the use of the addressee. Any unauthorized disclosure or 
use is prohibited. If you are not the intended recipient of the message, please 
notify the sender immediately and do not disclose the contents to any other 
person, use it for any purpose, or store or copy the information in any medium. 
The views of the author may not necessarily reflect those of the company.

Re: pgAdmin 8.1 install

[Adrian Klaver]

On 1/10/24 10:47 AM, David Barron wrote:


Is there a problem with the installation of 8.1?  I’ve installed it 3 
times but when I bring it up it still says I’m at 7.8.  The Help > 
About menu shows 7.8 as well.




What OS are you using?

Where did you get pgAdmin from?

How did you install it?


I am hesitant to uninstall and install from scratch because I don’t 
want to lose my connections, etc.


Thanks

/architects of vision and strategy for business intelligence/

*David Barron*
Senior Consultant

Zencos Consulting: david.bar...@zencos.com 





www.zencos.com 




mobile:



919-995-2356

fax:



919-287-2335

The information in this e-mail and any attached files is confidential. 
It is intended solely for the use of the addressee. Any unauthorized 
disclosure or use is prohibited. If you are not the intended recipient 
of the message, please notify the sender immediately and do not 
disclose the contents to any other person, use it for any purpose, or 
store or copy the information in any medium. The views of the author 
may not necessarily reflect those of the company.

RE: pgAdmin 8.1 install

[David Barron]

David Barron
Zencos Consulting LLC
919-995-2356 (Mobile)

From: Adrian Klaver 
Sent: Wednesday, January 10, 2024 1:56 PM
To: David Barron ; pgsql-general@lists.postgresql.org
Subject: Re: pgAdmin 8.1 install

EXTERNAL SENDER


On 1/10/24 10:47 AM, David Barron wrote:

Is there a problem with the installation of 8.1?  I’ve installed it 3 times but 
when I bring it up it still says I’m at 7.8.  The Help > About menu shows 7.8 
as well.



What OS are you using?

Where did you get pgAdmin from?

How did you install it?


I am running Windows 10.

I downloaded the Dec 14 release from pgadmin.org

I installed by double-clicking on the executable I downloaded and running 
through the installation wizard without making any changes.


I am hesitant to uninstall and install from scratch because I don’t want to 
lose my connections, etc.

Thanks

architects of vision and strategy for business intelligence
David Barron
Senior Consultant
Zencos Consulting: david.bar...@zencos.com
[www.zencos.com]
mobile:
919-995-2356
fax:
919-287-2335
The information in this e-mail and any attached files is confidential. It is 
intended solely for the use of the addressee. Any unauthorized disclosure or 
use is prohibited. If you are not the intended recipient of the message, please 
notify the sender immediately and do not disclose the contents to any other 
person, use it for any purpose, or store or copy the information in any medium. 
The views of the author may not necessarily reflect those of the company.

Re: pgAdmin 8.1 install

[Adrian Klaver]

On 1/10/24 10:58 AM, David Barron wrote:


On 1/10/24 10:47 AM, David Barron wrote:

Is there a problem with the installation of 8.1?  I’ve installed
it 3 times but when I bring it up it still says I’m at 7.8.  The
Help > About menu shows 7.8 as well.

What OS are you using?

Where did you get pgAdmin from?

How did you install it?

I am running Windows 10.

I downloaded the Dec 14 release from pgadmin.org

I installed by double-clicking on the executable I downloaded and 
running through the installation wizard without making any changes.




Well that looks good to me. Unfortunately at the moment I don't have a 
Windows instance available


I can test against.

You might get an answer sooner here:

https://www.postgresql.org/list/pgadmin-support/




I am hesitant to uninstall and install from scratch because I
don’t want to lose my connections, etc.

Thanks

/architects of vision and strategy for business intelligence/

*David Barron*
Senior Consultant

Zencos Consulting: david.bar...@zencos.com




www.zencos.com 




mobile:



919-995-2356

fax:



919-287-2335

The information in this e-mail and any attached files is
confidential. It is intended solely for the use of the addressee.
Any unauthorized disclosure or use is prohibited. If you are not
the intended recipient of the message, please notify the sender
immediately and do not disclose the contents to any other person,
use it for any purpose, or store or copy the information in any
medium. The views of the author may not necessarily reflect those
of the company.

RE: pgAdmin 8.1 install

[David Barron]

On 1/10/24 10:58 AM, David Barron wrote:

On 1/10/24 10:47 AM, David Barron wrote:

Is there a problem with the installation of 8.1?  I’ve installed it 3 times but 
when I bring it up it still says I’m at 7.8.  The Help > About menu shows 7.8 
as well.



What OS are you using?

Where did you get pgAdmin from?

How did you install it?



I am running Windows 10.

I downloaded the Dec 14 release from pgadmin.org

I installed by double-clicking on the executable I downloaded and running 
through the installation wizard without making any changes.



Well that looks good to me. Unfortunately at the moment I don't have a Windows 
instance available

I can test against.

You might get an answer sooner here:

https://www.postgresql.org/list/pgadmin-support/


Yes, I had done some searching without finding anything.  It’s odd because I 
have gone from 7.6 to 7.7 and 7.7 to 7.8 following the same steps.

Re: pgAdmin 8.1 install

[Adrian Klaver]

On 1/10/24 11:37 AM, David Barron wrote:




I am running Windows 10.

I downloaded the Dec 14 release from pgadmin.org

I installed by double-clicking on the executable I downloaded and
running through the installation wizard without making any changes.

Well that looks good to me. Unfortunately at the moment I don't have a 
Windows instance available


I can test against.

You might get an answer sooner here:

https://www.postgresql.org/list/pgadmin-support/



Yes, I had done some searching without finding anything.  It’s odd
because I have gone from 7.6 to 7.7 and 7.7 to 7.8 following the
same steps.



I will be able to access a Windows machine later today to test the 
download. In meantime asking on the


other mailing list might get you an answer sooner.

Re: pgAdmin 8.1 install

[Craig McIlwee]

>  Is there a problem with the installation of 8.1?  I’ve installed it 3
times but when I bring it up it still says I’m at 7.8.  The Help > About
menu shows 7.8 as well.

> It’s odd because I have gone from 7.6 to 7.7 and 7.7 to 7.8 following the
same steps.

When I look at the directory on my machine that pgAdmin is installed to, I
see the following:

C:\Users\ \AppData\Local\Programs\pgAdmin 4>dir
 Volume in drive C has no label.
 Volume Serial Number is 42DF-4B8A

 Directory of C:\Users\\AppData\Local\Programs\pgAdmin 4

11/28/2023  09:44 AM  .
11/28/2023  09:44 AM  ..
11/20/2023  12:26 PM91,965 DEPENDENCIES
11/28/2023  09:43 AM  docs
11/28/2023  09:44 AM  installer
11/20/2023  12:26 PM 1,196 LICENSE
11/20/2023  12:26 PM35,147 pgAdmin4.ico
11/28/2023  09:44 AM  python
11/28/2023  12:50 PM  runtime
11/20/2023  12:46 PM 3,417,277 sbom.json
11/28/2023  09:44 AM 4,766,927 unins000.dat
11/28/2023  09:43 AM 3,166,605 unins000.exe
02/01/2023  10:59 AM  v6
08/16/2023  01:17 PM  v7
11/28/2023  09:49 AM  web

Note that v6 and v7 are in subdirectories but v8 is not.  If you have an
existing shortcut that you are using, then perhaps it is pointed to the v7
directory and needs to be updated to point to the directory that v8 is
installed in.  That would also explain why you had no problems with minor
updates of v7 but are seeing this after moving to v8.

Craig

>

RE: pgAdmin 8.1 install

[David Barron]

>  Is there a problem with the installation of 8.1?  I’ve installed it 3 times 
> but when I bring it up it still says I’m at 7.8.  The Help > About menu shows 
> 7.8 as well.

> It’s odd because I have gone from 7.6 to 7.7 and 7.7 to 7.8 following the 
> same steps.

When I look at the directory on my machine that pgAdmin is installed to, I see 
the following:

C:\Users\ \AppData\Local\Programs\pgAdmin 4>dir
 Volume in drive C has no label.
 Volume Serial Number is 42DF-4B8A

 Directory of C:\Users\\AppData\Local\Programs\pgAdmin 4

11/28/2023  09:44 AM  .
11/28/2023  09:44 AM  ..
11/20/2023  12:26 PM91,965 DEPENDENCIES
11/28/2023  09:43 AM  docs
11/28/2023  09:44 AM  installer
11/20/2023  12:26 PM 1,196 LICENSE
11/20/2023  12:26 PM35,147 pgAdmin4.ico
11/28/2023  09:44 AM  python
11/28/2023  12:50 PM  runtime
11/20/2023  12:46 PM 3,417,277 sbom.json
11/28/2023  09:44 AM 4,766,927 unins000.dat
11/28/2023  09:43 AM 3,166,605 unins000.exe
02/01/2023  10:59 AM  v6
08/16/2023  01:17 PM  v7
11/28/2023  09:49 AM  web

Note that v6 and v7 are in subdirectories but v8 is not.  If you have an 
existing shortcut that you are using, then perhaps it is pointed to the v7 
directory and needs to be updated to point to the directory that v8 is 
installed in.  That would also explain why you had no problems with minor 
updates of v7 but are seeing this after moving to v8.

Craig

Good thinking but no.  All I have in 
D:\Users\dbarron-aas\AppData\Local\Programs\pgAdmin 4 is the v7 directory.

Re: pgAdmin 8.1 install

[Adrian Klaver]

On 1/10/24 12:20, David Barron wrote:

Note that v6 and v7 are in subdirectories but v8 is not.  If you have an 
existing shortcut that you are using, then perhaps it is pointed to the 
v7 directory and needs to be updated to point to the directory that v8 
is installed in.  That would also explain why you had no problems with 
minor updates of v7 but are seeing this after moving to v8.


Craig

Good thinking but no.  All I have in 
D:\Users\dbarron-aas\AppData\Local\Programs\pgAdmin 4 is the v7 directory.


I downloaded and installed pgAdmin 8.1 and it shows up as such when I 
run it.


Look in:

D:\Users\dbarron-aas\AppData\Local\Programs\pgAdmin 4\runtime

for

pgAdmin4

Then double click on that. I'm guessing it will be 8.1.

Per Craig's comment I'm betting that the icon/shortcut you are clicking 
on currently is pointing back at the 7.x version.


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

RE: pgAdmin 8.1 install

[David Barron]

On 1/10/24 12:20, David Barron wrote:

> Note that v6 and v7 are in subdirectories but v8 is not.  If you have 
> an existing shortcut that you are using, then perhaps it is pointed to 
> the
> v7 directory and needs to be updated to point to the directory that v8 
> is installed in.  That would also explain why you had no problems with 
> minor updates of v7 but are seeing this after moving to v8.
>
> Craig
>
> Good thinking but no.  All I have in
> D:\Users\dbarron-aas\AppData\Local\Programs\pgAdmin 4 is the v7 directory.

I downloaded and installed pgAdmin 8.1 and it shows up as such when I run it.

Look in:

D:\Users\dbarron-aas\AppData\Local\Programs\pgAdmin 4\runtime

for

pgAdmin4

Then double click on that. I'm guessing it will be 8.1.

Per Craig's comment I'm betting that the icon/shortcut you are clicking on 
currently is pointing back at the 7.x version.


The icon I am using to start points to 
D:\Users\dbarron-aas\AppData\Local\Programs\pgAdmin 4\v7\runtime\pgAdmin4.exe  
I did a search and that is the only pgAdmin4.exe installed.  When I 
double-click on it, 7.8 comes up.
I just went through the install again and there is no v8 directory but watching 
the install run I see that it's copying files to C:\Program Files\pgAdmin 4 
instead.  
Interesting.  And Look!  It's 8.1.  Who would have thought.

Well, that's straightened out, anyway.  Now, can I uninstall v7 without losing 
all my connection data?

Postgres 13 streaming replication standby not sending password, 'fe_sendauth: no password supplied'

[Keaney, Will]

Hello,

I'm building a new 2-node Postgreql 13 streaming replication cluster. I'm able 
to clone the primary to the standby using pg_basebackup.
However, the standby is unable to authenticate to the primary to begin recovery 
during startup. It logs an error, "FATAL:  could not connect to the primary 
server: fe_sendauth: no password supplied".

I've tried using both .pgpass and passing the password explicitly in the 
myrecovery.conf primary_conninfo string. Debug logs on the primary show the 
initial connection. but no user provided and no authentication attempt.

pg_hba.conf on the primary:
hostall,replication replprimary-database-server   
scram-sha-256
hostall,replication replstandby-database-server   
scram-sha-256

myrecovery.conf on the standby:
primary_conninfo = 'host=primary-database-server port=5432 user=repl 
application_name=standby-server-name'
recovery_target_timeline = 'latest'
primary_slot_name = 'standby_replication_slot'

.pgpass on the standby:
# hostname:port:database:username:password
*:*:replication:repl:repl_user_password

I've triple-checked that the password in .pgpass matches the password set for 
the repl user in the database, and the repl user has REPLICATION access.
I'm able to connect to the primary server using the repl user and the psql 
client, both via .pgpass and providing the password directly.
I can't figure out why the standby postgres server is skipping the provided 
credentials when connecting to the primary.

Thank you for your time,

Will Keaney



This e-mail message, including any attachments, is for the sole use of the 
intended recipient(s) and may contain information that is confidential and 
protected by law from unauthorized disclosure. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply e-mail and destroy all copies of 
the original message.

Re: Postgres 13 streaming replication standby not sending password, 'fe_sendauth: no password supplied'

[Ron Johnson]

On Wed, Jan 10, 2024 at 5:51 PM Keaney, Will  wrote:

> Hello,
>
> I'm building a new 2-node Postgreql 13 streaming replication cluster. I'm
> able to clone the primary to the standby using pg_basebackup.
> However, the standby is unable to authenticate to the primary to begin
> recovery during startup. It logs an error, "FATAL:  could not connect to
> the primary server: fe_sendauth: no password supplied".
>
> I've tried using both .pgpass and passing the password explicitly in the
> myrecovery.conf primary_conninfo string. Debug logs on the primary show the
> initial connection. but no user provided and no authentication attempt.
>
> pg_hba.conf on the primary:
> hostall,replication replprimary-database-server
>scram-sha-256
> hostall,replication replstandby-database-server
>scram-sha-256
>
> myrecovery.conf on the standby:
> primary_conninfo = 'host=primary-database-server port=5432 user=repl
> application_name=standby-server-name'
> recovery_target_timeline = 'latest'
> primary_slot_name = 'standby_replication_slot'
>
> .pgpass on the standby:
> # hostname:port:database:username:password
> *:*:replication:repl:repl_user_password
>
> I've triple-checked that the password in .pgpass matches the password set
> for the repl user in the database, and the repl user has REPLICATION access.
> I'm able to connect to the primary server using the repl user and the psql
> client, both via .pgpass and providing the password directly.
> I can't figure out why the standby postgres server is skipping the
> provided credentials when connecting to the primary.
>
>
Let pg_basebackup do all the work for you:

$ cat ~postgres/.pg_service.conf
[basebackup]
host=
port=5432
user=replicator
passfile=/var/lib/pgsql/.pgpass

$ pg_basebackup \
--pgdata=$PGDATA \
--dbname=service=basebackup \
--verbose --progress \
--checkpoint=fast \
--write-recovery-conf \
--wal-method=stream \
--create-slot --slot=pgstandby1