Re: ltree and PHP

[Tom Lane]

Open _  writes:
> I've created the ltree extensioncreated a table with an ltree column called 
> ltree_path
> In php I can access the table, insert, update, delete.but ltree operators 
> don't work.

It sounds like the ltree extension is in a schema that's not in the
search_path setting you're using from PHP.

regards, tom lane

updating(column) Porting from Oracle Trigger to PostgreSQL trigger

[Jagmohan Kaintura]

Hi Team,

When we do an implementation from Oracle to PostgreSQL in trigger for
clause like :
 updating(column_name) ==>   (TG_OP='UPDATE' and OLD.column_name IS
DISTINCT FROM NEW.column_name)
But this condition would mostly not be sufficient when we are updating any
column and with similar value somehow.
Like : column_name OLD values is 2 and New values updates is 2.

In PG it would become (TG_OP="UPDATE' and *2 is DISTINCT FROM 2*), Overall
it becomes FALSE , so from conversion from ORACLE to PostgreSQL it
doesn't give a clear picture for exact implementation for these UPDATING
clause.

Now why I brought up this as I got into one of the implementations which
made me crazy to implement.

Scenario :
Column c3 is right not updating to the same value of column, for some
bypass implementation.

update table
c1 = 'abc',
c2 ='xyz',
*c3=c3;*


Inside trigger we have implementation like :

*Oracle:*
--THis block is being placed at the start of Trigger to bypass all other
trigger blocks whenever we are updating column c3 , along with other
columns.
IF UPDATING('C3') THEN
   RETURN;
END IF;

If this column c3 is not placed in the update clause, then trigger would go
through and execute other statements.

*PostgreSQL:*
We use the same method as :
IF TC_OP='UPDATE' and OLD.c3 is DISTINCT FROM NEW.C3 THEN
   RETURN NEW;
END IF;

But here it won't ever go inside the IF clause and will never return out
the start of trigger only. So technically not able to map the same Oracle
fundamentals.

Is there any other method in POstgreSQL using which I can check which
column is getting updated. Will just replace at least this clause with any
other block.

Help would be really appreciated.

*Best Regards,*
Jagmohan

ltree and PHP

[Open _]

I've created the ltree extensioncreated a table with an ltree column called 
ltree_path
In php I can access the table, insert, update, delete.but ltree operators don't 
work.
select count() from schema.table 
where 'A.B.C' @> ltree_path;
gives me a "could not determine polymorphic type anyrange because input has 
type unknown'
The same statement works just fine in psql, but not in PHP
If I change the statement to:

select count() from schema.table 
where cast('A.B.C' as schema.ltree) @> ltree_path;
I get a different error: "operator does not exist:"
pointing to the @>
Is there someplace to 'create' or include ltree in PHP ?

Re: Unable To Drop Tablespace

[Thomas Munro]

On Fri, Feb 5, 2021 at 12:43 PM Ian Lawrence Barwick  wrote:
> 2021年2月5日(金) 3:52 Pavan Pusuluri :
>> We are trying to drop a table space on RDS Postgres . We have removed the 
>> objects etc, but it still won't drop.
>>
>> I have checked and there's no reference anywhere to this tablespace but it 
>> complains it's not empty.
>>
>> I checked if it is a default for a database, revoked all privileges on the 
>> tablespace.
>>
>> We dropped the database but underpinning tablespace remained but when I 
>> query to see if any reference i get no hits.
>>
>> "Select c.relname,t.spcname from pg_class c JOIN pg_tablespace t ON 
>> c.reltablespace=t.oid where t.spcname='mytablespace'
>>
>> I dont find any objects referencing. Kindly let me know if anything else 
>> needs to be checked?
>
>
> There's a handy function "pg_tablespace_databases()" to check which databases
> might still have objects in a database. There are a couple of useful queries 
> demonstrating
> usage here:
>
>   https://pgpedia.info/p/pg_tablespace_databases.html

It's also possible for there to be stray files in there, in some crash
scenarios where PostgreSQL doesn't currently clean up relation files
that it ideally should.  The one with the widest window AFAIK is where
you crash after creating a table but before committing[1].  You'd need
a directory listing to investigate that.

[1] 
https://www.postgresql.org/message-id/flat/CAEepm%3D0ULqYgM2aFeOnrx6YrtBg3xUdxALoyCG%2BXpssKqmezug%40mail.gmail.com

Re: Unable To Drop Tablespace

[Pavan Pusuluri]

Thank you very much Ian. Will check it out.

Regards


On Thu, Feb 4, 2021, 5:43 PM Ian Lawrence Barwick  wrote:

> 2021年2月5日(金) 3:52 Pavan Pusuluri :
>
>> Hi there
>>
>> We are trying to drop a table space on RDS Postgres . We have removed the
>> objects etc, but it still won't drop.
>>
>> I have checked and there's no reference anywhere to this tablespace but
>> it complains it's not empty.
>>
>> I checked if it is a default for a database, revoked all privileges on
>> the tablespace.
>>
>> We dropped the database but underpinning tablespace remained but when I
>> query to see if any reference i get no hits.
>>
>> "Select c.relname,t.spcname from pg_class c JOIN pg_tablespace t ON
>> c.reltablespace=t.oid where t.spcname='mytablespace'
>>
>> I dont find any objects referencing. Kindly let me know if anything else
>> needs to be checked?
>>
>
> There's a handy function "pg_tablespace_databases()" to check which
> databases
> might still have objects in a database. There are a couple of useful
> queries demonstrating
> usage here:
>
>   https://pgpedia.info/p/pg_tablespace_databases.html
>
> Regards
>
> Ian Barwick
>
>
> --
> EnterpriseDB: https://www.enterprisedb.com
>
>

Re: Unable To Drop Tablespace

[Ian Lawrence Barwick]

2021年2月5日(金) 3:52 Pavan Pusuluri :

> Hi there
>
> We are trying to drop a table space on RDS Postgres . We have removed the
> objects etc, but it still won't drop.
>
> I have checked and there's no reference anywhere to this tablespace but it
> complains it's not empty.
>
> I checked if it is a default for a database, revoked all privileges on the
> tablespace.
>
> We dropped the database but underpinning tablespace remained but when I
> query to see if any reference i get no hits.
>
> "Select c.relname,t.spcname from pg_class c JOIN pg_tablespace t ON
> c.reltablespace=t.oid where t.spcname='mytablespace'
>
> I dont find any objects referencing. Kindly let me know if anything else
> needs to be checked?
>

There's a handy function "pg_tablespace_databases()" to check which
databases
might still have objects in a database. There are a couple of useful
queries demonstrating
usage here:

  https://pgpedia.info/p/pg_tablespace_databases.html

Regards

Ian Barwick


-- 
EnterpriseDB: https://www.enterprisedb.com

Re: Partition Creation Permissions

[Samuel Nelson]

Ah, I didn't realize that was an option on the function.  They're already
being created by a trigger (the table is partitioned on a foreign key, so
partitions are created by a trigger on the referenced table); it sounds
like I can just update that trigger function with `security definer`.

-Sam

https://git.sr.ht/~nelsam
https://github.com/nelsam

"As an adolescent I aspired to lasting fame, I craved factual certainty, and
I thirsted for a meaningful vision of human life -- so I became a scientist.
This is like becoming an archbishop so you can meet girls."
-- Matt Cartmill


On Thu, Feb 4, 2021 at 4:42 PM David G. Johnston 
wrote:

> On Thu, Feb 4, 2021 at 3:39 PM Samuel Nelson 
> wrote:
>
>> I've been trying to restrict permissions of some users in our system and
>> noticed that `create table foo partition of bar for values from (x) to (y)`
>> complains that I must be the owner of the table.  Is there another GRANT I
>> can give to my user to allow creation and dropping of partitions without
>> allowing them to drop the parent table?
>>
>
>
> I doubt it...might want to consider writing a security definer function
> that you can give them permission to run instead of having them do things
> directly.
>
> David J.
>

Re: Partition Creation Permissions

[David G. Johnston]

On Thu, Feb 4, 2021 at 3:39 PM Samuel Nelson 
wrote:

> I've been trying to restrict permissions of some users in our system and
> noticed that `create table foo partition of bar for values from (x) to (y)`
> complains that I must be the owner of the table.  Is there another GRANT I
> can give to my user to allow creation and dropping of partitions without
> allowing them to drop the parent table?
>


I doubt it...might want to consider writing a security definer function
that you can give them permission to run instead of having them do things
directly.

David J.

Partition Creation Permissions

[Samuel Nelson]

Hi list!

I've been trying to restrict permissions of some users in our system and
noticed that `create table foo partition of bar for values from (x) to (y)`
complains that I must be the owner of the table.  Is there another GRANT I
can give to my user to allow creation and dropping of partitions without
allowing them to drop the parent table?

-Sam

https://git.sr.ht/~nelsam
https://github.com/nelsam

"As an adolescent I aspired to lasting fame, I craved factual certainty, and
I thirsted for a meaningful vision of human life -- so I became a scientist.
This is like becoming an archbishop so you can meet girls."
-- Matt Cartmill

Re: vacuumdb not letting me connect to db

[Ravi Krishna]

>There is no error message, when I try to connect the database while 
>running vacuumdb with 300 jobs, it gets stuck.

But you mentioned max connection which now seems to be a red herring.
Based on your description, the impression I got is that you are getting
"sorry, too many clients already" error.

As others have pointed out, the db is probably saturated with I/O error.

Unable To Drop Tablespace

[Pavan Pusuluri]

Hi there

We are trying to drop a table space on RDS Postgres . We have removed the
objects etc, but it still won't drop.

I have checked and there's no reference anywhere to this tablespace but it
complains it's not empty.

I checked if it is a default for a database, revoked all privileges on the
tablespace.

We dropped the database but underpinning tablespace remained but when I
query to see if any reference i get no hits.

"Select c.relname,t.spcname from pg_class c JOIN pg_tablespace t ON
c.reltablespace=t.oid where t.spcname='mytablespace'

I dont find any objects referencing. Kindly let me know if anything else
needs to be checked?


Regards
Pavan

Re: vacuumdb not letting me connect to db

[Ron]

Your problem screams "IO saturation".

On 2/4/21 12:07 PM, Atul Kumar wrote:
There is no error message, when I try to connect the database while 
running vacuumdb with 300 jobs, it gets stuck.


On Thursday, February 4, 2021, Ravi Krishna > wrote:


>The CPU and RAM are normal even on 300 jobs ( only 1-4% of consumption)
>but I don’t understand one thing here that if max_connections is set
to 700 then
>why I am not able to connect the db. As the running jobs (300) are
lesser than
>half of max_connections.

Please paste the error message



--
Angular momentum makes the world go 'round.

Re: vacuumdb not letting me connect to db

[Atul Kumar]

There is no error message, when I try to connect the database while running
vacuumdb with 300 jobs, it gets stuck.

On Thursday, February 4, 2021, Ron  wrote:

> On 2/4/21 5:26 AM, Atul Kumar wrote:
>
> Hi,
>
> I have 160 GB of RAM, postgres 9.6 is running on the server.
>
> after upgrade I ran the below command:
>
> "/usr/edb/as9.6/bin/vacuumdb" -U 'enterprisedb' --verbose --all -j 300
> --analyze-only
>
> after running that  command I was not able to connect the database
> using psql for few minutes.
>
>
> What's the exact error message?
>
> After 20-30 minutes i was able to connect to the db and at that time I
> checked the pg_stst_activity, the active connections was reduced to
> 27.
>
> my max_connections is set to 700.
>
> I tried to find out the reason for not being abled to connect the db
> (when 300 jobs were running) but still not got the answer. So
>
>
> Connect to the cluster *before* running "vacuumdb -j300", and start
> looking at pg_stst_activity while vacuumdb is running.
>
> --
> Angular momentum makes the world go 'round.
>

Re: vacuumdb not letting me connect to db

[Atul Kumar]

There is no error message, when I try to connect the database while running
vacuumdb with 300 jobs, it gets stuck.

On Thursday, February 4, 2021, Ravi Krishna  wrote:

> >The CPU and RAM are normal even on 300 jobs ( only 1-4% of consumption)
> >but I don’t understand one thing here that if max_connections is set to
> 700 then
> >why I am not able to connect the db. As the running jobs (300) are lesser
> than
> >half of max_connections.
>
> Please paste the error message
>
>

Re: vacuumdb not letting me connect to db

[Ravi Krishna]

>The CPU and RAM are normal even on 300 jobs ( only 1-4% of consumption) 
>but I don’t understand one thing here that if max_connections is set to 700 
>then 
>why I am not able to connect the db. As the running jobs (300) are lesser than 
>half of max_connections.

Please paste the error message

Re: vacuumdb not letting me connect to db

[Ron]

What about disk IO?  That's what really gets saturated when running 300 threads.

On 2/4/21 11:00 AM, Atul Kumar wrote:

Hi,

The CPU and RAM are normal even on 300 jobs ( only 1-4% of consumption) 
but I don’t understand one thing here that if max_connections is set to 
700 then why I am not able to connect the db.


As the running jobs (300) are lesser than half of max_connections.


Regards
Atul


On Thursday, February 4, 2021, Laurenz Albe > wrote:


On Thu, 2021-02-04 at 16:56 +0530, Atul Kumar wrote:
> I have 160 GB of RAM, postgres 9.6 is running on the server.
>
> after upgrade I ran the below command:
>
> "/usr/edb/as9.6/bin/vacuumdb" -U 'enterprisedb' --verbose --all -j 300
> --analyze-only
>
> after running that  command I was not able to connect the database
> using psql for few minutes.

That is to be expected.

If you have 300 processes performing I/O and using CPU, your machine
will vertainly be overloaded.

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





--
Angular momentum makes the world go 'round.

Re: vacuumdb not letting me connect to db

[Ron]

On 2/4/21 5:26 AM, Atul Kumar wrote:

Hi,

I have 160 GB of RAM, postgres 9.6 is running on the server.

after upgrade I ran the below command:

"/usr/edb/as9.6/bin/vacuumdb" -U 'enterprisedb' --verbose --all -j 300
--analyze-only

after running that  command I was not able to connect the database
using psql for few minutes.


What's the exact error message?


After 20-30 minutes i was able to connect to the db and at that time I
checked the pg_stst_activity, the active connections was reduced to
27.

my max_connections is set to 700.

I tried to find out the reason for not being abled to connect the db
(when 300 jobs were running) but still not got the answer. So


Connect to the cluster *before* running "vacuumdb -j300", and start looking 
at pg_stst_activity while vacuumdb is running.


--
Angular momentum makes the world go 'round.

Re: vacuumdb not letting me connect to db

[Atul Kumar]

Hi,

The CPU and RAM are normal even on 300 jobs ( only 1-4% of consumption) but
I don’t understand one thing here that if max_connections is set to 700
then why I am not able to connect the db.

As the running jobs (300) are lesser than half of max_connections.


Regards
Atul


On Thursday, February 4, 2021, Laurenz Albe 
wrote:

> On Thu, 2021-02-04 at 16:56 +0530, Atul Kumar wrote:
> > I have 160 GB of RAM, postgres 9.6 is running on the server.
> >
> > after upgrade I ran the below command:
> >
> > "/usr/edb/as9.6/bin/vacuumdb" -U 'enterprisedb' --verbose --all -j 300
> > --analyze-only
> >
> > after running that  command I was not able to connect the database
> > using psql for few minutes.
>
> That is to be expected.
>
> If you have 300 processes performing I/O and using CPU, your machine
> will vertainly be overloaded.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: Request for example using pqconnectstart,pqconnectpoll part.

[Laurenz Albe]

On Thu, 2021-02-04 at 15:27 +, Om Prakash Jaiswal wrote:
> Please provide a sample example using pqconnectstart , pqconnectpoll to make
> a asynchronous connection and executing a query for client application in C.
> It is part of libpq - C Library.
> I am able to do query using PQconnectdb and PQexec.
> I have also used PQconnectdb and PQsendQuery.  PQconnectdb is blocking call,
>  now I want to replace PQconnectdb with non blocking connection 
> pqconnectstart and pqconnectpoll.
> 
> Please provide an example.

I used it in
https://github.com/laurenz/pgreplay/blob/master/database.c#L404
but it certainly is not the most concise example.

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

Request for example using pqconnectstart,pqconnectpoll part.

[Om Prakash Jaiswal]

Hi,Please provide a sample example using pqconnectstart , pqconnectpoll to make 
 a asynchronous connection and executing a query for client application in C.It 
is part of 
libpq - C Library.
I am able to do query using PQconnectdb and PQexec.I have also used PQconnectdb 
and PQsendQuery.  PQconnectdb is blocking call, now I want to replace 
PQconnectdb with non blocking connection pqconnectstart and pqconnectpoll.
Please provide an example.


Thanks in advance.
RegardsOm PrakashDBA, Bangalore

Re: cant connect to localhost:5432 (but unix socket ok)

[Joao Miguel Ferreira]

On Thu, Feb 4, 2021 at 3:04 PM Joao Miguel Ferreira <
joao.miguel.c.ferre...@gmail.com> wrote:

>
>
> On Thu, Feb 4, 2021 at 3:02 PM Joao Miguel Ferreira <
> joao.miguel.c.ferre...@gmail.com> wrote:
>
>> Hi Tom
>>
>> On Thu, Feb 4, 2021 at 2:50 PM Tom Lane  wrote:
>>
>>> Joao Miguel Ferreira  writes:
>>> > On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski <
>>> dep...@depesz.com>
>>> > wrote:
>>> >>> My database is not listening on TCP/localhost, desptite it is
>>> listening
>>>  on the unix socket. How can I investigate this?
>>>
>>> > it's on 5433:
>>>
>>> Hmm, something odd there, because a port number mismatch should have
>>> resulted in psql failing to connect via unix socket either.  Maybe
>>> you have more than one active postmaster?
>>>
>>
> "ps xauwww | grep postgres" shows only one postgres process (and a few
> vaccum related)
>

here is the full list:

root@deb10tp:~# ps xauww | grep postgres
postgres   825  0.0  0.1 213472 14980 ?S09:59   0:01
/usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c
config_file=/etc/postgresql/11/main/postgresql.conf
postgres   847  0.0  0.0 213572  5660 ?Ss   09:59   0:00 postgres:
11/main: checkpointer
postgres   848  0.0  0.0 213472  3808 ?Ss   09:59   0:00 postgres:
11/main: background writer
postgres   849  0.0  0.0 213472  3688 ?Ss   09:59   0:00 postgres:
11/main: walwriter
postgres   850  0.0  0.0 214012  5628 ?Ss   09:59   0:01 postgres:
11/main: autovacuum launcher
postgres   852  0.0  0.4 102172 34612 ?Ss   09:59   0:10 postgres:
11/main: stats collector
postgres   853  0.0  0.0 213880  4260 ?Ss   09:59   0:00 postgres:
11/main: logical replication launcher
root  9652  0.0  0.0   6208   884 pts/1S+   15:06   0:00 grep
postgres
root@deb10tp:~#




>
>
>>
>> yes, I see your point. makes sense. the unix socket is actually also on
>> 5433
>>
>> root@deb10tp:~# grep -nr 543 /etc/postgresql
>> /etc/postgresql/11/main/postgresql.conf:63:port = 5433
>> root@deb10tp:~# find /var/run/postgresql/ | grep 543
>> /var/run/postgresql/.s.PGSQL.5433
>> /var/run/postgresql/.s.PGSQL.5433.lock
>> root@deb10tp:~#
>>
>>
>>>
>>> Anyway, given these settings, "psql -p 5433 -h localhost" should
>>> connect.  If you still get "connection refused" then you need to
>>> look at the kernel firewall (packet filter) settings.
>>>
>>
>> yes, with "-p 5433" I can connect
>>
>>
>>>
>>> regards, tom lane
>>
>>
>> thanks
>>
>>

Re: PgAdmin 4 GUI not responding

[Ron]

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

On 2/4/21 3:46 AM, Adith Suresh wrote:

Hi Team,

I have installed postgres 13 on windows server and I am facing an issue 
while opening PgAdmin 4.
The GUI is not responding. I have tried restarting the service, still the 
issue is persisting.


Please provide help for fixing this issue as early as possible

Regards,
Adith Suresh


--
Angular momentum makes the world go 'round.

Re: cant connect to localhost:5432 (but unix socket ok)

[Joao Miguel Ferreira]

On Thu, Feb 4, 2021 at 3:02 PM Joao Miguel Ferreira <
joao.miguel.c.ferre...@gmail.com> wrote:

> Hi Tom
>
> On Thu, Feb 4, 2021 at 2:50 PM Tom Lane  wrote:
>
>> Joao Miguel Ferreira  writes:
>> > On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski <
>> dep...@depesz.com>
>> > wrote:
>> >>> My database is not listening on TCP/localhost, desptite it is
>> listening
>>  on the unix socket. How can I investigate this?
>>
>> > it's on 5433:
>>
>> Hmm, something odd there, because a port number mismatch should have
>> resulted in psql failing to connect via unix socket either.  Maybe
>> you have more than one active postmaster?
>>
>
"ps xauwww | grep postgres" shows only one postgres process (and a few
vaccum related)


>
> yes, I see your point. makes sense. the unix socket is actually also on
> 5433
>
> root@deb10tp:~# grep -nr 543 /etc/postgresql
> /etc/postgresql/11/main/postgresql.conf:63:port = 5433
> root@deb10tp:~# find /var/run/postgresql/ | grep 543
> /var/run/postgresql/.s.PGSQL.5433
> /var/run/postgresql/.s.PGSQL.5433.lock
> root@deb10tp:~#
>
>
>>
>> Anyway, given these settings, "psql -p 5433 -h localhost" should
>> connect.  If you still get "connection refused" then you need to
>> look at the kernel firewall (packet filter) settings.
>>
>
> yes, with "-p 5433" I can connect
>
>
>>
>> regards, tom lane
>
>
> thanks
>
>

Re: cant connect to localhost:5432 (but unix socket ok)

[Joao Miguel Ferreira]

Hi Tom

On Thu, Feb 4, 2021 at 2:50 PM Tom Lane  wrote:

> Joao Miguel Ferreira  writes:
> > On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski <
> dep...@depesz.com>
> > wrote:
> >>> My database is not listening on TCP/localhost, desptite it is listening
>  on the unix socket. How can I investigate this?
>
> > it's on 5433:
>
> Hmm, something odd there, because a port number mismatch should have
> resulted in psql failing to connect via unix socket either.  Maybe
> you have more than one active postmaster?
>

yes, I see your point. makes sense. the unix socket is actually also on 5433

root@deb10tp:~# grep -nr 543 /etc/postgresql
/etc/postgresql/11/main/postgresql.conf:63:port = 5433
root@deb10tp:~# find /var/run/postgresql/ | grep 543
/var/run/postgresql/.s.PGSQL.5433
/var/run/postgresql/.s.PGSQL.5433.lock
root@deb10tp:~#


>
> Anyway, given these settings, "psql -p 5433 -h localhost" should
> connect.  If you still get "connection refused" then you need to
> look at the kernel firewall (packet filter) settings.
>

yes, with "-p 5433" I can connect


>
> regards, tom lane


thanks

Re: cant connect to localhost:5432 (but unix socket ok)

[Tom Lane]

Joao Miguel Ferreira  writes:
> On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski 
> wrote:
>>> My database is not listening on TCP/localhost, desptite it is listening
 on the unix socket. How can I investigate this?

> it's on 5433:

Hmm, something odd there, because a port number mismatch should have
resulted in psql failing to connect via unix socket either.  Maybe
you have more than one active postmaster?

Anyway, given these settings, "psql -p 5433 -h localhost" should
connect.  If you still get "connection refused" then you need to
look at the kernel firewall (packet filter) settings.

regards, tom lane

PgAdmin 4 GUI not responding

[Adith Suresh]

Hi Team,

I have installed postgres 13 on windows server and I am facing an issue
while opening PgAdmin 4.
The GUI is not responding. I have tried restarting the service, still the
issue is persisting.

Please provide help for fixing this issue as early as possible

Regards,
Adith Suresh

Re: cant connect to localhost:5432 (but unix socket ok)

[Joao Miguel Ferreira]

On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski 
wrote:

> On Thu, Feb 04, 2021 at 02:20:10PM +, Joao Miguel Ferreira wrote:
> > My database is not listening on TCP/localhost, desptite it is listening
> on the unix socket. How can I investigate this?
> > I could have done something that is out of my understanding because I
> have been loading some big pg_dumpall files that might contain
> > administrative changes that I am not fully aware of.
>
> There are couple of potential issues:
> 1. it might listen on different port than 5432. What does "show port;"
>show in psql?
> 2. it could be that there is a firewall (weird, but possible)
> 3. it could be that it's listening on another address(es) than
>127.0.0.1 / ::1 - what is output of "show listen_addresses;"?
> 4. Verify that it really does listen on something. As root run:
>ss -ntlp | grep postgres
>

it's on 5433:

postgres=# show port;
 port
--
 5433
(1 row)

postgres=# show listen_addresses;
 listen_addresses
--
 localhost
(1 row)

postgres=#
postgres=# \q
postgres@deb10tp:/$ exit
root@deb10tp:~# ss -ntlp | grep postgres
LISTEN0 128  127.0.0.1:5433 0.0.0.0:*
 users:(("postgres",pid=825,fd=5))

LISTEN0 128  [::1]:5433[::]:*
 users:(("postgres",pid=825,fd=3))

root@deb10tp:~#

Thank you very much


>
> depesz
>
> > Here are some details about the connection issue:
> > root@deb10tp:~# id
> > uid=0(root) gid=0(root) groups=0(root)
> > root@deb10tp:~# su postgres
> > postgres@deb10tp:/root$ id
> > uid=116(postgres) gid=126(postgres) groups=126(postgres),125(ssl-cert)
> > postgres@deb10tp:/root$ cd /
> > postgres@deb10tp:/$ psql -h localhost
> > psql: could not connect to server: Connection refused
> > Is the server running on host "localhost" (::1) and accepting
> > TCP/IP connections on port 5432?
> > could not connect to server: Connection refused
> > Is the server running on host "localhost" (127.0.0.1) and accepting
> > TCP/IP connections on port 5432?
> > postgres@deb10tp:/$ psql
> > psql (11.9 (Debian 11.9-0+deb10u1))
> > Type "help" for help.
> > postgres=#
> > Thank you
> > Joao
>

Re: cant connect to localhost:5432 (but unix socket ok)

[hubert depesz lubaczewski]

On Thu, Feb 04, 2021 at 02:20:10PM +, Joao Miguel Ferreira wrote:
> My database is not listening on TCP/localhost, desptite it is listening on 
> the unix socket. How can I investigate this?
> I could have done something that is out of my understanding because I have 
> been loading some big pg_dumpall files that might contain
> administrative changes that I am not fully aware of.

There are couple of potential issues:
1. it might listen on different port than 5432. What does "show port;"
   show in psql?
2. it could be that there is a firewall (weird, but possible)
3. it could be that it's listening on another address(es) than
   127.0.0.1 / ::1 - what is output of "show listen_addresses;"?
4. Verify that it really does listen on something. As root run:
   ss -ntlp | grep postgres

depesz

> Here are some details about the connection issue:
> root@deb10tp:~# id
> uid=0(root) gid=0(root) groups=0(root)
> root@deb10tp:~# su postgres
> postgres@deb10tp:/root$ id
> uid=116(postgres) gid=126(postgres) groups=126(postgres),125(ssl-cert)
> postgres@deb10tp:/root$ cd /
> postgres@deb10tp:/$ psql -h localhost
> psql: could not connect to server: Connection refused
> Is the server running on host "localhost" (::1) and accepting
> TCP/IP connections on port 5432?
> could not connect to server: Connection refused
> Is the server running on host "localhost" (127.0.0.1) and accepting
> TCP/IP connections on port 5432?
> postgres@deb10tp:/$ psql
> psql (11.9 (Debian 11.9-0+deb10u1))
> Type "help" for help.
> postgres=#
> Thank you
> Joao

cant connect to localhost:5432 (but unix socket ok)

[Joao Miguel Ferreira]

Hello all,

My database is not listening on TCP/localhost, desptite it is listening on
the unix socket. How can I investigate this?

I could have done something that is out of my understanding because I have
been loading some big pg_dumpall files that might contain administrative
changes that I am not fully aware of.

Here are some details about the connection issue:

root@deb10tp:~# id
uid=0(root) gid=0(root) groups=0(root)
root@deb10tp:~# su postgres
postgres@deb10tp:/root$ id
uid=116(postgres) gid=126(postgres) groups=126(postgres),125(ssl-cert)
postgres@deb10tp:/root$ cd /
postgres@deb10tp:/$ psql -h localhost
psql: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?
postgres@deb10tp:/$ psql
psql (11.9 (Debian 11.9-0+deb10u1))
Type "help" for help.
postgres=#

Thank you
Joao

Re: when is useful min_wal_size?

[Laurenz Albe]

On Thu, 2021-02-04 at 12:41 +0100, Luca Ferrari wrote:
> this may sound trivial, but in my opinion min_wal_size is useful only
> when the cluster is initialized or the wals are reset.
> Am I wrong?

The amount of WAL that PostgreSQL pre-creates for future use
depends on the database activity but has a lower limit of "min_wal_size".

If your workload is subject to long lulls followed by activity spikes,
raising "min_wal_size" can improve the performance at the beginning
of an activity spike.

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

Re: curious vacuum full behavior

[Laurenz Albe]

On Thu, 2021-02-04 at 10:03 +, Zwettler Markus (OIZ) wrote:
> I have 2 identical systems A + B.
> B being a clone of A.
> 
> The table pg_catalog.pg_largeobject was identical on both systems: 300GB in 
> total size; 100GB bloated.
> 
> I did following on A:
> ð  vacuum full pg_catalog.pg_largeobject;
> (using the default maintenance_work_mem of 64MB)
> It took around 45 minutes and increased the diskspace by around 125% until 
> the vacuum had been finished.
> 
> I did following on B:
> ð  set maintenance_work_mem = '256MB';
> ð  vacuum full pg_catalog.pg_largeobject;
> This took around 5 minutes. I don't know if the diskspace ever increased.
> 
> I was really surprised.
> Is there any explanation on this behavior?
> Is vacuum full heavily using on-disk sort areas if maintenance_work_mem is 
> too low?
> 
> Postgres Version 9.6

VACUUM (FULL) will re-create the indexes too, and "maintenance_work_mem"
has an impact on index build speed.
But I have no explanation for such a large difference.

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

when is useful min_wal_size?

[Luca Ferrari]

Hi all,
this may sound trivial, but in my opinion min_wal_size is useful only
when the cluster is initialized or the wals are reset.
Am I wrong?

Luca

Re: vacuumdb not letting me connect to db

[Laurenz Albe]

On Thu, 2021-02-04 at 16:56 +0530, Atul Kumar wrote:
> I have 160 GB of RAM, postgres 9.6 is running on the server.
> 
> after upgrade I ran the below command:
> 
> "/usr/edb/as9.6/bin/vacuumdb" -U 'enterprisedb' --verbose --all -j 300
> --analyze-only
> 
> after running that  command I was not able to connect the database
> using psql for few minutes.

That is to be expected.

If you have 300 processes performing I/O and using CPU, your machine
will vertainly be overloaded.

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

vacuumdb not letting me connect to db

[Atul Kumar]

Hi,

I have 160 GB of RAM, postgres 9.6 is running on the server.

after upgrade I ran the below command:

"/usr/edb/as9.6/bin/vacuumdb" -U 'enterprisedb' --verbose --all -j 300
--analyze-only

after running that  command I was not able to connect the database
using psql for few minutes.

After 20-30 minutes i was able to connect to the db and at that time I
checked the pg_stst_activity, the active connections was reduced to
27.

my max_connections is set to 700.

I tried to find out the reason for not being abled to connect the db
(when 300 jobs were running) but still not got the answer. So
suggestions are welcome.

curious vacuum full behavior

[Zwettler Markus (OIZ)]

I have 2 identical systems A + B.
B being a clone of A.


The table pg_catalog.pg_largeobject was identical on both systems: 300GB in 
total size; 100GB bloated.


I did following on A:

?  vacuum full pg_catalog.pg_largeobject;
(using the default maintenance_work_mem of 64MB)
It took around 45 minutes and increased the diskspace by around 125% until the 
vacuum had been finished.


I did following on B:

?  set maintenance_work_mem = '256MB';

?  vacuum full pg_catalog.pg_largeobject;
This took around 5 minutes. I don't know if the diskspace ever increased.


I was really surprised.
Is there any explanation on this behavior?
Is vacuum full heavily using on-disk sort areas if maintenance_work_mem is too 
low?


Postgres Version 9.6


Thanks, Markus

COPY command in ODBC

[brajmohan saxena]

Hi,

How to use COPY command in ODBC for reading STDIN buffer.
It would be great if someone explains with an example of ODBC COPY call
along with STDIN buffer.

Thanks in advance.

Thanks
Braj