Since it's a 24x7 app, you have database replication, virtual IPs and a
fail-over manager in case a server crashes?
Anyway, read through the PG 15 release notes. If none really affect you,
then stay on 15.3. You're certain to miss *something*, though, or not
understand the ramifications. And be
On Fri, Mar 7, 2025 at 10:59 AM Atul Kumar wrote:
> Hi,
>
> Please could you help me by sharing any redhat linux command through which
> I can count the no. of wal files and no. of ".ready" files generated in
> last 10 minutes.
>
What problem are you trying to solve?
--
Death to , and butter s
On Thu, Mar 6, 2025 at 3:12 AM Laurenz Albe
wrote:
> [redirecting to pgsql-general]
>
> On Thu, 2025-03-06 at 07:39 +, Abraham, Danny wrote:
> > I have many customers using PG 15.3 happily, and I cannot just snap
> upgrade them all to 15.12.
>
> Why do you think you cannot do that?
> In the l
Chandran,
1. For PITR, you should use a tool like PgBackRest. It handles all
$PGDATA and WAL archiving. It's multithreaded, too,
2. pg_restore is just for logical backups.
3. Streaming Replication is for *hot standby*, not backups.
On Tue, Mar 4, 2025 at 10:00 AM chandan Kumar
wr
On Wed, Mar 5, 2025 at 9:44 PM me nefcanto wrote:
> I once worked with a monolithic SQL Server database with more than 10
> billion records and about 8 Terabytes of data. A single backup took us more
> than 21 days. It was a nightmare.
>
25 years ago (meaning *much* slower hardware), I managed a
On Thu, Mar 6, 2025 at 4:59 AM Dominique Devienne
wrote:
> On Thu, Mar 6, 2025 at 10:38 AM Ron Johnson
> wrote:
> > This statement runs great from the psql prompt. Does exactly what I
> want.
> > select datname from pg_database WHERE datname !~ 'template|po
On Thu, Mar 6, 2025 at 10:47 AM Igor Korot wrote:
> Hi,
>
> On Thu, Mar 6, 2025, 7:32 AM Greg Sabino Mullane
> wrote:
>
>> On Wed, Mar 5, 2025 at 9:44 PM me nefcanto wrote:
>>
>>> Anyway, that's why I asked you guys. However, encouraging me to go back
>>> to monolith without giving solutions on
On Thu, Mar 6, 2025 at 6:11 AM hubert depesz lubaczewski
wrote:
> On Thu, Mar 06, 2025 at 04:37:56AM -0500, Ron Johnson wrote:
> > This statement runs great from the psql prompt. Does exactly what I
> want.
> > select datname from pg_database WHERE datname !~ 'template|p
Do you *currently* take regular backups?
We'd be glad to show you how to take regular logical backups.
On Tue, Mar 11, 2025 at 10:29 AM mark bradley
wrote:
> An "interesting" effect of reindexing is that all the records that were
> dups in the nodes table were deleted, both copies.
>
> Also, al
On Tue, Mar 11, 2025 at 12:23 PM Paul Foerster
wrote:
> Hi,
>
> we are considering changing the PostgreSQL platform from SUSE SLE to Red
> Hat. To keep service interruptions as short as possible, the idea is to set
> up a streaming replication from the SUSE server to be replaced to a
> temporary
When you know that the SELECT statement runs on the local machines, then on
each server, run these SQL statements:
SHOW search_path;
SELECT * FROM pg_extension;
On Fri, Feb 28, 2025 at 12:26 PM Krishnakant Mane
wrote:
>
> On 2/28/25 22:53, Ron Johnson wrote:
>
>
> Differ
On Fri, Feb 28, 2025 at 11:41 AM Krishnakant Mane
wrote:
> Hello all.
>
> I am not able to use create_immv in postgresql 16.6 even after installing
> it and doing create extention.
>
> I did a git clone of the repository and then make sudo make install to
> install it.
>
> The issue is not happen
On Fri, Feb 28, 2025 at 6:50 AM KK CHN wrote:
> List
>
> postgres=# SELECT PID, now() - pg_stat_activity.query_start AS duration,
> query, state FROM pg_stat_activity WHERE (now() - pg
> _stat_activity.query_start) > interval '5 minutes' AND state = 'active';
> pid |duration |
On Tue, Mar 11, 2025 at 9:48 PM Siraj G wrote:
> Hello Experts!
>
> What are the features available in Postgresql to hide PII (personal
> identifiable information) from the Admin team? Like in Oracle we have data
> vault and data redaction, I am looking for similar features in
> PostgreSQL.We do
On Wed, Mar 12, 2025 at 4:16 PM Paul Foerster
wrote:
> Hi Ron,
>
> > On 12 Mar 2025, at 17:59, Ron Johnson wrote:
> >
> > Developers making DDL changes on production databases?
>
> Of course not. But I can't block developer databases. That'd make a few
On Wed, Mar 5, 2025 at 12:36 PM mark bradley
wrote:
> Although I did not enter them, somehow duplicate primary key values have
> appeared in one of my tables.
>
Is it a text/varchar column? Has the distro been upgraded "recently", or
maybe streamed from an older Linux system to a newer Linux sy
On Wed, Mar 5, 2025 at 10:53 AM Greg Sabino Mullane
wrote:
> On Wed, Mar 5, 2025 at 10:22 AM Marc Millas
> wrote:
>
>> Then the flow contains a single line: \. to my understanding this means
>> end of the copy
>>
>> but, Postgres generates an error : invalid input syntax for type numeric
>> "\."
On Wed, Mar 5, 2025 at 10:22 AM Marc Millas wrote:
> Hi,
> on a Postgres 16 DB running on a redhat 8.5 x86 machineI want to input
> some data using copy from stdin
> so.. more that 700 000 lines goes well.
> Then the flow contains a single line: \.
> to my understanding this means end of the copy
records
by specifying another column with a different value;
On Wed, Mar 5, 2025 at 6:14 PM mark bradley wrote:
> Hi Ron,
>
> The key is an integer. I'm using pGAdmin4 and recently updated to the
> latest version.
>
> The records are not all identical, some have NULL values
On Tue, Mar 11, 2025 at 2:35 PM Paul Foerster
wrote:
> Hi Devrim, Thomas, Adrian, Ron, Joe,
>
> answering to myself as answering to five postings in one go is impossible.
> 🤣
>
> > Are there any obstacles that definitely make that a no-go? Do I risk
> corruption?
On Wed, Mar 12, 2025 at 12:48 PM Paul Foerster
wrote:
> Hi Christophe,
>
> > On 12 Mar 2025, at 12:16, Christophe Pettus wrote:
> >
> > You *can* apply DDL while logical replication is going on, as long as
> you do so in a disciplined way. This generally means applying it to the
> subscriber be
On Wed, Feb 12, 2025 at 3:29 PM Adrian Klaver
wrote:
> On 2/12/25 12:03, Ron Johnson wrote:
> > On Wed, Feb 12, 2025 at 2:29 PM Tom Lane > <mailto:t...@sss.pgh.pa.us>> wrote:
>
> > There's got to be something in your shell config which says "use
On Wed, Feb 12, 2025 at 1:50 PM Rich Shepard
wrote:
> My web searches suggest that using the psql command line I'm limited to
> moving the cursor one character at a time. Is there a way to use a small
> editor, e.g., joe on linux, to move by words or to the begining and end of
> the line?
The P
On Sun, Feb 16, 2025 at 8:13 AM Y_Bharani_mbsv wrote:
> Team
> Good Morning.
> As part of DB upgrade from EC2 - PGS - community Edn Ver 13.X to 14.X
> I followed steps of "pg_upgrade" and had executed the last step (post
> successful db migration)
>
> vacuumdb --analyze-in-stages
>
> and later no
On Mon, Feb 24, 2025 at 5:50 PM Yongye Serkfem wrote:
> Hi Everyone!
> I am having a series of idle connections and unable to delete them with a
> single command. Any help in realizing this would be greatly appreciated.
>
This will kill idle connections older than two hours:
select pid, pg_termi
On Mon, Feb 24, 2025 at 6:53 AM Matthias Apitz
wrote:
[snip]
> pgsql -Usisis sisis
>
> sisis=# REINDEX (VERBOSE) DATABASE sisis;
> sisis=# ALTER COLLATION "de_DE.utf8" REFRESH VERSION;
> ALTER COLLATION
>
> Correct?
>
>
Just reindex those with text columns.
create or replace view dba.all_indices
On Thu, Feb 20, 2025 at 8:07 AM Dominique Devienne
wrote:
> Hi. I've just read
> https://www.dbi-services.com/blog/postgresql-18-virtual-generated-columns/
>
> and I'm wondering whether there will be a way to ALTER existing STORED
> generated columns, to be virtual? W/o rewriting the whole table
On Wed, Feb 26, 2025 at 9:06 AM Greg Sabino Mullane
wrote:
> On Wed, Feb 26, 2025 at 2:21 AM sivapostg...@yahoo.com <
> sivapostg...@yahoo.com> wrote:
>
>> issue in PostgreSQL 15.7
>>
>
> Still missing a ton of bug fixes - Postgres 15 is on version 15.12. Try to
> get that upgraded.
>
And it tak
On Wed, Apr 2, 2025 at 1:32 PM Dimitrios Apostolou wrote:
> Hello list.
>
> My database includes one table with 1000 partitions, all of them rather
> sizeable. I run:
>
>pg_restore -j12 --no-tablespaces --disable-triggers --exit-on-error
> --no-owner --no-privileges -n public -d newdb custom_
On Wed, Mar 19, 2025 at 1:06 PM Siraj G wrote:
> Hello!
>
> I have a PG (v16) instance which is occupying around 1TB of storage. Out
> of this, around 350GB is occupied by the table pg_catalog.pg_attribute.
> Why is the catalog table's size so big?
>
> Here are the sizes:
>
> pg_attribute
> 338 G
On Wed, Apr 2, 2025 at 12:29 PM Danny Im wrote:
> Hello,
>
> I am having issues trying to install the postgis30_13 package on a RHEL9
> host. Attempting to install the package gives me this error:
>
> $ sudo dnf install postgis30_13
> Updating Subscription Management repositories.
> Zabbix 6.0 LT
On Mon, May 19, 2025 at 12:12 PM Dias Thomas wrote:
> Hello all,
> Could i get a help, postgres 1 billion records indexed table, search
> speed in a normal machine, no parallel processing ... for a knowledge ??
>
1. How big are the records?
2. How big are the keys?
3. What is a normal machine?
small table
> indexed on that char - 20 field , say 16 gb ram, amd normal power processor
> no parallel processing, to know it, the speed in single processor
>
> On Mon, May 19, 2025 at 9:55 PM Ron Johnson
> wrote:
>
>> On Mon, May 19, 2025 at 12:12 PM Dias Thomas
>> wr
Hi,
> I mean any manual
> I have a small code, that searches some 1 billion records in 1
> seconds thru index simple case -- I want to test that with postgres
>
>
> On Mon, May 19, 2025 at 10:14 PM Ron Johnson
> wrote:
>
>> Spinning rust? SSD? NVMe? SATA? SCSI
Foreign key "code tables" are ubiquitous in good database design; another
example is zip/postal codes and cities. Statuses are another standard
usage. Thus, what you're doing is absolutely bog-standard. (In your case,
I would add "taxonomy_edition" to the species table. You get more
information
On Thu, Jun 19, 2025 at 2:23 AM sivapostg...@yahoo.com <
sivapostg...@yahoo.com> wrote:
> Hello,
> May be a very basic question. We all here are new to Linux / PostgreSQL
>
> Ubuntu Server 22.04
> PostgreSQL 15
> PgAdmin4 6.16
>
> When I run the following query in pg_admin
>
> Select today_now
>
On Fri, Jun 6, 2025 at 8:57 AM Laurenz Albe
wrote:
> On Fri, 2025-06-06 at 14:10 +0530, Durgamahesh Manne wrote:
> > Can we generate a fill factor for tables that have delete ops ?
> >
> > Does the fill factor really work and help to minimize the bloat for
> tables that have delete ops?
> >
> > I
On Fri, Jun 6, 2025 at 4:36 AM Durgamahesh Manne
wrote:
> Hi Team
>
> Can we generate a fill factor for tables that have delete ops ?
>
> Does the fill factor really work and help to minimize the bloat for tables
> that have delete ops?
>
> I have parent table with weekly partitions So for every
On Wed, Jun 4, 2025 at 1:53 PM Adrian Klaver
wrote:
>
>
> On 6/4/25 9:39 AM, Dominique Devienne wrote:
> > On Wed, Jun 4, 2025 at 5:29 PM Adrian Klaver
> wrote:
> >>> $ ./myruntests.csh tests_pdgm_schemas -t "..." --pq-version
> >>> Connected to ... (17.4, server 18.0)
> >>
> >> What is '(17.4,
Note also that 15.6 is about 18 months old. Upgrading really does only
take a few minutes, if you download the binaries before installation.
On Wed, Jun 4, 2025 at 2:37 PM Matthew Tice wrote:
>
> > On Jun 3, 2025, at 6:23 PM, David Rowley wrote:
> >
> > On Wed, 4 Jun 2025 at 07:22, Matthew Tic
On Tue, Jun 10, 2025 at 4:53 PM Logan Grosz wrote:
> Hi,
>
> I have a `DOMAIN` on `JSONB`. I need to verify the shape of the JSON, so I
> have a check expression written in PL/pgSQL. The docs say
>
> > Each constraint must be an expression producing a Boolean result
>
> Would it be correct to rai
On Sat, Jun 7, 2025 at 7:53 AM Laurenz Albe
wrote:
> On Fri, 2025-06-06 at 09:59 -0400, Ron Johnson wrote:
> > On Fri, Jun 6, 2025 at 8:57 AM Laurenz Albe
> wrote:
> > > On Fri, 2025-06-06 at 14:10 +0530, Durgamahesh Manne wrote:
> > > > Can we generate a fill f
On Thu, Jun 12, 2025 at 9:24 AM Laurenz Albe
wrote:
> On Thu, 2025-06-12 at 15:14 +0200, Pavol Sekeres wrote:
> > We recently updated our production database to PostgreSQL 12.22 from the
> 9.6.24 version.
> > We didn't want to make a big jump.
>
> But you should have. v12 is out of support.
>
T
On Thu, Jun 12, 2025 at 9:14 AM Pavol Sekeres
wrote:
> Hi,
>
> We recently updated our production database to PostgreSQL 12.22 from the
> 9.6.24 version.
>
Will you soon make another jump to a supported version?
> We didn't want to make a big jump.
> It is around 2 TB in size with one stand-by
On Sat, Jul 5, 2025 at 2:11 PM Adrian Klaver
wrote:
> On 7/5/25 09:52, Pierre Fortin wrote:
> > Hi,
> >
> > [Hope this gets through after dumping DKIM-ignorant mail provider.]
> >
> > Wanting to upgrade from:
> > PostgreSQL 15.13 on x86_64-mageia-linux-gnu,
> > compiled by gcc (Mageia 15.1.0-1.mg
On Sat, Jul 5, 2025 at 2:24 PM wrote:
> On Sat, 5 Jul 2025 11:11:32 -0700 Adrian Klaver wrote:
>
> >On 7/5/25 09:52, Pierre Fortin wrote:
>
> >> Wanting to upgrade from:
> >> PostgreSQL 15.13 on x86_64-mageia-linux-gnu,
> >> compiled by gcc (Mageia 15.1.0-1.mga10) 15.1.0, 64-bit
> >> to:
> >> PG
On Sat, Jul 5, 2025 at 3:19 PM Pierre Fortin wrote:
> On Sat, 05 Jul 2025 14:30:20 -0400 Tom Lane wrote:
>
> Forgive my ignorance; always trying to learn more... :)
>
> >p...@pfortin.com writes:
> >> On Sat, 5 Jul 2025 11:11:32 -0700 Adrian Klaver wrote:
> >>> How did you measure above?
> >
> >>
On Wed, Jul 2, 2025 at 8:21 AM Durumdara wrote:
> Hello!
>
> I have to store some fetched records into two lists (arrays) to work with
> them.
>
There's almost certainly a way to do what you need done without using
arrays. Might require a bit of rethinking, though.
--
Death to , and butter sa
Using PgBackRest might be more convenient, since it handles everything you
need, is multithreaded, never removes too many wal files, compresses files
if you want and also encrypts them if you want.
(In 2025, I also leave pg_wal on the same mount point as data/. Disk space
is plentiful and it's ju
On Fri, Jun 27, 2025 at 9:35 AM Zechman, Derek S
wrote:
>
>
> We recently performed an upgrade from pg14 (14.18) to pg16 (16.9) and
> performed the analyze-in-stages post upgrade. It has been noticed that
> some plans changed to use hash joins instead of nested loops. Further
> investigation fo
On Sat, Jun 28, 2025 at 9:59 AM Peter J. Holzer wrote:
> On 2025-06-27 19:00:36 +0200, raphi wrote:
> >
> >
> > Am 26.06.2025 um 14:27 schrieb Peter J. Holzer:
> > > On 2025-06-25 17:55:12 +0200, raphi wrote:
> > > > Am 25.06.2025 um 17:33 schrieb Peter J. Holzer:
> > > > > On 2025-06-25 14:42:26
On Fri, Jul 4, 2025 at 5:50 AM wrote:
> > One coincidence is that we started seeing the first symptoms of this
> > around the same time as libicu was updated with a security patch.
> > However, postgres hasn’t been restarted and doesn’t reference the
> > new version in its process maps. Plus stat
On Fri, Jul 4, 2025 at 9:49 AM Erik Johnston wrote:
> Hi, a quick update:
>
> - We have discovered that the corruption was present from before libicu
> update.
> - We ran `pg_amcheck --index state_groups_state_type_idx --heapallindexed
> matrix`, which returned nothing
> - We believe that means t
On Wed, Jul 9, 2025 at 10:59 AM Greg Sabino Mullane
wrote:
> On Wed, Jul 9, 2025 at 9:57 AM Alpaslan AKDAĞ
> wrote:
>
>> Is it expected behavior that users created with scram-sha-256 passwords
>> can still connect via md5 in pg_hba.conf?
>
>
> Yes. From the docs:
>
>> To ease transition from the
On Wed, Jul 9, 2025 at 11:11 AM Adrian Klaver
wrote:
> On 7/9/25 06:56, Alpaslan AKDAĞ wrote:
> > Hello all
> >
>
> > As a result, some users are able to connect, while others cannot.
>
> What client is being used and what version of said client?
>
This is a salient point:clients from the pre-PG
On Fri, Jul 11, 2025 at 2:44 PM Greg Sabino Mullane
wrote:
> Looks like someone testing out the fake Postgres CVE 2019-9193
>
> https://nvd.nist.gov/vuln/detail/CVE-2019-9193
>
> See for example:
>
> https://packetstorm.news/files/id/166540
>
> But certainly the first step is finding out who or w
On Wed, Jul 9, 2025 at 11:26 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wed, Jul 9, 2025 at 8:09 AM Ron Johnson
> wrote:
>
>> That requires setting the password to null and then recreating the
>> password, no?
>>
>
> You might want
On Fri, Jul 11, 2025 at 5:01 AM Dominique Devienne
wrote:
> On Thu, Jul 10, 2025 at 7:11 PM Ron Johnson
> wrote:
> > On Thu, Jul 10, 2025 at 12:26 PM Adrian Klaver <
> adrian.kla...@aklaver.com> wrote:
> >> On 7/10/25 04:48, Dominique Devienne wrote:
> >&g
On Thu, Jul 10, 2025 at 12:26 PM Adrian Klaver
wrote:
> On 7/10/25 04:48, Dominique Devienne wrote:
>
> > Seems so logical to me, that these hashing functions were available
> > are aggregates, I can't be the first one to think of that, can it?
> >
>
> I've been on this list since late 2002 and I
On Fri, Jul 11, 2025 at 8:20 AM gzh wrote:
> Dear,
>
> I am encountering an issue with the COPY command in PostgreSQL regarding
> the handling of line breaks in data fields. My PostgreSQL instance is
> installed on a Linux system.
>
> When I use the COPY command to export data to a CSV file and t
On Fri, Jul 11, 2025 at 10:46 AM Dimitrios Apostolou wrote:
>
> On Thu, 10 Jul 2025, Dimitrios Apostolou wrote:
>
> > Hello list,
> >
> > I have a database split across many tablespaces, with temp_tablespaces
> > pointing to a separate, less reliable device (single local NVMe drive).
> How
> > da
Quoting Tom's earlier email:
"(But I too *would not use Postgres-over-NFS for any critical data*.
Too many moving parts. It's tough enough to ensure crash safety
with local storage.)"
You're going through a lot of security effort to implement a Worst Practice.
On Wed, Jul 16, 2025 at 9:25 AM Amo
On Wed, Jul 16, 2025 at 8:42 PM Greg Sabino Mullane
wrote:
> On Wed, Jul 16, 2025 at 9:25 AM Amol Inamdar wrote:
>
>>
>>1. NFS mount point is for /nfs-mount/postgres (and permissions locked
>>down so that Postgres cannot create directories in here)
>>2. Postgres data directory is /nf
On Thu, Jul 17, 2025 at 8:55 PM Craig Ringer
wrote:
[snip]
>
> FS-based sizing isn't really enough
>
>
> Asking users to monitor at the filesystem level works, kind-of, but
> it'll lead to confusion due to WAL and temp files in simple installs.
> To get decent results they will n
1601 - 1664 of 1664 matches
Mail list logo
