Hi all. I've got an existing very small database on assets (commercial real estate) in Postgres that I've been maintaining, Very keen to add relationship data to map transactions, developments, tenants etcetc for the purposes of enriching an existing RAG process using LLMs.

Saw a post before that recommended to start with neo4j to get familiar with the whole graph database space (I'm a complete newbie in this) and maybe jump into AGE later so I'm wondering if that is the right approach.

dBeaver gives me the option to backup a database as:

- A directory (output - each database as lots of .dat.gz files)

- 'Plain' - output format = .sql

- Tar - output format = .tar

Between .sql and .tar ...... is one considered more reliable than the other or is it a matter of preference?

Is there any book out there that talks about the Logical Query Processing Phases, as in the classic book "Inside Microsoft SQL Server 2008: T-SQL Querying" by Ben-Gan et al, but for postgreSQL?

Thank you!

Hello, I need help with organizing collections of locations (Country, City, CityArea,CitySubArea) in DB.
I'm not sure the way I've done it now is correct.

Also, in userdata I have cityid, countryid, cityareaid, citysubareaid, which I think can be better. Does anyone have an idea?

https://preview.redd.it/j6vcyfne6gyc1.png?width=1457&format=png&auto=webp&s=63445fc9e79e0df9eb02c1ea0f5ff025606a4f10

Do regular tables (non-hypertables) created in a database with TimescaleDB installed as an extension have any performance benefits over regular (non-hypertables) in a Postgres instance without TimescaleDB?

If I'm using TimescaleDB do I have to create hypertables to see any benefit or can I just use regular tables.

I have a cluster of about 20 PostgreSQL DBs that I'm populating from various "small data" troves (CSV, etc).

For the most part, it's been a fairly manual process, with lots of mapping fields onto new ones with updated names and specified data formats.

I would love, of course, to find something to make this process a bit easier.

There seem to be tons of CRUD tools on the market but most seem to limit you to 1 DB (ie, they expect people to be managing data and the schema and table data which I guess makes lots of sense if you're building an app).

Is there anything that's kind of purpose-built for managing a cluster but which gives the same kind of "make a DB look like a spreadsheet" functionality that these tools excel in?

TIA!

A new release

Introduces improvements and bug fixes.

https://github.com/GreenmaskIO/greenmask

This release introduces improvements and bug fixes

Changes

• Fixed transformer parameters decoding/encoding issue
• Fixed TOC entries merge behavior when the data section is empty - important when you create a dump witout schema
• Fixed integration tests for S3 storage

If you are not familiar yet with Greenmask - it is a Database anonymization tool that brings wide anonymization functionalities and techniques. Check out the Playground page to get started

I have a beta testing db (downtime is no issue, no production stuff running there, just the one db), postgresql13, quite a few gigs of data, a few user/roles and postgis extensions. Ubuntu 20.04.

I had to do some work (extend tables, insert data, fix some triggers, etc,) so I did the "pg_dumpall > somefile.sql" (without any additional parameters set, no "--clean" or anything), and then did the changes, which didn't work at all. Now I want to restore the database to the previous state (no 'new' data needs to be kept, I don't need any of the changes).

What's the best way to do that? The sql dump has no "DROP ..." statements, so I'd probably have to "dropdb" first? That would probably leave the roles inside, do I have to delete those too (the sql file has the "create role ..." statements). Drop the whole cluster? Just do the "pg_restore -c"?

The dump took a few hours, the restore will probably take a bit more (indexes,...), and I'd hate it to break after 6 hours with 'x already exists' possibly in the middle of a wall of text, unnoticed, while 'x' being different than before and not overwritten.

Thank you!

It seems a dump question from me.

But I still don't understand how I can write the trigger function if Postgres does not support any kind of debug tools, like write logs or return values.

We just published this weeks episode of our Cloud Commute podcast and had Jimmy Angelakos, who talks about common issues in PostgreSQL, what customers typically ask for in consulting, his new book ("PostgreSQL Mistakes and How to Avoid Them", find a discount link in the show notes) and what you need to look out for when running PostgreSQL in the cloud or Kubernetes.

https://www.simplyblock.io/cloud-commute-podcast/episode/2c0e1cf7/postgresql-mistakes-and-how-to-avoid-them-jimmy-angelakos

Hello r/PostgreSQL Community!

Excited to share our latest Medium guide, "PG Internals, PostgreSQL Vacuum from A to Z." This comprehensive article explores the critical process of vacuuming in PostgreSQL, essential for maintaining database health and efficiency.

Dive into the details here: PGInternals, PostgreSQL Vacuum from A to Z

Do you have any PostgreSQL vacuuming tips or challenges? Let’s share our experiences and learn from each other!

Looking forward to your feedback!

Hello I am currently trying to complete an assignment for my masters class and I am new to PostgreSQL and struggling. The goal was to create 3 tables through script and what I have written is not running. Any help to pinpoint my errors would be greatly appreciated.

https://preview.redd.it/hqqvw8hx9vxc1.png?width=2350&format=png&auto=webp&s=569291cd0eea8b61fa09ea1e3bb2358379cae6a0

I'm in the process of moving an application to AWS Aurora Postgres from another database engine. Part of the process is testing performance of several batch processing jobs where we ingest a day's worth of files, and then run our daily processing job sequence against it. It's very difficult to manually rollback the database between tests, so we've been dropping the schema and re-creating before each test run.

We've noticed that if we drop the schema a couple of days before we actually run the test, the performance is *much* better. However it's not really practical for our testing cycle times to wait 1-2 days after dropping the schema to perform the test. So we're really curious as to what could be causing the database to be so much faster after 1-2 days of just sitting there empty, and if there is any way to immediately trigger whatever process is making things run faster so that we can run our tests the same day that we reset the database.

Hey all, wanna ask for help on this issue and I have been stuck there for couple of days.

So I have a flyway migration set up, and previously only has 1 step. The first step is using `TransactionIsolationLevel.SERIALIZABLE`, and I manually call `commit` then close the session. Till this point, everything works.

Recently I added a second step, which does the similar thing, and uses the same isolation level. I also call `commit` then close the session. However, when I bring up a local postgres and run the migration, the second step raises an error
```

PSQLException: Cannot change transaction isolation level in the middle of a transaction

```

I made the postgres server prints more logs by using `log_statement=all` and also print some logs in in the transaction itself, but none of them point me to why the error happens.

Does anyone know how to debug such kind of issue? Thanks!

Stupid me auto updated my postgresql 15 database for Immich and now I am getting a error that its not supported because the pgvector.rs extension is 0.1.11 and Immich only supports 0.2.0 and later.

I found a Postgresql docker container that is custom made for Immich and has the proper pgvector.rs version but idk how to migrate my database.

Does anyone here have experience on how I need to do this within UnRaid?

Hello again, r/PostgreSQL community!

Building on our previous discussions about the power of PostgreSQL, I'm excited to share one of our latest Medium article: "Converting Oracle PL/SQL to PostgreSQL PL/pgSQL." This guide is perfect for anyone involved in or considering migrating their database systems.

In this article, we delve into the practical aspects of converting Oracle’s PL/SQL code to PostgreSQL’s PL/pgSQL. We provide detailed insights and step-by-step instructions to help ensure your migration process is smooth and efficient. This can save you time and reduce errors, making your transition to PostgreSQL seamless.

Check out the full article here: Converting Oracle PL/SQL to PostgreSQL PL/pgSQL

If you’ve gone through or are considering a database migration, what challenges have you faced? What insights can you share about managing such transitions effectively? Let's continue our learning journey together and help each other master these crucial skills!

Looking forward to hearing your stories and tips!

The REFERENCING option enables collection of transition relations, which are row sets that include all of the rows inserted, deleted, or modified by the current SQL statement. This feature lets the trigger see a global view of what the statement did, not just one row at a time.

https://www.postgresql.org/docs/current/sql-createtrigger.html

Does this "global view" include rows inserted on tables elsewhere in a statement? The only examples use the rows on the triggered table, and I'm guessing the case is it's limited to that: but I'd like to know for sure.

I'm looking for an elegant way to access a returned row within a trigger. For example: is there an elegant way for a trigger function on table_b to access values from a in this CTE?

WITH a AS (INSERT INTO table_a ('external_value') RETURNING *)
     INSERT INTO table_b ('internal_value') RETURNING *

P.S. the main documentation https://www.postgresql.org/docs/current/index.html is a great resource, and google/stackoverflow is wonderful for the ho-hum kind of questions (google used to be really good for everything 10 years ago: easy to find forums, blogs, posts, etc. relevant to even niche searches I had. Today it's terrible for this kind of thing 🤷‍♀️, but I digress) I'm having some hiccups when it comes to the nitty gritty of semantics and terminology, like "statement" vs. "expression" or what a in the above CTE is even called: a label, variable, record??? Overall I have some conceptual gaps. I don't have the right postgres-zen down which leads to questions like these. Any recommened books or resources?

I'm moderately new when it comes to postgres, however am comfortable with the basics. There is one area I keep going back and forth on when designing my schema, and after a lot of research I am still unsure of what the correct approach is for dealing with deeply complex data structures.

What is the preferable way to store data that is deeply nested, yet still structured? I have a complex form with a data structure that contains multiple levels of nesting, and is best described as a big blob of json. As far as I have read, normalisation is the desired approach - however it feels wrong/overkill to create so many tables for this one data structure. Also the thought of migrating this structure to update fields is off-putting.

Jsonb seems like an obvious choice, but I am also aware of the challenges that come with this, and have read that a lot of the time you should strive for normalisation if the data is structured. The data is structured - it's just a very complex shape.

Perhaps I'm overthinking it, but I can't settle on whether to bite the bullet and create the tables and benefit from the strong structure, or preserve flexibility with jsonb but then have to be much more careful with validation/versioning etc.

I would be very interested to hear peoples general approaches of complex data structures, and whether jsonb has been worth it for them!

Edit:

Here's an example of the challenge I'm describing:

create table app.example (
    id uuid default uuid_generate_v4() primary key,
    name text not null,
    tags text[] default array[]::text[] not null,
    source text not null,
    config jsonb not null,
    persona_id uuid references app.persona (id),
    created_ts timestamptz default current_timestamp not null,
    updated_ts timestamptz default current_timestamp not null,
    deleted_ts timestamptz,    created_by text not null default current_setting('app.user_uid')::text references app.user (uid)
);

In this case config is jsonb, and config loosely looks like:

{
  field_a: [ { field: string, values: [  ...json  ]} ],
  field_b: [ string ],
}

I expect the config to grow/change over time.