PostgreSQL

21 Apr 2025 15:56

The conneciton was fast like all of you recently listed is normal and expected. I'll keep an eye out for any discrepancies and quickly capture the packets. Tbh I wish I could have captured the initial reason for the extremelly long conn time.

PostgreSQL

21 Apr 2025 15:21

I’ll check it out with tcpdump as-well, thank you for clarification. I was already thinking it’s odd that conn takes so long ^-^

PostgreSQL

21 Apr 2025 15:15

When running a traceroute it also takes a while to get to server, about 330ms, with 18 hops.

PostgreSQL

21 Apr 2025 15:12

Names in pg_hba?

(Well, it's definitely not normal. A typical TLS is usually not more than 6 rtt).

PostgreSQL

21 Apr 2025 15:02

( it’s my first time using a remote db, it’s hosted on my own VPS )

PostgreSQL

21 Apr 2025 12:39

Then, share the full command and log

PostgreSQL

21 Apr 2025 12:23

I have the notion that there were errors earlier on in your log. Like "no space left on device" or so.

PostgreSQL

21 Apr 2025 08:15

Because the backup is incomplete without the missing WAL segments.

PostgreSQL

20 Apr 2025 17:37

pg_basebackup: write-ahead log end point: 1F97/4AD81F98
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: error: child process exited with exit code 1
pg_basebackup: removing contents of data directory "/postgresql14/bbddpostgresql/"
pg_basebackup: removing contents of WAL directory "/postgresql14/xlogs"
pg_basebackup: changes to tablespace directories will not be undone

PostgreSQL

20 Apr 2025 12:18

Then it should _at least_ do an index scan. Really strange 🤔

PostgreSQL

20 Apr 2025 10:47

is there any option/workaround doing something like this on older versions?

PostgreSQL

20 Apr 2025 10:40

what's the right way to create logical replication in case tables are big and i want to make it as fast as possible?

PostgreSQL

19 Apr 2025 22:02

And I take it there's no index on sub_id?

PostgreSQL

19 Apr 2025 15:53

Yeah, at the end i made a function myself to do max() only on the first highest non-empty partition

PostgreSQL

19 Apr 2025 15:37

https://explain.dalibo.com/plan/725d5efe653a8a01#grid

PostgreSQL

21 Apr 2025 15:55

Well the issue resolved it’s self. Which idk if I should be happy or concerned. I did however intercept both traffic from local machine and vps. The traffic indicated a usual three-way handshake with almost no latency.

PostgreSQL

21 Apr 2025 15:20

That's a lot, but shouldn't take more than a couple of seconds either.

Well, you could use strace and tcpdump to check exactly — what's going on and what are they waiting for.

PostgreSQL

21 Apr 2025 15:14

Mhh odd, I currently have the following very simple pg_hba;

# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl    test            postgres        0.0.0.0/0               md5
hostssl    test            postgres        ::/0                    md5

PostgreSQL

21 Apr 2025 15:06

Nope, It's not normal. I can setup VM in AWS US-EAST-1 and psql connection from EU is always in milliseconds.

PostgreSQL

21 Apr 2025 15:01

I am currently setting up a SSL/TLS remote db instance. Although initial connection takes a long time. I am connecting from VPS > VPS and both servers are in EU..

Is it normal for the initial conn to take about 1min seems strangely long to me..

Or is it normal for psql handshake to take that long, and will db operations be fast?

PostgreSQL

21 Apr 2025 12:35

i don't think it's space issue because i have TB available

PostgreSQL

21 Apr 2025 08:42

and how can troubleshoot this?

PostgreSQL

20 Apr 2025 17:38

Do you know why is deleting entire data directory?

PostgreSQL

20 Apr 2025 16:46

https://pgdba.org/post/2025/04/size_matter/

PostgreSQL

20 Apr 2025 10:51

Yes but it's complicated.
https://pganalyze.com/blog/5mins-postgres-zero-downtime-upgrades-logical-replication

PostgreSQL

20 Apr 2025 10:46

Create physical replica and convert it to logical.

https://www.dbi-services.com/blog/postgresql-17-convert-a-physical-replica-to-a-logical-replica-using-pg_createsubscriber/

PostgreSQL

19 Apr 2025 22:06

Covering index on sub_id+user_id

PostgreSQL

19 Apr 2025 21:45

Seems very much an edge case. To generalise it, there would be interesting questions on whether a table is actually empty for your snapshot or not.

PostgreSQL

19 Apr 2025 15:51

Likely the planner doesn't know how to optimize this step to just scan one partition.

PostgreSQL

19 Apr 2025 15:33

Now, i want to know the highest value of sub_id in all partitions, ideally, it would check the first partition with at least one row, starting from the highest possible partition sub_id, instead it starts a parallel sequential scan on every single partition and then aggregates that