Re: Memory settings when running postgres in a docker container

[David Mullineux]

i dont get why you think all memroy will be used.
 When you say
shared_buffers = 16GB
effective_cache_size = 48GB

...then this is using only 16GB for shared buffers.

The effective _cache_size doesn't cause any memory to.be allocated. It's
just a hint to optomizer 

On Wed, 20 Nov 2024, 11:16 Koen De Groote,  wrote:

> Assuming a machine with:
>
> * 16 CPU cores
> * 64GB RAM
>
> Set to 500 max connections
>
> A tool like this: https://pgtune.leopard.in.ua/
>
> Will output recommended settings:
>
> max_connections = 500
> shared_buffers = 16GB
> effective_cache_size = 48GB
> maintenance_work_mem = 2GB
> checkpoint_completion_target = 0.9
> wal_buffers = 16MB
> default_statistics_target = 100
> random_page_cost = 1.1
> effective_io_concurrency = 200
> work_mem = 8388kB
> huge_pages = try
> min_wal_size = 1GB
> max_wal_size = 4GB
> max_worker_processes = 16
> max_parallel_workers_per_gather = 4
> max_parallel_workers = 16
> max_parallel_maintenance_workers = 4
>
> And they basically use up all the memory of the machine.
>
> 16GB shared buffers, 48GB effective cache size, 8MB of work_mem for some
> reason...
>
> This seems rather extreme. I feel there should be free memory for
> emergencies and monitoring solutions.
>
> And then there's the fact that postgres on this machine will be run in a
> docker container. Which, on Linux, receives 64MB of /dev/shm shared memory
> by default, but can be increased.
>
> I feel like I should probably actually lower my upper limit for memory,
> regardless of what the machine actually has, so I can have free memory, and
> also not bring the container process itself into danger.
>
> Is it as straightforward as putting my limit on, say 20GB, and then giving
> more /dev/shm to the container? Or is there more to consider?
>
> Regards,
> Koen De Groote
>
>
>
>
>
>
>

Re: PostgreSQL 15.9 Update: Partitioned tables with foreign key constraints

[Adrian Klaver]

On 11/19/24 08:11, Paul Foerster wrote:

Hi,

the PostgreSQL 15.9 release notes instruct to look out for especially detached 
partitions with foreign key constraints. I'm in the process of updating our 
databases from 15.8 to 15.9 now and found a case where the select statement 
returns a constraint.

The release notes say nothing about when to fix that using the generated add or 
drop statements.

Do I want/need to do that before or after I exchange the software? And if it is 
to be done on a particular of the two releases, why is that?

https://www.postgresql.org/docs/15/release-15-9.html
Section E.1.2, changelog entry 5.

Any insight would be highly appreciated. Thanks in advance.


Read this:

https://www.postgresql.org/about/news/out-of-cycle-release-scheduled-for-november-21-2024-2958/

and hold off awhile.



Cheers
Paul



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

Re: Re : Credcheck extension

[Adrian Klaver]

On 11/19/24 00:40, 張宸瑋 wrote:
Sorry for the inconvenience, but I used make and make install to build 
the credcheck--2.8.0.sql sources zip file. I would like to ask how I can 
update and apply the changes to the system, as I modified the files in 
credcheck/test/expected/06_reuse_interval.out and 
credcheck/test/sql/06_reuse_interval.sql. However, after running make 
and make install again, I don’t see any changes.


Pretty sure you need to do:

make clean

first, then the rest of the install process.

That process is shown here:

https://github.com/hexacluster/credcheck?tab=readme-ov-file#installation





Adrian Klaver >於 2024年11月18日 週一，下午11:15寫道：


On 11/18/24 01:03, 張宸瑋 wrote:
 > Hello!
 >    I would like to inquire about the installation of the credcheck
 > third-party package to support password complexity and expiration
date,
 > etc., when setting up open-source PostgreSQL. I am using the
 > credcheck--2.8.0.sql version from GitHub. After completing the
setup, I
 > encountered the following issue: when an account exceeds the
configured
 > number of incorrect login attempts, it gets locked. The command
SELECT *
 > FROM pg_banned_role; should display the columns roleid,
failure_count,
 > and banned_date, and the view is working properly and shows the
 > information. However, according to the example, the roleid does not
 > correctly display the corresponding oid for the account with failed
 > login attempts. I would like to ask if there is a solution for this
 > issue. Thank you!

Have you looked a?:

https://github.com/HexaCluster/credcheck/issues/39


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



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

Re: A table lock inside a transaction depends on query protocol being used?

[Tom Lane]

Istvan Soos  writes:
> The following minimal reproduction case can be used locally:

> Setup:
> CREATE TABLE a (
>   a_id INTEGER PRIMARY KEY NOT NULL,
>   a_other_id INTEGER NOT NULL
> );
> CREATE TABLE b (other_id INTEGER PRIMARY KEY NOT NULL);

> BEGIN;
> SELECT * FROM a;
> ALTER TABLE a ADD CONSTRAINT fk_other FOREIGN KEY (a_other_id)
> REFERENCES b(other_id);

> At which point we get '55006: cannot ALTER TABLE "a" because it is
> being used by active queries in this session'. It makes sense,
> however, if we change the SELECT to simple query protocol, the error
> is not present and the transaction completes.

Your message trace isn't too clear (it's not apparent where you're
issuing the ALTER TABLE), but I wonder if you could be failing to
close out the SELECT statement before issuing ALTER.  The error
message implies that something is still holding a reference count
on "a"'s relcache entry, and it's hard to see what that could be
except a still-open Portal for the SELECT.

regards, tom lane

Re: A table lock inside a transaction depends on query protocol being used?

[Istvan Soos]

On Wed, Nov 20, 2024 at 7:57 PM Tom Lane  wrote:
> but I wonder if you could be failing to close out the SELECT statement before 
> issuing ALTER.

Thanks! This led me to check some debug details, and in fact we don't
close the portal, only the statement. (Besides a bug in the debug
log...)

Now I know what to fix :).

Thanks,
  Istvan

Re: Fwd: Error when opening pgAdmin

[Adrian Klaver]

Reply to list also

Ccing list

On 11/20/24 9:36 AM, Violetta wrote:

What exactly did you delete, the entire package or just pgAdmin4?

I deleted the entire package.

  How did you delete/uninstall it?

I deleted everything that was in the automatically created folder


I don't use Mac OS, but still I am pretty sure that won't work.

Take a look at:

https://www.enterprisedb.com/docs/supported-open-source/postgresql/uninstalling/#uninstalling-postgresql-on-mac




Are you sure you completely uninstalled the package?

I'm not sure, especially because I got this screen during installation, 
saying that existing PostreSQL installation has been found, but I 
couldn't find the folder, which was mentioned, in the Library.


ср, 20 лист. 2024 р. о 18:05 Adrian Klaver > пише:


On 11/19/24 11:57, Violetta wrote:
 > Hi, I got an error, opening pgAdmin, after installation from EDB
host.
 > Please take a look at it. This is how I got it incrementally:
 > 1. First time I downloaded postgreSQL v17.0 from the site
 >
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

 >
> for Mac OS
 > Screenshot 2024-11-19 at 21.22.40.png
 > 2. I successfully installed the app, used pgAdmin for several
days and
 > then I deleted it.
 > 3. Recently I downloaded the app again (same version from the
screen in
 > step 1).
 > 4. During installation I got a strange warning
 > Screenshot 2024-11-17 at 14.34.16.png
 > I just pressed «OK». The installation was finished.
 >
 > 5. When I tried to open pgAdmin I got an error
 > Screenshot 2024-11-17 at 14.35.56.png
 >
 > Could you help me with it?

What you show is downloading the entire Postgres package which includes
the Postgres server, extensions and tools like pgAdmin4.

What exactly did you delete, the entire package or just pgAdmin4?

How did you delete/uninstall it?

Are you sure you completely uninstalled the package?

Looks to me like there where leftover's from the first
install/uninstall.

 >
 > Thank you and I'm looking forward to hearing from you!

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



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

Re: Running docker in postgres, SHM size of the docker container in postgres 16

[felix . quintgz]

Docker databases do not perform well under heavy load.
Everything can be configured to improve performance but going through the 
Docker disk access layer will significantly decrease performance.

 On Wednesday, November 20, 2024 at 06:06:36 AM GMT-5, Koen De Groote 
 wrote:

 That explains a lot.
I have the default of 2 max_parallel_maintenance_workers set, should I set this 
to 0?
I realize this is of course an improvement, but working with docker containers, 
I'd like to avoid taking /dev/shm away from regular queries.
I assume setting max_parallel_maintenance_workers to 0 is the fix here, is 
there perhaps something else I should know about, if I want to have control 
over this?
Regards,Koen De Groote


On Wed, Nov 20, 2024 at 12:38 AM Thomas Munro  wrote:
On Wed, Nov 20, 2024 at 11:22 AM Koen De Groote  wrote:

> Why would that be? It's the exact same data. The install is about 50GB in 
> size. Is there something wrong with postgres 16, or did some settings 
> significantly change, that I need to know about? I went over all the 
> changelogs, nothing stood out as relevant to this, but that might be a lack 
> of experience on my end.



Parallel vacuum arrived in PostgreSQL 13, and that uses "dynamic

shared memory" to share state between workers, and assuming

dynamic_shared_memory_type=posix, that means shm_open(), which opens

files under /dev/shm on Linux.

Re: Running docker in postgres, SHM size of the docker container in postgres 16

[Koen De Groote]

That explains a lot.

I have the default of 2 max_parallel_maintenance_workers set, should I set
this to 0?

I realize this is of course an improvement, but working with docker
containers, I'd like to avoid taking /dev/shm away from regular queries.

I assume setting max_parallel_maintenance_workers to 0 is the fix here, is
there perhaps something else I should know about, if I want to have control
over this?

Regards,
Koen De Groote



On Wed, Nov 20, 2024 at 12:38 AM Thomas Munro 
wrote:

> On Wed, Nov 20, 2024 at 11:22 AM Koen De Groote  wrote:
> > Why would that be? It's the exact same data. The install is about 50GB
> in size. Is there something wrong with postgres 16, or did some settings
> significantly change, that I need to know about? I went over all the
> changelogs, nothing stood out as relevant to this, but that might be a lack
> of experience on my end.
>
> Parallel vacuum arrived in PostgreSQL 13, and that uses "dynamic
> shared memory" to share state between workers, and assuming
> dynamic_shared_memory_type=posix, that means shm_open(), which opens
> files under /dev/shm on Linux.
>

Clarification on CVE-2024-10979 and PostgreSQL Upgrade Necessity Without PL/Perl Usage

[Subhash Udata]

Dear PostgreSQL Community,

I have a query related to the recent security vulnerability,
*CVE-2024-10979*, concerning the PL/Perl extension.

>From the advisory, it appears the vulnerability impacts systems utilizing
the PL/Perl extension. My question is:

   - If we do not use the PL/Perl extension in our PostgreSQL instance, is
   it still necessary to upgrade to the patched version of PostgreSQL? Or can
   we safely continue using our current version without concern?

We would like to understand whether this vulnerability has any implications
for environments where the PL/Perl extension is not installed or used.

Thank you so much for your guidance on this.

Best regards,

Subhash Udata

Re: Postgres service not starting on windows after install if not installed into standard locations

[Thomas Schweikle]

Am Mo., 18.Nov..2024 um 18:48:56 schrieb Adrian Klaver:
> On 11/18/24 09:22, Thomas Schweikle wrote:
>> Hi!
>>
>> Had installed PostgreSQL 16.5 on Windows 10:
> 
> Using what installer?

postgresql-16.5-1-windows-x64.exe as found on 
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads


>> Install directory: "C:\PRGS\pgSQL\16"
>> DB-Directory:  "C:\DB\pgSQL\16\data"
>>
>> Environment variable "PGDATA" is set to "C:\DB\pgSQL\16\data".
>>
>> Initialized the database. Then started postgres services using:
>>
>> c:\PRGS\pgSQL\16\bin\pg_ctl.exe start
>>
>> Worked nice. Server starts, the serves as expected. But: if I try to
>> start the service by "net start pgSQL" I am reported that "pg_ctl.exe"
>> could not find "postgres.exe".
>>
>> What I've tried:
>> - added environment variable "PGPATH" pointing to "C:\PRGS\pgSQL\16\bin"
>>     -> did not help.
>>
>> - added "C:\PRGS\pgSQL\16\bin" to path. -> did not help either, even
>> after rebooting. I've added it to system path.
>>
>> Are paths somewhere within some configuration file or registry entry?
>> And set to standard install locations?
>>
>> Any idea is welcome to solve this problem!



-- 
Thomas

Re: Suddenly all queries moved to seq scan

[Achilleas Mantzios - cloud]

On 11/20/24 12:50, Sreejith P wrote:


Hi,

We are using PostgresQL 10 in our production database. We have around 
890 req /s request on peak time.


We have 1 primary and 4 slave databases as well in the same postgres 
cluster.


2 days back we applied some patches in the primary server and 
restarted. We didn't do anything on the secondary server.


Next day, After 18 hours all our queries from secondary servers 
started taking too much time.  queries were working in 2 sec started 
taking 80 seconds. Almost all queries behaved the same way.


After half an hour of outage we restarted all db servers and system 
back to normal.


Still we are not able to understand the root case. We couldn't find 
any error log or fatal errors.  During the incident, in  one of the 
read server disks was full. We couldn't see any replication lag or 
query cancellation due to replication.


please help
Still you say seq scan, how do you know that? You run explain (buffers, 
analyze, settings) ? If this is indeed the case, then you need to check 
your indexes. Check when they were last used/scanned and if they are 
usable. Did the disk error had any of your PostgreSQL tablespaces / 
tables/indexes  ?


Regards
Sreejith





*Solutions for Care Anywhere*


*dWise HealthCare IT Solutions Pvt. Ltd.*| www.lifetrenz.com 

*Disclaimer*: The information and attachments contained in this email 
are intended for exclusive use of the addressee(s) and may contain 
confidential or privileged information. If you are not the intended 
recipient, please notify the sender immediately and destroy all copies 
of this message and any attachments. The views expressed in this email 
are, unless otherwise stated, those of the author and not those of 
dWise HealthCare IT Solutions or its management. 

Re: Suddenly all queries moved to seq scan

[Daniel Gustafsson]

> On 20 Nov 2024, at 11:50, Sreejith P  wrote:

> We are using PostgresQL 10 in our production database.  We have around 890 
> req /s request on peak time.

PostgreSQL 10 is well out of support and does not receive bugfixes or security
fixes, you should plan a migration to a supported version sooner rather than
later.

> 2 days back we applied some patches in the primary server and restarted. We 
> didn't do anything on the secondary server.

Patches to the operating system, postgres, another application?

> Next day, After 18 hours all our queries from secondary servers started 
> taking too much time.  queries were working in 2 sec started taking 80 
> seconds. Almost all queries behaved the same way.
> 
> After half an hour of outage we restarted all db servers and system back to 
> normal.
> 
> Still we are not able to understand the root case. We couldn't find any error 
> log or fatal errors.  During the incident, in  one of the read server disks 
> was full. We couldn't see any replication lag or query cancellation due to 
> replication.

You say that all queries started doing sequential scans, is that an assumption
from queries being slow or did you capture plans for the queries which be
compared against "normal" production plans?

--
Daniel Gustafsson

Re: Suddenly all queries moved to seq scan

[Efrain J. Berdecia]

Make sure to run analyze on the entire database, possibly using vacuumdb would 
be faster.
Also, check for invalid indexes.
Efrain J. Berdecia 

On Wednesday, November 20, 2024 at 08:02:36 AM EST, Daniel Gustafsson 
 wrote:  
 
 > On 20 Nov 2024, at 11:50, Sreejith P  wrote:

> We are using PostgresQL 10 in our production database.  We have around 890 
> req /s request on peak time.

PostgreSQL 10 is well out of support and does not receive bugfixes or security
fixes, you should plan a migration to a supported version sooner rather than
later.

> 2 days back we applied some patches in the primary server and restarted. We 
> didn't do anything on the secondary server.

Patches to the operating system, postgres, another application?

> Next day, After 18 hours all our queries from secondary servers started 
> taking too much time.  queries were working in 2 sec started taking 80 
> seconds. Almost all queries behaved the same way.
> 
> After half an hour of outage we restarted all db servers and system back to 
> normal.
> 
> Still we are not able to understand the root case. We couldn't find any error 
> log or fatal errors.  During the incident, in  one of the read server disks 
> was full. We couldn't see any replication lag or query cancellation due to 
> replication.

You say that all queries started doing sequential scans, is that an assumption
from queries being slow or did you capture plans for the queries which be
compared against "normal" production plans?

--
Daniel Gustafsson



  

Re: Validating check constraints without a table scan?

[Philip Couling]

Thanks for the suggestion of directly updating the system catalog, that may
be the “best” option.

“*Most likely your query is not exactly the same as the check constraint.
Think about NULL and similar.*”

No that’s not sufficient to explain it. The SELECT is obviously the
negation of the check constraint, so there is a difference there. But
besides that, it’s very clear that Postgres does not make use of indexes
while validating constraints. I suspect it doesn’t involve the query
planner at all.

On Fri, 15 Nov 2024 at 15:28, Torsten Förtsch 
wrote:

> On Fri, Nov 15, 2024 at 9:38 AM Philip Couling  wrote:
>
>> Is there a solid reason why adding a check constraint does not use
>> existing indexes for validation.
>>
>> We are currently looking at partitioning a multi TB table leaving all
>> existing data in place and simply attaching it as a partition to a new
>> table. To prevent locking, we are trying to add an INVALID check constraint
>> first and then validate it.
>>
>> I can trivially prove the invalid constraint is valid with a simple
>> SELECT which will use an existing index and return instantaneously. But
>> AFAIK Theres no way to mark a constraint as valid without scanning all the
>> rows.
>>
>
> Most likely your query is not exactly the same as the check constraint.
> Think about NULL and similar.
>
>
>> This operation is really problematic on a production database with heavy
>> IO load.
>>
>> Is there a solid ready why validating check constraints cannot use
>> existing indexes? If I can prove the constraint is valid so trivially with
>> a SELECT, then why can Postgres not do the same (or similar)?
>>
>
> Here is what has worked for me many times:
>
> 1. create the check constraint as NOT VALID. From now on no new or updated
> row is allowed to violate it.
> 2. check if the constraint holds with a query on a binary replica. Make
> sure the query starts only when the constraint is visible on the replica.
> 3. on the master: UPDATE pg_constraint SET conisvalidated=true WHERE
> conname='your_constraint_name' AND conrelid='schema.table'::regclass
>
> How you perform step 2 is irrelevant. Checking it on a replica would
> simply avoid the load on the master. You just need to make sure there is no
> conflicting data in the table.
>
> WARNING, you need to be damn sure of your data if you do that. But if you
> are, it works.
>
> Here is the procedure how I solved the same problem for some of our
> multi-TB tables (PG14):
>
> The table has a column called transaction_time. We wanted to partition by
> that column. For some historical reason the column did not have a NOT NULL
> constraint. However, there was no way our processes could insert NULL in
> that column and there was no row with NULL in that field. So, first was to
> add the NOT NULL constraint:
>
> BEGIN;
>
> ALTER TABLE my.table
> ADD CONSTRAINT transaction_time_not_null CHECK(transaction_time IS NOT
> NULL) NOT VALID;
>
> UPDATE pg_constraint
>SET convalidated=true
>  WHERE conname = 'transaction_time_not_null'
>AND conrelid = 'my.table'::REGCLASS
> RETURNING conname, conrelid::REGCLASS, convalidated;
>
> COMMIT;
>
> Now for cosmetic purposes we first turn the check constraint above into a
> normal NOT NULL constraint:
>
> BEGIN;
>
> SET LOCAL client_min_messages = 'debug4';
> -- expecting this message
> -- DEBUG:  existing constraints on column "table.transaction_time" are
> sufficient to prove that it does not contain nulls
> ALTER TABLE my.table
>   ALTER COLUMN transaction_time SET NOT NULL;
> RESET client_min_messages;
>
> ALTER TABLE my.table
>   DROP CONSTRAINT transaction_time_not_null;
>
> COMMIT;
>
> If you set client_min_messages to something like debug4, then the database
> tells you if it wants to scan the table or if existing constraints are
> sufficient to prove the condition.
>
> transaction_time in our case is never in the future. Also database
> transactions are short-lived. Nothing exceeds 30 seconds. So, I can trust
> the effect of any action performed more than 30 seconds ago in the database
> is visible.
>
> So, I set the time after which new rows go to the new partition at least
> 10 minutes from now at the next hour boundary. 30 seconds would be good
> enough. I chose 10 minutes just for extra safety.
>
> SELECT date_trunc('hour', now()+'1h 10min'::interval)::timestamp AS
> switch_time\gset
>
> Next comes the actual change:
>
> BEGIN;
>
> -- rename the existing table
> ALTER TABLE my.table RENAME TO table_old;
>
> -- drop triggers. We will recreate them later.
> DROP TRIGGER ... ON my.table_old;
> DROP TRIGGER ...;
>
> -- create partitioned table
> CREATE TABLE my.table (
> LIKE my.table_old
> INCLUDING DEFAULTS
> INCLUDING CONSTRAINTS
> )
> PARTITION BY RANGE (transaction_time);
>
> -- recreate triggers
> CREATE TRIGGER ...
> BEFORE DELETE ON my.table
>FOR EACH ROW EXECUTE FUNCTION my,trigger_fun();
> CREATE TRIGGER ...;
>
> -- create the partition for future rows
> CREATE TABLE my.table

Suddenly all queries moved to seq scan

[Sreejith P]

Hi,

We are using PostgresQL 10 in our production database.  We have around 890
req /s request on peak time.

We have 1 primary and 4 slave databases as well in the same postgres
cluster.

2 days back we applied some patches in the primary server and restarted. We
didn't do anything on the secondary server.

Next day, After 18 hours all our queries from secondary servers started
taking too much time.  queries were working in 2 sec started taking 80
seconds. Almost all queries behaved the same way.

After half an hour of outage we restarted all db servers and system back to
normal.

Still we are not able to understand the root case. We couldn't find any
error log or fatal errors.  During the incident, in  one of the read server
disks was full. We couldn't see any replication lag or query
cancellation due to replication.

please help

Regards
Sreejith

-- 




 

*Solutions for Care Anywhere*
*dWise HealthCare IT Solutions Pvt. 
Ltd.* | www.lifetrenz.com 
*Disclaimer*:
 The 
information and attachments contained in this email are intended 
for 
exclusive use of the addressee(s) and may contain confidential or 
privileged information. If you are not the intended recipient, please 
notify the sender immediately and destroy all copies of this message and
 
any attachments. The views expressed in this email are, unless 
otherwise 
stated, those of the author and not those of dWise HealthCare IT Solutions 
or its management.

Re: Fwd: Error when opening pgAdmin

[Lucía Rotger]

PLEASE DEAR FRIEND SEND BIGGER MESSAGES! WE DON'T MIND!

On 19/11/2024 20:57, Violetta wrote:

Hi, I got an error, opening pgAdmin, after installation from EDB host.
Please take a look at it. This is how I got it incrementally:
1. First time I downloaded postgreSQL v17.0 from the site
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads for
Mac OS
Screenshot 2024-11-19 at 21.22.40.png
2. I successfully installed the app, used pgAdmin for several days and
then I deleted it.
3. Recently I downloaded the app again (same version from the screen in
step 1).
4. During installation I got a strange warning
Screenshot 2024-11-17 at 14.34.16.png
I just pressed «OK». The installation was finished.

5. When I tried to open pgAdmin I got an error
Screenshot 2024-11-17 at 14.35.56.png

Could you help me with it?

Thank you and I'm looking forward to hearing from you!

Re: Validating check constraints without a table scan?

[jian he]

On Fri, Nov 15, 2024 at 4:38 PM Philip Couling  wrote:
>
> Is there a solid reason why adding a check constraint does not use existing 
> indexes for validation.
>

can you give an sql example (except not-null)
where indexes can be used for check constraint validation?
i am not sure I understand it correctly.

Re: Postgres service not starting on windows after install if not installed into standard locations

[Thomas Schweikle]

Am Mo., 18.Nov..2024 um 19:43:36 schrieb Laurenz Albe:
> On Mon, 2024-11-18 at 17:22 +, Thomas Schweikle wrote:
>> Had installed PostgreSQL 16.5 on Windows 10:
>>
>> Install directory: "C:\PRGS\pgSQL\16"
>> DB-Directory:  "C:\DB\pgSQL\16\data"
>>
>> Environment variable "PGDATA" is set to "C:\DB\pgSQL\16\data".
>>
>> Initialized the database. Then started postgres services using:
>>
>> c:\PRGS\pgSQL\16\bin\pg_ctl.exe start
>>
>> Worked nice. Server starts, the serves as expected. But: if I try to
>> start the service by "net start pgSQL" I am reported that "pg_ctl.exe"
>> could not find "postgres.exe".
>>
>> What I've tried:
>> - added environment variable "PGPATH" pointing to "C:\PRGS\pgSQL\16\bin"
>> -> did not help.
>>
>> - added "C:\PRGS\pgSQL\16\bin" to path. -> did not help either, even
>> after rebooting. I've added it to system path.
>>
>> Are paths somewhere within some configuration file or registry entry?
>> And set to standard install locations?
> 
> How exactly is the Windows service defined?
> Perhaps you need to use an absolute path for the executable.

Servicename: postgresql-x64-16
Name:postgresql-x64-16-PostgreSQL Server 16
Comment: Provides relational database storage
Path:"C:\PRGS\pgSQL\16\bin\pg_ctl.exe"
  runservice -N "postgresql-x64-16"
  -D "C:\DB\pgSQL\16\data" -w
Type:Automatic
Account: Networkservice
Password:set
Dependencies:RPC, DCOM-Service, RPC-Endpoint

Errors found within system logs:
pg_ctl: could not find program 'postgres.exe'

Seems, since "C:\PRGS\pgSQL\16\bin" is in system path, it does not 
search there at all:

# dir "C:\PRGS\pgSQL\16\bin"
[...]
Mi, 13.Nov.2024  13:24   123.392 pg_ctl.exe
[...]
Mi, 13.Nov.2024  13:24 8.659.968 postgres.exe

Any way to tell a service in Windows to use a path?
Any registry key to assign a path to?

# sc qc "postgresql-x64-16"
[SC] QueryServiceConfig ERFOLG

SERVICE_NAME: postgresql-x64-16
 TYPE   : 10  WIN32_OWN_PROCESS
 START_TYPE : 2   AUTO_START
 ERROR_CONTROL  : 1   NORMAL
 BINARY_PATH_NAME   : "C:\PRGS\pgSQL\16\bin\pg_ctl.exe" 
runservice -N "postgresql-x64-16" -D "C:\DB\pgSQL\16\data" -w
 LOAD_ORDER_GROUP   :
 TAG: 0
 DISPLAY_NAME   : postgresql-x64-16 - PostgreSQL Server 16
 DEPENDENCIES   : RPCSS
 SERVICE_START_NAME : NT AUTHORITY\NetworkService


-- 
Thomas

Re: Suddenly all queries moved to seq scan

[Sreejith P]

> On 20 Nov 2024, at 6:32 PM, Daniel Gustafsson  wrote:
> 
>> On 20 Nov 2024, at 11:50, Sreejith P  wrote:
> 
>> We are using PostgresQL 10 in our production database.  We have around 890 
>> req /s request on peak time.
> 
> PostgreSQL 10 is well out of support and does not receive bugfixes or security
> fixes, you should plan a migration to a supported version sooner rather than
> later.
> 
>> 2 days back we applied some patches in the primary server and restarted. We 
>> didn't do anything on the secondary server.
> 
> Patches to the operating system, postgres, another application? 
PostgreSQL Common 10.23-6 
> 
>> Next day, After 18 hours all our queries from secondary servers started 
>> taking too much time.  queries were working in 2 sec started taking 80 
>> seconds. Almost all queries behaved the same way.
>> 
>> After half an hour of outage we restarted all db servers and system back to 
>> normal.
>> 
>> Still we are not able to understand the root case. We couldn't find any 
>> error log or fatal errors.  During the incident, in  one of the read server 
>> disks was full. We couldn't see any replication lag or query cancellation 
>> due to replication.
> 
> You say that all queries started doing sequential scans, is that an assumption
> from queries being slow or did you capture plans for the queries which be
> compared against "normal" production plans?.

Queries were taking 20 ms started taking 60 seconds. So have done SQL analyse 
to understand about query plan. There we found that query planner taking seq 
scan instead in index scan.

I would like to add one ore point.  A delete query were running in DB from 2 
days for deleting around 80 million records. 
> 
> --
> Daniel Gustafsson
> 


-- 




 

*Solutions for Care Anywhere*
*dWise HealthCare IT Solutions Pvt. 
Ltd.* | www.lifetrenz.com 
*Disclaimer*:
 The 
information and attachments contained in this email are intended 
for 
exclusive use of the addressee(s) and may contain confidential or 
privileged information. If you are not the intended recipient, please 
notify the sender immediately and destroy all copies of this message and
 
any attachments. The views expressed in this email are, unless 
otherwise 
stated, those of the author and not those of dWise HealthCare IT Solutions 
or its management.

Re: Update to postgresql 14.14

[Adrian Klaver]

On 11/20/24 06:26, Levente Birta wrote:

Hi

I'm trying to update postgresql 14.13->14.14, but there is a libllvm 
18.1 dependency.
I have enabled the pgdg-centos8-sysupdates repo, but as I see there is 
no llvm 18, only 16/17.

Is something that I miss?


See:

https://yum.postgresql.org/news/rocky-9-8-llvm-repo-is-available/



All this on rocky linux 8, up to date.

Thanks
Levi







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

Re: Help with restoring database from old version of PostgreSQL

[Catherine Frock]

Adrian: Yes, db_2017 is the name I originally gave the database in the
PostgreSQL 9.6 server. I can't remember if I used pg_dump or pg_dumpall.

Ron: Okay, I think it finally worked!

Thank you both for your help.

--
Arden


On Tue, Nov 19, 2024 at 6:00 PM Adrian Klaver 
wrote:

> On 11/19/24 14:13, Catherine Frock wrote:
> > Do you know what versions of PG and pgAdmin4 I need? Also, I don't know
> > where to get a different version of PG.
> >
>
> You need to take a step back and take stock of where you started and
> where you want to be.
>
> To that end answer the following:
>
> 1) What are the Postgres and PostGIS versions you started with?
>
> 2) What database or databases do you want to move forward?
>
> 3) Is there a hard requirement for what currently supported version of
> Postgres you want to use?
>
> 4) What do you want to do with the new install?
>
> 5) What versions of Postgres, pgAdmin4 and PostGIS do you currently have
> installed?
>
> 6) You are looking at jumping a minimum of 4 major Postgres versions up
> to a maximum of 8 versions. You are also jumping some number of PostGIS
> versions as well. In either case you should at some point go over the
> release notes for each major version jump to see if anything is going to
> cause issues.
>
> > --
> > Arden
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Fwd: Error when opening pgAdmin

[Adrian Klaver]

On 11/19/24 11:57, Violetta wrote:
Hi, I got an error, opening pgAdmin, after installation from EDB host. 
Please take a look at it. This is how I got it incrementally:
1. First time I downloaded postgreSQL v17.0 from the site 
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads 
 for Mac OS

Screenshot 2024-11-19 at 21.22.40.png
2. I successfully installed the app, used pgAdmin for several days and 
then I deleted it.
3. Recently I downloaded the app again (same version from the screen in 
step 1).

4. During installation I got a strange warning
Screenshot 2024-11-17 at 14.34.16.png
I just pressed «OK». The installation was finished.

5. When I tried to open pgAdmin I got an error
Screenshot 2024-11-17 at 14.35.56.png

Could you help me with it?


What you show is downloading the entire Postgres package which includes 
the Postgres server, extensions and tools like pgAdmin4.


What exactly did you delete, the entire package or just pgAdmin4?

How did you delete/uninstall it?

Are you sure you completely uninstalled the package?

Looks to me like there where leftover's from the first install/uninstall.



Thank you and I'm looking forward to hearing from you!


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

Re: Clarification on CVE-2024-10979 and PostgreSQL Upgrade Necessity Without PL/Perl Usage

[Adrian Klaver]

On 11/20/24 00:54, Subhash Udata wrote:

Dear PostgreSQL Community,

I have a query related to the recent security vulnerability, 
*CVE-2024-10979*, concerning the PL/Perl extension.


 From the advisory, it appears the vulnerability impacts systems 
utilizing the PL/Perl extension. My question is:


  * If we do not use the PL/Perl extension in our PostgreSQL instance,
is it still necessary to upgrade to the patched version of
PostgreSQL? Or can we safely continue using our current version
without concern?


Yes you should upgrade.

See the rest of the issues fixed:

https://www.postgresql.org/about/news/postgresql-171-165-159-1414-1317-and-1221-released-2955/

It has further CVE's.

Though I would wait until the out-of cycle release that lands 
tomorrow(2024-11-21) is out, see:


https://www.postgresql.org/about/news/out-of-cycle-release-scheduled-for-november-21-2024-2958/

As it fixes some regressions in the previous release.




We would like to understand whether this vulnerability has any 
implications for environments where the PL/Perl extension is not 
installed or used.


Thank you so much for your guidance on this.

Best regards,

Subhash Udata



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

Re: Postgres service not starting on windows after install if not installed into standard locations

[Adrian Klaver]

On 11/20/24 05:26, Thomas Schweikle wrote:

Am Mo., 18.Nov..2024 um 19:43:36 schrieb Laurenz Albe:

On Mon, 2024-11-18 at 17:22 +, Thomas Schweikle wrote:

Had installed PostgreSQL 16.5 on Windows 10:



How exactly is the Windows service defined?
Perhaps you need to use an absolute path for the executable.


Servicename: postgresql-x64-16
Name:postgresql-x64-16-PostgreSQL Server 16
Comment: Provides relational database storage
Path:"C:\PRGS\pgSQL\16\bin\pg_ctl.exe"
   runservice -N "postgresql-x64-16"
   -D "C:\DB\pgSQL\16\data" -w
Type:Automatic
Account: Networkservice
Password:set
Dependencies:RPC, DCOM-Service, RPC-Endpoint



Did you change settings during the install?

Here on my Windows machine I get:

C:\Program Files\PostgreSQL\bin



Errors found within system logs:
pg_ctl: could not find program 'postgres.exe'

Seems, since "C:\PRGS\pgSQL\16\bin" is in system path, it does not
search there at all:

# dir "C:\PRGS\pgSQL\16\bin"
[...]
Mi, 13.Nov.2024  13:24   123.392 pg_ctl.exe
[...]
Mi, 13.Nov.2024  13:24 8.659.968 postgres.exe

Any way to tell a service in Windows to use a path?
Any registry key to assign a path to?

# sc qc "postgresql-x64-16"
[SC] QueryServiceConfig ERFOLG

SERVICE_NAME: postgresql-x64-16
  TYPE   : 10  WIN32_OWN_PROCESS
  START_TYPE : 2   AUTO_START
  ERROR_CONTROL  : 1   NORMAL
  BINARY_PATH_NAME   : "C:\PRGS\pgSQL\16\bin\pg_ctl.exe"
runservice -N "postgresql-x64-16" -D "C:\DB\pgSQL\16\data" -w
  LOAD_ORDER_GROUP   :
  TAG: 0
  DISPLAY_NAME   : postgresql-x64-16 - PostgreSQL Server 16
  DEPENDENCIES   : RPCSS
  SERVICE_START_NAME : NT AUTHORITY\NetworkService




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

Memory settings when running postgres in a docker container

[Koen De Groote]

Assuming a machine with:

* 16 CPU cores
* 64GB RAM

Set to 500 max connections

A tool like this: https://pgtune.leopard.in.ua/

Will output recommended settings:

max_connections = 500
shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 8388kB
huge_pages = try
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4

And they basically use up all the memory of the machine.

16GB shared buffers, 48GB effective cache size, 8MB of work_mem for some
reason...

This seems rather extreme. I feel there should be free memory for
emergencies and monitoring solutions.

And then there's the fact that postgres on this machine will be run in a
docker container. Which, on Linux, receives 64MB of /dev/shm shared memory
by default, but can be increased.

I feel like I should probably actually lower my upper limit for memory,
regardless of what the machine actually has, so I can have free memory, and
also not bring the container process itself into danger.

Is it as straightforward as putting my limit on, say 20GB, and then giving
more /dev/shm to the container? Or is there more to consider?

Regards,
Koen De Groote

Update to postgresql 14.14

[Levente Birta]

Hi

I'm trying to update postgresql 14.13->14.14, but there is a libllvm 
18.1 dependency.
I have enabled the pgdg-centos8-sysupdates repo, but as I see there is 
no llvm 18, only 16/17.

Is something that I miss?

All this on rocky linux 8, up to date.

Thanks
Levi

Re: Specifying columns returned by a function, when the function is in a SELECT column list?

[David G. Johnston]

On Tue, Nov 19, 2024 at 8:48 AM Ron Johnson  wrote:

> It's trivial to specify columns when a table-returning function is the
> FROM clause, but how does one specify columns when the table-returning
> function is a column in a SELECT clause?
>

You don't/cannot.  And with lateral you shouldn't find the need to hack
around it either.  Non-scalar function calls in the select clause are now
obsolete.

In the select clause the function call returns a single-column of composite
type with the names of the fields in the composite already known.  As the
query scope where you invoke the function you should only use the
composite.  If you need to dive into its fields you'd need a subquery -
ideally one that doesn't risk being optimized away due to a
multiple-evaluation hazard.

David J.

Re: Suddenly all queries moved to seq scan

[Justin]

On Wed, Nov 20, 2024 at 9:09 AM Sreejith P  wrote:

>
>
>
> Queries were taking 20 ms started taking 60 seconds. So have done SQL
> analyse to understand about query plan. There we found that query planner
> taking seq scan instead in index scan.
>
> I would like to add one ore point.  A delete query were running in DB from
> 2 days for deleting around 80 million records.


This can cause this specific problem where the number of dead tuples and
lack of autovacuum running can cause the statistics to favor a sequential
scan over an index scan.  Taking into account the length of time the delete
took it would hold a number of datapages and tuples in a lock state, which
can lead to blocking queries and prevent autovacuum/analyze.

It is best to do bulk deletes in batches and have a rest period between
batches to allow autovacuum and analyze to keep up..  Doing  deletes in
batches reduces the number of resources being consumed.

A table lock inside a transaction depends on query protocol being used?

[Istvan Soos]

Hi,

I'm developing the Dart client for Postgresql, which is using the
extended query protocol by default. I have received a report which
shows a difference in locking behavior inside a transaction, depending
which protocol we are using.

My main question: is this something that is implemented in a wrong way
in the Dart client? Or is it something that is intrinsic to Postgresql
server? How should the user who found this go ahead?

The following minimal reproduction case can be used locally:

Setup:
CREATE TABLE a (
  a_id INTEGER PRIMARY KEY NOT NULL,
  a_other_id INTEGER NOT NULL
);
CREATE TABLE b (other_id INTEGER PRIMARY KEY NOT NULL);

BEGIN;
SELECT * FROM a;
ALTER TABLE a ADD CONSTRAINT fk_other FOREIGN KEY (a_other_id)
REFERENCES b(other_id);

At which point we get '55006: cannot ALTER TABLE "a" because it is
being used by active queries in this session'. It makes sense,
however, if we change the SELECT to simple query protocol, the error
is not present and the transaction completes.

Internal inside the Dart client, this is the rough message flow debug
for the extended protocol:

[292da4d4][out] Aggregated [Parse SELECT * FROM a;, Instance of 'SyncMessage']
[292da4d4][in] Parse Complete Message
[292da4d4][in] ReadyForQueryMessage(state = T)
[292da4d4][out] Aggregated [Instance of 'BindMessage', Instance of
'DescribeMessage', Instance of 'ExecuteMessage', Instance of
'SyncMessage']
[292da4d4][in] Bind Complete Message
[292da4d4][in] Instance of 'RowDescriptionMessage'
[292da4d4][in] CommandCompleteMessage(0 affected rows)
[292da4d4][in] ReadyForQueryMessage(state = T)
[292da4d4][out] Aggregated [Instance of 'CloseMessage', Instance of
'SyncMessage']
[out] Aggregated [Instance of 'CloseMessage', Instance of 'SyncMessage']
[292da4d4][in] Bind Complete Message
[292da4d4][in] ReadyForQueryMessage(state = T)

And for the simple protocol:

[3f02e699][out] Query: SELECT * FROM a;
[3f02e699][in] Instance of 'RowDescriptionMessage'
[3f02e699][in] CommandCompleteMessage(0 affected rows)
[3f02e699][in] ReadyForQueryMessage(state = T)

Thank you,
  Istvan

Re: CVE-2024-10979 Vulnerability Impact on PostgreSQL 11.10

[Adrian Klaver]

On 11/20/24 22:44, 김주연 wrote:
Hello, I am currently using PostgreSQL 11.10 and would like to know if 
the CVE-2024-10979 vulnerability affects this version.


Postgres 11 is past EOL, see:

https://www.postgresql.org/support/versioning/


If it does impact my version, I would like to know which version I 
should upgrade to.


Any version from 13+.

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

Re: Help with restoring database from old version of PostgreSQL

[Peter J. Holzer]

On 2024-11-19 16:47:40 -0500, Catherine Frock wrote:
> I am trying to restore a previously backed up database .sql file that was
> created in PostgreSQL 9.6 using pgAdmin4 (I'm not sure which version I was
> using).
> 
> In response to Ron: Do I need to use PG16 instead of pgAdmin4?

As Ron already pointed out, these are different things. PostgreSQL is a
Database. PgAdmin ist a Client for that database. You can't use one
instead of the other. You can only use them together (but of course you
could use a different client instead of PgAdmin4).

> I set the PostgreSQL Binary Path to: C:\Program
> Files\PostgreSQL\17\bin. I have tried to restore my database using
> this in the command prompt: "psql.exe db_2024_9 <
> C:\postgis\db_2017-08-16.sql postgres" using an empty database in both
> the PostgreSQL 9.6 and 17 servers, and this is the result:
> 
> Password for user postgres:
> SET
[...]
> ALTER TABLE
> ERROR:  role "basic_user" does not exist
[...]
> ALTER DEFAULT PRIVILEGES
> ERROR:  role "basic_user" does not exist

That looks fine except that the user "basic_user" doesn't exist. You
should create that before restoring the backup. Or could fix the errors
after the fact but for that you need to understand what went wrong.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: CVE-2024-10979 Vulnerability Impact on PostgreSQL 11.10

[김주연]

Thank you for your response.

2024년 11월 21일 (목) 오후 3:54, Adrian Klaver 님이 작성:

> On 11/20/24 22:44, 김주연 wrote:
> > Hello, I am currently using PostgreSQL 11.10 and would like to know if
> > the CVE-2024-10979 vulnerability affects this version.
>
> Postgres 11 is past EOL, see:
>
> https://www.postgresql.org/support/versioning/
>
>
> > If it does impact my version, I would like to know which version I
> > should upgrade to.
>
> Any version from 13+.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

CVE-2024-10979 Vulnerability Impact on PostgreSQL 11.10

[김주연]

Hello, I am currently using PostgreSQL 11.10 and would like to know if the
CVE-2024-10979 vulnerability affects this version.
If it does impact my version, I would like to know which version I should
upgrade to.