PostgreSQL

20 May 2025 15:49

Tapping the hive mind:

My client experiences random HAProxy check timeouts (up to 2 seconds) when polling Patroni. Quite a PITA when HAProxy takes the backend down and kills all connections... :/

Sadly, since those events appear randomly, chances to look at the system when they happen are close to zero. What we can see in hindsight is that atop reports high numbers of "scan", "compact" and also "memsome" & "memall" events. And those "kcompactd[01]" kernel threads use quite some CPU time as well. But at 10 min granularity, that's just hints...

However, I'm quite certain that the root cause is (transparent) huge page stuff (which gives me terribel RHEL 6 vibes tbth.).

Is anyone else experiencing stalls on Ubuntu 24.04 Postgres servers with contemporary amounts of RAM (512-768GB)? Any hints how I can get rid of them? Kernel is "6.8.0-53-generic #55-Ubuntu SMP PREEMPT_DYNAMIC", GUC "huge_pages" is "try".

PostgreSQL

19 May 2025 15:58

How do you usually stop the streaming replication? In case needed...

PostgreSQL

17 May 2025 16:19

Hi , we have got a request to plan for mysql ( on prem) to postgres RDS migration . Is there any guide available ? I searched and found pgloader . Is that a good option ?

PostgreSQL

15 May 2025 10:24

https://www.postgresql.eu/events/pgconfeu2017/sessions/session/1645/slides/18/db2-postgresql-migration-169.pdf

PostgreSQL

15 May 2025 10:20

Unlogged tables are just like normal tables in backup and usage, they just get erased when there is a crash. See:

https://www.postgresql.org/docs/17/sql-createtable.html#SQL-CREATETABLE-UNLOGGED

Data written to unlogged tables is not written to the write-ahead log (see Chapter 28), which makes them considerably faster than ordinary tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown. The contents of an unlogged table are also not replicated to standby servers.

PostgreSQL

15 May 2025 10:16

Are UNLOGGED tables exist in pg_basebackup / pg_dump?
didn't find something related to this int he offical docs

PostgreSQL

15 May 2025 09:12

In that case, you can just use pg_dump whitelisting the table. Be careful, though, it might be slow if there's plenty of files. File-based backup making (basebackup or rsync with start backup or any other solution) might be a better choice if your database is so big

PostgreSQL

15 May 2025 09:03

Hi Everyone, I have a postgresql partitioned table in which there are around 200 partitions , I want to take the backup of that partitioned table do we have any way for this kind of structure or we need to take the backup of all partitions manually???

PostgreSQL

15 May 2025 07:29

Hi All
Any one have any pdf book for postgreSql DBA

PostgreSQL

13 May 2025 12:17

Hi all looking for postgre sql dba real time training,any one provide pls ping to me.

PostgreSQL

12 May 2025 14:11

Consider that every database runs in a container with qcow2 as the backing image formats for easy backups and restores with proxmox-backup-server, so rolling back is really not an issue and usually is nearly instantaneous assuming only the schema and only a minimal amount of data is changed

PostgreSQL

12 May 2025 14:08

In case everybody needs to rollback (e.g. A software bug, so people updated and then used the software and long after they encounter the bug) -> new schema version is actually a roll back (example: schema version 301 is a roll back of schema version 300)

PostgreSQL

11 May 2025 11:47

so sql scripts are created on your hand, you commit and upgrade when asked?
do you also have log table to track which sql script was deployed already and when?

PostgreSQL

11 May 2025 11:44

But my stuff is on premise. Every customer has the stuff managed by us

PostgreSQL

11 May 2025 11:42

If you need to rollback, you write an upgrade that does the rollback

PostgreSQL

19 May 2025 16:33

stop instance, and delete the standby.signal and start the instance

PostgreSQL

19 May 2025 15:41

Hi everyone.im an postgresql developer.. I want to learn to snow flake with real time scenarios . please help me

PostgreSQL

16 May 2025 17:00

Any one here want to learn SQL DBA , please DM me

PostgreSQL

15 May 2025 10:23

Yeah I understand this but according to this article https://www.dbi-services.com/blog/postgresql-unlogged-tables-and-backups/
it seems like the data doesn't get backed up , only the structure.

PostgreSQL

15 May 2025 10:18

Just the structure or the data as well?
Could you please direct me to the docs?

PostgreSQL

15 May 2025 09:14

Michael Banck did a talk on such a migration a while back, slides should be online... Let me ask him.

PostgreSQL

15 May 2025 09:10

You only want to make a backup of that specific table+partitions?

PostgreSQL

15 May 2025 07:33

Hello All,

Anyone has experience in migration of DB2 mainframe to postgres? Please help me with some migration tools.

PostgreSQL

13 May 2025 12:21

Im not trainer, rather amateur but what troubles do you have with learning dbms?

PostgreSQL

12 May 2025 14:11

Do not use this approach for your 20tb government database, or well, don't blame me if it doesn't scale 😳

PostgreSQL

12 May 2025 14:08

In case we want to roll back immediately (example, upgrade failure) -> restore from snapshot

PostgreSQL

12 May 2025 12:42

@unfoxo May I ask how you managed the rollback mechanism? in case of failure it will automatically run the rollback sql files?
In case you want to trigger rollback - how is it done?

PostgreSQL

11 May 2025 11:44

Which means they call us and we do a backup, a git pull and restart the software and it self upgrades

PostgreSQL

11 May 2025 11:43

You working with git? github actions? jenkins?

PostgreSQL

11 May 2025 11:42

how are you taking care the rollback? or you just have sql script also for the downgrade/rollback?
I find those tools (flyway/liquibase) good mainly for this