Re: Error creating materialized view

[David Mullineux]

On Mon, 25 Nov 2024, 06:08 Shaun Robinson, 
wrote:

> Hi,
>
> I'm currently testing an application with Postgres 17.2 and am getting an
> error when creating a materialized view which works in version 16 and
> below. The sql works fine running as a query, but adding the
> create materialized view breaks it.
>
> The error comes when calling a custom function and the error is that a
> relation doesn't exist (which it does as it works within the same query
> when not creating a view).
>
> Is this a known issue in the version 17.2?
>
> Thanks
> Shau
>


Can you please post the DDL statements so we can see?
Thanks

> can you
>

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

[Ilya Anfimov]

On Sun, Nov 24, 2024 at 09:35:15PM +0530, Subhash Udata wrote:
>Dear PostgreSQL Community,
> 
>I have a production database setup with a primary server and a standby
>server. The database is currently running on PostgreSQL 15.0, and I plan
>to upgrade both servers to 15.9.
> 
>I have the following questions regarding the upgrade and replication
>process:
> 
> 1. Upgrade and Replication Compatibility:
> 
>   * My plan is to perform a failover, promote the standby server
> (currently 15.0) to primary, and then upgrade the old primary
> server to version 15.9.

 1) Why do you want to use a switchover first?
 You can upgrade the standby, then switchover to it.
 (You  could  even  don't  switchover  back, when the old primary
would be upgraded and synchonized).


>   * After upgrading the old primary server to version 15.9, I want to
> configure it as a standby server and set up streaming replication
> with the new primary server, which will still be running version
> 15.0.
>   * Is it possible to establish streaming replication between these
> two versions (15.0 as primary and 15.9 as standby)?
> 2. Efficient Replication Setup:
> 
>   * The production database is around 1TB in size, and creating
> replication using pg_basebackup is taking more than 2-3 hours to
> complete.
>   * Is there an alternative method to set up replication without
> taking a full backup of the entire cluster but instead using only
> the WAL files that have changed on both servers?

 Well, there are some.

pg_rewind  is one of those (you should keep all the WAL files be-
tween switchover point and now on both servers. Also, maximum one
switchover/failover AFAIK. Also, it's a bit fragile nevertheless,
bad things could happen  if  you  mix  timelines  from  the  very
straight  scenario  of one switchover+pg_rewind on the old prima-
ry).

 Hoewever, I'd usually use rsync+low-level backup protocol
 
https://www.postgresql.org/docs/15/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP

 This  requires some manual commands, writing backup_label and so
on -- but looks more straightforward to me.
 (And yes, rsync uses block-level comparision and transfers  only
change blocks.
 setting block-size to 8k in rsync could be beneficial).

> 
>Your guidance and recommendations on these questions will be greatly
>appreciated.
> 
>Thank you for your time and support!
> 
>Best regards,
> 
>Subhash

License question

[prashant sinha]

Hello There,I am looking to install PostgreSQL on a Microsoft Azure cloud VM 
for a product I am developing for business purpose. Is there a free version of 
PostgreSQL available which I can use without buying any licenses? In case I 
want to self install and manage the database? Just need guidance if I can 
install the available version from site for business purpose too or I must buy 
licenses? Will appreciate an appropriate related answer.
Thanks,Prashant 

Re: License question

[Christophe Pettus]

> On Nov 21, 2024, at 21:40, prashant sinha  wrote:
> Is there a free version of PostgreSQL available which I can use without 
> buying any licenses? In case I want to self install and manage the database? 
> Just need guidance if I can install the available version from site for 
> business purpose too or I must buy licenses? Will appreciate an appropriate 
> related answer.

PostgreSQL itself is an open source project distributed under a very permissive 
license:

https://www.postgresql.org/about/licence/

No payment is required to download and run it.

Re: Unique key constraint Issue

[ajit wangkhem]

If all syntax correct while creating unique key or composite unique key than 
there is no chances of fail. In my experience I m facing same issue but it’s 
rear case, if any extra space in data (insert query) than duplicate data 
inserted. Please chk thorough insert query . May be the issue not sure. In 
oracle extra space omit but in Postgres it would not.

Sent from Outlook for iOS

From: shashidhar Reddy 
Sent: Monday, November 25, 2024 2:14:37 PM
To: ajit wangkhem 
Cc: pgsql-general 
Subject: Re: Unique key constraint Issue

Hi Ajit,

Thank you for your reply!

The result of the query is same across all servers and yes streaming 
replication does not have any issue but the question is how did duplicate 
values entered when there unique key in place.

On Mon, 25 Nov, 2024, 1:21 pm ajit wangkhem, 
mailto:ajit.wangk...@gmail.com>> wrote:
  Output should be consistent across servers below query o/p

SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'your table name'::regclass AND contype = 'u';
(different datatype combine in UK is not an issue). This issue may not happen 
in asynchronous, log shipping or streaming replication. In logical replication 
it may create problem.

On Mon, Nov 25, 2024 at 12:01 PM shashidhar Reddy 
mailto:shashidharreddy...@gmail.com>> wrote:
Hello,

We have postgresql servers with PostgreSQL 13.10   on Ubuntu release 22.04.

The issue is a unique key constraint with two columns one is character another 
is integer. At some point the unique key did not work as I see duplicate values 
with these two columns combination and it happened on multiple servers on 
multiple databases on same table with same unique key. I tried db dump and 
restore but the key is failing to create when restore with duplicate value 
errors.

Re: Unique key constraint Issue

[shashidhar Reddy]

Hi Ajit,

Thank you for your reply!

The result of the query is same across all servers and yes streaming
replication does not have any issue but the question is how did duplicate
values entered when there unique key in place.

On Mon, 25 Nov, 2024, 1:21 pm ajit wangkhem, 
wrote:

>   Output should be consistent across servers below query o/p
>
> SELECT conname, pg_get_constraintdef(oid)
> FROM pg_constraint
> WHERE conrelid = 'your table name'::regclass AND contype = 'u';
> (different datatype combine in UK is not an issue). This issue may not
> happen in asynchronous, log shipping or streaming replication. In logical
> replication it may create problem.
>
> On Mon, Nov 25, 2024 at 12:01 PM shashidhar Reddy <
> shashidharreddy...@gmail.com> wrote:
>
>> Hello,
>>
>> We have postgresql servers with PostgreSQL 13.10   on Ubuntu release
>> 22.04.
>>
>> The issue is a unique key constraint with two columns one is character
>> another is integer. At some point the unique key did not work as I see
>> duplicate values with these two columns combination and it happened on
>> multiple servers on multiple databases on same table with same unique key.
>> I tried db dump and restore but the key is failing to create when restore
>> with duplicate value errors.
>>
>>
>>
>>

Re: Error creating materialized view

[Shaun Robinson]

Hi David,

I've created a basic example which produces the issue for me and the SQL is
below.

Thanks
Shaun


create table diagnosisTest
(
id serial primary key,
icd_code varchar(10)
);

create table encounterTest
(
id serial primary key,
dx1 integer,
dx2 integer,
dx3 integer,
dx4 integer,
dx5 integer,
dx6 integer,
dx7 integer,
dx8 integer,
dx9 integer,
dx10 integer,
dx11 integer,
dx12 integer
);

create table chargeTest
(
id serial primary key,
encounter_id integer,
amount varchar(10),
dx_list text
);

INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.1');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.2');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.3');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.4');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.5');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.6');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.7');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M3.3');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M4.4');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M6.5');


insert into encounterTest(dx1, dx2, dx3)
VALUES (1, 4, 6);
insert into encounterTest(dx1, dx2, dx3)
VALUES (7, 1, 9);
insert into encounterTest(dx1, dx2, dx3)
VALUES (10, 3, 1);
insert into encounterTest(dx1, dx2, dx3)
VALUES (5, 4, 1);

insert into chargeTest (encounter_id, amount, dx_list)
VALUES (1, '100.00', '1, 2');
insert into chargeTest (encounter_id, amount, dx_list)
VALUES (2, '500.00', '1,2,3');
insert into chargeTest (encounter_id, amount, dx_list)
VALUES (3, '300.00', '1,2,3');


CREATE OR REPLACE FUNCTION get_chg_dxs_test(INTEGER)
RETURNS TABLE
(
dx_codes TEXT,
primary_dx TEXT
)
AS
$$
DECLARE
chg_id ALIAS FOR $1;
chg_row chargeTest%ROWTYPE;
enc_row encounterTest%ROWTYPE;
chg_dxs TEXT[];
dx_list TEXT[];
loop_counter INTEGER;
current_dx_str TEXT;
primary_dx TEXT;
BEGIN
SELECT * INTO chg_row FROM chargeTest c WHERE c.id = chg_id;
SELECT * INTO enc_row FROM encounterTest e WHERE e.id =
chg_row.encounter_id;
SELECT regexp_split_to_array(coalesce(chg_row.dx_list, ''), ',') INTO
chg_dxs;

loop_counter = 0;

LOOP
EXIT WHEN loop_counter = (array_length(chg_dxs, 1));
loop_counter := loop_counter + 1;

IF chg_dxs[loop_counter] = '1'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =
enc_row.dx1;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '2'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =
enc_row.dx2;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '3'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =
enc_row.dx3;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '4'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =
enc_row.dx4;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '5'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =
enc_row.dx5;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '6'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =
enc_row.dx6;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '7'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =
enc_row.dx7;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '8'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =
enc_row.dx8;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '9'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =
enc_row.dx9;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '10'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =
enc_row.dx10;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '11'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =
enc_row.dx11;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '12'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =
enc_row.dx12;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF loop_counter = 1
THEN
primary_dx := current_dx_str;
END IF;

END LOOP;

RETURN QUERY SELECT array_to_string(dx_list, ','),
primary_dx;
END;
$$ LANGUAGE plpgsql;

create materialized view vtest as
SELECT chg.id AS charge_id,

(get_chg_dxs_test(chg.id)).primary_dx AS primary_dx
FROM chargeTest chg
with data;

On Mon, 25 Nov 2024 at 08:58, David Mullineux  wrote:

>
>
> On Mon, 25 Nov 2024, 06:08 Shaun Robinson, 
> wrote:
>
>> Hi,
>>
>> I'm currently testing an application with Postgres 17.2 and am getting an
>> error when creating a materialized view which works in version 16 and
>> below. The sql works fine running as a query, but adding the
>> create materialized view breaks it.
>>
>> The error comes when calling a cust

Re: Unique key constraint Issue

[Adrian Klaver]

On 11/25/24 00:44, shashidhar Reddy wrote:

Hi Ajit,

Thank you for your reply!

The result of the query is same across all servers and yes streaming 
replication does not have any issue but the question is how did 
duplicate values entered when there unique key in place.


Corrupted index?

Have you tried doing a REINDEX?


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

Re: Unique key constraint Issue

[Tom Lane]

shashidhar Reddy  writes:
> The issue is a unique key constraint with two columns one is character
> another is integer. At some point the unique key did not work as I see
> duplicate values with these two columns combination and it happened on
> multiple servers on multiple databases on same table with same unique key.

If the table has existed for some time (like, across updates of the
underlying operating system) then your problem likely traces to
changes in the OS' sorting rules for character strings:

https://wiki.postgresql.org/wiki/Locale_data_changes

Such a change causes the unique key's index to be out of sort order
and thus effectively corrupt from PG's viewpoint: searches may or
may not find an entry that is there.  Once that happens it's pretty
easy for duplicate entries to get added.

The fix is to REINDEX affected indexes.  But if you already have
duplicate entries in the table, you'll need to correct them before
REINDEX will succeed.

regards, tom lane

Re: Error creating materialized view

[Laurenz Albe]

On Mon, 2024-11-25 at 06:08 +, Shaun Robinson wrote:
> I'm currently testing an application with Postgres 17.2 and am getting an 
> error
> when creating a materialized view which works in version 16 and below. The sql
> works fine running as a query, but adding the create materialized view breaks 
> it.
> 
> The error comes when calling a custom function and the error is that a 
> relation
> doesn't exist (which it does as it works within the same query when not 
> creating
> a view).
> 
> Is this a known issue in the version 17.2?

That's a deliberate improvemen; see the first point in
https://www.postgresql.org/docs/current/release-17.html#RELEASE-17-MIGRATION

you will have to schema-qualify all tables in the function or set the
"search_path" on the function.

Yours,
Laurenz Albe

Re: Pg_basebackup failed or completed?

[ajit wangkhem]

Put wal log in the restore directory and check during start postgresql
consumed or not. If consumed then no issue. Instead of risk free create
recovery.conf or standby.signal and put restore_command in that above file
and see whether consumed or not. If consumed its ok.

On Mon, Nov 25, 2024 at 11:10 AM jayesh thakare 
wrote:

> Hi Team,
>
> We have got below error while doing basebackup
>
>
> pg_basebackup: could not close file "0002022E00027": No error
>
>
> Can we assume that backup is completed successfully?
>
>
> Regards,
> Jayeshthakare
> Postgresql DBA
> 8828986182
>

DB Switchover using repmgr--Error

[jayakumar s]

Hi Team,

I have tried to configure DB switch over using repmgr. Post updated repmgr
file then tried to register in primary db but getting the below error.

Kindly check and let me know to fix the issue.

*Error:*

[postgres@post1 bin]$ ./repmgr -f /var/lib/pgsql/repmgr.conf primary
register
ERROR: following errors were found in the configuration file:
  syntax error in file "/var/lib/pgsql/repmgr.conf" line 3, near token
"data_directory"
  syntax error in file "/var/lib/pgsql/repmgr.conf" line 6, near token
"log_file"
[postgres@post1 bin]$

*Conf file:*

[postgres@post1 data]$ cat /var/lib/pgsql/repmgr.conf
node_id=1
node_name=primary
conninfo='host=192.168.29.193 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/application/pgsql/data' failover=automatic
promote_command='/usr/pgsql-16/bin/repmgr standby promote -f
/var/lib/pgsql/repmgr.conf --log-to-file'
follow_command='/usr/pgsql-16/bin/repmgr standby follow -f
/var/lib/pgsql/repmgr.conf --log-to-file --upstream-node-id=%n'
pg_bindir='/usr/pgsql-16/bin' log_file='/usr/pgsql-16/repmgr.log'
[postgres@post1 data]$

Re: License question

[prashant sinha]

Hi Christophe,Thank you so much for the response. One of my client is also 
requesting details of licensed version of PostgreSQL. Could you please guide me 
cost of PostgreSQL license which they want to get installed on their (client’s) 
on premise server. Not sure how pricing works for enterprise license which have 
all associated support for enterprise. Also not sure if we can install on any 
supported license for on-Prem server or we have to buy specific server 
recommended by PostgreSQL. Will appreciate your guidance here. 
Thanks,Prashant 


Sent from Yahoo Mail for iPhone


On Tuesday, November 26, 2024, 1:29 AM, Christophe Pettus  
wrote:



> On Nov 21, 2024, at 21:40, prashant sinha  wrote:
> Is there a free version of PostgreSQL available which I can use without 
> buying any licenses? In case I want to self install and manage the database? 
> Just need guidance if I can install the available version from site for 
> business purpose too or I must buy licenses? Will appreciate an appropriate 
> related answer.

PostgreSQL itself is an open source project distributed under a very permissive 
license:

    https://www.postgresql.org/about/licence/

No payment is required to download and run it.

Re: License question

[Adrian Klaver]

On 11/25/24 14:47, Clay Jackson (cjackson) wrote:

Christophe is spot on when it comes to the PostgreSQL software.

Not trying to be "snarky" or condescending, but there are some other considerations.   "Open 
Source" or "Free" does not mean "without cost".

No matter where you decide to host the software, there will be storage, 
compute, and network costs, not only to support the software itself, but also 
the data you store.

With respect to the data, since you or your employer have decided to 
persistently store the data in a database, I would presume the data, and more 
importantly, access to that data, has value.

It seems from the original post that you've decided to pay someone (a cloud vendor) to "take 
care" of the storage, network, and compute "services".  That's great!

But, what about that "non-licensed" software?   Once you use that software to store and 
access your data, you have created a CONTINUING need for that software.   What will it cost you or 
your employer if a "bug" in that software prevents access to, or worse yet, corrupts your 
data.


1) It is not "non-licensed", it is permissively licensed.

2) Not sure what the point of '... CONTINUING need for that software' 
is? If you have a process that depends on something to run then you have 
established a need. If you don't want that then don't start any project.





Are you willing to sign up for "maintaining" PostgreSQL in your environment, INCLUDING things like 
patching, finding and fixing bugs, upgrades, backup and recovery, and off-hours support?  If you or your 
employer have any concerns about this, and IHMO, you should, you might want to consider a "licensed and 
supported" version of PostgreSQL.  There are several out there, including EDB and others.  Even the big 
cloud vendors have "support plans" for PostgreSQL.


That is done by the community as shown here:

https://www.postgresql.org/developer/

and here:

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

and here:

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

and here:

https://www.postgresql.org/docs/current/backup.html


As to off hours support look at the timestamps here:

https://www.postgresql.org/list/pgsql-general/


Now if you want a specific company to contact then yes there is this:

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

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





Oh, and don't forget, a backup only as good as the last time you tested a 
restore.

Clay Jackson
Database Solutions Architect
clay.jack...@quest.com

-Original Message-
From: Christophe Pettus 
Sent: Monday, November 25, 2024 11:59 AM
To: prashant sinha 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: License question



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

Re: Unique key constraint Issue

[shashidhar Reddy]

Hi Adrian,

Thank you for your response.

I don't think index corrupted as I can generate the ddl script from the
index and the index is still in use when we query.
 To rebuild the index we are still removing duplicate entries. But nothing
found in logs.

On Mon, 25 Nov, 2024, 9:30 pm Adrian Klaver, 
wrote:

> On 11/25/24 00:44, shashidhar Reddy wrote:
> > Hi Ajit,
> >
> > Thank you for your reply!
> >
> > The result of the query is same across all servers and yes streaming
> > replication does not have any issue but the question is how did
> > duplicate values entered when there unique key in place.
>
> Corrupted index?
>
> Have you tried doing a REINDEX?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

RE: License question

[Clay Jackson (cjackson)]

Thanks, Ron!   Exactly my points!

I do not want to “discount” the community in ANY way.In fact, I’m a huge 
fan of Open Source, as long as everyone, including “management”, is bought into 
community support concept.

I’ve seen the “wrong side” of “What do you mean, ‘You posted something to the 
list’?   Why can’t you CALL someone (and/or get someone logged in) RIGHT NOW?” 
discussions, usually “in the heat of battle”,  a few too many times.

Of course, the flip side of this is “Whew – the community solved this before 
the  even got back to me”.

It comes down to being “fully informed”.


Clay Jackson
Database Solutions Architect


From: Ron Johnson 
Sent: Monday, November 25, 2024 5:06 PM
To: pgsql-general 
Subject: Re: License question

CAUTION: This email originated from outside of the organization. Do not follow 
guidance, click links, or open attachments unless you recognize the sender and 
know the content is safe.

On Mon, Nov 25, 2024 at 5:47 PM Clay Jackson (cjackson) 
mailto:clay.jack...@quest.com>> wrote:
[snip]
Are you willing to sign up for "maintaining" PostgreSQL in your environment, 
INCLUDING things like patching, finding and fixing bugs, upgrades, backup and 
recovery, and off-hours support?

Azure's Postgresql managed database handles all that (except off-hours support) 
for you, and there's much less off-hours support required (at least there was 
with AWS RDS Postgresql, so pressuming so with Azure, too).

Of course, OP just mentioned an Azure VM, so he'd have to do all that himself.

Using the Azure Postgresql managed database still means you'll have to think 
about archiving data, and properly configuring PG (mainly autovacuum 
parameters).

--
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Database stats ( pg_stat_database.stats_reset ) get reset on daily basis - why?

[Steeve Boulanger]

>  As a superuser, rename pg_stat_reset inside one of the commonly affected
databases:
> alter function pg_stat_reset rename to
hey_stop_running_pg_stat_reset_already;
> Then see who starts complaining. Additionally, your server log will get
helpful entries like this:

Thanks for the tip Greg - that's very clever! I've done this just now,
and will check back tomorrow to see if there's any corresponding error
message.

NB:  I hadn't originally realized that the system functions (ie pg_*) were
created for each database, so I had renamed only the one in the postgres
database, thinking it was global (ie I'll blame this on my Oracle days
;-)).

Now I renamed the function under one database that had been verified as
getting its stats reset on a daily basis. Hopefully tomorrow we'll get more
interesting info!

-Steeve

On Sat, Nov 23, 2024 at 11:50 AM Greg Sabino Mullane 
wrote:

> As a superuser, rename pg_stat_reset inside one of the commonly affected
> databases:
>
> alter function pg_stat_reset rename to
> hey_stop_running_pg_stat_reset_already;
>
> Then see who starts complaining. Additionally, your server log will get
> helpful entries like this:
>
> ERROR:  function pg_stat_reset() does not exist
>
> Cheers,
> Greg
>
>

RE: License question

[Clay Jackson (cjackson)]

Christophe is spot on when it comes to the PostgreSQL software.

Not trying to be "snarky" or condescending, but there are some other 
considerations.   "Open Source" or "Free" does not mean "without cost".

No matter where you decide to host the software, there will be storage, 
compute, and network costs, not only to support the software itself, but also 
the data you store.

With respect to the data, since you or your employer have decided to 
persistently store the data in a database, I would presume the data, and more 
importantly, access to that data, has value.

It seems from the original post that you've decided to pay someone (a cloud 
vendor) to "take care" of the storage, network, and compute "services".  That's 
great!

But, what about that "non-licensed" software?   Once you use that software to 
store and access your data, you have created a CONTINUING need for that 
software.   What will it cost you or your employer if a "bug" in that software 
prevents access to, or worse yet, corrupts your data.

Are you willing to sign up for "maintaining" PostgreSQL in your environment, 
INCLUDING things like patching, finding and fixing bugs, upgrades, backup and 
recovery, and off-hours support?  If you or your employer have any concerns 
about this, and IHMO, you should, you might want to consider a "licensed and 
supported" version of PostgreSQL.  There are several out there, including EDB 
and others.  Even the big cloud vendors have "support plans" for PostgreSQL.

Oh, and don't forget, a backup only as good as the last time you tested a 
restore.

Clay Jackson
Database Solutions Architect
clay.jack...@quest.com

-Original Message-
From: Christophe Pettus 
Sent: Monday, November 25, 2024 11:59 AM
To: prashant sinha 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: License question

CAUTION: This email originated from outside of the organization. Do not follow 
guidance, click links, or open attachments unless you recognize the sender and 
know the content is safe.


> On Nov 21, 2024, at 21:40, prashant sinha  wrote:
> Is there a free version of PostgreSQL available which I can use without 
> buying any licenses? In case I want to self install and manage the database? 
> Just need guidance if I can install the available version from site for 
> business purpose too or I must buy licenses? Will appreciate an appropriate 
> related answer.

PostgreSQL itself is an open source project distributed under a very permissive 
license:

https://www.postgresql.org/about/licence/

No payment is required to download and run it.

Re: License question

[prashant sinha]

Thank you Christophe I am very clear now. And post your email researched 
various vendors and will get in touch with them. 

Thanks,Prashant 

On Tuesday, November 26, 2024, 11:42 AM, Christophe Pettus  
wrote:

Hello,

I think there is a fundamental misunderstanding here about how PostgreSQL's 
licensing works.

You do not pay for the community version of PostgreSQL.  The license 
specifically states it is available without a fee.  It's free.  No one collects 
money for the community version of PostgreSQL.

There's no "enterprise" version of PostgreSQL that is provided by the 
community.  It's just PostgreSQL.

There are vendors which provide either commercial versions of PostgreSQL based 
on the community version, or which provide commercial support for PostgreSQL, 
but those are separate things from the PostgreSQL community version, and you 
would need to talk to the individual companies to find out what their 
arrangements are.

> On Nov 25, 2024, at 22:04, prashant sinha  wrote:
> 
> Hi Christophe,
> Thank you so much for the response. One of my client is also requesting 
> details of licensed version of PostgreSQL. Could you please guide me cost of 
> PostgreSQL license which they want to get installed on their (client’s) on 
> premise server. Not sure how pricing works for enterprise license which have 
> all associated support for enterprise. Also not sure if we can install on any 
> supported license for on-Prem server or we have to buy specific server 
> recommended by PostgreSQL. Will appreciate your guidance here. 
> 
> Thanks,
> Prashant 
> 
> 
> Sent from Yahoo Mail for iPhone
> 
> On Tuesday, November 26, 2024, 1:29 AM, Christophe Pettus  
> wrote:
> 
> 
> > On Nov 21, 2024, at 21:40, prashant sinha  wrote:
> > Is there a free version of PostgreSQL available which I can use without 
> > buying any licenses? In case I want to self install and manage the 
> > database? Just need guidance if I can install the available version from 
> > site for business purpose too or I must buy licenses? Will appreciate an 
> > appropriate related answer.
> 
> 
> PostgreSQL itself is an open source project distributed under a very 
> permissive license:
> 
>    https://www.postgresql.org/about/licence/
> 
> No payment is required to download and run it.

Re: License question

[Ron Johnson]

On Mon, Nov 25, 2024 at 5:47 PM Clay Jackson (cjackson) <
clay.jack...@quest.com> wrote:
[snip]

> Are you willing to sign up for "maintaining" PostgreSQL in your
> environment, INCLUDING things like patching, finding and fixing bugs,
> upgrades, backup and recovery, and off-hours support?


Azure's Postgresql managed database handles all that (except off-hours
support) for you, and there's much less off-hours support required (at
least there was with AWS RDS Postgresql, so pressuming so with Azure, too).

Of course, OP just mentioned an Azure VM, so he'd have to do all that
himself.

Using the Azure Postgresql managed database still means you'll have to
think about archiving data, and properly configuring PG (mainly autovacuum
parameters).

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: License question

[Alan Hodgson]

On Mon, 2024-11-25 at 22:47 +, Clay Jackson (cjackson) wrote:
> 
> Are you willing to sign up for "maintaining" PostgreSQL in your
> environment, INCLUDING things like patching, finding and fixing
> bugs, upgrades, backup and recovery, and off-hours support?
> 

Not sure what your point is, you have to do all that with commercial
software, too. And at least with open source you actually can find
and patch bugs yourself, in theory, although you certainly don't have
to, not with any major project.

Re: License question

[Alan Hodgson]

On Fri, 2024-11-22 at 05:40 +, prashant sinha wrote:
> Hello There,
> I am looking to install PostgreSQL on a Microsoft Azure cloud VM
> for a product I am developing for business purpose. Is there a free
> version of PostgreSQL available which I can use without buying any
> licenses? In case I want to self install and manage the database?
> Just need guidance if I can install the available version from site
> for business purpose too or I must buy licenses? Will appreciate an
> appropriate related answer.
> 

Open source PostgreSQL is and always has been free for any use.

If you're running it for real on Azure though, consider their managed
service. If it's half as good as Amazon's RDS, it's probably worth a
premium over the bare instance costs.

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

[Subhash Udata]

This would help me. I will try out the pg_rewind and rsync options.

On Mon, 25 Nov 2024 at 15:19, Ilya Anfimov  wrote:

> On Sun, Nov 24, 2024 at 09:35:15PM +0530, Subhash Udata wrote:
> >Dear PostgreSQL Community,
> >
> >I have a production database setup with a primary server and a standby
> >server. The database is currently running on PostgreSQL 15.0, and I
> plan
> >to upgrade both servers to 15.9.
> >
> >I have the following questions regarding the upgrade and replication
> >process:
> >
> > 1. Upgrade and Replication Compatibility:
> >
> >   * My plan is to perform a failover, promote the standby server
> > (currently 15.0) to primary, and then upgrade the old primary
> > server to version 15.9.
>
>  1) Why do you want to use a switchover first?
>  You can upgrade the standby, then switchover to it.
>  (You  could  even  don't  switchover  back, when the old primary
> would be upgraded and synchonized).
>
>
> >   * After upgrading the old primary server to version 15.9, I
> want to
> > configure it as a standby server and set up streaming
> replication
> > with the new primary server, which will still be running
> version
> > 15.0.
> >   * Is it possible to establish streaming replication between
> these
> > two versions (15.0 as primary and 15.9 as standby)?
> > 2. Efficient Replication Setup:
> >
> >   * The production database is around 1TB in size, and creating
> > replication using pg_basebackup is taking more than 2-3
> hours to
> > complete.
> >   * Is there an alternative method to set up replication without
> > taking a full backup of the entire cluster but instead using
> only
> > the WAL files that have changed on both servers?
>
>  Well, there are some.
>
> pg_rewind  is one of those (you should keep all the WAL files be-
> tween switchover point and now on both servers. Also, maximum one
> switchover/failover AFAIK. Also, it's a bit fragile nevertheless,
> bad things could happen  if  you  mix  timelines  from  the  very
> straight  scenario  of one switchover+pg_rewind on the old prima-
> ry).
>
>  Hoewever, I'd usually use rsync+low-level backup protocol
>
> https://www.postgresql.org/docs/15/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP
>
>  This  requires some manual commands, writing backup_label and so
> on -- but looks more straightforward to me.
>  (And yes, rsync uses block-level comparision and transfers  only
> change blocks.
>  setting block-size to 8k in rsync could be beneficial).
>
> >
> >Your guidance and recommendations on these questions will be greatly
> >appreciated.
> >
> >Thank you for your time and support!
> >
> >Best regards,
> >
> >Subhash
>
>
>

Re: Unique key constraint Issue

[Adrian Klaver]

On 11/25/24 19:07, shashidhar Reddy wrote:

Hi Adrian,

Thank you for your response.

I don't think index corrupted as I can generate the ddl script from the 


What DDL script?


index and the index is still in use when we query.


See Tom Lanes post for more detail. Short version the index will still 
'work' except for the part where it lets in duplicate entries.


  To rebuild the index we are still removing duplicate entries. But 
nothing found in logs.


On Mon, 25 Nov, 2024, 9:30 pm Adrian Klaver, > wrote:


On 11/25/24 00:44, shashidhar Reddy wrote:
 > Hi Ajit,
 >
 > Thank you for your reply!
 >
 > The result of the query is same across all servers and yes streaming
 > replication does not have any issue but the question is how did
 > duplicate values entered when there unique key in place.

Corrupted index?

Have you tried doing a REINDEX?


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



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

Re: License question

[Christophe Pettus]

Hello,

I think there is a fundamental misunderstanding here about how PostgreSQL's 
licensing works.

You do not pay for the community version of PostgreSQL.  The license 
specifically states it is available without a fee.  It's free.  No one collects 
money for the community version of PostgreSQL.

There's no "enterprise" version of PostgreSQL that is provided by the 
community.  It's just PostgreSQL.

There are vendors which provide either commercial versions of PostgreSQL based 
on the community version, or which provide commercial support for PostgreSQL, 
but those are separate things from the PostgreSQL community version, and you 
would need to talk to the individual companies to find out what their 
arrangements are.

> On Nov 25, 2024, at 22:04, prashant sinha  wrote:
> 
> Hi Christophe,
> Thank you so much for the response. One of my client is also requesting 
> details of licensed version of PostgreSQL. Could you please guide me cost of 
> PostgreSQL license which they want to get installed on their (client’s) on 
> premise server. Not sure how pricing works for enterprise license which have 
> all associated support for enterprise. Also not sure if we can install on any 
> supported license for on-Prem server or we have to buy specific server 
> recommended by PostgreSQL. Will appreciate your guidance here. 
> 
> Thanks,
> Prashant 
> 
> 
> Sent from Yahoo Mail for iPhone
> 
> On Tuesday, November 26, 2024, 1:29 AM, Christophe Pettus  
> wrote:
> 
> 
> > On Nov 21, 2024, at 21:40, prashant sinha  wrote:
> > Is there a free version of PostgreSQL available which I can use without 
> > buying any licenses? In case I want to self install and manage the 
> > database? Just need guidance if I can install the available version from 
> > site for business purpose too or I must buy licenses? Will appreciate an 
> > appropriate related answer.
> 
> 
> PostgreSQL itself is an open source project distributed under a very 
> permissive license:
> 
> https://www.postgresql.org/about/licence/
> 
> No payment is required to download and run it.