News for hackers. Stuff that matters.
Re:PostgreSQL Schema Design by combatentropy on Sunday March 29, 2020 @03:33PM (#22721914)
In PostgreSQL, the hierarchy is: host > cluster > database > schema > object.

By host, I mean a server. A host can have many database clusters. Usually it has just one. To have more than one, you would have to have more than one PostgreSQL instance running, each listening on a separate port. The default port is 5432. I don't recommend more than one cluster per host. I just mention it as possible.

A cluster can have many databases.

A database can have many schemas.

A schema can have many objects. By object, the most familiar is a table, but there are other kinds of objects: views, functions, custom types, sequences. An object cannot exist directly in the database. It must be part of a schema. The default schema is called "public". Traditionally in other databases, there was a schema for each user. So if jdoe logs in, his default schema is also called jdoe. In fact in other databases this is the only schema a user can have. You cannot make more schemas and name them whatever you wish.

The advantage of a schema over a database is that you can make a query that uses objects in different schemas.

  select *
  from schema1.table1
      join schema2.table2 on table1.col = table2.col
If the tables were in different databases, then you could not combine them as easily. I think you would have to resort to Foreign Data Wrappers.

I have gotten a long way, with many applications over many years, with one host, one cluster, one database, and many schemas.

Re:PostgreSQL Schema Design by megous on Sunday March 29, 2020 @03:52PM (#22722113)
> I don't recommend more than one cluster per host. I just mention it as possible.

I actually use this as a cheap data backup mechanism. I have a primary database cluster on SSD, and two replicas on two separate harddrives. All running as three postgresql clusters on the same machine.

The harddrives run intentionally on different filesystems, so that filesystem bug will not eat all my DB data. (in case someone wants to suggest raid ;))

Re:PostgreSQL Schema Design by manigandham on Sunday March 29, 2020 @09:25PM (#22724392)
This seems complicated and would still fail if something happens to the host hardware itself right? What about projects like wal-e/wal-g that continuously archive to cloud storage?

https://github.com/wal-e/wal-e

https://github.com/wal-g/wal-g

Re:PostgreSQL Schema Design by megous on Monday March 30, 2020 @11:09AM (#22728610)
Looks like an interesting option too.

But it's not really complicated. Making a replica is just a few shell commands and two new systemd service files. It's probably simpler than setting up wal-e, especially if you count in the setup and maintenance costs of those cloud accounts, and the need to keep up with regular payments for the services, and recovery not being as simple as switching to an already configured and uptodate replica is.

Re:PostgreSQL Schema Design by mbrameld on Sunday March 29, 2020 @11:30PM (#22724976)
It would only fail if the host hardware failed in a way that damaged all three discs, which seems unlikely. It's for data backup, not uptime redundancy.
Re:PostgreSQL Schema Design by mjard on Sunday March 29, 2020 @11:52PM (#22725074)
Re:PostgreSQL Schema Design by bzzzt on Monday March 30, 2020 @07:04AM (#22726934)
A power surge caused by a lightning strike would kill your master as well as your backup. Besides that, do you also keep all your WAL logs so you can roll back an accidentally dropped database?
Re:PostgreSQL Schema Design by megous on Monday March 30, 2020 @10:58AM (#22728517)
A power surge here where I am may kill the power supply. It's low probability that it would kill everything.

Anyway, I don't fear the lightnings/storms/power surges where I am as much as I do the inevitable failures of the storage devices, or kernel bugs.

This is just a part of layered protection I have. Offline backups are nice, and I have them too, but they are out of sync all the time by definition. So unless absolutely needed, having a real-time synchronized replica is much more prefered.