Merging logical subscriptions

Hi, I have a question about an algorithm to “merge” two logical replication subscriptions. Here is the scenario: We have a server with many tables replicated using logical replication to downstream replicas. Each table is published with a separate publication. At the moment each replica uses a

Re: Support for dates before 4713 BC

Hi Nina Richards, Instead of using PostgreSQL's DATE or TIMESTAMP types, you can store years as NUMERIC or BIGINT values. You can manually represent dates before 4713 BC and build custom functions for date operations like addition, subtraction, or comparison. To facilitate comparisons or operatio

Re: Query performance issue

On Thu, Oct 17, 2024 at 3:06 AM Peter J. Holzer wrote: > On 2024-10-16 23:20:36 +0530, yudhi s wrote: > > Below is a query which is running for ~40 seconds. > [...] > > In the execution path below , the line number marked in bold are the top > lines > > for the IN and NOT IN subquery evaluation a

Re: how to know if the sql will run a seq scan

> > > > I don't have time now to create an example, but I can point you at: > > > https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE > > 5.12.2.2. Partition Maintenance > > "As an alternative to creating a new partition, it is sometimes more > convenient to c

Re: Help in dealing with OOM

Thanks Joe, I will set these kernel parameters. I also would like to highlight that the issue happened on SECONDARY. While the PRIMARY has less memory and computation in comparison to SECONDARY, not sure if there is anything wrong in the PgSQL. PRIMARY: 48vCPUs & 48GB memory SECONDARY: 64vCPUs &

Re: Backup

Hi Andy, I hope you're doing well. Based on your inquiry about PostgreSQL backups for your 100GB historical database with images, here are some suggestions that should help you achieve compressed, efficient backups without running into storage issues. *1. Use Custom Format with Compression* A mor

Re: Backup

Hi, you can refer the following link where many backup methods are mentioned according to your need. Backups in PostgreSQL. In PostgreSQL, different types of… | by Usman Khan | Aug, 2024 | Medium On Thu, 17 Oct 2024 at 00:37

Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

Hi, When you execute schema-altering operations on the publisher, these changes are not automatically replicated to the subscriber which causes the following error logical replication target relation "public.dummy_table" is missing replicated columns: "contact_email", "status", "phone_number", "us

Re: Support for dates before 4713 BC

David Rowley writes: > It's by no means a trivial thing to do, but it is possible to > implement new types in PostgreSQL [1]. If you invented your own type, > you could significantly widen the upper and lower bounds when compared > with the standard date type. However, you'd then have to reimplem

Re: Support for dates before 4713 BC

On Thu, 17 Oct 2024 at 01:26, Richards, Nina wrote: > Even though we know there was no calendar at that time, it would make our > work much easier if we could use data before 4713 BC in the same way. > Especially for statistical analyses and scientific dating methods (14C, > dendrochronology),

Re: Backup

I'm on Ver16 and yes Our database has image in a bytea field. Running on Win22 box... On Wed, Oct 16, 2024 at 5:49 PM Peter J. Holzer wrote: > On 2024-10-16 16:02:24 -0400, Ron Johnson wrote: > > On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios < > > a.mantz...@cloud.gatewaynet.com> wrote: >

Re: What are best practices wrt passwords?

"Peter J. Holzer" writes: > On Linux, unprivileged users can only see the environment of their own > processes since a *very* long time ago. Possibly even before Ubuntu even > existed. So I'm somewhat sceptical about that. Some other Unixes were > more permissive. I don't know what camp MacOS fall

Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

If this question is more suitable for another mailing list, please let me know. I've set up the following table on both publisher and subscriber, both are pg16: CREATE TABLE dummy_table ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, age INT, created_at TI

Re: Backup

On 2024-10-16 16:02:24 -0400, Ron Johnson wrote: > On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios < > a.mantz...@cloud.gatewaynet.com> wrote: > Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε: > On Wed, Oct 16, 2024 at 3:37 PM Andy Hartman > wrote: [...] > > Step 1: re

Re: What are best practices wrt passwords?

On Wed, Oct 16, 2024 at 11:27:15PM +0200, Peter J. Holzer wrote: > On 2024-10-16 09:50:41 -0700, Christophe Pettus wrote: > > > On Oct 16, 2024, at 09:47, Tom Lane wrote: > > > I believe it depends on your platform --- some BSDen are pretty > > > permissive about this, if memory serves. On a Linu

Re: Query performance issue

On 2024-10-16 23:20:36 +0530, yudhi s wrote: > Below is a query which is running for ~40 seconds. [...] > In the execution path below , the line number marked in bold are the top lines > for the IN and NOT IN subquery evaluation and they are showing "Actual time" > as >  Approx ~9 seconds and ~8 s

Re: What are best practices wrt passwords?

On 2024-10-16 09:50:41 -0700, Christophe Pettus wrote: > > On Oct 16, 2024, at 09:47, Tom Lane wrote: > > I believe it depends on your platform --- some BSDen are pretty > > permissive about this, if memory serves. On a Linux box it seems > > to work for processes owned by yourself even if you're

Re: Backup

On 10/16/24 12:52, Tomas Vondra wrote: On 10/16/24 21:37, Andy Hartman wrote: I am very new to Postgres and have always worked in the mssql world. I'm You didn't specify the Postgres version - that matters, because older pg_dump versions (before PG 16) do not support compression. Since PG 16

Re: Backup

Στις 16/10/24 23:02, ο/η Ron Johnson έγραψε: On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios wrote: Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε: On Wed, Oct 16, 2024 at 3:37 PM Andy Hartman wrote: I am very new to Postgres and have always worked in the mssql

Re: Backup

On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios < a.mantz...@cloud.gatewaynet.com> wrote: > Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε: > > On Wed, Oct 16, 2024 at 3:37 PM Andy Hartman > wrote: > >> I am very new to Postgres and have always worked in the mssql world. I'm >> looking for sugges

Re: Backup

On Wed, Oct 16, 2024 at 3:52 PM Tomas Vondra wrote: [snip] > You didn't specify the Postgres version - that matters, because older > pg_dump versions (before PG 16) do not support compression. Since PG 16 > you can use either -Fc or -Fd (instead of the tar format), and it'll > compress the output

Re: Backup

Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε: On Wed, Oct 16, 2024 at 3:37 PM Andy Hartman wrote: I am very new to Postgres and have always worked in the mssql world. I'm looking for suggestions on DB backups. I currently have a DB used to store Historical information that has imag

Re: Backup

On Wed, Oct 16, 2024 at 3:37 PM Andy Hartman wrote: > I am very new to Postgres and have always worked in the mssql world. I'm > looking for suggestions on DB backups. I currently have a DB used to store > Historical information that has images it's currently around 100gig. > > I'm looking to tak

Re: Backup

On 10/16/24 21:37, Andy Hartman wrote: > I am very new to Postgres and have always worked in the mssql world. I'm > looking for suggestions on DB backups. I currently have a DB used to > store Historical information that has images it's currently around 100gig.  > > I'm looking to take a monthly b

Backup

I am very new to Postgres and have always worked in the mssql world. I'm looking for suggestions on DB backups. I currently have a DB used to store Historical information that has images it's currently around 100gig. I'm looking to take a monthly backup as I archive a month of data at a time. I am

Re: serializable master and non-serializable hot standby: feasible set up?

On Tue, Oct 15, 2024 at 9:23 PM Laurenz Albe wrote: > On Tue, 2024-10-15 at 16:27 -0700, Jacob Biesinger wrote: > > *would you* expect to be able to stand up a `repeatable read` replica > against a > > `serializable` master? My expectation is that you'd simply change the > setting in > > a .conf

Re: serializable master and non-serializable hot standby: feasible set up?

> > Just out of curiosity, what’s the use case for this? > > We use the read-only replica as a data source for analytics workflows. The setup allows us to have fresh data without affecting performance of the production environment.

Re: What are best practices wrt passwords?

> On Oct 16, 2024, at 10:50 AM, Christophe Pettus wrote: > > > >> On Oct 16, 2024, at 09:47, Tom Lane wrote: >> I believe it depends on your platform --- some BSDen are pretty >> permissive about this, if memory serves. On a Linux box it seems >> to work for processes owned by yourself eve

Re: What are best practices wrt passwords?

Achilleas Mantzios writes: > Στις 16/10/24 19:47, ο/η Tom Lane έγραψε: >> I believe it depends on your platform --- some BSDen are pretty >> permissive about this, if memory serves. On a Linux box it seems > As of FreeBSD smadevnu 14.1-RELEASE-p5 , only the user and root can view > the env, suc

Re: What are best practices wrt passwords?

Στις 16/10/24 19:47, ο/η Tom Lane έγραψε: Bruce Momjian writes: On Wed, Oct 16, 2024 at 06:16:57PM +0200, mb...@mbork.pl wrote: `ps auxe` shows all processes with their environments, no? I think that only shows for super-user. I believe it depends on your platform --- some BSDen are pretty

Re: Query performance issue

On 10/16/24 10:50 AM, yudhi s wrote: Hi, Below question got in one discussion.Appreciate any guidance on this. Below is a query which is running for ~40 seconds. As it's a query which is executed from UI , we were expecting it to finish in <~5 seconds. It has a "IN" and a "NOT IN" subquery

Query performance issue

Hi, Below question got in one discussion.Appreciate any guidance on this. Below is a query which is running for ~40 seconds. As it's a query which is executed from UI , we were expecting it to finish in <~5 seconds. It has a "IN" and a "NOT IN" subquery , from the execution path it seems the tota

Re: What are best practices wrt passwords?

> On Oct 16, 2024, at 09:47, Tom Lane wrote: > I believe it depends on your platform --- some BSDen are pretty > permissive about this, if memory serves. On a Linux box it seems > to work for processes owned by yourself even if you're not superuser. I just tried it on an (admittedly kind of o

Re: What are best practices wrt passwords?

Bruce Momjian writes: > On Wed, Oct 16, 2024 at 06:16:57PM +0200, mb...@mbork.pl wrote: >> `ps auxe` shows all processes with their environments, no? > I think that only shows for super-user. I believe it depends on your platform --- some BSDen are pretty permissive about this, if memory serves.

Re: how to know if the sql will run a seq scan

On 10/16/24 00:02, Vijaykumar Jain wrote: postgres=# create table t(col1 int) partition by list(col1); CREATE TABLE postgres=# create table t1(col1 int) postgres-# ; CREATE TABLE postgres=# insert into t1 select 0 from generate_series(1, 10) x; INSERT 0 10 postgres=# select relname,s

Re: What are best practices wrt passwords?

On Wed, Oct 16, 2024 at 06:16:57PM +0200, mb...@mbork.pl wrote: > On 2024-10-16, at 14:41, Dominique Devienne wrote: > > > On Wed, Oct 16, 2024 at 2:25 PM wrote: > >> I'd like to be able to use psql without typing passwords again and > >> again. I know about `.pgpass` and PGPASSFILE, but I spec

Re: What are best practices wrt passwords?

On 2024-10-16, at 14:41, Dominique Devienne wrote: > On Wed, Oct 16, 2024 at 2:25 PM wrote: >> I'd like to be able to use psql without typing passwords again and >> again. I know about `.pgpass` and PGPASSFILE, but I specifically do not >> want to use it - I have the password in the `.env` file

Re: What are best practices wrt passwords?

On 2024-Oct-16, mb...@mbork.pl wrote: > I understand why giving the password on the command line or in an > environment variable is a security risk (because of `ps`), but I do not > understand why `psql` doesn't have an option like `--password-command` > accepting a command which then prints the p

Re: What are best practices wrt passwords?

Use the PGPASSWORD environment variable. Example: SET PGPASSWORD=P0stgres psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT 1;'" https://www.postgresql.org/docs/current/libpq-envars.html On Wednesday, October 16, 2024 at 08:26:05 AM GMT-4, wrote: Hello all, I'd like to be able t

Re: What are best practices wrt passwords?

On Wed, Oct 16, 2024 at 2:25 PM wrote: > I'd like to be able to use psql without typing passwords again and > again. I know about `.pgpass` and PGPASSFILE, but I specifically do not > want to use it - I have the password in the `.env` file, and having it > in _two_ places comes with its own set o

Re: Support for dates before 4713 BC

Dear all, As Alex, I'm involved with the OpenAtlas system (https://openatlas.eu), and in a recent meeting the issue about tracking dates before 4713 BC came up again. We appreciate all the work you put into developing PostgreSQL and love working with it. But as already mentioned in the discuss

What are best practices wrt passwords?

Hello all, I'd like to be able to use psql without typing passwords again and again. I know about `.pgpass` and PGPASSFILE, but I specifically do not want to use it - I have the password in the `.env` file, and having it in _two_ places comes with its own set of problems, like how to make sure th

Re: how to know if the sql will run a seq scan

On Wed, 16 Oct 2024 at 02:59, Adrian Klaver wrote: > On 10/15/24 13:50, Vijaykumar Jain wrote: > > Sorry top posting, coz Gmail app on phone. > > > > Yeah, my point was for example we have a large table and we are > > attaching a table as a partition. Now it will scan the whole table to > > valid