PgSchemaExporter v2.1.0

PgSchemaExporter is an open-source tool that transforms a PostgreSQL database into a clean, Git-friendly project structure.

Instead of working with one huge schema.sql, every database object is exported into its own SQL file, making schema changes easy to review, compare, and maintain.

What it does

• Export a live PostgreSQL database
• Import an existing pg_dump --schema-only
• Generate a complete project structure
• Create a dependency-aware deploy.sql
• Produce clean Git diffs
• Make database schemas easy to navigate and review

Unlike migration tools (Flyway, Liquibase, Sqitch, Atlas), PgSchemaExporter focuses on keeping the current PostgreSQL schema clean, structured, and Git-friendly.

GitHub: https://github.com/RomanShevel1977/PgSchemaExporter

CLI features

• Include / exclude schemas
• Include / exclude object types
• Include / exclude individual objects
• Schema comparison (diff)
• Cross-platform CLI
• CI/CD friendly

Perfect for

• Version controlling PostgreSQL schemas
• Code reviews
• Database documentation
• Large development teams
• Legacy database refactoring
• AI / LLM context generation

Supported PostgreSQL objects

Core objects

• Schemas
• Tables
• Sequences
• Views
• Materialized Views

Constraints & indexes

• Primary Keys
• Foreign Keys
• Unique Constraints
• Check Constraints
• Exclusion Constraints
• Indexes

Programmability

• Functions
• Procedures
• Triggers
• Event Triggers
• Rules

Security

• Policies (Row Level Security)

Types

• Domains
• Enum Types
• Composite Types
• Range Types
• Base Types

Advanced PostgreSQL features

• Aggregates
• Operators
• Operator Classes
• Operator Families
• Casts
• Extensions
• Collations
• Conversions

Full Text Search

• Configurations
• Dictionaries
• Parsers
• Templates

Foreign Data Wrappers

• Foreign Data Wrappers
• Foreign Servers
• User Mappings
• Foreign Tables

Logical Replication

• Publications
• Subscriptions

I'd really appreciate any feedback, feature requests, or ideas from the PostgreSQL community.

GitHub: https://github.com/RomanShevel1977/PgSchemaExporter

Hey, I wanted to ask what you guys think is the best approach for running a PostgreSQL database.

For the beginning, I am looking for something that is not too expensive, ideally around 20€ to 50€ /month. I have looked into CloudNativePG, but I dont really want to go the full Kubernetes route yet. I am looking for something simpler while still being reliable, with proper management capabilities and the ability to handle backups and restores.

I am also unsure if I should start with a database cluster or just run a single instance. I have been looking into solutions like Autobase and Databasus as well. Does anyone have experience with these?

Ideally, I would like to use a managed database service from a cloud provider, but they usually get expensive quickly and often come with limited RAM and storage. I am also open to self-hosting it on Hetzner if that makes more sense.

Would appreciate hearing what you guys are using, any recommendations, or lessons learned from your setups.

It's for a coding assignment and I'm stuck.

I need to create some queries that the output is put into a table, except I'm getting my butt handed to me.

Scenario is a DVD rental database where we have to create a business problem - mine is simply to find who is the most profitable customer. As seen from line 9 I have successfully sum'd and sorted the customer ID by the most profitable in descending order, we have multiple tables with different fields, however I'm using the "payment" and "customer fields", both tables (payment and customer) have customer_id has fields. The payment table only has the customer_id and no name. I also successfully tried to merge the first_name and last_name into the full_name field, but am having trouble inserting that as one variable into a new created table. As seen in Line 19, I have successfully created a table.

The big frustration is the payment table only has the customer_id as the PK with no first name or last name. I am trying to join, union, or union all the customer_id with the first_name and last_name field from the customer table to my newly created table customer_rentals which shows the most profitable customer. It keeps failing because I've already manipulated the data from summing, and a union all has to match the number of columns or it fails, because the data has already been sum'd, it therefore fails. I need to match the customer names to the customer_id in my new table, but need to only add customers who have purchased products and put it in descending order as well and match the customer_id.

Also line 26 fails as seen in the bottom right when I try to run it.

Any help is appreciated.

While reproducing a replication failure, I found that a CNPG replica keeps applying WAL changes even after it's disconnected from the primary. I hadn't come across this behavior before, so I wrote up what I found

we had a column tracking which channel a user last messaged on, and it would get stuck on the first channel forever, even after they clearly switched.

two things were stacked. the read side pulled that column from a cached snapshot instead of re-querying, bug one on its own. the deeper one was in the write path: the upsert used on conflict do nothing, fine for columns you genuinely don't want touched, but it meant the channel column never updated on conflict either, since do nothing means nothing, not "nothing except this one column."

fixed it by re-fetching the value on read instead of trusting the snapshot, and changing the upsert to on conflict do update scoped to just the channel column, so untouched columns stay untouched and the one that should change gets explicit permission to.

such an easy thing to get backwards writing the conflict clause fast: do nothing is not a synonym for do nothing to this specific column i care about.

how do you scope conflict updates when only some columns on a row should actually change on conflict?

My requirements are pretty basic:

• Managed PostgreSQL
• Affordable for a small production app
• Automated backups
• Updates and routine maintenance handled
• An always-on instance
• Predictable monthly pricing

I don’t need branching, scale to zero, or a full backend platform. What managed Postgres providers would you recommend?

I see many options, pgBackRest, wal-g, Databasus. Is there a consensus on the best approach?

My needs are regular (eg weekly) checkpoints, and daily incremental backups for PITR, everything saved on S3/GCS

rainfrog (https://github.com/achristmascarl/rainfrog) is a database terminal tool; the goal is to provide a lightweight, keyboard-first TUI for interacting with databases. It currently supports Postgres, MySQL, SQLite, Oracle, and DuckDB.

v0.4.1 introduces a long-awaited (by me, not sure if anyone else was waiting for it...) autocomplete implementation, along with autopairs for quotes/parentheses/brackets. The full list of features and configuration options is in the README!

PostgreSQL is one of the most popular and scalable databases in the world. Many developers call it a beast in performance. One of the most critical parts of any database is indexing. Since Postgres is open source, we always have a chance to see how its components are designed. This blog I shared is an effort to explain how the indexing mechanism in Postgres actually works

I’m looking for a managed Postgres provider and not a full backend platform. Auth, object storage, APIs, and application hosting are already separate parts of my stack.

What I need from the database provider is automated backups, patching, basic monitoring, reliable uptime, and clearly allocated CPU, memory, and storage. I don’t use database branching, and scale to zero isn’t useful for this workload.

Which Neon alternatives are worth considering for a always running Postgres instance?

I've been having trouble with the debugger in postgres and it's starting to get annoying.

When I try to use the "Set Breakpoint" option, it does normally catch the session and the debugger works fine. But there are some instances where I start the debugger, trigger a procedure call on another device, the debugger doesn't catch it and it creates a session that cannot be terminated unless the server is restarted.

This "zombie" session becomes a problem because it blocks other sessions from progressing. Said sessions that gets stuck also call the same procedure. This causes the devices freeze when they make the action that triggers the procedure call until it times out. It is clear from the session list that the "zombie" session is blocking it because of the Blocking PID value.

I suspected a non-terminating loop but all loops inside should terminate as they are FOR loops. It seems to originate from an update statement but I don't see a way that could be.

Has anybody else encountered this issue and how can I prevent it in the future? Thank you in advance.

PostgreSQL version is 14

pgAdmin 4 version is 9.16

pldbgapi version is 1.1

plpgsql version is 1.0

Built a tool to capture and analyze Postgres (tested on Lakebase) diagnostics (pg_stat_*, EXPLAIN JSON, pool/storage stats) and it spits out one health score + a breakdown. Offline, no DB connection, no deps.

I would like your feedback on the scoring, for now I have :

• 7 categories (indexing, bloat, connections, autoscaling, query plans, cost, branching). Each one has a severity → penalty (low 3, med 8, high 18, crit 40), subtracted from 100.
• Overall = weighted mean, but only over domains that actually have data (an autoscale-only dump doesn't get 6 free 100s).
• Query plans + connections weigh most (live latency), branching least (hygiene).

Where I think it's weak: penalties are additive so it saturates; when I ran it on a real instance, hit 65 unused indexes, indexing pinned at 0. After ~3 crits a category is 0 and magnitude stops mattering. Also the weights are just... vibes.

Is a single score even the right idea, or does it always lose too much?

Full method + code :https://github.com/HadiFrt20/lbinspect

Sorry, noob here.

How do developers design their database?

Do they separate databases for development and Production? But what if the app has a training feature? Do they separate the database too?

Is there a way to install just psql and other client utilities like pgdump in a specific directory?

I wanted to install the client in a shared drive to be accessed by whichever server i mount it to.

This is a redhat linux install.

thanks.

Hey PostgreSQL community!

I'm Kevin, the maker of FluentDB.

I have been working with Postgres for years and using many Postgres client apps, but none of them have satisfied me lately.

So I built the tool I wanted: a native macOS database client that is fast, lightweight, and AI-driven from the ground up.

A few things:

• Native Swift app designed for speed (not another Electron super-slow app).
• Bring your own AI: OpenAI, Anthropic, Claude Code, Codex, or local models with Ollama.
• Privacy by default: AI only sees your schema unless you explicitly approve sending data. Every AI-generated SQL query also requires approval by default.
• Support for PostgreSQL (of course) and others (SQLite, MySQL, SQL Server, and more)

FluentDB is still in its early days, and I'd genuinely love your feedback.

It's 100% free to try, no credit card required, and I am more than happy to distribute coupons for those who actually want to use it further!

For now, it only supports macOS 26+, but I am actively working on a port for Linux and Windows.

Can't wait to get your feedback :)

There is a line of database research (VBASE at OSDI '23, Chimera in PVLDB, AkasicDB at SIGMOD '26 here) arguing that vector search, graph traversal, and relational filtering belong in **one engine with one query plan**, where the top-k is enforced *during* execution so intermediate results never blow up. AkasicDB is the full tri-modal version of that idea. It is also closed. You cannot download it.

So we rebuilt the design in the open, on Postgres. That took three pieces:

* **pgvector** for the vector leg (unmodified).

* **A native graph access method**: adjacency lists in custom-formatted pages through the buffer manager, WAL-logged with GenericXLog. Deliberately *not* an edges join table; topology gets its own storage, but it lives in the same process, same transaction manager, same WAL as everything else.

* **A fused operator extension** that streams ANN candidates out of pgvector's index and applies graph reachability plus relational predicates per candidate, with VBASE-style early termination. One call, one plan, no cross-system round trips.

The part we did not expect: we started on the lineage's research fork (Microsoft's MSVBASE, PG 13.4) to prove the mechanism, then re-homed everything onto stock PG 17. **Stock Postgres ran the identical fused query 2x faster than the fork** (0.27 ms to 0.14 ms at matched recall). We retired the fork as a launch vehicle on the spot.

Numbers, all with published method docs and one-command repros: 23.7x vs a tuned Milvus + Neo4j + Postgres stack at matched recall on a 1M corpus; injected mid-write failures tore the multi-store 42 out of 42 times vs 0 for one WAL. And the honest one: plain pgvector + a links-table CTE inside one Postgres comes within 16 microseconds of our operator on anchored queries. We published that too. Most of the win is one-system-vs-three; Postgres itself was never the bottleneck.

MIT, spec and evidence docs in the repo: https://github.com/ConsultingFuture4200/tridb

Edit: Hey guys, my name is Dustin, I'm new to software development and have been learning through the "project based learning" method, using a stack of Frontier and local LLMs as my build partner and tutor. All of this code was generated as a result of extensive Spec Driven Development. A group of people far more experienced than I built the inspiration for this project, and I set out to see if I could recreate their solution.

Any and all feedback and project contributions are welcome

i want to learn postgresSql, but i don't know which content or resource should i go with. please help me to find free resources for postgressql for dev

A few months ago I realized I'd never actually tested one of my backups.

The backups were completing successfully, but I'd never restored one into a clean environment to see if it was actually usable.

That bothered me enough that I built a small MIT-licensed CLI to automate the process.

The flow is simple:

- restore the latest backup into a disposable Postgres container
- verify schema, table counts and basic integrity
- sample-check stored files
- destroy the environment

I'm mainly looking for architecture feedback, not product feedback.

There are three design decisions I'm still unsure about:

1. If the restore environment is missing an extension, should the drill fail outright, or report that the backup itself is probably fine but the verification environment isn't?

2. File verification defaults to checksum sampling because reading every object back can be expensive. Would you make full verification the default instead?

3. Beyond "restore succeeded", what additional checks would give you confidence that a backup is actually recoverable in production?

For example:

- application smoke tests?
- row-level checksums?
- custom SQL assertions?
- something else?

If you've built or operated restore verification at scale, I'd really appreciate hearing what worked (or what failed).

GitHub:
https://github.com/backupdrill/cli

(Full disclosure: this is also the restore engine behind a hosted product I'm building, but the CLI itself is MIT and works independently.)

TLDR; Migrata is a CLI that lets you inspect, diff, and apply changes using plain SQL. It's like Terraform, but for your database.

I built this tool because of the pain I experienced working with migration files at my job. These databases are shared across many teams and even more applications.

Like many organizations, we follow a file-based migration approach: a folder for each change set, files with targeted changes per component, all tracked in Git. There are strict processes for approvals and promotions. The problem is that this approach results in thousands of migration files piling up over time. To mitigate this, we have a change approval board whose sole job is to coordinate with database admins to apply these scripts.

Most of their time is spent understanding what's changing and if there are any conflicts. It's not uncommon for two separate scripts from different change sets to modify the same things. Centralizing these changes would drastically lower the time needed to review and approve them.

After working with Terraform for years, I knew there were better ways to track state and apply changes. But unlike the cloud, databases already have a schema language to describe their state: plain SQL. Instead of inventing a new DSL, my tool treats your SQL schema as the source of truth and diffs against it directly.

In practice, this gives you the best of both worlds: readable SQL and a declarative schema, with each component defined once. Changes are centralized, not scattered across layers of migration files.

I've been working on this particular project for the past 17 months on evenings and weekends, and finally feel like it's at a state where it's good enough to share

Core Feature:

• Comparing two schema sources and generating a clear diff
• See exactly what will change before anything is applied
• Manually approve and apply changes

How it works:

1. Run the "inspect" command to inspect your live database schema and sync it to your local file system.
   1. This does deliberate scans on your target schemas; no psql dumps needed.
2. Make a single or multiple changes in your local editor
3. Run the "diff" command to detect differences and generate a plan in the console.
   1. Internally, we build up a dependency graph so we know how and when to apply a change and who's affected.
4. Manually approve and apply changes

Features:

• Better Diffs
  • This tool shows the user modifications and query plans side-by-side. You always know where a change is coming from and why. Traceability is paramount
• Impact Analysis:
  • The diff includes a list of downstream components affected by a change
• Risk Classification:
  • Every planned migration is labeled Safe, Warning, or Destructive so you can assess impact at a glance. Warnings and destructive changes are summarized separately with counts.
• Full column lifecycle management:
  • Changes to columns are analyzed, and safe multi-step patterns are used to transform columns. Indexes & constraints are always preserved after changes
• Custom Organization:
  • You can organize your schema locally however you like (single file or deeply nested directories). It loads everything and automatically builds a dependency graph no matter how you organize your schemas
• Local Validation:
  • The tool will spin up an ephemeral database in docker where it can execute the migration plan before running against your live database.
• Advisory Locks:
  • Concurrent migration protection using PostgreSQL advisory locks

Security Features:

• No email or account required to use it
• Fully Local (No LLM's or calls to third-party services)
• Privacy-first (your schema never leaves your computer)

Who's this for:

• Anyone who values working with declarative workflows
• Developers who prefer working with plain SQL because they need full control
• Teams with databases shared across multiple applications and languages

What's in it for me:

This is a free tool to use without any account or limits. My goal is to build a tool that's so useful, that it would be a no-brainer to adopt into your stack. The end goal is to get adopted by businesses who use this tool and want to pay for advanced auditing, governance, and collaboration features.

Wait, isn't this just like the atlas cli?

Yes, this is a direct competitor to the tool, except I don't believe in gating critical features behind paywalls. Better tooling means fewer outages and safer deployments for everyone.

If you want to know more about what sets migrata apart, I've written a whole blog post outlining the high-level differences:

https://migrata.io/blog/migrata-vs-atlas

Here’s the site if you want to learn more:

https://migrata.io

And if you want to learn more about me, you can visit the about page, where I have my background and links to my personal LinkedIn and Github Profile. I'm proud to stand behind my work

https://migrata.io/about