5 ms·
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
by combatentropy 7y ago
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.
- megous 7y ago> 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 ;))
- manigandham 7y agoThis 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-e/wal-e https://github.com/wal-g/wal-g https://github.com/wal-g/wal-g
- mbrameld 7y agoIt 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.
- mjard 7y agoYou never know: https://support.hpe.com/hpesc/public/docDisplay?docId=emr_na-a00092491en_us https://support.hpe.com/hpesc/public/docDisplay?docId=emr_na...
- bzzzt 7y agoA 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?
- megous 7y agoA 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.
- megous 7y agoLooks 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.