Hacker News Reader

Comment by pqdbr | Hacker News Reader

Let's say I have a Rails app and I want to do a major postgres version upgrade using this tool.

I see one of the requirements is that 'Both databases should have the same schema'. How should I manually load the schema to the new database (running on port 5433 for instance)?

View on HN

Yeah I think the documentation is a bit ambiguous here. Postgres "schemas" have a very specific meaning, different from what most people think of when they hear the word. It's more like a table namespace.

https://www.postgresql.org/docs/current/ddl-schemas.html

I have historically had a “drop-create script” that could handle building me empty tables. Typically split up so that I can create tables, import (test) data from a snapshot or synthetic source, then enable constraints and indexes.

So even if a tool like that needed a little boost at the beginning to get going, I’d have something that already took care of that.

That’s a great question: internally what we have done is when preparing the new empty database, we also run a “bundle exec rake db:schema:load” against it. Depending on your setup, it can be hard to do.

I am exploring some options like loading schema from a schema.sql file or something along the lines of what pg_dump does for schema export.

Would love to hear any ideas.

The easy answer would be on some non-production copy of the Rails app, point at the new db in database.yml, and simply run `rails db:create; rails db:schema:load`, or some variation rails command like `rails db:prepare`.

Rather than this ad-hoc thing, you could also try to create a new rails "environment" for this use, I guess.

The postgres documentation for logical replication suggests using "pg_dump --schema-only" as a starting point.

I am sure the pg_easy_replicate tool brings something to the table(probably replication lifecycle management) but reading the docs, the bare postgres replication looks just as easy if not easier than the easy replicate tool. I am going to try it on my toy db and see how it goes.

https://www.postgresql.org/docs/13/logical-replication-restr...

This looks like a script that basically just runs all the necessary SQL for you. It looks pretty similar to an internal Python tool I came up with to do the same thing (basically just scripting out setting up logical rep and waiting for it to catch up, then executing switchover commands)

Thats right! Its mostly a CLI orchestration tool (mentioned in the readme), with some logging, stats and safety checks so that user/engineer can be mostly hands off during the process. Perhaps, even automate the entire process by wiring up different pg_easy_replicate commands together.

We do that with Tines, where we can be fully hands off (zero touch ops) and are able to kick off upgrade with switchover for tens of DBs (one at a time for now :D).

Reply on news.ycombinator.com