Re: Postgres 13 streaming replication standby not sending password, 'fe_sendauth: no password supplied'

On Wed, Jan 10, 2024 at 5:51 PM Keaney, Will wrote: > Hello, > > I'm building a new 2-node Postgreql 13 streaming replication cluster. I'm > able to clone the primary to the standby using pg_basebackup. > However, the standby is unable to authenticate to the primary to begin > recovery during sta

Re: Refresh Materialized View Issue

On Thu, Jan 11, 2024 at 3:31 PM Jeremiah Bauer wrote: > Hello all, > > We are having an issue with a materialized view refresh never finishing, > any help is appreciated. It will run at 100% CPU and no IO traffic > indefinitely after about 15 minutes of parallel workers and the parent > worker c

Re: Refresh Materialized View Issue

On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer wrote: > My question is: what indexes are on public.large_table? Hopefully > there's a compound b-tree index on id1, id2, id3. > > There is not, after further investigation. There are these 4 indexes that > involve id1, id2, and id3. Should I try

Re: How much size saved by updating column to NULL ?

On Fri, Jan 12, 2024 at 7:45 AM Sébastien TANIERE wrote: > Hello, > in my company, some columns rarely used in a PG database 14.8 have been > set to NULL in order to save disk space (datecreation & acteurcreation in > following table) . > > create table example > ( > id varchar(

Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

I think this might be an A-B problem. Tell us the "business problem" you are trying to solve, not the problem you're having with your solution to the "business problem". (If you've already mentioned it, please restate it.) On Sat, Jan 13, 2024 at 11:49 AM David Ventimiglia < davidaventimig...@ha

Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

*No,* that's a technology problem. What is the purpose of storing them back in the database using psql? On Sat, Jan 13, 2024 at 4:34 PM David Ventimiglia < davidaventimig...@hasura.io> wrote: > The business problem I'm trying to solve is: > > "How do I capture logical decoding events with the wa

Re: Why scan all columns when we select distinct c1?

On Sun, Jan 14, 2024 at 6:18 AM Yongtao Huang wrote: > PostgreSQL version: 16.1 > Operating system: centos7 > Description: > > Let me show these explain results first, in PG9.4 and PG16.1. > > ### Behavior in PG9.4 > ``` SQL > gpadmin=# create table t1 (c1 int, c2 text); > CREATE TABLE > gpadmi

Mimic ALIAS in Postgresql?

Some RDBMSs have CREATE ALIAS, which allows you to refer to a table by a different name (while also referring to it by the original name). We have an application running on DB2/UDB which (for reasons wholly unknown to me, and probably also to the current developer) extensively uses this with two s

Re: postgres sql assistance

"*invalid input syntax for type boolean: "15"*" That is the problem. You can't insert 15 into a column of type "boolean". On Tue, Jan 16, 2024 at 7:35 AM arun chirappurath wrote: > Dear all, > > I am an accidental postgres DBA and learning things every day. Apologies > for my questions if not

Re: Moving to Postgresql database

On Tue, Jan 16, 2024 at 12:10 PM Adrian Klaver wrote: > On 1/16/24 09:04, Dominique Devienne wrote: > > On Tue, Jan 16, 2024 at 5:07 PM Adrian Klaver > > wrote: > > > > On 1/16/24 00:06, Dominique Devienne wrote: > > > On Mon, Jan 15, 2024 at 5:17 AM ve

Re: Mimic ALIAS in Postgresql?

On Tue, Jan 16, 2024 at 12:40 PM Adrian Klaver wrote: > On 1/16/24 09:20, Ron Johnson wrote: > > Some RDBMSs have CREATE ALIAS, which allows you to refer to a table by a > > different name (while also referring to it by the original name). > > > > > > > Ma

Re: Moving to Postgresql database

On Tue, Jan 16, 2024 at 1:09 PM Adrian Klaver wrote: > On 1/16/24 09:59, Ron Johnson wrote: > > > Performance-killing alternatives are not really altternatives. > > Unless it is the only one that solves your problem. > Amputating one head cures one's migraines, but n

Re: Mimic ALIAS in Postgresql?

On Tue, Jan 16, 2024 at 2:24 PM Adrian Klaver wrote: > > On 1/16/24 10:11 AM, Ron Johnson wrote: > > On Tue, Jan 16, 2024 at 12:40 PM Adrian Klaver > wrote: > >> On 1/16/24 09:20, Ron Johnson wrote: >> > Some RDBMSs have CREATE ALIAS, which allows you to refer

Re: replication not replicating

On Tue, Jan 16, 2024 at 4:10 PM Brad White wrote: > Errors from the primary > > 2024-01-15 00:00:51.157 CST [2660] ERROR: requested WAL segment > 0001000200A2 has already been removed > 2024-01-15 00:00:51.157 CST [2660] STATEMENT: START_REPLICATION > 2/A200 TIMELINE 1 > 2024-01

Re: Mimic ALIAS in Postgresql?

On Tue, Jan 16, 2024 at 5:31 PM Rob Sargent wrote: > On 1/16/24 10:20, Ron Johnson wrote: > > Some RDBMSs have CREATE ALIAS, which allows you to refer to a table by a > different name (while also referring to it by the original name). > > We have an application running on

Re: Mimic ALIAS in Postgresql?

On Tue, Jan 16, 2024 at 5:57 PM Rob Sargent wrote: > On 1/16/24 15:39, Ron Johnson wrote: > > On Tue, Jan 16, 2024 at 5:31 PM Rob Sargent wrote: > >> On 1/16/24 10:20, Ron Johnson wrote: >> >> Some RDBMSs have CREATE ALIAS, which allows you to refer to a table by a

Re: replication not replicating

On Tue, Jan 16, 2024 at 6:26 PM Brad White wrote: > On Tue, Jan 16, 2024 at 4:35 PM Ron Johnson > wrote: > >> On Tue, Jan 16, 2024 at 4:10 PM Brad White wrote: >> >>> Errors from the primary >>> >>> 2024-01-15 00:00:51.157 CST [2660] ERROR: re

Re: WAL file clean up

On Tue, Jan 16, 2024 at 10:03 PM Brad White wrote: > I have the 'archive_cleanup_command' command specified, but I still have > WAL files. > The documentation seems to indicate that it will run automatically, but it > doesn't seem to be running. > > archive_cleanup_command = 'pg_archiveclea

Re: pg_basebackup Restore problem

On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh wrote: > Hello, > > I am trying to restore a pg_basebackup and have the following errors. > > You need to tell us the PG version number *and* show us the full command you ran. > nohup: ignoring input > tar: /tnt/backup/current/7400.tar.gz: Not f

Re: Initiate backup from routine?

On Wed, Jan 17, 2024 at 9:41 AM Troels Arvin wrote: > Hello, > > I would like to allow a co-worker to perform a backup of a database, such > that the backup is saved to the database server itself. One use case is > that (s)he would like an extra backup of a database, just before an > application

Re: pg_basebackup Restore problem

??? Or /mnt? > /tnt/backup/current > > This is where the backup files are. > > > > > > On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson > wrote: > >> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh < >> johnathantia...@gmail.com> wrote: >> &g

Re: pg_basebackup Restore problem

> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs? > > > On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson > wrote: > >> 1. What's in $PATH_FOLDER? >> 2. What pg_basebackup command did you use? >> 3. Why aren't you letting pg_base

Re: pg_basebackup Restore problem

Then you've got a bug somewhere in: tar -h -zxvf $PATH_FOLDER/* .tar.gz ls $PATH_FOLDER/*.tar.gz | xargs -I {} tar -h -zxvf {} On Wed, Jan 17, 2024 at 4:07 PM Johnathan Tiamoh wrote: > Yes. > > I am trying to restore the backups on a standby > > On Wed, Jan 17,

Re: pg_basebackup Restore problem

Perfectly understandable, but tar *did* fail. Time to start debugging your shell script. On Wed, Jan 17, 2024 at 4:26 PM Johnathan Tiamoh wrote: > Ok. > > I'm a little confused because has always work > > On Wed, Jan 17, 2024 at 4:11 PM Ron Johnson > wrote: >

B-tree index balance?

On an RDMS which I used in the 1990s and 2000s, b-tree indices of sequences would get unbalanced, since every new leaf was added to the far right corner of the tree. Sure, they would auto-balance *to a degree* during node splits, but all those "far-right corner" inserts still left them pretty lopsi

Re: B-tree index balance?

On Fri, Jan 19, 2024 at 11:37 AM Tom Lane wrote: > Ron Johnson writes: > > On an RDMS which I used in the 1990s and 2000s, b-tree indices of > sequences > > would get unbalanced, since every new leaf was added to the far right > > corner of the tree. > > Sure,

Re: unbale to list schema

On Wed, Jan 17, 2024 at 1:46 PM Atul Kumar wrote: > Hi, > > I am not able to find any solution to list all schemas in all databases at > once, to check the structure of the whole cluster. > > As I need to give a few privileges to a user to all databases, their > schemas and schemas' objects (tabl

Re: Mimic ALIAS in Postgresql?

y much everyone uses frameworks. (Go > is actually an interesting exception to this.) > > 1: https://guides.rubyonrails.org/active_record_querying.html#find > > You may well be correct, but I have to ask the OP (Ron) if this is the > case in the current situation. I find it d

Re: Backup certain months old data

On Mon, Jan 22, 2024 at 10:12 PM Siraj G wrote: > Hello! > > I would like to know how we can backup certain months old data from PgSQL > Use the COPY command. > and then delete it. > That would depend on how many records, how big the records are, and if there's index support on the "date" fie

[no subject]

PG 14.10 (and 9.6.24, which we're migrating off of). EXPLAIN SELECT works inside a FOR loop, but only the first line of the EXPLAIN output is stored. What's the magic sauce for seeing the whole EXPLAIN output? (The purpose is to generate many queries and see how the BIND and SELECT times change

Re:

On Thu, Jan 25, 2024 at 12:29 PM Tom Lane wrote: > Ron Johnson writes: > > EXPLAIN SELECT works inside a FOR loop, but only the first line of the > > EXPLAIN output is stored. What's the magic sauce for seeing the whole > > EXPLAIN output? > > The usual way is

Re: permission denied on socket

On Thu, Jan 25, 2024 at 3:23 PM Atul Kumar wrote: > Hi, > > I have postgres 13 running on centos 7. > > I am facing an issue while trying to connect the cluster using the below > command. > > -bash-4.2$ psql postgres > > psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: > P

Re: permission denied on socket

On Thu, Jan 25, 2024 at 3:32 PM Adrian Klaver wrote: [snip] > Best guess is you are using a version of psql that is expecting the > socket to be somewhere else then where it actually is. > Is "permission denied" really the error you get when the socket does not exist? Trying "psql --host=/var/r

Query performance in 9.6.24 vs 14.10

(I don't know how this will look in text mode. Hopefully it will be comprehensible in the archives.) This is the summary of EXPLAIN (ANALYZE) on eight frequently-run complex queries from our application, extracted from the Postgresql log because either the BIND or SELECT takes longer than 3000 ms

Re: Query performance in 9.6.24 vs 14.10

On Sun, Jan 28, 2024 at 10:44 PM David Rowley wrote: > On Mon, 29 Jan 2024 at 07:37, Ron Johnson wrote: > >> 08 9.6.24 1,142.164 1,160.801 1,103.716 1,249.852 1,191.081 >> 14.10 159.354 155.111 155.111 162.797 158.157 86.72% >> > > Your speedup per cent calculat

Re: Scriptable way to validate a pg_dump restore ?

On Mon, Jan 29, 2024 at 3:12 AM Laura Smith < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > Hi > > Let's say I've got a scenario where I'm doing a pg_dump replication rather > than online streaming, e.g. due to air-gap or whatever. > > Is there a scriptable way to validate the restore ? e.g. using

Re: Query performance in 9.6.24 vs 14.10

Yes, jit=on. I'll test them with jit=off, to see the difference. (The application is 3rd party, so will change it at the system level.) On Mon, Jan 29, 2024 at 7:09 AM Bob Jolliffe wrote: > Out of curiosity, is the pg14 running with the default jit=on setting? > > This is obviously entirely du

Re: Query performance in 9.6.24 vs 14.10

According to my tests, sometimes JIT is a little faster, and sometimes it's a little slower. Mostly within the realm of statistical noise (especially with each query having a sample size of only 13, on a VM that lives on a probably-busy host). On Mon, Jan 29, 2024 at 9:18 AM Ron Johnson

Re: Query running longer

On Thu, Feb 1, 2024 at 4:13 PM Laurenz Albe wrote: > On Fri, 2024-02-02 at 02:27 +0530, veem v wrote: > > We have the below query which is running for ~45 seconds on postgres > aurora reader instance. > > I have captured the explain analyze. Want to understand, where exactly > the resources are >

Re: How to do faster DML

On Sat, Feb 3, 2024 at 8:55 AM Lok P wrote: > Apology. One correction, the query is like below. I. E filter will be on > on ctid which I believe is equivalent of rowid in oracle and we will not > need the index on Id column then. > > But, it still runs long, so thinking any other way to make the

Re: How to do faster DML

On Sun, Feb 4, 2024 at 10:30 AM Lok P wrote: > On Sun, Feb 4, 2024 at 8:14 PM Dennis White > wrote: > >> I'm surprised no one has mentioned perhaps it's a good idea to partition >> this table while adding the pk. By your own statements the table is >> difficult to work with as is. Without partit

Re: How to do faster DML

On Sat, Feb 3, 2024 at 11:09 AM Lok P wrote: [snip] > show maintenance_work_mem; - 4155MB > show work_mem; - 8MB > show shared_buffers ; -22029684 > Those are pretty small values. What are your server specs?

Re: Question on partitioning

On Sun, Feb 4, 2024 at 4:40 PM veem v wrote: > Hello All, > In postgresql, Is it possible to partition an existing nonpartitioned > table having data already residing in it and indexes and constraints > defined in it, without the need of manually moving the data around, to make > it faster? Simil

Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY

On Mon, Feb 5, 2024 at 7:23 AM Sean v wrote: > This is related to a question I asked on dbs.stackexchange.com: > https://dba.stackexchange.com/questions/335501/why-doesnt-postgres-apply-limit-on-groups-when-retrieving-n-results-per-group > > But to reiterate - I have a query like this: > > SELECT

Re: Unused indexes

On Mon, Feb 5, 2024 at 9:02 AM arun chirappurath wrote: > Hi All, > > Do we have a script to get unused indexes for 30 days and once identified > do we have an option to disable and enable when required? > The pg_stat_*_tables tables idx_* columns has accumulated usage since the last time you s

Re: Improving pg_dump performance when handling large numbers of LOBs

On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis wrote: > Hi, > > We've inherited a series of legacy PG 12 clusters that each contain a > database that we need to migrate to a PG 15 cluster. Each database contains > about 150 million large objects totaling about 250GB. > 250*10^9 / (150*10^6) = 1667

Re: Improving pg_dump performance when handling large numbers of LOBs

at use of LOBs for this purpose was > not necessary. > > Wyatt > > On Mon, Feb 5, 2024 at 11:36 AM Ron Johnson > wrote: > >> On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis >> wrote: >> >>> Hi, >>> >>> We've inherited a series of leg

Re: Unused indexes

index without having to drop the index. Its only purpose is to make the DBA's life easier. IMNSHO, that's an excellent reason to have such a feature. > but Ron is correct, we have nothing equivalent. General usage in Postgres > is to drop the index if it is unused. If you need to cr

Exclude certain application pgaudit logging?

Currently, we use Object audit logging to capture all READ access to columns FOO_1, FOO_2 and FOO_3 in table BAR.SCRABBLE. (They are the three columns have PII data.) The problem is that the application legitimately reads these columns thousands of times per day. Thus, the log fills up with mean

Re: Question on partitioning

On Tue, Feb 6, 2024 at 2:40 PM veem v wrote: > Thank you Laurenz. Got it. > > So basically , you mean to say any DDL on a table won't allow the table to > be read by other processes. I was under the assumption that it should allow > the read queries to move ahead at least. I must be wrong here. T

Re: How to do faster DML

On Sun, Feb 11, 2024 at 11:54 AM veem v wrote: [snip] > When you said *"you would normally prefer those over numeric " *I was > thinking the opposite. As you mentioned integer is a fixed length data type > and will occupy 4 bytes whether you store 15 or .But in case of > variable length t

Re: Query hangs (and then timeout) after using COPY to import data

On Sun, Feb 11, 2024 at 4:41 PM Adrian Klaver wrote: > On 2/11/24 13:37, ste...@gmail.com wrote: > [snip] > > > > The same query, executed from pgAdmin, returns the result in less than a > > second (even if it’s executed while the query from my app is running). > > > > (actually the result are 0

Re: Query hangs (and then timeout) after using COPY to import data

Since the query works in PgAdmin, but not in npgsql, the problem has to be somewhere in Npgsql. https://www.npgsql.org/doc/diagnostics/overview.html Maybe increasing the log level will lead to a solution. On Sun, Feb 11, 2024 at 6:13 PM wrote: > Thanks, Adrian, for the suggestion, but same pro

Re: Safest pgupgrade jump distance

On Mon, Feb 12, 2024 at 5:08 AM Dávid Suchan wrote: > Hi, I was wondering what is the safest pg_upgrade version upgrade distance > going from 9.6 version. Do I need to go version by version or I can go from > 9.6 to 15? We have a very huge database(TBs) with one replication server, > so we will f

Re: How to do faster DML

On Mon, Feb 12, 2024 at 3:23 PM veem v wrote: [snip] > So it looks like the fixed length data type(like integer, float) should be > the first choice while choosing the data type of the attributes > wherever possible, as these are native types. > Correct. > (Like choosing "Integer/float" over "

Re: Compressing large column by moving it to a unique table

On Mon, Feb 12, 2024 at 10:12 PM Adrian Garcia Badaracco < adr...@adriangb.com> wrote: > I am using Timescale so I'll be mentioning some timestamp stuff but I > think this is a general postgres question for the most part. > > I have a table with some fixed, small columns (id, timestamp, etc) and a

Re: Compressing large column by moving it to a unique table

1. Show us the PG version, view definition, the exact query that's slow, and the EXPLAIN (ANALYZE). 2. Presumably there's an index on each table's *_hash column? On Tue, Feb 13, 2024 at 8:48 AM Adrian Garcia Badaracco wrote: > Thank you for the reply Ron. > > Yes there

Re: MAT. VIEW security problems and PG 10-11 versions?

On Tue, Feb 13, 2024 at 3:44 AM Daniel Gustafsson wrote: > > On 13 Feb 2024, at 08:56, Durumdara wrote: > > > But maybe that's because PG 10 and 11 are no longer supported - and not > because they aren't affected by the issues. > > EOL versions do not recieve security updates and are not verifie

Re: Compressing large column by moving it to a unique table

; ); > -- > https://app.pgmustard.com/#/explore/f05de3e8-ca8d-4404-ad33-69f103a3b85a > > > -- A relatively selective query > explain (analyze, buffers, verbose, settings, format json) > SELECT * > FROM joined_view > WHERE ( > get_byte(attributes, 4) < 100 > AND &g

Re: How to do faster DML

On Tue, Feb 13, 2024 at 4:17 PM veem v wrote: [sni[] > One question here, if we have defined one column as a fixed length data > type "integer" and slowly we noticed the length of data keeps increasing > (in case of a sequence generated PK column which will keep increasing), and > we want to alte

Re: Postgres pg_cron extension

On Wed, Feb 14, 2024 at 10:23 AM Greg Sabino Mullane wrote: > Yes, definitely the wrong forum. RDS is not Postgres, and "parameter > groups" is an Amazon thing. A quick web search would seem to indicate that > the default group is replaced by the custom one, rather than enhancing it. > But really

Re: PITR

On Wed, Feb 14, 2024 at 1:39 PM Yongye Serkfem wrote: > Hi, > I hope you are all doing well. I am trying to do PITR on Postgresql v12. > Now that the recovery.conf file is not available in this version, where > should I set the recovery_target_time? I checked the Postgresql.conf file > and can't

Re: How to do faster DML

On Wed, Feb 14, 2024 at 11:58 PM veem v wrote: > > > On Thu, 15 Feb 2024 at 00:43, Adrian Klaver > wrote: > >> It depends: >> >> https://www.postgresql.org/docs/current/sql-altertable.html >> >> "Adding a column with a volatile DEFAULT or changing the type of an >> existing column will require t

Re: Encryption Options

The phrases "personal information" and "data at rest encryption" strongly indicate PCI, or something similar. On Fri, Feb 16, 2024 at 12:20 PM Greg Sabino Mullane wrote: > You need to clearly define your threat model. What exactly are you > defending against? What scenario do you want to avoid?

Re: Encryption Options

On Fri, Feb 16, 2024 at 1:53 AM sud wrote: > Hello Friends, > > We are newly moving to postgres database (yet to decide if it would be an > on premise one or AWS aurora postgres). However , we want to understand > what encryption / decryption techniques are available in the postgres > database.

Re: Encryption Options

> https://www.varonis.com/blog/pci-dss-requirements > > Agreed. The on-premise vs aurora will take a different approach for > catering to above needs. We are currently evaluating , what would be the > possible options in each of these cases? and if this would be a factor in > choosing t

Re: PostgreSQL Guard

On Tue, Feb 27, 2024 at 3:43 AM Jason Long wrote: > Hello, > I have some questions about the PostgreSQL database: > > 1- If I want to distribute the PostgreSQL database on several servers at > the same time. what should I do? Something similar to high availability. > HA replication is native in

Re: PostgreSQL Guard

I used this web page to implement hot standby via physical streaming. This command sets up everything for you: pg_basebackup \ --pgdata=$PGDATA \ --dbname=service=basebackup \ --verbose --progress \ --checkpoint=fast \ --write-recovery-co

Re: PostgreSQL Guard

to transfer the database of a website like Amazon.com to > a new server and delete the old one. Many users are buying and selling on > this website at the same time and it is not possible to turn off the > server. What do you do to move a database to another server? > > On Tue, Feb 27

Re: PostgreSQL Guard

he steps the same for other Linux distributions like Debian? > > On Wed, Feb 28, 2024 at 9:29 AM, Ron Johnson > wrote: > As before, I encourage you to read > https://www.tecmint.com/configure-postgresql-streaming-replication-in-centos-8/ > . > > On Tue, Feb 27, 2024 at 3:48

Re: Non-Stored Generated Columns

On Wed, Feb 28, 2024 at 2:11 PM Tom Lane wrote: > Dominique Devienne writes: > > Views can have foreign-keys? > > Surely you'd put the FK on the underlying table. > > > Generated view columns be indexed? > > You want an index on a virtual column? Sure, just build an expression > index (on the u

Re: How to add columns faster

On Sun, Mar 3, 2024 at 2:06 PM yudhi s wrote: > Hello, > We have an application in which the tables will have approx ~200 columns > in some of the big transaction tables when we will be serving all the > business use cases. Currently it has ~100 columns to serve current business > use cases to st

Re: v11.5- v15.3 upgrade (linux)

On Wed, Mar 6, 2024 at 7:19 PM David Gauthier wrote: > Hi: > I'm a PG user in a big corp with an IT dept that administers a PG > server/instance that I use. It's an old install, v11.5, and we need to > upgrade to v15.3. They want to bring the upgraded DB up on a new linux vm > which has OS upgr

Re: v11.5- v15.3 upgrade (linux)

t. But it would only make sense > if it's a significant win and not just options/features that we don't use. > > On Thu, Mar 7, 2024 at 12:53 AM Ron Johnson > wrote: > >> On Wed, Mar 6, 2024 at 7:19 PM David Gauthier >> wrote: >> >>> Hi: &

Re: update to 16.2

On Fri, Mar 8, 2024 at 5:01 AM Matthias Apitz wrote: > El día viernes, marzo 08, 2024 a las 12:56:16 -0800, Christophe Pettus > escribió: > > > > > > > > On Mar 8, 2024, at 00:53, Matthias Apitz wrote: > > > It does not say definitely that for all other versions a dump/restore > is > > > require

Re: Windows service randomly stops with no indication why

On Fri, Mar 8, 2024 at 9:17 AM Jay Madren wrote: > Running PostgreSQL 15.6 on Windows Server 2022. The database service > randomly just stops and the Windows Service auto-restart options don't kick > in. The stop is unexpected (not a controlled shut down) because after > restarting the service th

Re: creating a subset DB efficiently ?

On Fri, Mar 8, 2024 at 11:22 AM David Gauthier wrote: > Here's the situation > > - The DB contains data for several projects. > - The tables of the DB contain data for all projects (data is not > partitioned on project name or anything like that) > - The "project" identifier (table column) ex

Re: Seeing high query planning time on Azure Postgres Single Server version 11.

On Sat, Mar 9, 2024 at 7:18 AM hassan rafi wrote: > Hi team, > > We are seeing unusually high query planning times on our Postgres server. > I am attaching a few query plans. > Postgresql version number? Rows in the tables? System load?

Re: Seeing high query planning time on Azure Postgres Single Server version 11.

On Sun, Mar 10, 2024 at 1:34 PM Greg Sabino Mullane wrote: > > On Sat, Mar 9, 2024 at 1:57 PM hassan rafi > wrote: > >> Would upgrading to the latest version of Postgres potentially solve the >> issue? >> > > Potentially, yes, but the only one who can answer that for sure is you. > Upgrade to 11

Re: Simple way to simulate a bug in logical replication

On Tue, Mar 12, 2024 at 11:16 AM Avi Weinberg wrote: > Hi All, > > > > I think I hit a bug in logical replication in version 15.2. > But 15.6 is the latest version. Maybe it's been fixed since then.

Re: Postgres database encryption

On Tue, Mar 19, 2024 at 9:22 AM Johnathan Tiamoh wrote: > Hello, > > > Good morning. Please I wish to find out the best way of encrypting a > Postgres database. > > This could either be encrypting a whole database ( I don’t if this is > possible), a specific table and a specific tablespace. > Lo

Re: How to store in hours:minutes:seconds where hours may be bigger than 24

On Tue, Mar 19, 2024 at 10:05 PM Celia McInnis wrote: > Hi: > > I want to store times in a database as hours:minutes:seconds where hours > can be greater than 24. How do I do this? I will want to be able to add > such times. > Try the INTERVAL data type.

Re: soft lockup - CPU#16 stuck for 3124s! [postmaster:2273]

On Fri, Mar 22, 2024 at 1:27 PM Tom Lane wrote: > Matthias Apitz writes: > > We have a PostgreSQL 15.1 server in production at a customer for some > > weeks (migrated from an older version) on SuSE SLES 15. > > > The customer is facing machine locks and before the Linux server does > > not respo

Re: Statistics information.

On Sat, Mar 23, 2024 at 12:33 AM arun chirappurath wrote: > Dear All, > > Apologies the way i am asking question as i am more a SQL Server person > and a new postgre man.. > > I have used a query store in SQL server. it provides me option to load > statistics data to temp table and get below imp

Re: Is this a buggy behavior?

On Mon, Mar 25, 2024 at 9:49 AM Christophe Pettus wrote: > > > > On Mar 25, 2024, at 02:50, Thiemo Kellner > wrote: > > My bad. I was under the impression that the create table statement was > an atomic process/transaction with all its bells and whistles for > constraints and keys, instead of a

Re: Table level restore in postgres

On Thu, Mar 28, 2024 at 2:27 PM arun chirappurath wrote: > Dear all, > > I am a new bie in postgres world > > Suppose I have accidently deleted a table or deleted few rows ,is it safe > to drop this table and restore just this table from custom backup to same > database? > By "custom backup"

Re: Cron not running

On Thu, Mar 28, 2024 at 5:28 PM Lok P wrote: > Hello All, > In RDS postgres(version 15.4), we have scheduled partition maintenance > through pg_partman and it's scheduled through pg_cron as below. The same > script has been executed in dev and test environments, and we are seeing > the cron job i

Re: how to check if the license is expired.

On Sat, Mar 30, 2024 at 9:15 AM 黄宁 wrote: > I want to develop a postgresql paid extension, then there is a local > license file, how do I check if the license file is expired, check it once > at each api execution, will that affect the performance of the api, is > there any other way? > What you

Re: Multiple COPY statements for one table vs one for ~half a billion records

On Thu, Apr 4, 2024 at 2:04 PM Carl L wrote: > Hi there, > > I have around half a billion records that are being generated from a back > end that are split into 80 threads (one per core) and I'm performing a copy > from memory ( from stdin binary) into Postgres from each of these threads - > i.e.

What is referential_action?

Four times, the word "referential_action" is used on this page, but it's never mentioned what the possible referential actions are. Am I missing something? https://www.postgresql.org/docs/14/sql-altertable.html [ CONSTRAINT constraint_name ] [snip] PRIMARY KEY index_parameters | REFERENCES r

Re: What is referential_action?

On Mon, Apr 8, 2024 at 9:41 AM David G. Johnston wrote: > On Monday, April 8, 2024, Ron Johnson wrote: > >> Four times, the word "referential_action" is used on this page, but it's >> never mentioned what the possible referential actions are. >>

PL/pgSQL techniques better than bash for dynamic DO?

PG 9.6.11, if relevant, migrating to PG 14 Real Soon Now. I must purge the oldest X period of records from 70 tables, every Sunday. The field name, interval (X days or months) and date (CURRENT_DATE or CURRENT_TIMESTAMP) varies for each table. Thus, I put all the relevant data in a tab-separated v

Recursively trace all Foreign Key "referenced by" tables?

The attached PG function dba.get_fk_referenced_by() has been tested on PG 9.6 and 14. A recursive bash function (also attached) calls the PG function, and displays the whole tree of tables that the table in question depends on. Output also attached. Is there a better way to do this? (I'm limited

Re: Failing streaming replication on PostgreSQL 14

On Mon, Apr 15, 2024 at 2:53 AM Nicolas Seinlet wrote: > Hello everyone, > > Since I moved some clusters from PostgreSQL 12 to 14, I noticed random > failures in streaming replication. I say "random" mostly because I haven't > got the source of the issue. > > I'm using the Ubuntu/cyphered ZFS/Pos

Re: efficiency random values / sequential ID values in indexes

On Mon, Apr 15, 2024 at 6:05 AM Sanjay Minni wrote: > Hi > > Is there any appreciable difference in using random values or sequential > values in indexes > > in a multi tenanted application there is a choice that the single field > ID's value is totally random / UUID or the numbers are created wi

Re: [help] Error in database import

On Fri, Apr 19, 2024 at 11:58 PM Tu Ho wrote: > Hi, > > I am currently having a project where I need to combine 2 large database. > I was not however able to import an excel file .csv into the database. The > error was ERROR: syntax error at or near "OIDS" LINE 1: ...ing Site" , > "International

Re: error in trigger creation

On Sun, Apr 21, 2024 at 2:58 PM yudhi s wrote: > the partition drop from parent is taking longer as it scans all the > partitions of the child table > Does the relevant supporting index exist on the child table?

CLUSTER vs. VACUUM FULL

PG 14.11 on RHEL8 Why is VACUUM FULL recommended for compressing a table, when CLUSTER does the same thing (similarly doubling disk space), and apparently runs just as fast? My tests: Table: CDSLBXW.public.log Time 1 Time 2 Time 3 secssecssecs VACUUM FULL 44.2

Re: CLUSTER vs. VACUUM FULL

On Sun, Apr 21, 2024 at 6:45 PM Tom Lane wrote: > Ron Johnson writes: > > Why is VACUUM FULL recommended for compressing a table, when CLUSTER does > > the same thing (similarly doubling disk space), and apparently runs just > as > > fast? > > CLUSTER makes the

Re: CLUSTER vs. VACUUM FULL

On Sun, Apr 21, 2024 at 6:45 PM Tom Lane wrote: > Ron Johnson writes: > > Why is VACUUM FULL recommended for compressing a table, when CLUSTER does > > the same thing (similarly doubling disk space), and apparently runs just > as > > fast? > > CLUSTER makes the

<    8   9   10   11   12   13   14   15   16   17   >