Re: Need help understanding has_function_privilege

Hi, and thanks (both of you!) Shouldn't the create role my_user NOINHERIT; avoid this? And since not, why? :-) On Thu, 20 Mar 2025 at 15:07, David G. Johnston wrote: > On Wednesday, March 19, 2025, Cosimo Simeone > wrote: >> >> >> true? >> Well... Ok, "whatever"... I revoke it: >> =# revoke

Re: Need help understanding has_function_privilege

On Friday, March 21, 2025, Cosimo Simeone wrote: > Hi, and thanks (both of you!) > Shouldn't the > create role my_user NOINHERIT; > avoid this? And since not, why? :-) > > We might need to improve documentation surrounding the public pseudo-role a bit. Since it’s not a true group role I suspect

Re: Need help understanding has_function_privilege

On Wednesday, March 19, 2025, Cosimo Simeone wrote: > > > true? > Well... Ok, "whatever"... I revoke it: > =# revoke execute on function my_schema.my_func(text) from my_user; > REVOKE > Roles can inherit privileges. my_user is inheriting its execute privilege from PUBLIC. You have to revoke a g

Re: Need help understanding has_function_privilege

On 3/19/25 04:24, Cosimo Simeone wrote: Hi! I'd need help understanding what i am doing wrong... Where am I: =# \c psql (15.8 (Homebrew), server 14.15 (Debian 14.15-1.pgdg120+1)) You are now connected to database "postgres" as user "postgres". Init: =# create role

Need help understanding has_function_privilege

Hi! I'd need help understanding what i am doing wrong... Where am I: =# \c psql (15.8 (Homebrew), server 14.15 (Debian 14.15-1.pgdg120+1)) You are now connected to database "postgres" as user "postgres". Init: =# create role my_user; =# create schema my_s

Re: Need help in logical replication

Thank you for the response Justin found and fixed the error On Mon, 13 Jan 2025, 10:15 pm Justin, wrote: > Hi Divyansh, > > Go to the subscriber and look for errors in the PostgreSQL logs. > > When creating a subscription the default action is to sync the tables. Is > the subscriber table empty?

Re: Need help in logical replication

Hi Divyansh, Go to the subscriber and look for errors in the PostgreSQL logs. When creating a subscription the default action is to sync the tables. Is the subscriber table empty?? Thank you, On Mon, Jan 13, 2025 at 7:30 AM Divyansh Gupta JNsThMAudy < ag1567...@gmail.com> wrote: > Hii PostgreS

Re: Need help in logical replication

> On Jan 13, 2025, at 5:30 AM, Divyansh Gupta JNsThMAudy > wrote: > >  > Hii PostgreSQL Community, > > I am setting up logical replication between two clusters in the same subnet > group. I’ve created a publication on the primary and a subscription on the > secondary, and the replication

Need help in logical replication

Hii PostgreSQL Community, I am setting up logical replication between two clusters in the same subnet group. I’ve created a publication on the primary and a subscription on the secondary, and the replication slot has been created. However, the slot remains inactive, and changes aren’t being applie

Re: Need help in database design

You might also look into using a bitmap, for some or all of those fields. It depends on how many distinct values each can have, of course, and also on how exactly they are accessed, but bitmaps can save you quite a bit of space. Cheers, Greg

Re: Need help in database design

On Mon, Dec 23, 2024 at 11:26 AM Divyansh Gupta JNsThMAudy < ag1567...@gmail.com> wrote: > > gdid int8 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 > MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL, > > userid int8 NOT NULL, > > useremail varchar(600) NOT NULL, > > t

Re: Need help in database design

Just out of curiosity, not suggestion this is the solution. Why save json in PostgreSQL and not in a DB specialised on JSON like MongoDB? Divyansh Gupta JNsThMAudy escribió: Thank you everyone for giving your valuable responses, I am glad that everyone understands my concern. I got some go

Re: Need help in database design

The current application does not have support for that, If I go for that, it will require a lot of effort from the team which Is not possible right now. On Tue, 24 Dec 2024, 12:47 am , wrote: > Just out of curiosity, not suggestion this is the solution. Why save json > in PostgreSQL and not in a

Re: Need help in database design

Thank you everyone for giving your valuable responses, I am glad that everyone understands my concern. I got some good ideas about the database design that I am following after going through some stress testing I will implement the same. Thank you so much Everyone On Tue, 24 Dec 2024, 12:09 am Ro

Re: Need help in database design

Are these columns really unique for all 20M rows that a userid can have in the table? I'm dubious. Split a LOT of those columns out into a separate table named "user" with PK userid. It'll save a huge amount of disk space, and speed up queries by not having to fetch it all every time. useremail

Re: Need help in database design

I would like to make a suggestion, if I may. Granted, I do not understand the underlying task at hand, but: A table with multiple columns of the same type smacks of designs that harken back to the days of mainframes. (STOP THAT!) The data described is a non-normalized array of integers that is

Re: Need help in database design

Currently I haven't created those columns , I have created addons_json column which is a JSONB column yet in a discussion weather I should create or consider only one JSONB column. On Tue, 24 Dec 2024, 12:00 am Divyansh Gupta JNsThMAudy, < ag1567...@gmail.com> wrote: > Range partition can help wh

Re: Need help in database design

Range partition can help when you applies filter for a specific range but in my case I need to apply filter on userid always, however I have date columns but there is less variation in timestamp which I have that's why didn't go for range partition. On Mon, 23 Dec 2024, 11:57 pm Ron Johnson, wrot

Re: Need help in database design

Where are the 50 "t* columns? On Mon, Dec 23, 2024 at 1:26 PM Divyansh Gupta JNsThMAudy < ag1567...@gmail.com> wrote: > Ron here is the entire table schema FYI, userid is the mandate column on > which filter is always applies: > > CREATE TABLE dbo.googledocs_tbl ( > > gdid int8 GENERATED BY DEFAU

Re: Need help in database design

1. I bet you'd get better performance using RANGE partitioning. 2. Twenty million rows per userid is a *LOT*. No subdivisions (like date range)? On Mon, Dec 23, 2024 at 1:23 PM Divyansh Gupta JNsThMAudy < ag1567...@gmail.com> wrote: > Adrian, Please check this out; > > PARTITION BY HASH (userid)

Re: Need help in database design

Ron here is the entire table schema FYI, userid is the mandate column on which filter is always applies: CREATE TABLE dbo.googledocs_tbl ( gdid int8 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL, userid int8 NOT NULL,

Re: Need help in database design

Adrian, Please check this out; PARTITION BY HASH (userid); CREATE TABLE dbo.googledocs_tbl_clone_part_0 PARTITION OF dbo.googledocs_tbl_clone FOR VALUES WITH (modulus 84, remainder 0); ... CREATE TABLE dbo.googledocs_tbl_clone_part_83 PARTITION OF dbo. googledocs_tbl_clone FOR VALUES WITH (modulus

Re: Need help in database design

Adrian, the partition is on userid using hash partition with 84 partitions Ron, there could be more than 20 Million records possible for a single userid in that case if I create index on userid only not on other column the query is taking more than 30 seconds to return the results. On Mon, 23 Dec

Re: Need help in database design

If your queries all reference userid, then you only need indices on gdid and userid. On Mon, Dec 23, 2024 at 12:49 PM Divyansh Gupta JNsThMAudy < ag1567...@gmail.com> wrote: > I have one confusion with this design if I opt to create 50 columns I need > to create 50 index which will work with user

Re: Need help in database design

On 12/23/24 09:49, Divyansh Gupta JNsThMAudy wrote: I have one confusion with this design if I opt to create 50 columns I need to create 50 index which will work with userid index in Bitmap on the other hand if I create a JSONB column I need to create a single index ? There still is the part

Re: Need help in database design

I have one confusion with this design if I opt to create 50 columns I need to create 50 index which will work with userid index in Bitmap on the other hand if I create a JSONB column I need to create a single index ? On Mon, 23 Dec 2024, 11:10 pm Ron Johnson, wrote: > Given what you just wrote,

Re: Need help in database design

Given what you just wrote, I'd stick with 50 separate t* columns. Simplifies queries, simplifies updates, and eliminates JSONB conversions. On Mon, Dec 23, 2024 at 12:29 PM Divyansh Gupta JNsThMAudy < ag1567...@gmail.com> wrote: > Values can be updated based on customer actions > > All rows won't

Re: Need help in database design

Empty JSONB by mistake, I wrote array sorry about that On Mon, 23 Dec 2024, 10:59 pm Divyansh Gupta JNsThMAudy, < ag1567...@gmail.com> wrote: > Values can be updated based on customer actions > > All rows won't have all 50 key value pairs always if I make those keys > into columns the rows might

Re: Need help in database design

Values can be updated based on customer actions All rows won't have all 50 key value pairs always if I make those keys into columns the rows might have null value on the other hand if it is JSONB then the key value pair will not be there Yes in UI customers can search for the key value pairs Dur

Re: Need help in database design

Let's make it more understandable, here is the table schema with 50 columns in it CREATE TABLE dbo.googledocs_tbl ( gdid int8 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL, userid int8 NOT NULL, t1 int4 NULL, t2 int4 NUL

Re: Need help in database design

As I expected. Next: 1) Will all 50 of those k/v pairs be populated when you insert the record? 2) Will there be updates? 3) In each row, will some of those 50 pairs stay empty? 4) When querying a row, will the app care about all 50 k/v pairs at the same time, or just one -- or even some -- of the

Re: Need help in database design

On 12/23/24 09:07, Divyansh Gupta JNsThMAudy wrote: Each row have a PK (gdid) that will uniquely refrence 50 k/v pair set Are the 50 key/value pairs there to just define a PK or are they going to be used in some other combination in queries? -- Adrian Klaver adrian.kla...@aklaver.com

Re: Need help in database design

On Mon, Dec 23, 2024, 10:01 Divyansh Gupta JNsThMAudy wrote: > > So here my question is considering one JSONB column is perfect or > considering 50 columns will be more optimised. > The relational database engine is designed around the column-based approach. Especially if the columns are general

Re: Need help in database design

Each row have a PK (gdid) that will uniquely refrence 50 k/v pair set On Mon, 23 Dec 2024, 10:35 pm Ron Johnson, wrote: > How do you uniquely reference each set of 50 k/v pairs? > > On Mon, Dec 23, 2024 at 12:01 PM Divyansh Gupta JNsThMAudy < > ag1567...@gmail.com> wrote: > >> Sure , for example

Re: Need help in database design

On 12/23/24 09:01, Divyansh Gupta JNsThMAudy wrote: Sure , for example, I have 50 key with name as t1 , t2 ,t3 ...t50 Now each key could have values from 0 to 3 So let suppose we have JSONB like that for row r1 { t1: 1 t2: 2 t3 : 3 } As if I convert it into columns so r1 will have t

Re: Need help in database design

How do you uniquely reference each set of 50 k/v pairs? On Mon, Dec 23, 2024 at 12:01 PM Divyansh Gupta JNsThMAudy < ag1567...@gmail.com> wrote: > Sure , for example, I have 50 key with name as t1 , t2 ,t3 ...t50 > > Now each key could have values from 0 to 3 > > So let suppose we have JSONB

Re: Need help in database design

Sure , for example, I have 50 key with name as t1 , t2 ,t3 ...t50 Now each key could have values from 0 to 3 So let suppose we have JSONB like that for row r1 { t1: 1 t2: 2 t3 : 3 } As if I convert it into columns so r1 will have t1 column will contain 1 t2 column will contain 2 ...

Re: Need help in database design

On 12/23/24 08:55, David G. Johnston wrote: On Mon, Dec 23, 2024, 09:54 Adrian Klaver > wrote: On 12/23/24 08:46, Divyansh Gupta JNsThMAudy wrote: > Also as you ask how 50 pairs turns into 50 column so each column will be > a key and the value

Re: Need help in database design

On Mon, Dec 23, 2024, 09:54 Adrian Klaver wrote: > On 12/23/24 08:46, Divyansh Gupta JNsThMAudy wrote: > > Also as you ask how 50 pairs turns into 50 column so each column will be > > a key and the value of that key will store inside their respective > > column for each row > > My problem with un

Re: Need help in database design

On 12/23/24 08:46, Divyansh Gupta JNsThMAudy wrote: Also as you ask how 50 pairs turns into 50 column so each column will be a key and the value of that key will store inside their respective column for each row My problem with understanding this is 50 pairs = 100 values, I don't understand h

Re: Need help in database design

Also as you ask how 50 pairs turns into 50 column so each column will be a key and the value of that key will store inside their respective column for each row On Mon, 23 Dec 2024, 10:14 pm Divyansh Gupta JNsThMAudy, < ag1567...@gmail.com> wrote: > As per the discussion with other team members th

Re: Need help in database design

As per the discussion with other team members they suggested if we store 50 values for keys in an individual column that will provide better performance as the data type is native (INT2) on the other hand if we store all the key value pair in a single JSONB column the performance will degrade even

Re: Need help in database design

On 12/23/24 07:53, Divyansh Gupta JNsThMAudy wrote: Hii Community, I need to provide a support for some functionality for my application for that I need to store 50 key value pair set, so I am in a dilemma, weather I create 50 new columns of int2 data type each column will This is unclear,

Need help in database design

Hii Community, I need to provide a support for some functionality for my application for that I need to store 50 key value pair set, so I am in a dilemma, weather I create 50 new columns of int2 data type each column will contain value of a specific key or should I go with JSONB data type with 50

Need help to review a serializability implementation for MySQL Cluster

Hey guys, I've developed a serializability implementation for MySQL Cluster(NDB Cluster) and you are invited to peer-review it for me. I believe it is the fifth one in commercial database systems after: MySQL InnoDB's 2PL, PostgreSQL's Serializable Snapshot Isolation, Google's Spanner's isolatio

Re: Need help migrating MSSQL2008R2 tables into PGSQL & make PGSQL mimic MSSQL behaviour.

On Wed, 2024-05-08 at 00:20 +, bugge...@riseup.net wrote: > I heard PGSQL can act like MSSQL behaviour so I'm trying to migrating > tables to try it myself, but I really have hard time to migrating them. I think you have heard about Babelfish (https://babelfishpg.org/). It is a modified versio

Re-2: Need help migrating MSSQL2008R2 tables into PGSQL & make PGSQL mimic MSSQL behaviour.

You can use this tool: https://www.sqlmanager.net/products/postgresql/datapump It allows easy migration..  Original Message processed by david® Re: Need help migrating MSSQL2008R2 tables into PGSQL & make PGSQL mimic MSSQL behaviour. 8. Mai 2024, 03:20 Uhr Von Greenhorn An b

Re: Need help migrating MSSQL2008R2 tables into PGSQL & make PGSQL mimic MSSQL behaviour.

How many tables do you have, and what are their sizes? I'm asking because if you're considering migrating to PostgreSQL, starting from scratch might be cleaner. This approach will also help you gain a deeper understanding of how data types are handled in PostgreSQL compared to MSSQL. On Wed, 8 May

Need help migrating MSSQL2008R2 tables into PGSQL & make PGSQL mimic MSSQL behaviour.

I heard PGSQL can act like MSSQL behaviour so I'm trying to migrating tables to try it myself, but I really have hard time to migrating them. I already looked https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding but nothing is useful. For example, "Extensi

Re: Need help to make space on my database

On 4/29/24 08:51, Cocam' server wrote: > Did the above work for each table? Yes, except for the biggest table > Have you done something like?: > > select relname, n_dead_tup from pg_stat_all_tables where relname = > ''; I hadn't thought of that, but it seems that some tables have dead tupl

Re: Need help to make space on my database

On Mon, 2024-04-29 at 15:45 +0200, Cocam' server wrote: > I need help to make space on my database. I have tables that are several GB > in size. > I used to use the VACUUM FULL VERBOSE command; but now, this command is too > greedy in > free space to be used and I'm

Re: Need help to make space on my database

gt; > > > > Le lun. 29 avr. 2024 à 16:37, Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> a écrit : > > > > On 4/29/24 07:33, Cocam' server wrote: > > > > Please reply to list also > > Ccing list > > > > >

Re: Need help to make space on my database

om>> a écrit : On 4/29/24 07:33, Cocam' server wrote: Please reply to list also Ccing list > No, the aim is also to reallocate free space to the system for the other > tasks it performs.(That's why I said I'd like it returned to the OS)

Re: Need help to make space on my database

On 4/29/24 07:33, Cocam' server wrote: Please reply to list also Ccing list No, the aim is also to reallocate free space to the system for the other tasks it performs.(That's why I said I'd like it returned to the OS) You led with: "I need help to make space on my

Re: Need help to make space on my database

Please run VACUUM with ANALYZE option that will also update the DB Stats. Regards Kashif Zeeshan Bitnine Global On Mon, Apr 29, 2024 at 7:19 PM Adrian Klaver wrote: > On 4/29/24 06:45, Cocam' server wrote: > > Hello. > > > > I need help to make space on my databa

Re: Need help to make space on my database

On 4/29/24 06:45, Cocam' server wrote: Hello. I need help to make space on my database. I have tables that are several GB in size. I used to use the VACUUM FULL VERBOSE command; but now, this command is too greedy in free space to be used and I'm looking for a way to make free sp

Need help to make space on my database

Hello. I need help to make space on my database. I have tables that are several GB in size. I used to use the VACUUM FULL VERBOSE command; but now, this command is too greedy in free space to be used and I'm looking for a way to make free space (given back to the OS) Thanks in advan

Re: Need help with tuning a query

On 2/27/24 07:50, Siraj G wrote: Hello All! Can you please assist me with highlighting the bad area in the execution plan attached. I am kind of new to PgSQL and trying to get some help with troubleshooting a query. Little background. DB was migrated from Oracle to AlloyDB. The SQL that u

Re: Need help

On 12/29/23 21:56, ankit.si...@nest-is2.com wrote: It's giving one more error please have a look. All that is telling you is that the underlying error you first reported "pg restore unsupported 1.15" caused one of the Zulip commands to fail. The issue still remains that you have a mismatch

Re: Need help

server I have taken backup*.** 5) Do you have more then one version of Postgres installed? *14.10 & 16.1 in server which I have taken backup* ** *From:*Adrian Klaver *Sent:* 30 December 2023 00:45 *To:* ankit.si...@nest-is2.com; pgsql-general@lists.postgresql.org *Subject:* Re: Need help

Re: Need help

writes: > In Ubuntu 22.04LTS when we try to restore the database it's giving the error > - pg restore unsupported 1.15 You need a version of pg_restore that's at least as new as the pg_dump that made the archive file. File version 1.15 I believe requires PG 16. regards,

Re: Need help

On 12/29/23 6:02 AM, ankit.si...@nest-is2.com wrote: In Ubuntu 22.04LTS when we try to restore the database it's giving the error – pg restore unsupported 1.15 Needs  a lot more information: 1) Postgres version being restored to. 2) The complete pg_restore command. 3) The complete error

Need help

In Ubuntu 22.04LTS when we try to restore the database it's giving the error - pg restore unsupported 1.15

Re: [EXT] YNT: Need help tuning a query

Wow!! This is what I call cryptic!! On 9/29/23 2:46 a.m., Vladimir Sitnikov wrote: Oh, I misplaced the added where conditions. It should have been as follows, however, the overall idea is the same --- orignial.sql +++ tuned_v2.sql @@ -83,6 +83,7 @@                                 AND (judg1.jrt

Re: [EXT] YNT: Need help tuning a query

Oh, I misplaced the added where conditions. It should have been as follows, however, the overall idea is the same --- orignial.sql +++ tuned_v2.sql @@ -83,6 +83,7 @@ AND (judg1.jrt_opt_out_flag <> 'Y' OR judg1.jrt_opt_out_flag IS NULL) ) sub0

Re: [EXT] YNT: Need help tuning a query

Steve, It looks like PostgreSQL was not able to push the join condition into group by subquery. The problematic bits in the PostgreSQL plan are -> Nested Loop Left Join (cost=3423317.32..4522882.01 rows=4 width=261) (actual time=30839.132..35117.682 rows=39 loops=1) Joi

Need help setting up Windows authentication SSPI user mapping

Hi, I would like to get helo with setting Windows authentication in a testing scenario. I use Windows 11. Windows connects to AAD and thus I use a windows account. The domain name is na, user name is hai. I also setup a local user account hailocal. My machine name is haisurface I have Postgre

Re: Need Help On Upgrade

Thank you very much @Adrian Klaver . On Tue, Jul 11, 2023 at 7:32 PM Adrian Klaver wrote: > On 7/11/23 14:45, Johnathan Tiamoh wrote: > > Hello Adrian > > > > 1) PgBouncer version. > > > > pgbouncer.1.7.2 > > PgBouncer most recent version is from here: > > http://www.pgbouncer.org/changelog.htm

Re: Need Help On Upgrade

This also sounds like a fairly advanced setup and a corporate environment. Postgresql offers paid support and you probably want that. Thanks, Ben On Tue, Jul 11, 2023, 4:33 PM Adrian Klaver wrote: > On 7/11/23 14:45, Johnathan Tiamoh wrote: > > Hello Adrian > > > > 1) PgBouncer version. > > > >

Re: Need Help On Upgrade

On 7/11/23 14:45, Johnathan Tiamoh wrote: Hello Adrian 1) PgBouncer version. pgbouncer.1.7.2 PgBouncer most recent version is from here: http://www.pgbouncer.org/changelog.html PgBouncer 1.19.x 2023-05-31 - PgBouncer 1.19.1 - “Sunny Spring” And since 1.7.x there have been these related

Re: Need Help On Upgrade

Hello Adrian 1) PgBouncer version. pgbouncer.1.7.2 2) Did you change the settings when going from 9.5 to 14.8? No. I did not do any configuration changes on the bouncers 3) Does the PgBouncer log show anything relevant? No. It does not show anything 4) Does '...through the pgbouncers...' me

Re: Need Help On Upgrade

On 7/11/23 08:53, Johnathan Tiamoh wrote: I can connect. All applications and other users that connect from to the databases through the  pgbouncers can't connect. That would have been a good thing to have mentioned in your first post. I don't use PgBouncer so I am not going to be of much us

Re: Need Help On Upgrade

I can connect. All applications and other users that connect from to the databases through the pgbouncers can't connect. On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver wrote: > On 7/10/23 20:45, Laurenz Albe wrote: > > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote: > >> Below is th

Re: Need Help On Upgrade

On 7/10/23 20:45, Laurenz Albe wrote: On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote: Below is the full error message. 2023-06-27 05:01:27.385 CDT| XXX (60930)| APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| authentication| PGE-28P01: FATAL:  passwor

Re: Need Help On Upgrade

Hello KK, In the beginning, it was a little challenging, but as the process went on it became easy. No, it was not a multistage upgrade. It was possible to upgrade straight from 9.5 to 14. We did not change anything on the schema. For data type, we alter all tables that were created with OIDs to

Re: Need Help On Upgrade

On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote: > Below is the full error message. > > 2023-06-27 05:01:27.385 CDT| XXX (60930)| > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| authentication| > PGE-28P01: FATAL:  password authentication failed for use

Re: Need Help On Upgrade

Sorry Adrian, Below is the full error message. 2023-06-27 05:01:27.385 CDT| XXX (60930)| APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| authentication| PGE-28P01: FATAL: password authentication failed for user "grafana" 2023-06-27 05:01:27.385 CDT| XXX

Re: Need Help On Upgrade

On 7/10/23 13:20, Johnathan Tiamoh wrote: Ok Adrian, Postgres 9.5       postgresql.conf          password_encryption  = ?  md5  > wish is the default for 9.5       pg_hba.conf    -> md5           Provide the relevant lines Postgres 14.8       postgresql.conf        

Re: Need Help On Upgrade

> Ok Adrian, > > Postgres 9.5 > > postgresql.conf > > password_encryption = ? md5 > wish is the default for 9.5 > > pg_hba.conf-> md5 > > Provide the relevant lines > > Postgres 14.8 > > postgresql.conf > > password_encryption = ?

Re: Need Help On Upgrade

Ok Adrian, Postgres 9.5 postgresql.conf password_encryption = ? md5 > wish is the default for 9.5 pg_hba.conf-> md5 Provide the relevant lines Postgres 14.8 postgresql.conf password_encryption = ? scram-sha-256 > wish is t

Re: Need Help On Upgrade

On 7/10/23 12:55, Johnathan Tiamoh wrote: Please reply to list also. Ccing list Ok Adrian, Postgres 9.5     postgresql.conf        password_encryption  = ?  md5  > wish is the default for 9.5     pg_hba.conf    -> md5         Provide the relevant lines Postgres 14.

Re: Need Help On Upgrade

On 7/10/23 11:15, Johnathan Tiamoh wrote: Hello, I upgraded from postgresql 9.5 to 14.8. After the upgrade users were not able to connect due to password hash. The password encryption was on scram-sha-256. The password hash was on md5. in the pg_hba.conf file the authentication method was md

Need Help On Upgrade

Hello, I upgraded from postgresql 9.5 to 14.8. After the upgrade users were not able to connect due to password hash. The password encryption was on scram-sha-256. The password hash was on md5. in the pg_hba.conf file the authentication method was md5. I do not know why users could not connect be

Re: Need help

On 7/5/23 21:52, aarthi rajagopal wrote: Hi,    I have doubt about how to enable pg_cron in PgAdmin4 version 7, kindly guide me how to do it.. pg_cron: https://github.com/citusdata/pg_cron is an extension that has nothing to do with pgAdmin4. Follow the instructions at the link above to in

Need help

Hi, I have doubt about how to enable pg_cron in PgAdmin4 version 7, kindly guide me how to do it.. Thanks& Regards Aarthi Rajagopal

Re: Need help debugging slow logical replication

OK: so I think I've got some new insight by using test_decoding to peek into the changes being replicate, here is what i think is happening: - there is a background job generating a Huge transaction (10K changes) one after another that basically does DELETE on 1 table, INSERT on 1 table and UPDAT

Re: Need help debugging slow logical replication

ccing the mailist again in case someone else would have a idea how to debug: Here is what I see in the pg_locks on subscribe at all time: As you can see, it mostly concern following 3 tables, accounts, ledger, pending_ledger_fees, which I have tried analyze, vacuum them etc, none of them helped m

Re: Need help debugging slow logical replication

Hi Justin, thanks for the response! > REPLICA IDENTITY DEFAULT will only use primary keys, if the publisher > includes those tables, the subscriber when replaying the WAL will stop > throwing an error not knowing how to replay the UPDATE/DELETE. But I don't see any errors being thrown out

Re: Need help debugging slow logical replication

On Tue, Feb 7, 2023 at 8:07 PM sunyuc...@gmail.com wrote: > Hi Justin: > > - i checked that I have 2 tables using replication identity FULL, but > one table is empty and one table has only 1 row > - 7 tables using index > - overall I have ~100 tables in the publication: But I do find that > some

Re: Need help debugging slow logical replication

Hi Justin: - i checked that I have 2 tables using replication identity FULL, but one table is empty and one table has only 1 row - 7 tables using index - overall I have ~100 tables in the publication: But I do find that some tables doesn't have PK , but using replica identity default, I'm abit co

Re: Need help debugging slow logical replication

On Tue, Feb 7, 2023 at 6:38 PM sunyuc...@gmail.com wrote: > Hi there, > > I am using PG 14.14 on both primary and secondary DB on AWS, setup > using a logical replication, I'm having trouble with huge replication > lag. > > My setup is as follows: > > P1 - physical - P1-R > | (logical) > P2 - p

Re: Need help understanding error message [RESOLVED]

On 10/26/21 1:17 PM, Rich Shepard wrote: On Tue, 26 Oct 2021, Rob Sargent wrote: Aren’t lines 3 and 6 duplicates? Ah, shoot! The second was supposed to be 16 and that's how I saw it when I scanned the list. The second thing I deeply learned about computer programming: it's almost certainly

Re: Need help understanding error message [RESOLVED]

On Tue, 26 Oct 2021, Rob Sargent wrote: Aren’t lines 3 and 6 duplicates? Ah, shoot! The second was supposed to be 16 and that's how I saw it when I scanned the list. Thanks! Rich

Re: Need help understanding error message

On Tue, 26 Oct 2021, Rob Sargent wrote: Are you doing a batch of updates, Yes, ... and including/generating a duplicate in there? No. Rich

Re: Need help understanding error message

> On Oct 26, 2021, at 11:06 AM, Rich Shepard wrote: > > On Tue, 26 Oct 2021, Ron wrote: > >> Show us the actual UPDATE statement. > > Ron, > > insert into contacts > (person_nbr,contact_date,contact_type,notes,next_contact) values > (1,'2021-10-26','Email','message 3','2012-11-16'), > (4,'

Re: Need help understanding error message

> On Oct 26, 2021, at 11:05 AM, Rich Shepard wrote: > > On Tue, 26 Oct 2021, Rob Sargent wrote: > >> What do you get when you select * where person_nbr = 6 and contact_date = >> ‘2021-10-26’ and contact_type = ‘Email’ from activities; ? > > Rob, > > 'Email'; > person_nbr | contact_date | c

Re: Need help understanding error message

On Tue, 26 Oct 2021, Ron wrote: Show us the actual UPDATE statement. Ron, insert into contacts (person_nbr,contact_date,contact_type,notes,next_contact) values (1,'2021-10-26','Email','message 3','2012-11-16'), (4,'2021-10-26','Email','message 3','2012-11-16'), (6,'2021-10-26','Email','messa

Re: Need help understanding error message

On Tue, 26 Oct 2021, Rob Sargent wrote: What do you get when you select * where person_nbr = 6 and contact_date = ‘2021-10-26’ and contact_type = ‘Email’ from activities; ? Rob, person_nbr | contact_date | contact_type | notes | next_contact +--+--+--

Re: Need help understanding error message

> On Oct 26, 2021, at 10:07 AM, Rob Sargent wrote: > > > >> > What do you get when you select * where person_nbr = 6 and contact_date = > ‘2021-10-26’ and contact_type = ‘Email’ from activities; ? > > > Whoa. Not sure why I put the “from” last but I’m sure you know it goes before the

Re: Need help understanding error message

On 10/26/21 11:58 AM, Rich Shepard wrote: In a database table I have these rows: # select * from contacts where person_nbr=6;  person_nbr | contact_date | contact_type |  not es   | next_conta

  1   2   >