ETCD for Patroni configuration error

[Inzamam Shafiq]

Hi Team,

I am in configuring etcd cluster, and facing below error while I start etcd or 
see member list.

2023-04-19 11:31:31.001071 N | embed: serving insecure client requests on 
xxx.xx.xx.xx:2379, this is strongly discouraged!
2023-04-19 11:31:31.001184 N | embed: serving insecure client requests on 
127.0.0.1:2379, this is strongly discouraged!
WARNING: 2023/04/19 11:31:31 grpc: addrConn.resetTransport failed to create 
client transport: connection error: desc = "transport: Error while dialing dial 
tcp: missing address"; Reconnecting to {xxx.xx.xx.xx:2379 0  }
WARNING: 2023/04/19 11:31:32 grpc: addrConn.resetTransport failed to create 
client transport: connection error: desc = "transport: Error while dialing dial 
tcp: missing address"; Reconnecting to {xxx.xx.xx.xx:2379 0  }
WARNING: 2023/04/19 11:31:33 grpc: addrConn.resetTransport failed to create 
client transport: connection error: desc = "transport: Error while dialing dial 
tcp: missing address"; Reconnecting to {xxx.xx.xx.xx:2379 0  }
WARNING: 2023/04/19 11:31:36 grpc: addrConn.resetTransport failed to create 
client transport: connection error: desc = "transport: Error while dialing dial 
tcp: missing address"; Reconnecting to {xxx.xx.xx.xx:2379 0  }
WARNING: 2023/04/19 11:31:41 grpc: addrConn.resetTransport failed to create 
client transport: connection error: desc = "transport: Error while dialing dial 
tcp: missing address"; Reconnecting to {xxx.xx.xx.xx:2379 0  }
WARNING: 2023/04/19 11:31:48 grpc: addrConn.resetTransport failed to create 
client transport: connection error: desc = "transport: Error while dialing dial 
tcp: missing address"; Reconnecting to {xxx.xx.xx.xx:2379 0  }

when I execute this command

etcdctl member list

below error received,

client: etcd cluster is unavailable or misconfigured; error #0: proxyconnect 
tcp: dial tcp :80: connect: connection refused

can someone please help in identifying the issue?

does anything related to port 80 or any configuration issue?

my conf file is as follows,

name: "etcd"
initial-cluster: "etcd=http://xxx.xx.xx.xx:2380";
initial-cluster-state: "new"
initial-cluster-token: "etcd-cluster"
initial-advertise-peer-urls: "http://xxx.xx.xx.xx:2380";
data-dir: "/var/lib/etcd/pgsql"
listen-peer-urls: "http://xxx.xx.xx.xx:2380";
listen-client-urls: "http://xxx.xx.xx.xx:2379,http://localhost:2379";
advertise-client-urls: "http://xxx.xx.xx.xx:2379";

Regards,

Inzamam Shafiq
Sr. DBA

Re: Request for information about postgres version 15.2 stability

[Tom Lane]

Laurenz Albe  writes:
> We don't have any information about yet undiscovered PostgreSQL bugs.

Indeed.

> We believe that PostgreSQL 15.2 is a great and stable database, but we are 
> biased.

One moderately objective way to see how stable a release branch is
is to count how many commits fix bugs in that branch but not any
older branch.  If a bug would be problematic for you but it also
appears in (say) v14 and v13, then choosing to use one of those
branches instead of v15 wouldn't have saved you.  Also, if a commit
goes back that far, it means that the bug escaped detection for
multiple years in production, which suggests that it is minor and/or
doesn't affect many people.  (I hasten to add that we do find some
bugs that are both old and serious.)

A quick review of commits since 15.2 says that practically all of the
ones that don't also affect v14 or before have to do with glitches in
new-in-15 features, such as the MERGE command.  Which is code you
wouldn't be using anyway if your application would be satisfied with
an older branch.  So I'd judge that 15 is pretty stable at this point,
and there is very little reason to prefer any older branch.

If you want to do your own analysis along these lines, the gitweb
interface isn't especially friendly for comparing commit logs of
different branches.  I'd suggest using our git_changelog script:

git clone https://git.postgresql.org/git/postgresql.git
cd postgresql
src/tools/git_changelog >../pg-revision-history

This produces output that collates matching commits in different
branches.

regards, tom lane

Re: Logical Replication Sync Validation

[Laurenz Albe]

On Tue, 2023-04-18 at 10:20 +0200, Robert Sjöblom wrote:
> At the moment where we would switch to pg15 being the primary/lead we 
> will stop writes to pg10; at that point we will validate that we are 
> fully in sync, tear down pg10 and send writes to pg15. Our question is 
> how we can validate our sync status. Given that there have been no DDL 
> changes on pg10 (new tables, for instance), would the following check be 
> sufficient?
> 
> Compare byte diff between WAL LSNs:
> SELECT abs(pg_wal_lsn_diff(write_lsn, flush_lsn)) AS lag FROM 
> pg_stat_replication;
> 
> If the byte diff is 0, I would assume that we're fully in sync. Is this 
> understanding correct?

I would compare the last replayed log sequence number with the current
position on the primary:

SELECT abs(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag
FROM pg_stat_replication;

If that is 0, you are good.

Yours,
Laurenz Albe

Postgres as a LRU cache?

[David Tinker]

Has anyone tried using Postgres as a LRU cache for data in cloud blob
storage? I have a lot of data in Cassandra and want to move that to
Backblaze (like S3 but cheaper and less available). Cassandra is working
well but I have to add new nodes more frequently that I would like. The
older data is rarely needed but newer and recently accessed records need to
be local. Everything is on leased servers at Hetzner.

I was thinking that I could use a table partitioned by created date (one
partition per day) and drop the oldest partition(s) when it starts getting
full. This wouldn’t be LRU but would probably work for my application.
create table activity_stream (activity_id text primary key,created
timestamp,last_read timestamp,data bytea  -- 1k to approx 200k,
mostly on the lower side)-- Partition would use created to split by day
I could update last_read whenever a record is read. If I use a fill factor
of less than 100 and only update last_read would I avoid dead tuple
problems with HOT tuple optimisation?Then before dropping the oldest
partition I can update created = last_read on recently read records to move
them to other partitions making it LRU. This will be a small percentage of
the data.
Any suggestions / other approaches? Thanks.

Re: Request for information about postgres version 15.2 stability

[Laurenz Albe]

On Tue, 2023-04-18 at 12:26 +0530, gayathri ramesh wrote:
> Our company is currently supporting around 100+ postgres databases and we are 
> planning
> to upgrade to version 15.2. We want to ensure that there are no major 
> critical bugs
> that could impact our production systems.
> 
> I would appreciate if you could provide us with information about the 
> stability of
> postgres version 15.2. We are particularly interested in knowing if there are 
> any
> known critical bugs or issues that could potentially impact the system.

To see a list of bugs fixed since 15.2 came out, look at the commit log:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=shortlog;h=refs/heads/REL_15_STABLE

Release 15.2 came out on 2023-02-09.

We don't have any information about yet undiscovered PostgreSQL bugs.

We believe that PostgreSQL 15.2 is a great and stable database, but we are 
biased.

Yours,
Laurenz Albe

Re: Question on SSI - Serializable Snapshot Isolation

[Siddharth Jain]

ok thanks tom.

On Mon, Apr 17, 2023 at 2:39 PM Tom Lane  wrote:

> Siddharth Jain  writes:
> > When using SSI , lets say we have
> two
> > transactions T1 and T2 and there is a serialization conflict. Postgres
> > knows when one or the other transaction is doomed to fail
> > [image: image.png]
>
> Please don't use images for things that could perfectly well be
> expressed as text.  They're not quotable, they may not show up
> in the archives (as this one doesn't), etc etc.  email is a text
> medium, despite Google's attempts to convince you otherwise.
>
> > but will not raise serialization error until the transaction commits.
> This
> > can cause a massive perf hit because the transactions could be long
> > running. Why not raise the error early on when the conflict is detected
> to
> > avoid wasting CPU and other resources? Can anyone explain this to me?
>
> Try src/backend/storage/lmgr/README-SSI, notably this bit:
>
> * This SSI implementation makes an effort to choose the
> transaction to be canceled such that an immediate retry of the
> transaction will not fail due to conflicts with exactly the same
> transactions.  Pursuant to this goal, no transaction is canceled
> until one of the other transactions in the set of conflicts which
> could generate an anomaly has successfully committed.  This is
> conceptually similar to how write conflicts are handled.
>
> The main point here is that "at least one of these transactions
> will have to fail" is very different from "all of these transactions
> have to fail".  If the implementation prematurely forecloses on one
> of them, it may be that *no* useful work gets done because the others
> also fail later on for other reasons; moreover, it might be that the
> victim transaction could have committed after those others failed.
> Withholding judgment about which one to cancel until something has
> committed ensures that more than zero work gets completed.
>
> Also note that AFAICS we do notice fairly promptly once a transaction
> has been marked as doomed; it's not the case that we wait till the
> transaction's own commit to check that.
>
> regards, tom lane
>

Re: Request for information about postgres version 15.2 stability

[Adrian Klaver]

On 4/17/23 23:56, gayathri ramesh wrote:

Hi Team,

Our company is currently supporting around 100+ postgres databases and 
we are planning to upgrade to version 15.2. We want to ensure that there 
are no major critical bugs that could impact our production systems.


I would appreciate if you could provide us with information about the 
stability of postgres version 15.2. We are particularly interested in 
knowing if there are any known critical bugs or issues that could 
potentially impact the system.


https://www.postgresql.org/docs/current/release-15-2.html



Thanks in advance. looking forward to hearing from you.

Best Regards.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Logical Replication Sync Validation

[Christophe Pettus]

> On Apr 18, 2023, at 03:45, Robert Sjöblom  wrote:
> I'm aware of that. But you can, however, do something like:
> 
> SELECT * FROM FOO WHERE CTID = (SELECT MAX(CTID) FROM FOO);
> 
> on both sides. The idea being that if I change FOO, the CTID of the changed 
> row will not be the same on both sides, but the CTID will point to the 
> changed row on both sides. Or am I not understanding it correctly?

CTIDs are not monotonically increasing; they're locator in the file that holds 
the table data.  There's no reason to believe that a record on the subscriber 
will happen to land in any particular position.

Re: Guidance on INSERT RETURNING order

[John Howroyd]

Sorry, I may have jumped to a conclusion that's not quite correct.

On Mon, 17 Apr 2023 at 23:58, Federico  wrote:

> On Tue, 18 Apr 2023 at 00:21, John Howroyd 
> wrote:
> > ...
> >
> > Personally, I didn't see Frederico's comment as anything to do with
> order; just how one could output additional values in the RETURNING clause
> (namely, v.num from a subexpression of the SELECT but in whatever order it
> comes).  On the other hand, that seems a lot more complicated to me because
> it is not an expression in the overall SELECT feeding the INSERT, whereas
> the WITH ORDINALITY is a specific declaration to match input order with
> output order by inserting a counter.
>
> I didn't mean to suggest any particular order should be kept by insert
> or by returning. I was merely commenting on the David G. Johnston
> reply
>
>  I suppose breaking the restriction that only columns present on
> the insertion-table can be returned is a possible option that also
> solves another infrequent request.
>
>
...
> Best,
>   Federico
>

This might be a possibility.  The v.num (from the original example) is
accessible in the outer select, so one can a envisage a system to handle
this, but at (presumably) much greater expense: preparation of the SELECT,
orchestration of the output rows (some fields for INSERT some for
RETURNING) and (presumably) a whole load of mem copies to RETURNING.  Is
this something to consider (perhaps just for feasibility while writing an
initial patch based on WITH ORDINALITY)?

To put it another way, v.num is a valid expression in the overall SELECT,
but still a lot more complicated (and presumably expensive).

Re: Logical Replication Sync Validation

[Christophe Pettus]

> On Apr 18, 2023, at 01:20, Robert Sjöblom  wrote:
> Another idea we've had would be to use CTID to fetch the last row 
> (update/insert) in each table on both sides and compare row content, is this 
> feasible? Is it safe to rely on CTIDs across logical replication?

No.  CTIDs aren't sent over as part of logical replication, and there's no way 
to compare them between a publisher and a subscriber.

Logical Replication Sync Validation

[Robert Sjöblom]

Greetings,

We have the following setup:
A pg10 cluster with logical replication to a pg15 cluster, set up with 
the following query:

CREATE PUBLICATION "dbname_pub" FOR ALL TABLES;

We do an initial sync of DDL with pg_dump. The purpose of the 
replication is to perform an online upgrade with minimal downtime.


At the moment where we would switch to pg15 being the primary/lead we 
will stop writes to pg10; at that point we will validate that we are 
fully in sync, tear down pg10 and send writes to pg15. Our question is 
how we can validate our sync status. Given that there have been no DDL 
changes on pg10 (new tables, for instance), would the following check be 
sufficient?


Compare byte diff between WAL LSNs:
SELECT abs(pg_wal_lsn_diff(write_lsn, flush_lsn)) AS lag FROM 
pg_stat_replication;


If the byte diff is 0, I would assume that we're fully in sync. Is this 
understanding correct?


Another idea we've had would be to use CTID to fetch the last row 
(update/insert) in each table on both sides and compare row content, is 
this feasible? Is it safe to rely on CTIDs across logical replication?


best regards,
Robert Sjöblom

--
Innehållet i detta e-postmeddelande är konfidentiellt och avsett endast för 
adressaten.Varje spridning, kopiering eller utnyttjande av innehållet är 
förbjuden utan tillåtelse av avsändaren. Om detta meddelande av misstag 
gått till fel adressat vänligen radera det ursprungliga meddelandet och 
underrätta avsändaren via e-post