pgadmin4 versions on Ubuntu 22.04

[Richard Welty]

i'm currently running pgadmin4 6.12 on a Ubuntu 22.04 desktop.

it's regularly notifying me that 6.15 is available. i installed using the

apt repository method in the docs, and no upgrade is available there

(or at least, that's what apt reports when i ask for one.)



wondering when the repo might get updated, or whether i should

be concerned about it at all.



thanks,

   richard

Re: pgadmin4 versions on Ubuntu 22.04

[Richard Welty]

may have just found the issue:



# deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 main # 

disabled on upgrade to jammy



will retry shortly.



richard







 On Thu, 10 Nov 2022 10:58:12 -0500 Adrian Klaver 
 wrote ---



On 11/10/22 07:48, Richard Welty wrote: 
> i'm currently running pgadmin4 6.12 on a Ubuntu 22.04 desktop. 
> it's regularly notifying me that 6.15 is available. i installed using the 
> apt repository method in the docs, and no upgrade is available there 
> (or at least, that's what apt reports when i ask for one.) 
 
What repo? 
 
Have you run apt update on the repo? 
 
What is the command you are using to do the upgrade? 
 
> 
> wondering when the repo might get updated, or whether i should 
> be concerned about it at all. 
> 
> thanks, 
>     richard 
> 
> 
 
-- 
Adrian Klaver 
mailto:adrian.kla...@aklaver.com

Re: pgadmin4 versions on Ubuntu 22.04

[Richard Welty]

and that was it. uncommented the line in 



/etc/apt/sources.list.d/pgadmin4.list



updated and asked for an upgrade and it worked.

only reason i found it was i went looking to verify which repo

i was using and saw the comment.



richard







 On Thu, 10 Nov 2022 11:15:02 -0500 Richard Welty  
wrote ---



may have just found the issue:



# deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 main # 

disabled on upgrade to jammy



will retry shortly.



richard







 On Thu, 10 Nov 2022 10:58:12 -0500 Adrian Klaver 
<mailto:adrian.kla...@aklaver.com> wrote ---











On 11/10/22 07:48, Richard Welty wrote: 
> i'm currently running pgadmin4 6.12 on a Ubuntu 22.04 desktop. 
> it's regularly notifying me that 6.15 is available. i installed using the 
> apt repository method in the docs, and no upgrade is available there 
> (or at least, that's what apt reports when i ask for one.) 
 
What repo? 
 
Have you run apt update on the repo? 
 
What is the command you are using to do the upgrade? 
 
> 
> wondering when the repo might get updated, or whether i should 
> be concerned about it at all. 
> 
> thanks, 
>     richard 
> 
> 
 
-- 
Adrian Klaver 
mailto:adrian.kla...@aklaver.com

Re: pgadmin4 versions on Ubuntu 22.04

[Richard Welty]

didn't notice that. pgadmin4 was working anyway but i changed it to jammy and

updated anyway.



richard







 On Thu, 10 Nov 2022 11:31:07 -0500 Adrian Klaver 
 wrote ---



On 11/10/22 08:19, Richard Welty wrote: 
> and that was it. uncommented the line in 
> 
> /etc/apt/sources.list.d/pgadmin4.list 
> 
> updated and asked for an upgrade and it worked. 
> only reason i found it was i went looking to verify which repo 
> i was using and saw the comment. 
 
Did you change 
 
https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal 
 
to 
 
https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/jammy 
 
? 
 
> 
> richard 
> 
> 
> 
>  On Thu, 10 Nov 2022 11:15:02 -0500 *Richard Welty 
> <mailto:rwe...@salesium.com>* wrote --- 
> 
> may have just found the issue: 
> 
> # deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal 
> <https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal> pgadmin4 
> main # 
> disabled on upgrade to jammy 
> 
> will retry shortly. 
> 
> richard 
> 
> 
> 
>  On Thu, 10 Nov 2022 10:58:12 -0500 *Adrian Klaver 
> <mailto:adrian.kla...@aklaver.com 
> <mailto:mailto:adrian.kla...@aklaver.com>>* 
> wrote --- 
> 
> 
> 
> 
> On 11/10/22 07:48, Richard Welty wrote: 
>  > i'm currently running pgadmin4 6.12 on a Ubuntu 22.04 desktop. 
>  > it's regularly notifying me that 6.15 is available. i 
> installed using the 
>  > apt repository method in the docs, and no upgrade is 
> available there 
>  > (or at least, that's what apt reports when i ask for one.) 
> 
> What repo? 
> 
> Have you run apt update on the repo? 
> 
> What is the command you are using to do the upgrade? 
> 
>  > 
>  > wondering when the repo might get updated, or whether i should 
>  > be concerned about it at all. 
>  > 
>  > thanks, 
>  >    richard 
>  > 
>  > 
> 
> -- 
> Adrian Klaver 
> mailto:adrian.kla...@aklaver.com <mailto:mailto:adrian.kla...@aklaver.com> 
> 
> 
> 
 
-- 
Adrian Klaver 
mailto:adrian.kla...@aklaver.com

access issue with postgresql 14 docker image on MacOS Sonoma

[Richard Welty]

we're using Postgresql 14 with our Laravel/Php appliction. it is working well

in EC2 and in a Docker image on my Ubuntu 22.04 desktop at work. however,

a while back it stopped working with Docker on my Macbook Air in response

to a major OS upgrade. wondering if anyone knows what i might need to change to 
fix

this.

the docker image is launching, i can open a terminal in docker-desktop and use 
psql

to connect to it and poke about with a stick. i can also access it via pgadmin 
using

localhost with port 5432 as expected.



but when i try to migrate the db schema using Laravel Sail, i get the following 
error:



  SQLSTATE[08006] [7] connection to server at "localhost" (127.0.0.1), port 
5432 failed: Connection refused

Is the server running on that host and accepting TCP/IP connections?

connection to server at "localhost" (::1), port 5432 failed: Cannot assign 
requested address

Is the server running on that host and accepting TCP/IP connections? (SQL: 
select tablename, concat('"', schemaname, '"."', tablename, '"') as 
qualifiedname from pg_catalog.pg_tables where schemaname in ('public'))



this suggests that postgresql is not being configured properly for access from 
the container

containing the web app. i have included the configurations from 
docker-compose.yml below -

but this is the same docker-compose.yml that is currently working on Ubuntu and 
was working

until the MacOS upgrade, so i'm kind of baffled.



TIA, richard



--



Docker compose snippet



   salesium.test:

    build:

    context: ./vendor/laravel/sail/runtimes/8.1

    dockerfile: Dockerfile

    args:

    WWWGROUP: '${WWWGROUP}'

    image: sail-8.1/app

    extra_hosts:

    - 'host.docker.internal:host-gateway'

    ports:

    - '${APP_PORT:-80}:80'

    - '${HMR_PORT:-8080}:8080'

    environment:

    WWWUSER: '${WWWUSER}'

    LARAVEL_SAIL: 1

    XDEBUG_MODE: '${SAIL_XDEBUG_MODE:-off}'

    XDEBUG_CONFIG: 
'${SAIL_XDEBUG_CONFIG:-client_host=host.docker.internal}'

    volumes:

    - '.:/var/www/html'

    networks:

    - sail

    depends_on:

    - pgsql

    pgsql:

    image: 'postgres:14'

    ports:

    - '${FORWARD_DB_PORT:-5432}:5432'

    environment:

    PGPASSWORD: '${DB_PASSWORD:-secret}'

    POSTGRES_DB: '${DB_DATABASE}'

    POSTGRES_USER: '${DB_USERNAME}'

    POSTGRES_PASSWORD: '${DB_PASSWORD:-secret}'

    volumes:

    - 'sail-pgsql:/var/lib/postgresql/data'

    - 
'./vendor/laravel/sail/database/pgsql/create-testing-database.sql:/docker-entrypoint-initdb.d/10-create-testing-database.sql'

    networks:

    - sail

    healthcheck:

    test: ["CMD", "pg_isready", "-q", "-d", "${DB_DATABASE}", "-U", 
"${DB_USERNAME}"]

    retries: 3

    timeout: 5s

Re: access issue with postgresql 14 docker image on MacOS Sonoma

[Richard Welty]

ok, sorted it out. one of two things happened, either docker got

fussier about correct environment settings on the upgrade, or i

screwed up an edit of the environment file. i prefer to blame

docker rather than myself for obvious reasons...



richard







 On Mon, 19 Feb 2024 17:00:10 -0500 Richard Welty  
wrote ---



we're using Postgresql 14 with our Laravel/Php appliction. it is working well

in EC2 and in a Docker image on my Ubuntu 22.04 desktop at work. however,

a while back it stopped working with Docker on my Macbook Air in response

to a major OS upgrade. wondering if anyone knows what i might need to change to 
fix

this.

the docker image is launching, i can open a terminal in docker-desktop and use 
psql

to connect to it and poke about with a stick. i can also access it via pgadmin 
using

localhost with port 5432 as expected.



but when i try to migrate the db schema using Laravel Sail, i get the following 
error:



  SQLSTATE[08006] [7] connection to server at "localhost" (127.0.0.1), port 
5432 failed: Connection refused

Is the server running on that host and accepting TCP/IP connections?

connection to server at "localhost" (::1), port 5432 failed: Cannot assign 
requested address

Is the server running on that host and accepting TCP/IP connections? (SQL: 
select tablename, concat('"', schemaname, '"."', tablename, '"') as 
qualifiedname from pg_catalog.pg_tables where schemaname in ('public'))



this suggests that postgresql is not being configured properly for access from 
the container

containing the web app. i have included the configurations from 
docker-compose.yml below -

but this is the same docker-compose.yml that is currently working on Ubuntu and 
was working

until the MacOS upgrade, so i'm kind of baffled.



TIA, richard



--



Docker compose snippet



   salesium.test:

    build:

    context: ./vendor/laravel/sail/runtimes/8.1

    dockerfile: Dockerfile

    args:

    WWWGROUP: '${WWWGROUP}'

    image: sail-8.1/app

    extra_hosts:

    - 'host.docker.internal:host-gateway'

    ports:

    - '${APP_PORT:-80}:80'

    - '${HMR_PORT:-8080}:8080'

    environment:

    WWWUSER: '${WWWUSER}'

    LARAVEL_SAIL: 1

    XDEBUG_MODE: '${SAIL_XDEBUG_MODE:-off}'

    XDEBUG_CONFIG: 
'${SAIL_XDEBUG_CONFIG:-client_host=host.docker.internal}'

    volumes:

    - '.:/var/www/html'

    networks:

    - sail

    depends_on:

    - pgsql

    pgsql:

    image: 'postgres:14'

    ports:

    - '${FORWARD_DB_PORT:-5432}:5432'

    environment:

    PGPASSWORD: '${DB_PASSWORD:-secret}'

    POSTGRES_DB: '${DB_DATABASE}'

    POSTGRES_USER: '${DB_USERNAME}'

    POSTGRES_PASSWORD: '${DB_PASSWORD:-secret}'

    volumes:

    - 'sail-pgsql:/var/lib/postgresql/data'

    - 
'./vendor/laravel/sail/database/pgsql/create-testing-database.sql:/docker-entrypoint-initdb.d/10-create-testing-database.sql'

    networks:

    - sail

    healthcheck:

    test: ["CMD", "pg_isready", "-q", "-d", "${DB_DATABASE}", "-U", 
"${DB_USERNAME}"]

    retries: 3

    timeout: 5s

Re: A way to optimize sql about the last temporary-related row

[Richard Welty]

not really in direct response to this conversation, but is there any reason

on the face of the planet why read receipts need to be sent to every single

recipient of the mailing list?



just saying,

  richard







 On Fri, 28 Jun 2024 03:20:26 -0400   wrote ---



HOO-HA! This is HUGE!

Only 2.2 seconds on my data Amazing!

distinct on (field) followed by "*" is a hidden gem!

Thank you so much and thanks to everyone who helped me!  Thank
  you very much!!

Cheers,

Agharta

 



Il 27/06/24 6:16 PM, David Rowley ha
  scritto:






On Fri, 28 Jun 2024, 3:20
  am mailto:aghart...@gmail.com,
  
  wrote:



 
 Now the query:
 explain (verbose, buffers, analyze)
 with last_table_ids as materialized(
    select xx from (
    select LAST_VALUE(pk_id) over (partition by
  integer_field_2 order by 
 datetime_field_1 RANGE BETWEEN UNBOUNDED PRECEDING AND
  UNBOUNDED 
 FOLLOWING) xx
    from test_table
    where integer_field_1 = 1
    and datetime_field_1 <= CURRENT_TIMESTAMP
    ) ww group by ww.xx
 
 ),
 last_row_per_ids as (
    select tt.* from last_table_ids lt
    inner join test_table tt on (tt.pk_id = lt.xx)
 
 )
 
 select * /* or count(*) */ from last_row_per_ids;
 
 
 This query, on my PC, takes 46 seconds!!!





(Away from laptop and using my phone)



Something like:



select distinct on (integer_field_2) * from
  test_table where integer_field_1 = 1 and datetime_field_1
  <= CURRENT_TIMESTAMP order by
  integer_field_2,datetime_field_1 desc;



Might run a bit faster.  However if it's slow
  due to I/O then maybe not much faster.  Your version took
  about 5 seconds on my phone and my version ran in 1.5 seconds.



It's difficult for me to check the results match
  with each query from my phone. A quick scan of the first 10 or
  so records looked good.



If the updated query is still too slow on cold
  cache then faster disks might be needed.



David

uninstalling postgresql 13 on ubuntu 22.04?

[Richard Welty]

i installed pg13 from the postgres repos but am not really using it

in that form; i'm using docker images instead. i now find i need

to uninstall it to facilitate upgrading my desktop to ubuntu 24



is apt remove an effective way to uninstall or is there a better method?



thanks,

   richard

Re: Getting error "too many clients already" despite having a db connection limit set

[Richard Welty]

a coding error i've seen from inexperienced devs with little database experience

is inattentiveness to how the DB connections were being opened. last time i

saw this, a smart young dev with no DB background did not understand the cost

of opening connections and had on the order of 30 php calls to open connections

to PostgreSQL in a single page. he did not understand why it was slow.



richard






 On Mon, 16 Jun 2025 12:39:09 -0400 Tom Lane  wrote ---



adolfo flores < mailto:adolfoflores2...@gmail.com > writes: 
> I hope you can help me with an issue we're experiencing. We have an app 
> running on Kubernetes that opens a huge number of connections within a 
> couple of seconds. 
 
You need to fix that app to be less unfriendly, or maybe put it behind 
a connection pooler. 
 
> Is it expected behavior to reach the max_connections limit when that app 
> opens many connections in a short period of time, even if a connection 
> limit is set for that database and everything else uses no more than 10% of 
> the max_connections? 
 
It takes a finite amount of time for a new backend process to figure 
out which database it's supposed to connect to and then detect whether 
the per-DB connection limit is exceeded.  In the meantime, that 
session does count against the global limit, so yeah this isn't 
surprising if the connection arrival rate is high enough. 
 
regards, tom lane