Reindex after upgrade from PostgreSQL 12.10 to PostgreSQL 15.3

[Hellen Jiang]

Hi,
I am going to upgrade our production database( postgresql 12.10) to postgresql 
15.3.
After upgrade to 15.3,  Do you have to do re-index?
If I have to do re-index,  can I only do re-index on some partitioned tables?
We have a lot of big tables which are partitioned by date, for example, table1 
has partitioned table by month table1_2023_01, table1_2023_02 , table1_2023_03 
and so on.
Will that be good if I only run reindex on some partitioned 
table(table1_2023_06 and table1_2023_07), for example, REINDEX (VERBOSE) TABLE 
table1_2023_06; REINDEX (VERBOSE) TABLE table1_2023_07;
After reindex part of tables, I will run ANALYZE with vaccumdb
vacuumdb --analyze-only --jobs=8 -d 

Will the performance be as good as before I upgrade?


Thanks,

Compile static postgres.lib for postgres 15.3 on Windows

[Priess Stefan]

Hi all,

up to version 9.6.22 there was a win32.mak file in the src/interfaces/libpw 
folder which could be used to compile a static postgres.lib using nmake. Since 
Postgres 10 this file has been removed.

Is it still possilbe to build a static postgres.lib for windows ? And if so, 
how do I do it.

Thanks!

Stefan

nexus / chili

Stefan Priess
Softwareentwicklung

Tel: +49 6221 18079-10
E-Mail: stefan.pri...@nexus-chili.com


NEXUS / CHILI GmbH, Friedrich-Ebert-Str. 2, 69221 Dossenheim/Heidelberg
www.nexus-chili.com

Eingetragene Gesellschaft beim Registergericht Mannheim, HRB 337363
Geschäftsführer: Dr. Uwe Engelmann, Dr. Heiko Münch

Understanding pg_stat_io.evictions

[Daniel Westermann (DWE)]

Hi,

I am trying to understand the evictions statistic in pg_stat_io. I know what 
evictions are, so this is not the question.
Given this:

postgres=# select version();
   version  
  
--
 PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 
12.2.0, 64-bit
(1 row)

postgres=# select pg_stat_reset_shared('io');
 pg_stat_reset_shared 
--
 
(1 row)

postgres=# create table t ( a int, b text, c text, d text );
CREATE TABLE

postgres=# insert into t select i,  md5(i::text), md5(i::text), md5(i::text) 
from generate_series(1,100) i;
INSERT 0 100

postgres=# select backend_type,evictions,context 
 from pg_stat_io 
where backend_type = 'client backend'
  and object ='relation';
  backend_type  | evictions |  context  
+---+---
 client backend | 0 | bulkread
 client backend | 0 | bulkwrite
 client backend |   207 | normal
 client backend | 0 | vacuum

Shouldn't these evictions show up under context blkwrite? The description in 
docs is:

"Number of times a block has been written out from a shared or local buffer in 
order to make it available for another use.

In context normal, this counts the number of times a block was evicted from a 
buffer and replaced with another block. In contexts bulkwrite, bulkread, and 
vacuum, this counts the number of times a block was evicted from shared buffers 
in order to add the shared buffer to a separate, size-limited ring buffer for 
use in a bulk I/O operation."

As far as I understand this, a ring buffer is used in this case. Do I miss 
something?

Many thanks in advance
Daniel

Re: Reindex after upgrade from PostgreSQL 12.10 to PostgreSQL 15.3

[Laurenz Albe]

On Thu, 2023-07-27 at 20:02 +, Hellen Jiang wrote:
> I am going to upgrade our production database( postgresql 12.10) to 
> postgresql 15.3.
> After upgrade to 15.3,  Do you have to do re-index?

That is not necessary after a PostgreSQL upgrade.

(It may be necessary if you upgrade the operating system.)

> Will the performance be as good as before I upgrade?

It should be at least as good, but sometimes it isn't.
That's why you test (and tune if necessary).

Yours,
Laurenz Albe

Re: How to improve the performance of my SQL query？

[gzh]

Thank you very much for taking the time to reply to my question. 




> The problem is none of the explains you sent match with the description 
> above. The last one when you forced the optimizer to go with index scan (SET 
> enable_seqscan TO off;) the chosen index seems to be one composed by ms_cd 
> and cd_ate (following your standard of 2 characters column name). There may 
> have a couple of explanations to this:

> - One is that the index may not be exactly the same as described above;

The primary key of the tbl_sha table consists of several fields, and ms_cd is 
just one of them. I just explained the definitions of the fields used in the 
query SQL.




> - Another one is the order in the index. Because you have a composed index 
> the order of the columns in the index matters, and it seems the order is 
> (ms_cd, et_cd, etrys). I wonder if you could recreate this index with the 
> following order: (ms_cd, etrys, et_cd) and run the same query;

The index of TBL_SHA table is defined as follows.




CREATE INDEX index_search_01 ON mdb.TBL_SHA USING btree (MS_CD, ETRYS)

CREATE INDEX index_search_02 ON mdb.TBL_SHA USING btree (ET_CD, ETRYS)

CREATE INDEX index_search_03 ON mdb.TBL_SHA USING btree (MS_CD, ET_DAY, BK_CD, 
FR_CD, RM_CD)




> You can also try to trick the optimizer, for example, what is the result (and 
> explain) of the below query?

> 

> WITH tbi (ry_cd) AS (

> SELECT tbl_inf.ry_cd

> FROM tbl_inf tbi

> WHERE tbi.ms_cd = 'MLD009'

> AND tbl_inf.ry_cd = '0001'

> ) SELECT COUNT(et_cd) FROM tbl_sha tbs

> JOIN tbi ON tbi.ry_cd = tbs .etrys

> WHERE tbs .ms_cd = 'MLD009';

The SQL execution encountered an error, so I made some modifications.

Please refer to the execution plan.

 WHERE tbi.ms_cd = 'MLD009'

→

 WHERE tbl_inf.ms_cd = 'MLD009'




QUERY PLAN

Limit  (cost=2668811.76..2668811.77 rows=1 width=8) (actual 
time=133555.074..133557.729 rows=1 loops=1)

  ->  Aggregate  (cost=2668811.76..2668811.77 rows=1 width=8) (actual 
time=133555.072..133557.726 rows=1 loops=1)

->  Nested Loop  (cost=1000.29..2664512.83 rows=1719572 width=9) 
(actual time=29657.638..133341.053 rows=2113500 loops=1)

  ->  Index Only Scan using tbl_inf_pkc on tbl_inf  
(cost=0.29..8.31 rows=1 width=9) (actual time=1.316..1.321 rows=1 loops=1)

Index Cond: ((ms_cd = 'MLD009'::bpchar) AND (ry_cd = 
'0001'::bpchar))

Heap Fetches: 1

  ->  Gather  (cost=1000.00..2647308.80 rows=1719572 width=18) 
(actual time=29656.318..132969.910 rows=2113500 loops=1)

Workers Planned: 2

Workers Launched: 2

->  Parallel Seq Scan on tbl_sha tbs  
(cost=0.00..2474351.60 rows=716488 width=18) (actual time=29654.184..132876.292 
rows=704500 loops=3)

  Filter: ((ms_cd = 'MLD009'::bpchar) AND (etrys = 
'0001'::bpchar))

  Rows Removed by Filter: 14678996

Planning Time: 0.164 ms

Execution Time: 133557.767 ms




> Well, adding more resources tends to improve performance, but it's usually 
> not linear and the improvement may not be as large as you want for the extra 
> price you are paying. I would first try to understand the performance problem 
> because using the "add more resources" approach may just delay the problem 
> and it tends to get worse with time as the dataset increases.

I strongly agree with your viewpoint, but I currently don't have a solution in 
mind for the problem.
















At 2023-07-28 04:38:39, "Charly"  wrote:

Hi "gzh",


Based on the info you provided I'm assuming you are trying to use the TBL_SHA 
primary key to do an index-only scan as in you mentioned above you have:
> TBL_SHA
> ms_cd character(6) NOT NULL   -- PRIMARY KEY
> et_cd character(8)
> etrys character(8)

Assuming a composed index here by the 3 columns.


> TBL_INF
> ms_cd character(6) NOT NULL   -- PRIMARY KEY
> ry_cd character(8) NOT NULL   -- PRIMARY KEY
Here it's more clear that there is a composed index based on those 2 columns.



The problem is none of the explains you sent match with the description above. 
The last one when you forced the optimizer to go with index scan (SET 
enable_seqscan TO off;) the chosen index seems to be one composed by ms_cd and 
cd_ate (following your standard of 2 characters column name). There may have a 
couple of explanations to this:
 - One is that the index may not be exactly the same as described above;
 - Another one is the order in the index. Because you have a composed index the 
order of the columns in the index matters, and it seems the order is (ms_cd, 
et_cd, etrys). I wonder if you could recreate this index with the following 
order: (ms_cd, etrys, et_cd) and run the same query;
 There may be other problems happening there, but those are the ones I see more 
evident from the description of the problem.


Giving a closer look to your query I rea

impact pgbench on a physical replicated stream

[Gert Cuykens]

Hi, I would like to pgbench the production postgres that is being physical
replicated to a slave. Will I shoot myself in the foot because of the
physical replication if I try pgbench on prodcution postgres.
Thanks

Re: Compile static postgres.lib for postgres 15.3 on Windows

[Tom Lane]

Priess Stefan  writes:
> up to version 9.6.22 there was a win32.mak file in the src/interfaces/libpw 
> folder which could be used to compile a static postgres.lib using nmake. 
> Since Postgres 10 this file has been removed.

Yup.  It didn't really work right and there was insufficient interest
in maintaining it.

> Is it still possilbe to build a static postgres.lib for windows ? And if so, 
> how do I do it.

Run the normal build process using MSVC, and then just take libpq.lib
from the results.  On any modern machine that's just going to take
a few minutes, so it's not worth maintaining a separate libpq-only
build path.

regards, tom lane

Re: PostgreSQL - How to login with my Linux user account

[Amn Ojee Uw]

First of all, my gratitude to Adrian and David for taking the time to 
reply to my call for help; a 1k  thanks to you kids.


Having stated the above, PostgreSQL provides a default database namely 
'postgres'. Taking advantage of this service, I have change the 'psql' 
statement to 'psql -d postgres -U my_linux_user_name -W', I then entered 
my Linux account login password and got this error messages :

/*$ psql -d postgres -U my_linux_user_name -W *//*
*//*Password: *//*
*//*psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication 
failed for user "my_linux_user_name"*/


I also, using 'postres' account, have created another user account, 
chispa, who is a superuser; when trying to login as 'chispa' and using 
the above statement (/*psql -d postgres -U chispa -W *//*)*/ PostgreSQL 
throws the same error message.

/**/

What am I doing wrong?
Is there a webpage that explains how to login to PostgreSQL as other 
than 'postgres'?



Thanks in advance.

On 7/27/23 8:19 p.m., Amn Ojee Uw wrote:

psql -d mydb -U myuser -W

Re: password error in batch script

[Ron]

On 7/27/23 16:45, Adrian Klaver wrote:

On 7/27/23 1:17 PM, Atul Kumar wrote:

Hi,

I have a password Vl=SO*CIz%A83FQF that is working fine like that in the 
command prompt but when it is being used in a batch file it is giving me 
an error of wrong password, the password is something looks like that in 
batch script.


set "PGPASSWORD=Vl=SO*CIz%A83FQF"


Also, the first double quote seems to be in the wrong position. This is the 
canonical way to define a string:


set PGPASSWORD="Vl=SO*CIz%A83FQF"



More information needed.

1) What sort of batch script; shell, Python, etc?

2) OS and version.

3) What is set pointing at?

4) PGPASSWORD is an environment variable per:

https://www.postgresql.org/docs/15/libpq-envars.html

So I would expect export PGPASSWORD = .

5) Why not use pgpass?:

https://www.postgresql.org/docs/current/libpq-pgpass.html



Could you please help me in getting this password read by command prompt 
in the batch file.




Regards.





--
Born in Arizona, moved to Babylonia.

AW: Compile static postgres.lib for postgres 15.3 on Windows

[Priess Stefan]

Thanks Tom! 

I also need to change the default port from 5433 to 5432 (--with-pgport=5433). 
How can I do this using the build.bat toolchain? I don't see the pgport 
parameter in the config_default.pl file.

Best,

Stefan

nexus / chili

Stefan Priess
Softwareentwicklung

Tel: +49 6221 18079-10
E-Mail: stefan.pri...@nexus-chili.com


NEXUS / CHILI GmbH, Friedrich-Ebert-Str. 2, 69221 Dossenheim/Heidelberg 
www.nexus-chili.com

Eingetragene Gesellschaft beim Registergericht Mannheim, HRB 337363
Geschäftsführer: Dr. Uwe Engelmann, Dr. Heiko Münch

-Ursprüngliche Nachricht-
Von: Tom Lane  
Gesendet: Freitag, 28. Juli 2023 14:48
An: Priess Stefan 
Cc: pgsql-general@lists.postgresql.org
Betreff: Re: Compile static postgres.lib for postgres 15.3 on Windows

Priess Stefan  writes:
> up to version 9.6.22 there was a win32.mak file in the src/interfaces/libpw 
> folder which could be used to compile a static postgres.lib using nmake. 
> Since Postgres 10 this file has been removed.

Yup.  It didn't really work right and there was insufficient interest in 
maintaining it.

> Is it still possilbe to build a static postgres.lib for windows ? And if so, 
> how do I do it.

Run the normal build process using MSVC, and then just take libpq.lib from the 
results.  On any modern machine that's just going to take a few minutes, so 
it's not worth maintaining a separate libpq-only build path.

regards, tom lane

Re: AW: Compile static postgres.lib for postgres 15.3 on Windows

[Adrian Klaver]

On 7/28/23 07:26, Priess Stefan wrote:

Thanks Tom!

I also need to change the default port from 5433 to 5432 (--with-pgport=5433). 
How can I do this using the build.bat toolchain? I don't see the pgport 
parameter in the config_default.pl file.


1) The default port is 5432.

2) Why do you want to change it?



Best,

Stefan

nexus / chili

Stefan Priess
Softwareentwicklung

Tel: +49 6221 18079-10
E-Mail: stefan.pri...@nexus-chili.com



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

Re: PostgreSQL - How to login with my Linux user account

[Ron]

On 7/28/23 09:00, Amn Ojee Uw wrote:


First of all, my gratitude to Adrian and David for taking the time to 
reply to my call for help; a 1k  thanks to you kids.


Having stated the above, PostgreSQL provides a default database namely 
'postgres'. Taking advantage of this service, I have change the 'psql' 
statement to 'psql -d postgres -U my_linux_user_name -W', I then entered 
my Linux account login password and got this error messages :

/*$ psql -d postgres -U my_linux_user_name -W *//*
*//*Password: *//*
*//*psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication 
failed for user "my_linux_user_name"*/


I also, using 'postres' account, have created another user account, 
chispa, who is a superuser; when trying to login as 'chispa' and using the 
above statement (/*psql -d postgres -U chispa -W *//*)*/ PostgreSQL throws 
the same error message.

/**/

What am I doing wrong?
Is there a webpage that explains how to login to PostgreSQL as other than 
'postgres'?




https://www.postgresql.org/docs/12/auth-pg-hba-conf.html

You want peer authentication to be able to connect to postgresql as 
my_linux_username.


--
Born in Arizona, moved to Babylonia.

Re: PostgreSQL - How to login with my Linux user account

[Adrian Klaver]

On 7/28/23 07:00, Amn Ojee Uw wrote:
First of all, my gratitude to Adrian and David for taking the time to 
reply to my call for help; a 1k  thanks to you kids.


Having stated the above, PostgreSQL provides a default database namely 
'postgres'. Taking advantage of this service, I have change the 'psql' 
statement to 'psql -d postgres -U my_linux_user_name -W', I then entered 
my Linux account login password and got this error messages :

/*$ psql -d postgres -U my_linux_user_name -W *//*
*//*Password: *//*
*//*psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication 
failed for user "my_linux_user_name"*/


1) See

https://www.postgresql.org/docs/current/auth-peer.html

2) What OS are you on and how did you install Postgres?

3) Try

sudo -u postgres psql postgres


4) Provide the content of pg_hba.conf that starts at the line below

# TYPE  DATABASEUSERADDRESS METHOD

...




I also, using 'postres' account, have created another user account, 
chispa, who is a superuser; when trying to login as 'chispa' and using 
the above statement (/*psql -d postgres -U chispa -W *//*)*/ PostgreSQL 
throws the same error message.

/**/

What am I doing wrong?
Is there a webpage that explains how to login to PostgreSQL as other 
than 'postgres'?



Thanks in advance.

On 7/27/23 8:19 p.m., Amn Ojee Uw wrote:

psql -d mydb -U myuser -W


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

Re: impact pgbench on a physical replicated stream

[Ron]

On 7/28/23 07:20, Gert Cuykens wrote:
Hi, I would like to pgbench the production postgres that is being physical 
replicated to a slave. Will I shoot myself in the foot because of the 
physical replication if I try pgbench on prodcution postgres.


Replication or not, why are you running pgbench on a *production* server?

And by "shoot myself in the foot", are you asking whether it would overwhelm 
some system or another, thereby breaking replication?  If so, we can't 
answer that, since we don't know the hardware specifications, your current 
workload, and whether or not you're doing synchronous or asynchronous 
replication.


--
Born in Arizona, moved to Babylonia.

Re: AW: Compile static postgres.lib for postgres 15.3 on Windows

[Tom Lane]

Priess Stefan  writes:
> I also need to change the default port from 5433 to 5432 
> (--with-pgport=5433). How can I do this using the build.bat toolchain? I 
> don't see the pgport parameter in the config_default.pl file.

I don't use MSVC myself, but a quick look at the code suggests that
you want to add

"--with-pgport" => 5433

(with the quotes, probably).

regards, tom lane

Re: impact pgbench on a physical replicated stream

[Gert Cuykens]

Using asynchronous replication. Production is running on a gaming PC at the
moment because it was to slow on a state of the art hypervisor 128 core 1TB
mem and nvme  disk server rack... (where de replicated slave is running on)

I would like to know what number this gaming pc is pulling that supposedly
the server rack can't.

On Fri, Jul 28, 2023 at 4:50 PM Ron  wrote:

> On 7/28/23 07:20, Gert Cuykens wrote:
>
> Hi, I would like to pgbench the production postgres that is being physical
> replicated to a slave. Will I shoot myself in the foot because of the
> physical replication if I try pgbench on prodcution postgres.
>
>
> Replication or not, why are you running pgbench on a *production* server?
>
> And by "shoot myself in the foot", are you asking whether it would
> overwhelm some system or another, thereby breaking replication?  If so, we
> can't answer that, since we don't know the hardware specifications, your
> current workload, and whether or not you're doing synchronous or
> asynchronous replication.
>
> --
> Born in Arizona, moved to Babylonia.
>

Re: Bogus temp file reporting?

[Ron]

231*TB* seems, to me, an unreasonably large number.

On 7/27/23 02:30, kg.postgre...@olympiakos.com wrote:

Hello,

Is there a known issue with temp file reporting?  I have a production db 
that is reporting 231TB of temp space usage, which can’t be true. 
 temp_blks_written in pg_stat_statements sort of confirms this, as the 
reported queries show just a few MB of temp space usage.  I suspect a 
reboot will clear this (to do) and wanted to check with you to see if 
someone else has had similar experiences.


Regards,
Kiriakos Georgiou


select version();
version
---
PostgreSQL 14.8 (Ubuntu 14.8-1.pgdg20.04+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

(1 row)
select
datname,
    temp_files as "# of temp files",
    pg_size_pretty(temp_bytes) as "size of temp files"
from
pg_stat_database;

   datname | # of temp files | size of temp files
---+-+
               |   0 | 0 bytes
postgres   |   0 | 0 bytes
testdb         |  269647 | 231 TB
template0  |   0 | 0 bytes
template1  |   0 | 0 bytes


--
Born in Arizona, moved to Babylonia.

Re: backup_manifest rename to backup_manifest.old after successful postgres start up

[Michael Paquier]

On Tue, Jul 18, 2023 at 01:53:23AM +0200, Gert Cuykens wrote:
> Hi, suggest to automatically rename backup_manifest to backup_manifest.old
> like backup_label when postgres start up successfully because it has no use
> anymore for pg_verifybackup after postgres has been started.

Yes, I would agree with you that it is not especially useful to keep
it around once the cluster has been recovered from a base backup.  It
would actually lead to various errors if attempting to run
pg_verifybackup on its data folder, for instance.
--
Michael


signature.asc
Description: PGP signature

How to get an md5/sha256 hash of a really large object in psql?

[Alex Shan]

Hello,

In my DB I have a large object over 4GB in size.
I need to get its MD5 or SHA256 from within psql query, i.e. without
exporting it to FS first.

“SELECT md5(lo_get(loid));” doesnt work — “large object is too large”.

Is there any other way to do it?


Regards,
Al