PostgreSQL

15 Oct 2024 15:44

yes, psycopg 3 connection pool, but 4 databases on the actual server that each has connection pool on it's own

PostgreSQL

15 Oct 2024 15:41

I checked the last archive status,it was a month ago. But I can see today wal files in archive path..

PostgreSQL

15 Oct 2024 15:37

Why u found it difficult, I used HAproxy with Patroni, and it should implemented on a separate node other than the patroni nodes as it consume utilisation from the cpu to avoid overheads. I mean install and configure HA proxy on a separate node (Server) or vm

PostgreSQL

15 Oct 2024 15:32

I use for myself, HA proxy for managing the read write operational and pg bouncer for user connections

PostgreSQL

15 Oct 2024 15:12

That's right but when archive process fails it's going to keep archive segments at pg_wal directory. Other options are inactive replication slot, uncommitted two phase commit sentences or if you are setting wal_keep_size with high value

PostgreSQL

15 Oct 2024 14:34

Probably, the exact answer was in the logs of psql or pg_restore (depending on the dump format).

I'd suggest remove the database, restore it one more time and read the logs carfully.

PostgreSQL

15 Oct 2024 12:46

I'm currently busy doing other (paid) stuff, you can use my pointers and elaborate further on them

PostgreSQL

15 Oct 2024 12:44

Ok, tell me the the easy steps to take the back up from another server and restore it on another one without getting any errors?

PostgreSQL

15 Oct 2024 12:42

The best way to check in this case is to:
- pg_dump with the --schema-only flag to export only the schema (and not the data) in sql format
- start a fresh cluster with 13.12
- try to import your dump (\i <yourfile.sql> from psql)

If you get a stream of "ALTER" and "CREATE" statements then the problem lies elsewhere
If you get errors, then investigate on the line that caused it

PostgreSQL

15 Oct 2024 12:38

Were the constraints dependent on other objects (functions, extensions, ...)?

PostgreSQL

15 Oct 2024 12:34

Hi all,
I have taken the back up of a database from postgres 13.3 and I restored it in postgres 13.12..

But for some tables, constraints were missed(not restored in postgres 13.12)..

Anybody can help me why they are not restored?

PostgreSQL

15 Oct 2024 12:14

It depends on how they implemented the archiving. But yeah, we need more info about the setup.

PostgreSQL

15 Oct 2024 12:09

The purge of archive files is not automatically, please apply your retention policy to your backup and archive files in both servers

PostgreSQL

15 Oct 2024 11:25

So you're archiving both on the primary and secondary? Are you 100% sure archiving on the secondary is working well, then? If it's failing, it'll retain wal files forever.

PostgreSQL

15 Oct 2024 11:23

Do you want to archive the wal on the secondary side?

PostgreSQL

15 Oct 2024 15:42

Do u have a connection pooling?

PostgreSQL

15 Oct 2024 15:39

it is on it's own, but like 50 connections made big load on the primary, and when I removed it, and accessed the primary directly, the same load happens "only" after 300 connections

PostgreSQL

15 Oct 2024 15:35

I'm unable to properly configure HA-Proxy, it actually created more load on the server itself.
do you know any good guide for better configuration of it for Patroni?

thanks

PostgreSQL

15 Oct 2024 15:16

Hello,
I have a cluster of pg (using patroni) at the moment PG14 we wish to upgrade to 16.

The main usage is mostly writing data (metadata and states0, and even some of selects are part of the insert/update transaction operations to determine some stages of a stateless operations.

I have many connections to the db at the peak usage of the system, that generate big load on the primary (can arrive to 200 tasks waiting).

All of the servers (three) are on "bare metal" (self managed proxmox).

The system must be responsive in few milliseconds (the lower the better) with the stateless states and metadata, so it is impossible to use MQ/Task queue and waiting for writing in bulks.

Do you know a good tool/proxy to manage such usage to reduce if possible some of the load, or better way to manage cluster of primary-primary-secondary?

Thank you

PostgreSQL

15 Oct 2024 15:00

Did you *check* pg_stat_archiver?
Also, check if (and why) you have archive_mode = always set.
If not, maybe you have a replication slot lingering on the replica?

PostgreSQL

15 Oct 2024 14:23

It's already implemented, but purge is for archive path how it's related to pg_wal..

PostgreSQL

15 Oct 2024 12:46

Can you ping me personally?

PostgreSQL

15 Oct 2024 12:44

Well not the best way, just "my" preferred way :)

PostgreSQL

15 Oct 2024 12:39

Not sure I need to check

PostgreSQL

15 Oct 2024 12:37

You used pg_dump, right?

PostgreSQL

15 Oct 2024 12:26

"all other servers" is not *this* server. Check pg_stat_archiver and the server log (assuming you have log_collector on)

PostgreSQL

15 Oct 2024 12:10

It seems someone persists to index with fts a lo data type https://github.com/darold/ora2pg/issues/1829

PostgreSQL

15 Oct 2024 11:27

Yes, archiving working fine on all other servers. I can see the same wal files in both archive path and pg_wal

PostgreSQL

15 Oct 2024 11:24

Yes, the same thing happening on all other servers.

PostgreSQL

15 Oct 2024 11:23

Need to remove archive on secondary side?