Re: Failing GSSAPI TCP when connecting to server

On Sun, Sep 29, 2024 at 2:00 PM Peter wrote: > My application is trying to connect the database server, and meanwhile > tries to talk to the KDC server for a service ticket. > Earlier these TCP connections did run like this, and were successful: > > [snip] > > A configuration problem on the mach

Re: Regarding use of single column as primary key on partitioned table

On Sat, Sep 28, 2024 at 12:55 AM Tom Lane wrote: > Ron Johnson writes: > > On Sat, Sep 28, 2024 at 12:39 AM David G. Johnston < > > david.g.johns...@gmail.com> wrote: > >> On Friday, September 27, 2024, Durgamahesh Manne < > >>> Can't we use

Re: Regarding use of single column as primary key on partitioned table

On Sat, Sep 28, 2024 at 12:39 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Friday, September 27, 2024, Durgamahesh Manne < > maheshpostgr...@gmail.com> wrote: > >> >> ERROR: unique constraint on partitioned table must include all >> partitioning columns >> DETAIL: PRIMARY KEY c

Re: Repeatable Read Isolation Level "transaction start time"

On Wed, Sep 25, 2024 at 4:50 PM Greg Sabino Mullane wrote: > Since transactions should be "as short as possible, without being too >>> short", how much time is there between when you run "BEGIN;" and the first >>> "work statement"? >>> >> > I don't know that it really matters. For something autom

Re: Repeatable Read Isolation Level "transaction start time"

On Wed, Sep 25, 2024 at 4:23 PM Greg Sabino Mullane wrote: > On Wed, Sep 25, 2024 at 1:53 PM Tom Lane wrote: > >> Because we're not going to analyze the statement in the amount of depth >> needed to make that distinction before we crank up the >> transactional machinery. If it says SELECT, it g

Re: Repeatable Read Isolation Level "transaction start time"

On Wed, Sep 25, 2024 at 1:53 PM Tom Lane wrote: > Ron Johnson writes: > > But why does "SELECT 1;" need a snapshot? Heck, why does "SELECT > > ;" need a snapshot? > > Because we're not going to analyze the statement in the amount of > depth n

Re: Repeatable Read Isolation Level "transaction start time"

On Wed, Sep 25, 2024 at 1:45 PM Adrian Klaver wrote: > > > On 9/25/24 10:22 AM, Greg Sabino Mullane wrote: > > On Tue, Sep 24, 2024 at 10:28 AM Tom Lane > > wrote: > > > > It's even looser than that, really: it's the first statement that > > requires an MVCC sn

Re: Repeatable Read Isolation Level "transaction start time"

On Tue, Sep 24, 2024 at 12:06 PM Adrian Klaver wrote: > On 9/24/24 05:59, Ron Johnson wrote: > > On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston > > mailto:david.g.johns...@gmail.com>> wrote: > > > > On Monday, September 23, 2024, Wizard Brony > &

Re: Repeatable Read Isolation Level "transaction start time"

On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, September 23, 2024, Wizard Brony wrote: > >> >> https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ >> >> The PostgreSQL documentation for the Repeatable Read Isolation Lev

Re: glibc updarte 2.31 to 2.38

Shaheed, How often do you sort words in text editors? How often do you have your text editor care whether the word you just typed is the *only* instance of that word in the document? Not too often. So... yes, we ignore the problem. The real question is why nobody notices it in other RDBMSs like

Re: IO related waits

On Fri, Sep 20, 2024 at 4:47 PM Tom Lane wrote: > veem v writes: > > Able to reproduce this deadlock graph as below. Now my question is , > this > > is a legitimate scenario in which the same ID can get inserted from > > multiple sessions and in such cases it's expected to skip that (thus "On >

Re: Dependencies on the system view

On Fri, Sep 20, 2024 at 7:32 AM Olleg wrote: > Hi all. > > One of our programmer created a view based on the system view. I tried > to explain him, that he created a dependency from the ordinary database > to the system object and this is a bad idea. But he is not smart enough. > So I need a guru

Re: How batch processing works

On Thu, Sep 19, 2024 at 5:24 AM Lok P wrote: > > On Thu, Sep 19, 2024 at 11:31 AM Ron Johnson > wrote: > >> >> [snip] >> >>> >>> Method-4 >>> >>> INSERT INTO parent_table VALUES (1, 'a'), (2, 'a'); >&g

Re: How batch processing works

On Thu, Sep 19, 2024 at 1:31 AM Lok P wrote: > Hello, > Saw multiple threads around the same , so I want some clarification. As we > know row by row is slow by slow processing , so in heavy write systems(say > the client app is in Java) , people asked to do DMLS in batches rather in a > row by ro

Re: question on plain pg_dump file usage

On Tue, Sep 17, 2024 at 8:22 AM Zwettler Markus (OIZ) < markus.zwett...@zuerich.ch> wrote: > I have to do an out-of-place Postgres migration from PG12 to PG16 using: > > > > pg_dump -F p -f dump.sql … > > sed -i "s/old_name/new_name/g" > > psql -f dump.sql … > > > > Both databases are on UTF-8. >

Re: load fom csv

On Mon, Sep 16, 2024 at 11:36 AM Andy Hartman wrote: > I'm trying to run this piece of code from Powershell and it just sits > there and never comes back. There are only 131 records in the csv. > > $connectionString = > "Host=$pgServer;Database=$pgDatabase;Username=$pgUser;Password=$pgPassword" >

Re: Reg: Size difference

On Sat, Sep 14, 2024 at 1:19 PM Vinay Oli wrote: > Hi Team > > I have been using PostgreSQL for the past 6 years. PostgreSQL has > significantly impacted my life, providing me with great opportunities for > knowledge and self-development. > > I'm currently facing a strange issue with PostgreSQL 1

Re: update faster way

On Fri, Sep 13, 2024 at 11:59 PM yudhi s wrote: > >> >> Do you have any indexes? >> If not - you should, if yes - what are they? >> >> >> > Yes we have a primary key on this table which is on a UUID type column and > also we have other indexes in other timestamp columns . But how is this > going

Re: Better way to process records in bash?

On Thu, Sep 12, 2024 at 3:30 PM Christoph Moench-Tegeder wrote: > ## Ron Johnson (ronljohnso...@gmail.com): > > > I need to process table records in a bash script. Currently, I read them > > using a while loop and redirection. The table isn't that big (30ish > > th

Re: Better way to process records in bash?

On Thu, Sep 12, 2024 at 11:43 AM Thiemo Kellner wrote: > Hi > > What is this "something" that it cannot be calculated within the dB? > It's an external program that can't read a csv input file. -- Death to America, and butter sauce. Iraq lobster!

Better way to process records in bash?

(This might be a bash question instead of a PG question, or it might be an A/B question.) I need to process table records in a bash script. Currently, I read them using a while loop and redirection. The table isn't that big (30ish thousand rows), and performance is adequate, but am always lookin

Re: Faster data load

On Fri, Sep 6, 2024 at 12:43 AM Lok P wrote: > Also during index creation we are trying to do it multiple partitions at > same time from multiple sessions.But seeing out of memory error in 5th or > 6th session. > Had that same problem during pg_restore. Reduced maintenance_work_mem and the prob

Re: Faster data load

On Thu, Sep 5, 2024 at 4:14 PM Lok P wrote: > Hi, > > We are having a requirement to create approx 50 billion rows in a > partition table(~1 billion rows per partition, 200+gb size daily > partitions) for a performance test. We are currently using ' insert into > select.. From or transformed q

Re: question on audit columns

On Wed, Sep 4, 2024 at 9:10 AM yudhi s wrote: > > On Wed, Sep 4, 2024 at 6:29 PM Muhammad Usman Khan > wrote: > >> Hi, >> >> In your scenario, triggers can add some overhead since they require extra >> processing after each update operation. Considering the size of your table >> and the high tra

Re: Table and data comparison

postgres_fdw will be required if the tables are in separate databases. On Tue, Sep 3, 2024 at 12:36 PM David Mullineux wrote: > Yes, PostgreSQL. > Why not just do this via SQL join ? > > On Tue, 3 Sept 2024, 17:34 arun chirappurath, > wrote: > >> Hi All, >> >> Do we have any open-source utility

Re: PgBackRest Full backup and N/W reliability

On Thu, Aug 29, 2024 at 12:08 PM KK CHN wrote: > > On Thu, Aug 29, 2024 at 6:54 PM Greg Sabino Mullane > wrote: > >> On Thu, Aug 29, 2024 at 2:21 AM KK CHN wrote: >> >>> I am doing a full backup using PgBackRest from a production server to >>> Reposerver. >>> >> ... >> >>> If so, does the back

Re: PgBackRest client_loop: send disconnect: Connection reset

On Thu, Aug 29, 2024 at 10:12 AM Greg Sabino Mullane wrote: > On Thu, Aug 29, 2024 at 9:31 AM KK CHN wrote: > >> "Unable to acquire lock on file '/tmp/pgbackrest/Repo-backup.lock' >> > > When this happens, take a look inside this file. If there is another > pgbackrest process running, the pid wi

Re: Problem with a Query

Aggressive autoanalyze and autovacuum settings solve most query problems. These are my settings: default_statistics_target = 5000 autovacuum_vacuum_scale_factor = 0.015 autovacuum_vacuum_threshold = 250 autovacuum_analyze_scale_factor = 0.015 autovacuum_analyze_threshold = 250 Such a high default_

Re: ERROR: could not open relation with OID XXXX

On Sun, Aug 25, 2024 at 9:42 AM Marcelo Zabani wrote: > Hi all, > > I can reproduce the error in the subject from time to time when querying > catalog tables while DDL is happening concurrently. Here's a bash script > that reproduces it (not always, you might have to run it many times until > you

Re: where is postres installed?

On Thu, Aug 22, 2024 at 8:36 PM Arbol One wrote: > After installing PostgreSQL on my Debian-12 machine, I typed 'postgres > --version' and got this msg: > *bash: postgres: command not found* > > 'psql --version', however, does work and gives me this message : > > *psql (PostgreSQL) 16.3 (Debian 1

Re: How to validate restore of backup?

On Thu, Aug 22, 2024 at 9:59 AM o1bigtenor wrote: > On Thu, Aug 22, 2024 at 8:03 AM Ron Johnson > wrote: > >> On Thu, Aug 22, 2024 at 8:49 AM o1bigtenor wrote: >> >>> On Thu, Aug 22, 2024 at 6:24 AM Ron Johnson >>> wrote: >>> >>>

Re: How to validate restore of backup?

On Thu, Aug 22, 2024 at 10:22 AM Greg Sabino Mullane wrote: > On Thu, Aug 22, 2024 at 8:49 AM o1bigtenor wrote: > >> >> >> On Thu, Aug 22, 2024 at 6:24 AM Ron Johnson >> wrote: >> >>> That's great on small databases. Not so practica

Re: How to validate restore of backup?

On Thu, Aug 22, 2024 at 8:49 AM o1bigtenor wrote: > > > On Thu, Aug 22, 2024 at 6:24 AM Ron Johnson > wrote: > >> That's great on small databases. Not so practical when they're big. >> >> So - - - - what is the recommended procedure for '

Re: How to validate restore of backup?

That's great on small databases. Not so practical when they're big. On Thu, Aug 22, 2024 at 7:10 AM Muhammad Usman Khan wrote: > Hi Vince, > For validation of databases, you can use the following approach > > /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5428 | md5sum > > /var/lib/pgsql

Re: How to validate restore of backup?

On Thu, Aug 22, 2024 at 7:06 AM Vince McMahon wrote: > Hi, > > I have some questions When doing pg_restore of backup of a database to a > NEW server. > > Is there a way to ensure the data integrity is in tact, and user ID and > access works liked how it was in the old server? > pg_restore is jus

Re: Looking for pg_config for postgresql 13.16

On Tue, Aug 20, 2024 at 11:56 AM H wrote: > I am looking for pg_config for postgresql 13.16 that I run under Rocky > Linux 9. It seems RL appstream latest version is pg_config in > libpq-devel-13.11-1.el9.x86_64 but dnf complains: > installed package postgresql13-"devel-13.16-2PGDG.rhel9.x86_64 o

Re: What is the best way to upgrade pgAdmin on Windows?

On Thu, Aug 15, 2024 at 2:35 AM 毛毛 wrote: > Hey there, > > I just found out my pgAdmin is version 7.6, while the current version is > 8.6. Looks like it's time for an upgrade! > https://www.pgadmin.org/download/pgadmin-4-windows/ The latest is 8.10. > The notification I got had a link to the

Re: PG Dump on 11 - Restore on 16 - is possible?

On Tue, Aug 13, 2024 at 2:21 PM Tomas Vondra wrote: > On 8/13/24 11:47, Durumdara wrote: > > Hello! > > > > We have to upgrade our server. The PG upgrade is impossible because of > > too much data. > > I'm not sure I understand. What exactly is the problem with the amount > of data? > OP has a p

Re: Novice with Postgresql - trying simple Stored Procedure

On Tue, Aug 13, 2024 at 11:32 AM wrote: > I have a simple 3 column table. The Table is a Month Name to number table > which also includes an effective date column. So 12 rows, 3 columns each. > > > > Here is the Stored Procedure I constructed to get the number if given the > name (3 parameters fo

Re: PG Dump on 11 - Restore on 16 - is possible?

On Tue, Aug 13, 2024 at 5:47 AM Durumdara wrote: > Hello! > > We have to upgrade our server. The PG upgrade is impossible because of too > much data. > So we will create a new server with PG 16, and we have to migrate the > databases one by one (from a PG 11 server). > I'd also recommend that yo

Re: PG Dump on 11 - Restore on 16 - is possible?

On Tue, Aug 13, 2024 at 5:47 AM Durumdara wrote: > Hello! > > We have to upgrade our server. The PG upgrade is impossible because of too > much data. > So we will create a new server with PG 16, and we have to migrate the > databases one by one (from a PG 11 server). > > Is it possible? > > Does

Re: Re: searching for libpq5-14.1-42PGDG.rhel8.x86_64

On Sat, Aug 10, 2024 at 5:45 AM 王瞿 wrote: > > Hi Laurenz Albe > > Thank you! > > > >The client libraries for this version are in > >https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-8-x86_64/postgresql14-14.1-1PGDG.rhel8.x86_64.rpm > > But My project was specified to use the package >

Re: Getting specific partition from the partition name

On Thu, Aug 8, 2024 at 4:46 PM Greg Sabino Mullane wrote: > _MM_DD is already setup for sorting, so just do: > > SELECT table_name FROM information_schema.tables WHERE table_name ~ > 'table_part_p' ORDER BY 1 DESC; > > If you need to grab the numbers: > > SELECT substring('table_part_p2022_03

Re: Vacuum full connection exhaustion

On Thu, Aug 8, 2024 at 10:12 AM Christophe Pettus wrote: > > > > On Aug 7, 2024, at 10:34, Costa Alexoglou wrote: > > > > Hey folks, > > > > I noticed something weird, and not sure if this is the expected > behaviour or not in PostgreSQL. > > > > So I am running Benchbase (a benchmark framework)

Re: Vacuum full connection exhaustion

On Thu, Aug 8, 2024 at 5:18 AM Costa Alexoglou wrote: > Hey folks, > > I noticed something weird, and not sure if this is the expected behaviour > or not in PostgreSQL. > > So I am running Benchbase (a benchmark framework) with 50 terminals (50 > concurrent connections). > There are 2-3 additiona

Re: data checksums

On Wed, Aug 7, 2024 at 3:41 AM Daniel Gustafsson wrote: > > On 6 Aug 2024, at 18:29, Christophe Pettus wrote: > >> On Aug 6, 2024, at 08:11, bruno vieira da silva > wrote: > > >> the pg doc > >> mentions a considerable performance penality, how considerable it is? > > > > That line is probably

Re: Standard of data storage and transformation

On Tue, Aug 6, 2024 at 5:07 PM yudhi s wrote: > Hi All, > We are having a use case in which we are having transaction data for > multiple customers in one of the Postgres databases(version 15.4) and we > are consuming it from multiple sources(batch file processing, kafka event > processing etc).

Re: data checksums

On Tue, Aug 6, 2024 at 11:12 AM bruno vieira da silva wrote: > Hello. > I've been dealing with some database corruption events, so i've been > wondering to enable data checksums on my deployments. > > so my question is why data checksums aren't enabled by default on pg? the > pg doc > mentions a

Re: Impact from removal of pgadmin4

On Fri, Aug 2, 2024 at 3:57 AM Vince McMahon wrote: > In windows, when the pgadmin4 is removed, will it remove the libraries > from that windows? > > Basically, I have other database applications installed to get and to set > data into postgres db. Will removing of pgadmin4 remove the odbc, jdbc

Re: VACUUM on temp table blocks VACUUM on another...

PG 15.0 or 15.7? On Thu, Aug 1, 2024 at 3:58 PM Jim Vanns wrote: > Sorry, PG15. > > Jim > > On Thu, 1 Aug 2024 at 20:25, Adrian Klaver > wrote: > >> On 8/1/24 12:12, Tom Lane wrote: >> > Jim Vanns writes: >> >> I have two sessions, each creating a temporary table of the same name - >> >> 'foob

Re: Memory issues with PostgreSQL 15

On Thu, Jul 25, 2024 at 6:59 AM Christian Schröder < christian.schroe...@wsd.com> wrote: > Hi all, > I started this discussion in May and was then dragged into other topics, > so I could never follow up. Sorry for that! > Since then, the problem has resurfaced from time to time. Right now, we > se

Re: Issue while creating index dynamically

On Tue, Jul 23, 2024 at 4:10 PM veem v wrote: > Hi, > It's postgres version 15.4. We have a requirement to create an index on a > big partition table and want to do it online. And to make the script run in > an automated way on any day , through our ci/cd pipeline we were trying to > write it as

Re: Re. Select with where condition times out

(Because VACUUM FULL rewrites the table, an implicit REINDEX occurs.) I don't see mention of analyzing the database. Also, VACUUM FULL probably doesn't do what you think it does. On Sat, Jul 20, 2024 at 7:44 AM sivapostg...@yahoo.com < sivapostg...@yahoo.com> wrote: > Executed > VACUUM FULL VER

Re: Semantic cache capability for Postgresql db

What does "python client will support the pgprewarm cache usage" mean? Read the pgprewarm docs. On Wed, Jul 17, 2024 at 11:10 AM pradeep t wrote: > Thanks, I'll check it out. > Also is there any python client for the connection to pgprewarm? > Is the existing Postgresql python client will suppo

Re: PostgreSQL Active-Active Clustering

On Mon, Jul 15, 2024 at 5:54 PM Christoph Moench-Tegeder wrote: > ## Ron Johnson (ronljohnso...@gmail.com): > > > This "lack of products" puzzles me, because DEC was doing this with VAX > > (then Alpha and Itanium) clusters 40 years ago via a Distributed Lock > >

Re: PostgreSQL Active-Active Clustering

On Mon, Jul 15, 2024 at 3:28 PM Christophe Pettus wrote: > > > On Jul 15, 2024, at 12:06, Sarkar, Subhadeep > wrote: > > > [snip] > > • In the Community edition of PostgreSQL is it possible to setup a > cluster where all the nodes are able to concurrently read-write the > underlying database

Re: How does this FK constraint error happen?

On Mon, Jul 15, 2024 at 12:47 PM Adrian Klaver wrote: > On 7/15/24 09:21, Ron Johnson wrote: > > On Mon, Jul 15, 2024 at 11:37 AM Adrian Klaver > > mailto:adrian.kla...@aklaver.com>> wrote: > > > > > > I don't think it is entirely coincidental that

Re: How does this FK constraint error happen?

On Mon, Jul 15, 2024 at 11:37 AM Adrian Klaver wrote: > On 7/15/24 08:18, Ron Johnson wrote: > > On Mon, Jul 15, 2024 at 11:06 AM Adrian Klaver > > mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 7/15/24 07:53, Ron Johnson wrote: > > >

Re: How does this FK constraint error happen?

On Mon, Jul 15, 2024 at 11:06 AM Adrian Klaver wrote: > On 7/15/24 07:53, Ron Johnson wrote: > > On Mon, Jul 15, 2024 at 10:35 AM Peter J. Holzer > <mailto:hjp-pg...@hjp.at>> wrote: > > [snip] > > > > > > Is it possible that some other proce

Re: How does this FK constraint error happen?

On Mon, Jul 15, 2024 at 10:35 AM Peter J. Holzer wrote: [snip] > > Is it possible that some other process created an entry in > rel_group_user between these two queries? That was, in fact, the problem. At just the wrong time to impact one of the child databases (TAPd), but not the other two (T

How does this FK constraint error happen?

PG 14.12 The job does DELETE FROM rel_group_user; (no WHERE clause!!) then does DELETE FROM public.access_user; (also no WHERE clause), but the public.access_user statement fails on FK constraint error on rel_group_user (which was just recently emptied). Each statement is in a different transacti

Re: Dropping column from big table

On Thu, Jul 11, 2024 at 3:41 AM sud wrote: > > > On Thu, 11 Jul, 2024, 12:46 pm Ron Johnson, > wrote: > >> On Wed, Jul 10, 2024 at 11:28 PM sud wrote: >> >>> >>> >>> >>> Thank you so much. When you said *"you can execute one

Re: Dropping column from big table

On Wed, Jul 10, 2024 at 11:28 PM sud wrote: > > On Thu, Jul 11, 2024 at 2:52 AM Adrian Klaver > wrote: > >> >> https://www.postgresql.org/docs/current/sql-altertable.html >> >> "The DROP COLUMN form does not physically remove the column, but simply >> makes it invisible to SQL operations. Subseq

Re: can stored procedures with computational sql queries improve API performance?

On Tue, Jul 9, 2024 at 8:58 PM Krishnakant Mane wrote: > Hello. > > I have a straight forward question, but I am just trying to analyze the > specifics. > > So I have a set of queries depending on each other in a sequence to > compute some results for generating financial report. > > It involves

Re: Load a csv or a avro?

On Sat, Jul 6, 2024 at 4:10 PM sud wrote: > On Fri, Jul 5, 2024 at 8:24 PM Adrian Klaver > wrote: > >> On 7/5/24 02:08, sud wrote: >> > Hello all, >> > >> > Its postgres database. We have option of getting files in csv and/or in >> > avro format messages from another system to load it into our p

Re: Load a csv or a avro?

On Fri, Jul 5, 2024 at 5:08 AM sud wrote: > Hello all, > > Its postgres database. We have option of getting files in csv and/or in > avro format messages from another system to load it into our postgres > database. The volume will be 300million messages per day across many files > in batches. > >

Re: Design strategy for table with many attributes

On Thu, Jul 4, 2024 at 3:38 PM Lok P wrote: > Hello, > In one of the applications we are getting transactions in messages/events > format and also in files and then they are getting parsed and stored into > the relational database. The number of attributes/columns each transaction > has is ~900+.

Re: Alignment check

On Thu, Jun 27, 2024 at 1:26 PM Marthin Laubscher wrote: [snip] > So when you're done trolling me and my choices, Adrian didn't start this "conversation". > feel free to comment on the actual question. > YB says they are almost finished updating their system to the PG 15 (not sure which poin

Re: A way to optimize sql about the last temporary-related row

On Thu, Jun 27, 2024 at 11:33 AM aghart...@gmail.com wrote: > Hi, > > You are right. Too quickly copy-paste on my part :-) > > I take this opportunity to add a NOT insignificant detail. > > Before executing the select query I clear the cache: > > systemctl stop postgresql-16 && sync && echo 3 > /

Re: A way to optimize sql about the last temporary-related row

On Thu, Jun 27, 2024 at 11:20 AM aghart...@gmail.com wrote: [snip] > -- insert 4M records > insert into test_table(pk_id) select generate_series(1,400,1); > > -- now set some random data, distribuited between specific ranges (as in > my production table) > update test_table set > datetime_fie

Re: Autovacuum, dead tuples and bloat

On Wed, Jun 26, 2024 at 3:03 AM Shenavai, Manuel wrote: > Thanks for the suggestions. > I checked pg_locks shows and pg_stat_activity but I could not find a LOCK > or an transaction on this (at this point in time). > > I assume that this problem may relate to long running transactions which > wr

Re: Replication After manual Failover

How were you replicating? What was the status of the replication? On Tue, Jun 25, 2024 at 1:20 PM Yongye Serkfem wrote: > Hello Engineer, > Below is the error message I am getting after failing over to the standby > and reconfiguring the former master as the new standby. Any help will be > appr

Re: Replication After manual Failover

On Tue, Jun 25, 2024 at 2:39 PM Muhammad Ikram wrote: > Hi, > > Please reinitialize using pg_basebackup > Might not be possible during a switchover. > or use pg_rewind > > > > Muhammad Ikram > Bitnine Global > > > On Tue, 25 Jun 2024 at 22:20, Yongye Serkfem wrote: > >> Hello Engineer, >> Bel

Re: Issue with pgstattuple on Sequences in PostgreSQL

On Mon, Jun 24, 2024 at 7:09 AM Ayush Vatsa wrote: [snip] > How can one use pgstattuple on sequences? > Out of curiosity... *why*?

Re: Execute permission to function

On Mon, Jun 24, 2024 at 6:29 AM arun chirappurath wrote: > Hi all > > I am using rds postgres 14. I have created few users and added them to > pg_readall_data and pg_write_alldata groups > > > They are able to read all data and do update in tables > > However they can't execute functions > https

Re: Re: How to use createdb command with newly created user?

Better to run now, and save yourself hassle in the future: ALTER ROLE "Baba" RENAME TO baba; Also, use a .pgpass file: https://www.postgresql.org/docs/14/libpq-pgpass.html On Sun, Jun 23, 2024 at 3:22 PM 毛毛 wrote: > > > Thank you! You are right! > > After putting quotes around the username, it

Re: Password complexity/history - credcheck?

On Sat, Jun 22, 2024 at 7:28 PM Martin Goodson wrote: > Hello. > > Recently our security team have wanted to apply password complexity > checks akin to Oracle's profile mechanism to PostgreSQL, checking that a > password hasn't been used in x months There would have to be a pg_catalog table whi

Re: pg_dump restores as expected on some machines and reports duplicate keys on others

On Sat, Jun 22, 2024 at 1:02 PM Shaheed Haque wrote: > Hi, > > I am using Postgres 14 on AWS RDS and am seeing the output of pg_dump be > restored as expected by pg_restore on some database instances, and fail > with reports of duplicate keys on other database instances: > >- My deployments a

Re: Autovacuum, dead tuples and bloat

On Thu, Jun 20, 2024 at 12:47 PM Shenavai, Manuel wrote: > Hi everyone, > > > > we can see in our database, that the DB is 200GB of size, with 99% bloat. > After vacuum full the DB decreases to 2GB. > > DB total size: 200GB > > DB bloat: 198 GB > > DB non-bloat: 2GB > > > > We further see, that d

Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.

On Thu, Jun 20, 2024 at 3:23 AM Dmitry O Litvintsev wrote: > Hello, > > I am in the process of migrating DB to Alma9 host. The databse > is rather large - few TBs. > > I have run pg_basebackup on Alma9 host and established replication from > production to it. The idea is to quickly switch from ma

Re: Transaction issue

On Wed, Jun 19, 2024 at 5:39 PM Rich Shepard wrote: > On Wed, 19 Jun 2024, Ron Johnson wrote: > > > The problem is that you don't know where it's failing. > > Ron, > > True that. There's no specificity to why the transaction didn't complete. > &g

Re: Transaction issue

On Wed, Jun 19, 2024 at 4:54 PM Rich Shepard wrote: > On Wed, 19 Jun 2024, Adrian Klaver wrote: > > > Looks to me you have a left over unresolved transaction in your psql > session. > > The easiest solution if that is the case is to exit the session and > start a > > new session to run the script

Re: Transaction issue

On Wed, Jun 19, 2024 at 4:33 PM Rich Shepard wrote: > On Wed, 19 Jun 2024, Alban Hertroys wrote: > > > The error prior to those statements is what you need to look at. That’s > > what’s causing the transaction to fail. > > Alban/Adrian, > > > I get the impression that you’re executing shell scrip

Re: Proper format for pg_dump file date

On Wed, Jun 19, 2024 at 1:14 PM Rich Shepard wrote: > On Wed, 19 Jun 2024, David G. Johnston wrote: > > > If you are doing a custom format dump using .sql as the extension is > > objectively wrong. it is correct if you are doing an sql dump, but those > > are generally best avoided. > > David, >

Re: Proper format for pg_dump file date

On Wed, Jun 19, 2024 at 11:55 AM Rich Shepard wrote: > Is the correct date format for pg_dump > -$(date +%Y-%m-%d).sql > or > --MM-DD.sql > "Click select" stops at dash in some ssh clients, but not in others. That's what drives *my* decision making. (Also, "%F" is equivalent

Re: Manual Failover

On Wed, Jun 19, 2024 at 11:03 AM Yongye Serkfem wrote: > Hello Engineers, > I am facing an issue with the manual failover of the standby to the master > role. I was able to promote the standby and got it out of recovery mode. > How do I direct applications to point to the standby which has assume

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

On Tue, Jun 18, 2024 at 2:37 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tuesday, June 18, 2024, Ron Johnson wrote: > >> On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>>

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tuesday, June 18, 2024, Ron Johnson wrote: > >> >> But I stand by returning OUT params and records at the same time. >> > > You mean you dislike adding the optional ret

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

On Tue, Jun 18, 2024 at 1:16 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Jun 18, 2024 at 10:07 AM Ron Johnson > wrote: > >> fun2 puzzles me. Why would you return parameters AND *a single record* >> (unless >> it's an error status

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

fun1 returns a table set just like any other SELECT statement. fun2 puzzles me. Why would you return parameters AND *a single record* (unless it's an error status). fun3 just returns two parameters. Why isn't it a procedure? fun2, returning parameters AND a function value, would have made my Com

Re: Monitoring logical replication

On Tue, Jun 18, 2024 at 5:03 AM Shaheed Haque wrote: > Hi all, > > Is there an "official" pairing of LSN values on the publication and > subscription sides that should be used to track the delta between the two > systems? I ask because Google is full of different pairs being used. I > tried to id

Re: WAL settings for larger imports

On Mon, Jun 17, 2024 at 9:46 AM Benoit, Eric wrote: > What is a good data import configuration for only one server for > WRITE-AHEAD LOG settings postgresql.conf where no one is connecting to the > database accept me as a superuser. > How worried are you about data loss / database corruption? I

Re: pgstattuple - can it/does it provide stats by partition?

On Sat, Jun 15, 2024 at 9:47 AM Dragam wrote: > Hi all, > > As the title says - does (and/or can) pgstattuple provide a breakdown by > partition? > > Partitions are tables, created via CREATE TABLE. I know from experience that it works on inheritance tables; what happens wh

Re: Configure autovacuum

On Fri, Jun 14, 2024 at 2:20 AM Shenavai, Manuel wrote: > Hi everyone, > > > > I would like to configure the autovacuum in a way that it runs very > frequently (i.e. after each update-statement). I tried the following > settings on my table: > > alter table mytable set (autovacuum_vacuum_scale_fa

Re: Reset sequence to current maximum value of rows

On Thu, Jun 13, 2024 at 2:38 PM Rich Shepard wrote: > On Thu, 13 Jun 2024, Ron Johnson wrote: > > > If the table has a primary key, then the command *should* have failed > with > > a duplicate key error as soon as the first dupe was discovered. > > Ron, > > I

Re: Reset sequence to current maximum value of rows

On Thu, Jun 13, 2024 at 1:20 PM Rich Shepard wrote: > Two tables have a sequence for the PK. Over time I manually entered the PK > numbers not being aware of applying DEFAULT to generate the next number. > > I just tried to set one table's PK sequence to the current max(PK) value > using this exp

Re: UPDATE with multiple WHERE conditions

On Wed, Jun 12, 2024 at 5:28 PM Rich Shepard wrote: > I have a table with 3492 rows. I want to update a boolean column from > 'false' to 'true' for 295 rows based on the value of another column. > > Is there a way to access a file with those condition values? If not, should > I create a temporary

Re: PG16.1 security breach?

On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jun 10, 2024 at 2:21 AM Laurenz Albe > wrote: > >> > How is it that the default privilege granted to public doesn’t seem to >> care who the object creator >> > is yet when revoking the grant one suppo

Re: postgres table statistics

On Wed, Jun 12, 2024 at 3:48 AM Chandy G wrote: > Hi, > We have postgres 13.9 running with tables thats got billions of records > of varying sizes. Eventhough pg jdbc driver provides a way to set fetch > size to tune the driver to achieve better throughput, the JVM fails at the > driver level

Re: Does trigger only accept functions?

On Tue, Jun 11, 2024 at 2:53 PM veem v wrote: > > On Tue, 11 Jun 2024 at 17:03, hubert depesz lubaczewski > wrote: > >> On Tue, Jun 11, 2024 at 12:47:14AM +0530, veem v wrote: >> > to be called from ~50 triggers? or any other better approach exists to >> > handle this? >> >> pgaudit extension? >

  1   2   3   4   5   6   7   8   9   10   >