PostgreSQL

09 October 2024 05:38

Greetings, we do have a full time / permanent position available for a MSSQL developer (6+ yrs) which is based either in Chennai or Bangalore or Trichy. If anyone is interested to explore this further, please feel free to ping / DM me. I can help you with more details.

PostgreSQL

09 October 2024 03:33

How to understand the paragraph of the document?

The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database. However, this view will not necessarily always be consistent with some serial (one at a time) execution of concurrent transactions of the same level. For example, even a read-only transaction at this level may see a control record updated to show that a batch has been completed but not see one of the detail records which is logically part of the batch because it read an earlier revision of the control record. Attempts to enforce business rules by transactions running at this isolation level are not likely to work correctly without careful use of explicit locks to block conflicting transactions.

PostgreSQL

08 October 2024 22:40

Any one providing online classes for postgresql

PostgreSQL

07 October 2024 18:47

hi guys.
im running postgresql on docker

  psql:
    image: postgres:16.3
    restart: always
    environment:
      TZ: Asia/Tehran
      POSTGRES_DB: CometDb
      POSTGRES_USER: comet
      POSTGRES_PASSWORD: 1381hadi
      PGDATA: /var/lib/postgresql/data/pgdata

    volumes:
      - ./assets/database/postgres:/var/lib/postgresql/data
      - ./assets/tables.sql:/docker-entrypoint-initdb.d/init.sql

    volumes:
      - ./assets/database/postgres:/var/lib/postgresql/data

PostgreSQL

07 October 2024 17:21

Checking between the old and new data folders, most files which shrunk were TOAST tables

PostgreSQL

07 October 2024 17:18

and did you setup a WAL archive (and does that work???) and/or replication slots?

PostgreSQL

07 October 2024 17:12

Hi guys, i have pg_wal directory with 1 month old of archive logs, how to configure the retention of wall? I have pgpool configured with 1 synchronous and another asynchronous, the parameter wal_keep_size by default is 0

PostgreSQL

07 October 2024 17:08

Aka no need for tablespaces anymore - the filesystem will take care of migrating most used or least used data to the most appropriate storage type, be it ssd or hdd

PostgreSQL

07 October 2024 17:07

Postgresql will work fine with any posix-compliant filesystem

PostgreSQL

07 October 2024 16:59

yeah, does it make any difference on the performance?

PostgreSQL

07 October 2024 16:50

bcachefs is a new generation file system which i'm testing in place of zfs/ext4. It has some promising features but it's not 100% stable. I'm using it in a semi-production environment to see how it behaves

Supposedly it is impossible to lose data with it due to extensive self-healing and checksumming capabilities

PostgreSQL

07 October 2024 16:47

Could you pls explain more by what is bcasbefs on the filesystem that it is detected a corruption?

PostgreSQL

07 October 2024 16:33

Go figure. Perhaps i had some extra orphan data files somewhere in the data directory

PostgreSQL

07 October 2024 16:31

It finished yesterday

PostgreSQL

07 October 2024 16:08

Also surprisingly enough, after pg_upgrade my database size went from 1.7TB to 1TB!

PostgreSQL

09 October 2024 03:47

And I'm afraid the sentence causes some misleading, in fact:
In practical, NO transaction isolation level can ensure we get a view that consists with any serial execution of other transactions. Since isolation levels other than SERIALIZABLE is permitted.(So the sentence is likely unused.)
If my understanding correctly.

PostgreSQL

08 October 2024 23:32

In our postgres database we are using UDF function now we are going to move to RDS.. How can i manage UDF functions in RDS?

PostgreSQL

07 October 2024 19:09

PostgreSQL Person of the Week interview with: Yurii Rashkovskii

https://postgresql.life/post/yurii_rashkovskii/

PostgreSQL

07 October 2024 17:22

I have 2 replication slots

PostgreSQL

07 October 2024 17:20

I think that was related to TOAST

PostgreSQL

07 October 2024 17:13

How can clear the directory I have 800gb used of wal log

PostgreSQL

07 October 2024 17:12

... and leaves 70% garbarge lying around while doing so, according to your 1.7->1TB migration 🤓

PostgreSQL

07 October 2024 17:07

Massive performance gains due to the ability to use SSDs as caching and tiered storage

PostgreSQL

07 October 2024 17:00

and does postgreSQL work fine with it rather than the most popular filesystem which is ext4?

PostgreSQL

07 October 2024 16:58

how did u upgrade your overall cluster then? Like if you could tell the brief steps in order to carry this out in the most efficient way?

PostgreSQL

07 October 2024 16:49

>How much time does it take to vacuum full and analyze? And did u vacuum full and anaylze in all tables on the database?

in my case it was a four day job because it's a >1TB database on spinning rust.

>How u can avoid the implications of tables locks in your situation, even if it is carried out in the downtime?

Your best bet is pg_repack https://reorg.github.io/pg_repack/ which will take minimal locks and work like VACUUM FULL (but you need 2x the storage of the db, and at least one primary/unique key per table)

>Also, did u use pgupgrade utility with link or without link in your example?

I used pg_upgradecluster with the link parameter.

PostgreSQL

07 October 2024 16:46

How much time does it take to vacuum full and analyze? And did u vacuum full and anaylze in all tables on the database?

How u can avoid the implications of tables locks in your situation, even if it is carried out in the downtime?

Also, did u use pgupgrade utility with link or without link in your example?

PostgreSQL

07 October 2024 16:32

I decided to VACUUM FULL before the upgrade and ANALYZE because in the past i had data loss due to undetected corruption on a filesystem (I'm running bcachefs as experimentation)

PostgreSQL

07 October 2024 16:25

When is the last vacuum full u did on db?

PostgreSQL

07 October 2024 13:46

json_table and pg_basebackup now supports incremental backup