Re: Read only user permission

[jian he]

On Thu, Aug 24, 2023 at 2:49 AM Marcos Pegoraro  wrote:
>
> Have you tried grant pg_read_all_data to readonly ?
>

I tried. seems pg_read_all_data really means "read all data no matter what".
so you cannot grant pg_read_all_data then revoke certain schema privileges.

begin;
create user my_user login;
GRANT pg_read_all_data TO my_user;
create schema a;
create schema b;
create table a.a as select 1;
create table b.b as select 2;
revoke all PRIVILEGES on schema a, b from my_user CASCADE;
-- revoke all PRIVILEGES on schema a from my_user CASCADE;
revoke all PRIVILEGES on all tables in schema a,b from my_user CASCADE;
set role my_user;
table a.a;
table b.b;
rollback;

Re: PostgreSQL DB cluster migration from centos7/RHEL7/OEL7 to RHEL8

[Tushar Takate]

Hi Avinash,

Thanks for your input.


On Wed, Aug 23, 2023 at 4:35 PM Avinash Vallarapu <
avinash.vallar...@gmail.com> wrote:

>
> On Wed, Aug 23, 2023 at 6:23 AM Tushar Takate 
> wrote:
>
>> Hi Team,
>>
>> We want to know if there are any open/known issues that we may face
>> post-migration of the DB cluster from centos7/RHEL7/OEL7 to RHEL8.
>>
>> Depends on the method you choose to perform this migration ?
>
-->  The method will depend on data size and the complexity of the data
upon which the method will be decided.
Ex -
1. For less critical/small DB size, This Can be possible with the dump and
restore with the small outage.
2. For a Highly transactional system most probably streaming replication
will be the method.
3. For Less transactional and less complex data (No extensions like
Timescale/PostGIS etc.. .) most probably logical replication will be the
method.

Hence, I opened this discussion to understand if there are any other known
issues that the PostgreSQL community should be aware of while migrating the
database from centos7/RHEL7/OEL7 to RHEL8 via different methods.

If you are using pg_dump and pg_restore for the initial data copy,
> followed by logical replication, then, it should be technically okay.
>
>> We came across one issue regarding collation(
>> https://wiki.postgresql.org/wiki/Locale_data_changes), the solution for
>> which is to reindex or use logical replication. In a similar way, we want
>> to understand if there are any other known issues that the community should
>> be aware of.
>>
>> -
>> Thanks & Regards,
>> Tushar K Takate.
>>
>>
>
> --
> Regards,
> Avinash Vallarapu
>

Re: Read only user permission

[Rob Sargent]

On 8/23/23 13:23, Hellen Jiang wrote:


Sorry it is a typo in the email. My readonly role is dbreadonly. It 
works well so far except no access to new tables created by read write 
role. It has access to new tables created by admin role.


I granted dbreadonly as the following:

-- Read-only role
GRANT CONNECT ON DATABASE mydatabase TO dbreadonly;
GRANT USAGE ON SCHEMA public TO dbreadonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO 
dbreadonly;




Is "public" a good schema to use for this sort of thing, or does it matter?

Re: Read only user permission

[Hellen Jiang]

Sorry it is a typo in the email. My readonly role is dbreadonly. It works well 
so far except no access to new tables created by read write role. It has access 
to new tables created by admin role.
I granted dbreadonly as the following:
-- Read-only role
GRANT CONNECT ON DATABASE mydatabase TO dbreadonly;
GRANT USAGE ON SCHEMA public TO dbreadonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dbreadonly;

From: Erik Wienhold 
Date: Wednesday, August 23, 2023 at 1:57 PM
To: Hellen Jiang , 
pgsql-general@lists.postgresql.org 
Subject: Re: Read only user permission
> On 23/08/2023 18:42 CEST Hellen Jiang  wrote:
>
> I have created readonly and readwrite roles with the following grants:
> however, readonly user does not have access to the new tables created by
> readwrite user. (readonly user has the access to new tables created by admin).
> Any idea how I can grant the access to readonly to make sure it has the read
> access to NEW tables created by readwrite user?
>
> -- Read-only role
> GRANT CONNECT ON DATABASE mydatabase TO readonly;
> GRANT USAGE ON SCHEMA public TO dbreadonly;
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO 
> dbreadonly;

It says "dbreadonly" instead of "readonly".  Just a typo or a different role?

> -- Read/write role
> GRANT CONNECT ON DATABASE mydatabase TO readwrite;
> GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
> GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public 
> TO readwrite;
> GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, 
> DELETE,TRUNCATE ON TABLES TO readwrite;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO readwrite;

--
Erik

Re: Read only user permission

[Erik Wienhold]

> On 23/08/2023 18:42 CEST Hellen Jiang  wrote:
>
> I have created readonly and readwrite roles with the following grants:
> however, readonly user does not have access to the new tables created by
> readwrite user. (readonly user has the access to new tables created by admin).
> Any idea how I can grant the access to readonly to make sure it has the read
> access to NEW tables created by readwrite user?
>
> -- Read-only role
> GRANT CONNECT ON DATABASE mydatabase TO readonly;
> GRANT USAGE ON SCHEMA public TO dbreadonly;
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO 
> dbreadonly;

It says "dbreadonly" instead of "readonly".  Just a typo or a different role?

> -- Read/write role
> GRANT CONNECT ON DATABASE mydatabase TO readwrite;
> GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
> GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public 
> TO readwrite;
> GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, 
> DELETE,TRUNCATE ON TABLES TO readwrite;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO readwrite;

--
Erik

Re: Read only user permission

[Tom Lane]

Hellen Jiang  writes:
> I have created readonly and readwrite roles with the following grants: 
> however, readonly user does not have access to the new tables created by 
> readwrite user. (readonly user has the access to new tables created by admin).
> Any idea how I can grant the access to readonly to make sure it has the read 
> access to NEW tables created by readwrite user?

The readwrite user (not the admin) would need to issue ALTER DEFAULT
PRIVILEGES granting that.

regards, tom lane

Re: Read only user permission

[Marcos Pegoraro]

Have you tried grant pg_read_all_data to readonly ?

regards
Marcos

Em qua., 23 de ago. de 2023 às 14:30, Hellen Jiang <
hji...@federatedwireless.com> escreveu:

> Hi,
>
>
>
> I have created readonly and readwrite roles with the following grants:
> however, readonly user does not have access to the* new* tables created
> by readwrite user. (readonly user has the access to *new* tables created
> by admin).
>
> Any idea how I can grant the access to readonly to make sure it has the
> read access to *NEW* tables created by readwrite user?
>
>
>
> -- Read-only role
>
> GRANT CONNECT ON DATABASE mydatabase TO readonly;
>
> GRANT USAGE ON SCHEMA public TO dbreadonly;
>
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
>
> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
>
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO
> dbreadonly;
>
>
>
> -- Read/write role
>
> GRANT CONNECT ON DATABASE mydatabase TO readwrite;
>
> GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
>
> GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA
> public TO readwrite;
>
> GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
>
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE,
> DELETE,TRUNCATE ON TABLES TO readwrite;
>
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO
> readwrite;
>
>
>
>
>

Read only user permission

[Hellen Jiang]

Hi,

I have created readonly and readwrite roles with the following grants: however, 
readonly user does not have access to the new tables created by readwrite user. 
(readonly user has the access to new tables created by admin).
Any idea how I can grant the access to readonly to make sure it has the read 
access to NEW tables created by readwrite user?

-- Read-only role
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA public TO dbreadonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dbreadonly;

-- Read/write role
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public 
TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, 
DELETE,TRUNCATE ON TABLES TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO readwrite;

Re: archive_command debugging

[Christoph Moench-Tegeder]

## Nick Renders (postg...@arcict.com):

> I was wondering if anyone had any good tips for "debugging" the
> archive_command in the postgresql.conf.

For starters, you get rather noisy logging when that command fails
(plus all the output from the command itself), so check your postgres
logfile first. Also, you get some additional debug logging at level
DEBUG3.
Then archive_command basically inherits it's environment and user id
from the archiver process, so you could check that process' proc entries.
Third, archive_command is executed via system() (after template
processing), so you're free to do... quite a lot in there (just
don't mess with the exit status). It might be more convenient to
use some shell wrapper around your final archive command and have
that wrapper do all the logging etc. you might want.

And a final tip: do not rely on the environment, always use full
paths, and if you ever might have whitespace or other "funny
characters" in some path/file name (or always, just to be safe)
make sure you get your quoting right.

Regasrds,
Christoph

-- 
Spare Space

archive_command debugging

[Nick Renders]

Hello,

I was wondering if anyone had any good tips for "debugging" the archive_command 
in the postgresql.conf.

I want to see what certain settings are when the command is run, like $PATH and 
id, but I don't want it to be successful either, so that the WAL files remain 
untouched.

Any tips?

Thanks,

Nick Renders

Re: [EXTERNAL] Oracle FDW version

[Adrian Klaver]

On 8/22/23 23:10, Jethro Elmer Sanidad wrote:

Hello,

Can you confirm in this email that our current version of PostgreSQL 
(9.2.24) is not compatible with any of oracle_fdw versions released? And 
you are recommending an upgrade? Thanks!


In your first post you said:

"Can you provide us download links for oracle_fdw for psql (PostgreSQL) 
9.4.24. ..."


1) Are you on 9.2 or 9.4?

2) Where did you get Postgres from?


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

Re: PL/pgSQL RETURN QUERY and DOMAIN CHECKs

[David G. Johnston]

On Wed, Aug 23, 2023, 07:25 Christian Barthel  wrote:

> Hello,
>
> can anyone explain why the domain check is not generating an exception
> when used within PL/pgSQL ‘RETURN QUERY’ statement?
>
>
>
>  45 -- works not as expected:
>  46 CREATE OR REPLACE FUNCTION tf2() RETURNS SETOF t
>  47 AS $$
>  48 DECLARE
>  49 BEGIN
>  50   RETURN QUERY select 4::double precision, 5::double precision;
>  51 END; $$ LANGUAGE plpgsql SECURITY DEFINER;
>  52 /*
>  53  *** *x=# SELECT tf2();
>  54  *** +---+
>  55  *** |  tf2  |
>  56  *** +---+
>  57  *** | (4,5) |
>  58  *** +---+
>  59  *** (1 row)
>  60  ***
>


The above (and the type definition...) is all that is relevant for the bug
report.  Once you've gotten a value of some type nothing else in the system
that is simply passed that value is going to reevaluate the constraints.

David J.


>

PL/pgSQL RETURN QUERY and DOMAIN CHECKs

[Christian Barthel]

Hello,

can anyone explain why the domain check is not generating an exception
when used within PL/pgSQL ‘RETURN QUERY’ statement?


See tf2() and tf3() above - in particular, the INSERT statement in line
100 and 163:

--8<---cut here---start->8---
  1 
  2 -- x, y greater equal 0
  3 -- y lower equal than x.
  4 CREATE TYPE _t AS (
  5x double precision,
  6y double precision
  7);
  8 CREATE DOMAIN t AS _t
  9  CHECK (
 10  (VALUE).x >= 0 AND
 11  (VALUE).y >= 0 AND
 12  (VALUE).y <= (VALUE).x
 13  );
 14 
 15 CREATE TABLE test (v t);
 16 
 17 -- - Tests:
 18 
 19 -- works as expected
 20 insert into test values ('(1,1)'::t), ('(3, 2)'::t);
 21 -- works as expected
 22 select '(1,1)'::t;
 23 select '(4,2)'::t;
 24 
 25 -- works as expected / throws exception: t_check
 26 select '(4,5)'::t;
 27 /*
 28 ,,* x=# SELECT '(4,5)'::t;
 29 ERROR:  23514: value for domain t violates check constraint "t_check"
 30 SCHEMA NAME:  public
 31 DATATYPE NAME:  t
 32 CONSTRAINT NAME:  t_check
 33 LOCATION:  ExecEvalConstraintCheck, execExprInterp.c:3670
 34 ,,*/
 35 
 36 -- works as expected: call tf1() fails with constraint error
 37 CREATE OR REPLACE FUNCTION tf1() RETURNS t
 38 AS $$
 39 DECLARE
 40 BEGIN
 41  return '(4,5)'::t;
 42 END; $$ LANGUAGE plpgsql SECURITY DEFINER;
 43 
 44 
 45 -- works not as expected:
 46 CREATE OR REPLACE FUNCTION tf2() RETURNS SETOF t
 47 AS $$
 48 DECLARE
 49 BEGIN
 50   RETURN QUERY select 4::double precision, 5::double precision;
 51 END; $$ LANGUAGE plpgsql SECURITY DEFINER;
 52 /*
 53  *** *x=# SELECT tf2();
 54  *** +---+
 55  *** |  tf2  |
 56  *** +---+
 57  *** | (4,5) |
 58  *** +---+
 59  *** (1 row)
 60  *** 
 61  *** Time: 0.821 ms
 62  *** *x=# SELECT '(4,5)'::t;
 63  *** ERROR:  23514: value for domain t violates check constraint "t_check"
 64  *** SCHEMA NAME:  public
 65  *** DATATYPE NAME:  t
 66  *** CONSTRAINT NAME:  t_check
 67  *** LOCATION:  ExecEvalConstraintCheck, execExprInterp.c:3639
 68  *** Time: 0.521 ms
 69  *** *x=# SELECT tf2()::t;
 70  *** +---+
 71  *** |  tf2  |
 72  *** +---+
 73  *** | (4,5) |
 74  *** +---+
 75  *** 
 76  *** (1 row)
 77  *** 
 78  *** Time: 0.546 ms
 79  *** *x=# SELECT pg_typeof(tf2());
 80  *** +---+
 81  *** | pg_typeof |
 82  *** +---+
 83  *** | t |
 84  *** +---+
 85  *** (1 row)
 86  *** 
 87  *** *x=#   insert into test values ('(1,1)'::t), ('(3, 2)'::t);
 88  *** INSERT 0 2
 89  *** Time: 0.897 ms
 90  *** *x=# 
 91  *** *x=# SELECT tf2();
 92  *** +---+
 93  *** |  tf2  |
 94  *** +---+
 95  *** | (4,5) |
 96  *** +---+
 97  *** (1 row)
 98  *** 
 99  *** Time: 0.532 ms
100  *** *x=# INSERT into test VALUES ((select tf2())) returning *;
101  *** +---+
102  *** |   v   |
103  *** +---+
104  *** | (4,5) |
105  *** +---+
106  *** (1 row)
107  *** 
108  *** INSERT 0 1
109  *** Time: 0.759 ms
110  *** *x=# SELECT v::t from test;
111  *** +---+
112  *** |   v   |
113  *** +---+
114  *** | (1,1) |
115  *** | (3,2) |
116  *** | (4,5) |
117  *** +---+
118  *** (3 rows)
119  *** 
120  *** Time: 0.559 ms
121  *** 
122  *** *x=# SELECT '(4,5)'::t;
123  *** ERROR:  23514: value for domain t violates check constraint "t_check"
124  *** SCHEMA NAME:  public
125  *** DATATYPE NAME:  t
126  *** CONSTRAINT NAME:  t_check
127  *** LOCATION:  ExecEvalConstraintCheck, execExprInterp.c:3639
128  *** Time: 0.634 ms
129  *** 
130  */
131 
132 -- not expected, same example with REURN QUERY EXECUTE:
133 CREATE OR REPLACE FUNCTION tf3() RETURNS SETOF t
134 AS $$
135 DECLARE
136 BEGIN
137   RETURN QUERY EXECUTE
138 format(
139 $sql$
140 select 4::double precision,5::double precision
141 $sql$);
142 END; $$ LANGUAGE plpgsql SECURITY DEFINER;
143 
144 /*
145  *** *x=# SELECT tf3();
146  *** +---+
147  *** |  tf3  |
148  *** +---+
149  *** | (4,5) |
150  *** +---+
151  *** (1 row)
152  *** 
153  *** Time: 0.924 ms
154  *** *x=# SELECT tf3()::t;
155  *** +---+
156  *** |  tf3  |
157  *** +---+
158  *** | (4,5) |
159  *** +---+
160  *** (1 row)
161  *** 
162  *** Time: 0.538 ms
163  *** *x=# insert into test ((select tf3()));
164  *** INSERT 0 1
165  *** Time: 0.840 ms
166  *** *x=# SELECT v::t from test;
167  *** +---+
168  *** |   v   |
169  *** +---+
170  *** | (1,1) |
171  *** | (3,2) |
172  *** | (4,5) |
173  *** | (4,5) |
174  *** +---+
175  *** (4 rows)
176  */
--8<---cut here---end--->8---

Is that on purpose?
Have I missed something in the documentation?

-- 
Christian Barthel

Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?

[Rihad]

On 8/21/23 20:50, Adrian Klaver wrote:

On 8/21/23 09:31, Rihad wrote:

On 8/21/23 20:17, Adrian Klaver wrote:

On 8/21/23 09:09, Rihad wrote:

On 8/21/23 20:00, Adrian Klaver wrote:






Sorry, they are all as per default, commented out in the config.

There are no long running queries, otherwise they wouldn't be 
vacuumed/analyzed in due time after running first manual analyze, 
which updates n_live_tup to match reltuples.




My only remaining suggestion is to closely monitor the Postgres log 
and see if provides a clue.


I'm awfully sorry, I read the autovacuum manual carefully, it isn't 
n_live_tup, but reltuples that is taken into account during the calculation.



vacuum threshold = vacuum base threshold + vacuum scale factor * number of 
tuples

where the vacuum base threshold is autovacuum_vacuum_threshold 
, 
the vacuum scale factor is autovacuum_vacuum_scale_factor 
, 
*and the number of tuples is **|pg_class|**.**|reltuples|**.*



Your first suggestion was to RTFM.

Re: [EXTERNAL] Oracle FDW version

[Ian Lawrence Barwick]

2023年8月23日(水) 15:10 Jethro Elmer Sanidad :

> Hello,
>
> Can you confirm in this email that our current version of PostgreSQL
> (9.2.24) is not compatible with any of oracle_fdw versions released? And
> you are recommending an upgrade? Thanks!
>

OK, here's the summary:

- oracle_fdw builds against *community* PostgreSQL 9.3 and later (I just
verfied that)
- it is likely not compatible with PostgreSQL forks such as Greenplum
- if you are running a version of PostgreSQL based on 9.2.x or 9.4.x it's
hopelessly
  out-of-date and you should definitely upgrade anyway (but if it's a fork,
that still won't
  that mean oracle_fdw will compile against it)

HTH

Ian Barwick



>
> On Wed, Aug 23, 2023 at 2:04 PM Ian Lawrence Barwick 
> wrote:
>
>> 2023年8月23日(水) 12:47 Christophe Pettus :
>> >
>> >
>> >
>> > > On Aug 22, 2023, at 19:57, Jethro Elmer Sanidad <
>> jethroelmersani...@gardenia.com.ph> wrote:
>> > >
>> > > Hello,
>> > >
>> > > I tried both the 1.5.0 and 2.0.0. Both returned error during 'make'
>> command. Please see below:
>> >
>> > The API between PostgreSQL and foreign data wrappers has changed
>> significantly since 9.4.  As Tom mentioned, you need to upgrade your server
>> to something in-support.
>>
>> FWIW oracle_fdw should actually build against PostgreSQL as far back as
>> 9.3
>> (though finding packages would be a fruitless endeavour); the
>> documentation
>> does explicitly mention that it will likely not build against forks which
>> are
>> not compatible with the community code [1].
>>
>> [1] https://github.com/laurenz/oracle_fdw#5-installation-requirements
>>
>> Regards
>>
>> Ian Barwick
>>
>
>
> --
>
> [image: image.png] 
>
> *Jethro Elmer T. Sanidad*
> Management Information Systems
> O: +63 2 8889 8890 loc. 1354 | M: +63 9686809253
> Gardenia Bakeries Philippines Incorporated | Laguna International
> Industrial Park (LIIP) | Binan 4024 | Laguna
>
> *CONFIDENTIALITY NOTICE:* This email is confidential and subject to legal
> rights of Gardenia Bakeries (Philippines), Inc. (“GBPI”). If you received
> this email by error, you must not use or disclose any information in it and
> immediately notify the sender by return email and permanently delete this
> email (and all attachments) without any use of its contents. To the extent
> legally permitted, GBPI has no liability of any kind arising out of or in
> connection with any virus transmitted by this email, attachments, and/or
> any errors or omissions in content including transmissions through
> unauthorised use or tampering of email system and/or the integrity of the
> email being compromised. Any personal statements or opinions in this
> communication are those of the individual sender and do not reflect the
> views of GBPI. GBPI will never consent to or authorize the publication of
> defamatory statements or infringement of intellectual property. Only
> individuals authorized by GBPI’s Board of Directors may sign and/or accept
> proposals, contracts, or agreements as well as waive any legal right of
> GBPI. Any personal information in this email must be handled in accordance
> with the Data Privacy Act of 2012 of the Philippines and its implementing
> rules and regulations.
>

Re: PostgreSQL DB cluster migration from centos7/RHEL7/OEL7 to RHEL8

[Avinash Vallarapu]

On Wed, Aug 23, 2023 at 6:23 AM Tushar Takate 
wrote:

> Hi Team,
>
> We want to know if there are any open/known issues that we may face
> post-migration of the DB cluster from centos7/RHEL7/OEL7 to RHEL8.
>
> Depends on the method you choose to perform this migration ?
If you are using pg_dump and pg_restore for the initial data copy,
followed by logical replication, then, it should be technically okay.

> We came across one issue regarding collation(
> https://wiki.postgresql.org/wiki/Locale_data_changes), the solution for
> which is to reindex or use logical replication. In a similar way, we want
> to understand if there are any other known issues that the community should
> be aware of.
>
> -
> Thanks & Regards,
> Tushar K Takate.
>
>

-- 
Regards,
Avinash Vallarapu

PostgreSQL DB cluster migration from centos7/RHEL7/OEL7 to RHEL8

[Tushar Takate]

Hi Team,

We want to know if there are any open/known issues that we may face
post-migration of the DB cluster from centos7/RHEL7/OEL7 to RHEL8.

We came across one issue regarding collation(
https://wiki.postgresql.org/wiki/Locale_data_changes), the solution for
which is to reindex or use logical replication. In a similar way, we want
to understand if there are any other known issues that the community should
be aware of.

-
Thanks & Regards,
Tushar K Takate.

Add primary key to existing, partitioned table

[Arne Henrik Segtnan]

Hi,

We have an application currently running on PostgreSQL 12, partitioned as
shown below. Database size is approx. 1.4TB. We are now in the process of
upgrading the application, using PostgreSQL 15. In the new version, there
is an optional step to implement 'primary key' on history table.
In the documentation, there is no information/procedure describing how to
do this on a partitioned PostgreSQL database.

There are two questions related to this:
1: Is partitioning considered best options to deal with cleanup of large
chunks of historical data in PostgreSQL 15?

2: Based on findings so far, it seems like the solution is to:
- create script to create temporary tables for main table and partitions
(identical to original tables)
- add primary key and all other required dependencies to the temporary
tables
- copy the data back to main table (not each partition)

Does this seem correct? Any obvious pitfalls?  Anyone else who has
experienced similar cases?


SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 40;
relname | relpages

+--

events events_1
events
events_pkey
event_recovery_2
housekeeper
event_recovery_pkey
event_recovery_1
items
sessions_pkey
event_recovery
sessions_1
housekeeper_pkey
trends_uint_p2023_02
history_uint_p2023_08_17
trends_uint_p2023_06
trends_uint_p2023_01
trends_uint_p2023_07
history_uint_p2023_08_18
history_uint_p2023_08_19
alerts
history_uint_p2023_08_20
history_uint_p2023_08_16
history_uint_p2023_08_17_1
history_uint_p2023_08_15
history_uint_p2023_08_19_1
history_uint_p2023_08_18_1
history_uint_p2023_08_20_1
history_uint_p2023_08_16_1
trends_uint_p2023_08
trends_uint_p2023_05
trends_uint_p2023_02_1
history_uint_p2023_08_10
history_uint_p2023_08_15_1
history_uint_p2023_08_08
trends_uint_p2023_01_1
history_uint_p2023_08_09
trends_uint_p2023_06_1
history_uint_p2023_08_12
history_uint_p2023_08_13
(40 rows)

Best regards,
Arne Segtnan

Re: [EXTERNAL] Oracle FDW version

[Justin Clift]

On 2023-08-23 16:10, Jethro Elmer Sanidad wrote:

Hello,

Can you confirm in this email that our current version of PostgreSQL
(9.2.24) is not compatible with any of oracle_fdw versions released? 
And

you are recommending an upgrade? Thanks!


It seems more like you're not actually using PostgreSQL, but are instead
using a special proprietary spin-off called Greenplum.

If that's the case, then you'll need to have a chat with the Greenplum
support people.  They'll know their product better than we do. :)

Regards and best wishes,

Justin Clift

Re: [EXTERNAL] Oracle FDW version

[Jethro Elmer Sanidad]

Hello,

Can you confirm in this email that our current version of PostgreSQL
(9.2.24) is not compatible with any of oracle_fdw versions released? And
you are recommending an upgrade? Thanks!

On Wed, Aug 23, 2023 at 2:04 PM Ian Lawrence Barwick 
wrote:

> 2023年8月23日(水) 12:47 Christophe Pettus :
> >
> >
> >
> > > On Aug 22, 2023, at 19:57, Jethro Elmer Sanidad <
> jethroelmersani...@gardenia.com.ph> wrote:
> > >
> > > Hello,
> > >
> > > I tried both the 1.5.0 and 2.0.0. Both returned error during 'make'
> command. Please see below:
> >
> > The API between PostgreSQL and foreign data wrappers has changed
> significantly since 9.4.  As Tom mentioned, you need to upgrade your server
> to something in-support.
>
> FWIW oracle_fdw should actually build against PostgreSQL as far back as 9.3
> (though finding packages would be a fruitless endeavour); the documentation
> does explicitly mention that it will likely not build against forks which
> are
> not compatible with the community code [1].
>
> [1] https://github.com/laurenz/oracle_fdw#5-installation-requirements
>
> Regards
>
> Ian Barwick
>


-- 

[image: image.png] 

*Jethro Elmer T. Sanidad*
Management Information Systems
O: +63 2 8889 8890 loc. 1354 | M: +63 9686809253
Gardenia Bakeries Philippines Incorporated | Laguna International
Industrial Park (LIIP) | Binan 4024 | Laguna

-- 






















*CONFIDENTIALITY NOTICE:* This email is
confidential 
and subject to legal rights of Gardenia Bakeries (Philippines),
Inc. 
(“GBPI”). If you received this email by error, you must not use or
disclose 
any information in it and immediately notify the sender by return
email and 
permanently delete this email (and all attachments) without any use
of its 
contents. To the extent legally permitted, GBPI has no liability of any
kind arising out of or in connection with any virus transmitted by this 
email,
attachments, and/or any errors or omissions in content including 
transmissions
through unauthorised use or tampering of email system and/or 
the integrity of the email
being compromised. Any personal statements or
opinions in this communication are those of the individual sender and do 
not
reflect the views of GBPI. GBPI will never consent to
or authorize the 
publication of defamatory statements or infringement of
intellectual 
property. Only individuals authorized by GBPI’s Board of Directors
may sign 
and/or accept proposals, contracts, or agreements as well as waive any
legal right of GBPI. Any personal information in this email must be handled 
in
accordance with the Data Privacy Act of 2012 of the Philippines and its
implementing rules and regulations.

Re: [EXTERNAL] Oracle FDW version

[Ian Lawrence Barwick]

2023年8月23日(水) 12:47 Christophe Pettus :
>
>
>
> > On Aug 22, 2023, at 19:57, Jethro Elmer Sanidad 
> >  wrote:
> >
> > Hello,
> >
> > I tried both the 1.5.0 and 2.0.0. Both returned error during 'make' 
> > command. Please see below:
>
> The API between PostgreSQL and foreign data wrappers has changed 
> significantly since 9.4.  As Tom mentioned, you need to upgrade your server 
> to something in-support.

FWIW oracle_fdw should actually build against PostgreSQL as far back as 9.3
(though finding packages would be a fruitless endeavour); the documentation
does explicitly mention that it will likely not build against forks which are
not compatible with the community code [1].

[1] https://github.com/laurenz/oracle_fdw#5-installation-requirements

Regards

Ian Barwick